Incremental data caching approach – Revvity Signals

Date Posted: 2018-12-05
Product: TIBCO Spotfire®

Problem:

Incremental data caching approach

Solution:

In some situations with very large data sets, it may be impractical to completely refresh the cache data each time. It may be desirable to instead only update the most recent data (like for the current month or current day). This caching mode is called incremental data caching.

There is no built-in feature for incremental data caching in TIBCO Spotfire currently, like there is in TIBCO Data Virtualization, but this can still be accomplished with a solution using TIBCO Spotfire Server, TIBCO Spotfire Analyst, and TIBCO Spotfire Automation Services. In this example solution there are two ways of achieving the same. One solution is the current month’s data is updated regularly (hourly, daily, etc) while the historical months records remain cached and are updated monthly. This an be accomplished by embedding the historical months data and refreshing only the current month's data through an Information Link, while having the historical months data refreshed at the start of each month from database
In second approach, current month's data will be updated regularly, while historical data will remain cached in SBDF and at end of every month historical data in SBDF will be updated with current month's data as well avoiding to refresh the historical data from database To implement incremental caching natively in TIBCO Spotfire, the following steps can be used:
Scenario 1:

Create an Information Link for fetching historical months data (all data before the current month) using Tools > Information Designer > Information link and then open the data in the TIBCO Spotfire Analyst client. Add "HistoricalDateFilter" to the information link which is made up of expression %1<datefromparts(DATEPART(year, getdate()),DATEPART(month, getdate()),01) where it will only pull data whose date is prior to current month's first date. This expression is based on MS SQL server.

Add an filter "CurrentDateFilter' which is made up of expression datefromparts(DATEPART(year, %1),DATEPART(month, %1),01)=datefromparts(DATEPART(year, getdate()),DATEPART(month, getdate()),01) where it will only pull data whose date is equivalent to current month's first date. This expression is based on MS SQL server.
Create a dashboard which will have one data table linked to Current Month data IL and other data table linked to Historical Data IL.

Now insert rows from Historical data table to current month data table and create a dummy integer document property ChangeValue with Value as 0. Beneath this document property create a action control python script which will change the Historical Data Table to embedded

#HistoricalTable is the script parameter set to Historical Data table

from Spotfire.Dxp.Data import DataTableSaveSettings

settings = DataTableSaveSettings (HistoricalTable,False, True); 
#The 2nd argument decides the type ,False = embedded and True = Linked to source
Document.Data.SaveSettings.DataTableSettings.Add(settings);

To refresh Historical data IL every first day of month, you can setup Automation job with following steps which will embed Historical Data table by changing the document property value through configuration block which in turn will trigger iron python script and then save as a new dxp
1. Open Analysis
  Configuration block : ChangeValue =1;
2. Save Analysis? with new name
The above created new dxp can be cached using Scheduled updates daily for Web Player Users which will contain complete dataset but information link will only refresh current month data from source database as data table linked to Historical data IL will be embedded

Scenario 2:

Create an Information Link for fetching historical months data (all data before the current month) using Tools > Information Designer > Information link and then open the data in the TIBCO Spotfire Analyst client:

Note: If you would like to refresh previous months data from database, Historical information link created in step 1 needs to be refreshed and exported to SBDF file in step 2
Conclusion:
When new month starts, your current month information link will fetch new month data and Historical data SBDF will now have previous months data cached as well. So the dataset in analysis will have merged data i.e. current as well as Historical data.

Considerations:

If you are using Microsoft SQL as the TIBCO Spotfire Server database, then there will be a 2GB limit on the size of any content saved to the library which is imposed by the database itself. This includes dxp analysis files saved with embedded data and SBDF files saved or exported to the library. See KB 000028303 Size limit of items stored in the TIBCO Spotfire Library for more details.

Doc: Creating Automation Jobs