Caching data through Information Links – Revvity Signals

Date Posted: 2019-09-17
Product: TIBCO Spotfire®

Problem:

Caching data through Information Links

Solution:

There are multiple options for caching data within TIBCO Spotfire. There may be situations where the load time needs to be improved for both the TIBCO Spotfire Analyst and TIBCO Spotfire Web Player.

For large data sets, it can take a long time to open a dashboard on the TIBCO Spotfire Analyst or TIBCO Spotfire Web Player as the data will need to be read from the Information Link source database. The TIBCO Spotfire Server (TSS) will query the database for data for every user who opens any of those dashboards from either the TIBCO Spotfire Analyst or TIBCO Spotfire Web Player. There are approaches with complications in this situation:

Caching the analysis in Scheduled Updates will help the load time in the Web Player, but not the TIBCO Spotfire Analyst client.
Saving the analysis as embedded (dxp) or the data (SBDF) in the library may not be an option if the data is larger than 2GB and if the TIBCO Spotfire application database is on a Microsoft SQL Server (see KB 000028303 Size limit of items stored in the TIBCO Spotfire Library for more details).
If there are multiple analysis files which contain data imported from the same Information Link, then each analysis will be executing the same query which may cause unnecessary load on the TSS or the database.

This article outlines how Information Link caching can be used to open the dashboard faster on both the TIBCO Spotfire Analyst and TIBCO Spotfire Web Player while also helping avoid querying the database every time the report is opened as a way to decrease load on the database. Information link caching is done on the TIBCO Spotfire Server (TSS) end to speed up the time it takes to return data to the clients and to reduce load on the database, as the TSS will not have to communicate and retrieve the data from the database server as long as the cache is not stale and the "Validation SQL query" result is the same (if configured).

To enable Information Link caching on your Information Links, open the TIBCO Spotfire Analyst client > Tools > Information designer > select your Information Link > Caching, and check the "Cacheable" checkbox, and change any other settings like "Timeout (seconds)". Now, then that Information Link is executed, the corresponding cache entry will get created inside the Attachment Manager (<Spotfire Server Installation Directory>\7.5.X\tomcat\temp\AttachmentManager) folder. The settings in the TIBCO Spotfire Configuration Tool > Configuration tab > Attachment Manager apply here. Until the cache expires (per the Timeout on the Information Link and the cache expiration time in Attachment Manager whichever is shorter will take precedence over other) or the "Validation SQL query" returns a different result, the cached results will be used if a new request for the Information Link is received.

Below are the ways to achieve Information link caching in case of single TIBCO Spotfire Server:

a) Open the dashboard containing Information Links to be cached using either the TIBCO Spotfire Web Player or TIBCO Spotfire Analyst. This will trigger the Information Link to be cached on TIBCO Spotfire Server.

b) Open the dashboard containing Information Links to be cached using TIBCO Spotfire Automation Services or Scheduled Updates. This will trigger the Information Link to be cached on TIBCO Spotfire Server. This has the added benefit of being able to schedule the Schedule Updates Job/Automation Services Job in order to trigger the cache at a specific time (for example, at 7am daily before business hours).

Information link caching through Scheduled Updates:
https://docs.tibco.com/pub/spotfire_server/7.11.0/doc/html/TIB_sfire_server_tsas_admin_help/GUID-77D00E34-7A16-4984-B4E0-A7F404A24B70.html

Information link caching through TIBCO Spotfire Automation Services:

Create a Automation Services Job with Open Analysis from Library (which will be linked to Information Link)
Schedule this Automation Services job based on required intervals in Windows Task Scheduler to fetch new data from the database and cache the Information Link ((for example, at 7am daily before business hours) See KB 000021541 How to schedule an Automation Services job using the Microsoft Windows Task Scheduler for more details.

Considerations:

Whenever TIBCO Spotfire Server is restarted, the Information link cache will expire and needs to be re-cached again
Information link caching option is not useable in case of a clustered TIBCO Spotfire Servers because Information link caching needs to be done on each TIBCO Spotfire server, the cache is not shared across servers. Opening analysis on TIBCO Spotfire Web Player or TIBCO Spotfire Automation Services connecting to specific TIBCO Spotfire server will not guarantee caching Information link on that particular server as Node Manager services can cache Information link data on any TIBCO Spotfire servers or sometimes all. This may sometimes result in cached data being out of sync. However, opening dashboard manually connecting to each TIBCO Spotfire server through TIBCO Spotfire Analyst will create Information link cache on each server.
Spotfire Attachment Manager default cache expiration time is 1 day so if cache needs to be retained for few more days, this value needs to be increased.
Spotfire Attachment Manager caches library content and the results of information link executions when downloading or saving large amounts of data. If cache expiration time in Attachment Manager is extended to a week, then other contents will also not be cleaned up till a week and temp folder will start occupying more disk space
Spotfire Attachment Manager default cache size is 10GB so if caching large data sets this value needs to be increased provided the disk space is available
Multiple dashboards using same Information link should not be run at the same time otherwise all the dashboards will try to cache the same Information link multiple times. If data volume retrieved by that Information link is huge, disk space can also be exhausted and it will also impact your database performance as same query is running multiple times simultaneously. Let one dashboard cache the Information link completely and then trigger rest of the dashboards so they will use the Information link cache

Doc: Information link caching settings