Caching data with Spotfire® Automation Services

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, and it will be helpful to use the Export Data Table to Library task 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 can be done inside Spotfire, or outside Spotfire, typically using the Windows Scheduler. 

2.If using the Windows scheduler, 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 when using the windows scheduler

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 on how to use the Automation Services Job Builder refer to the documentation.

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 an 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 set up 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 the context of other solutions, here is a short comparison table