Caching data with TIBCO Spotfire® Automation Services
By:
Last updated:
6:12pm Jul 19, 2018

In many cases it is beneficial to periodically load data from databases and cache it, so that TIBCO Spotfire® analyses requiring the data can be opened quickly and without each analysis hitting the database with queries. In case you are sure that the analysis (DXP) will be used frequently by many users, using Spotfire Scheduled Updates (https://docs.tibco.com/pub/spotfire_server/7.6.0/doc/html/TIB_sfire_server_tsas_admin_help/GUID-8A271E80-C466-40F4-B206-70EF75CBB94F.html ) is a great solution that loads the analysis into a web player service’s RAM memory, meaning it is available for instant access.

However, if you are not sure that the analysis will be used very frequently, or it will be used by a smaller number of users, it might make more sense to just cache the data in the Spotfire Library which means it can be loaded relatively fast and without having to hit the source database with queries if a user wants to open the analysis, and also without consuming any RAM on the web player services machine if no user opens the analysis. This article describes how this can be done.

 

Overview

So, what we want to do is to create a solution where data from our data sources periodically (like overnight, weekly etc dependent on the needs for each data source) is loaded and stored as compressed files in the Spotfire library. Once the data is in the library, multiple analyses(DXPs) may use the data. A convenient way to achieve this is by using Spotfire Automation Services (https://docs.tibco.com/pub/spotfire-automation-services/7.6.0/doc/pdf/TI... ), and it will be helpful to use the new Export Data Table to Library in Spotfire 7.6 that allows saving SBDFs (Spotfire Binary Data File) in the library. Analyses (DXPs) can then use data from these SBDFs to open the analysis quickly and without hitting the database.

Let’s look at an overview of this solution:

1.The actual scheduling is done outside Spotfire, typically using the Windows Scheduler.

2. In order to make this easily manageable one or more windows batch (.BAT) files are used to point to a set of DXP files that will be loaded by Spotfire Automation Services. These DXP files are only used to load the data so that it can be exported in step 4.

3. Spotfire Automation Services loads the data from whatever data sources the DXP is using.

4. Spotfire Automation Services will then (through the configuration of a job) store one or more data tables as SBDF files in the Spotfire library.

5. Once a user opens a DXP file such as a dashboard or analytic application of some kind, the data in the SBDFs is loaded and used.

Note: this technique is especially beneficial when there are multiple Spotfire Analyses requiring the same data, since they can then share the data from the SBDF and still only querying the database once (while loading the DXP in step 3 above).

Details

Now let’s look closer on some of the steps required.

Setting up Windows Scheduler

Windows scheduler needs to be setup to activate the batch files on the desired schedule. IF you need different periods for the updates you need to setup one schedule for each period. Refer to the Microsoft Windows documentation for details.

 

Setting up the batch file(s) and defining the jobs

The batch files will be used to identify a set of DXPs to be loaded by activating the Spotfire Automation Services Client Job Sender.  The batch file could read like this:

for /r %i in (daily\*.xml) do Spotfire.Dxp.Automation.ClientJobSender.exe http[s]://spotfireserver[:port] “%i” async

This loops over all .XML files in the directory called “daily” and executes the Spotfire Automation Services  Client Job Sender for each of the .XML files. The .XML files themselves are Spotfire Automation Services job files created in the Spotfire Automation Services Job Builder. A job consists of one or more Automation Services Tasks. In this case we will need to use the Tasks “Open Analysis from Library” and “Export Data Table to Library”

For more details of how to use the Automation Services Job Builder refer to https://docs.tibco.com/pub/spotfire-automation-services/7.6.0/doc/pdf/TI...

Scaling the solution to 1000’s of files

This article describes an approach that lets you set up a generic solution that is scalable even when you have several data sources and possibly thousands of DXP files that users need. Actually, there is nothing required for each DXP file that uses the cached data - the DXP file will just use the data in the SBDF.

For each data source that you want to cache as an SBDF in the library you need to make sure that you load the data in a purpose built DXP file, create a Job in Spotfire Automation Services Job builder that loads the DXP and then add a Export Data Table task for each data table you want to cache as SBDF in the library. It is your choice if you want to load a single or multiple data sources from one DXP.

Conclusion

The approach described lets you manage caching of data to the Spotfire Library in a way that is scalable to many data sources and to a more or less infinite number of DXP files using these data sources. You need to setup the configuration of Automation Services for each data source that should be cached, but there is nothing to configure for each DXP file that uses the cached data (save from creating the DXP in the first place).

To put this approach in context of other solutions, here is a short comparison table

 

Solution

Open by user

Server RAM usage if the file is not used

Server RAM usage if the file is used

Queries towards source DB

Configuration required per DXP

Scheduled updates

Instant

FULL

FULL

Once

Some

Scheduled caching of data as described in this article

Fast

NONE

FULL

Once

None

Normal, users just open the DB from the library and data is loaded as needed

Slow

NONE

FULL

For each user

None

Save Analysis to Library (chapter 4.7 in https://docs.tibco.com/pub/spotfire-automation-services/7.6.0/doc/pdf/TI...)

Fast

NONE

FULL

Once for each file

Some