Jump to content
  • Caching data with TIBCO Spotfire® Automation Services


    Table of Contents

     


    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 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, 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:

    overview.png.626e334578c23ab071237a9b0490ca18.png

    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

     

    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
             
               

     

     

     


    User Feedback

    Recommended Comments

    There are no comments to display.


×
×
  • Create New...