Date Posted:
Product: TIBCO Spotfire®
Product: TIBCO Spotfire®
Problem:
Caching data in TIBCO Spotfire Analyst
Solution:
When dashboards contain data imported from either Information link or Data Connection or Database connection with your database, it can take a while to open the dashboard in the TIBCO Spotfire Analyst as it will read data from the database. It will hit the database for reading data for every user who opens dashboard on TIBCO Spotfire Analyst. This article describes what can be done to open the dashboard faster on TIBCO Spotfire Analyst and prevent the database from being queried every time any user opens the report.
Below are 3 methods:
Option 1: Embedding or Storing data in analysis using TIBCO Spotfire Analyst
Data can be self contained in the analysis itself to speed up the loading analysis. General steps:
- Data tables can be embedded through (Edit > Data Table Properties > Embedded in Analysis)
- Data tables can also store data through "Save As > File/Library Item > Review Data Loading Settings" or in expanded Data Panel > Select any data table > Source View
While selecting the approach certain points to be considered:
- Embedding data tables approach will sometimes disable linked to source selection in data table properties after analysis gets saved and reopened as that data table would have data from multiple data sources through insert rows or columns. If there is need to refresh that data table manually, data table will need to be replaced performing all the transformation/merging data steps again
- Stored data approach will not change the selection (Embedded in analysis or Linked to source) in Data Table properties
- Stored data approach is helpful when data table has merged data from multiple data sources either using insert rows or columns in Data table which needs to be embedded as stored data does not impact the selection in data table properties so none of the selection will be get disabled and can be refreshed whenever needed
Option 2: Embedding the analysis using TIBCO Spotfire Automation Services based on predefined schedules
If the analysis needs to be embedded periodically, this approach can be used as this has the added benefit of being able to schedule the Automation Services job at a specific time (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. General Steps:
- Create a Automation Services Job with following steps:
- Open Analysis from Library (which will be linked to Information Link/Data Connection)
- Save Analysis to Library (Select Embed data in Analysis option and save as the embedded analysis. Do not overwrite the existing one)
- Schedule this automation job based on specific intervals to fetch new data from Database (for example, at 7am daily before business hours)
Considerations:
- If you are using Microsoft SQL as the TIBCO Spotfire Server database, then there will be a 2GB limit on the size of the analysis (dxp) as imposed by the database. See KB 000028303 Size limit of items stored in the TIBCO Spotfire Library for more details.
Option 3: Cache the data from your database as an SBDF file in the TIBCO Spotfire Library at particular intervals
This approach has a few key benefits:
- Embedding data in the analysis (like in Option 1 or 2) would not be helpful if the cached data needs to be used in multiple analyses
- If changes need to be made in analysis containing multiple data connections/information links which takes say 2-3 hours loading on TIBCO Spotfire Analyst, analysis will again hit the database and take time in loading. This needs to be done every time any changes need to be made in analysis
- Create a analysis having data table linked to source containing information link/data connection with say SQL Server DB (one which you wanted to embed in final analysis)
- Create a Automation Services Job with following steps:
- Open Analysis from Library (which you created in step 1)
- Export Data Table to Library (Fill up the details)
- Schedule this automation job whenever needed and it will generate/overwrite SBDF file in the mentioned TIBCO Spotfire library location
- Now create the final analysis which will not take time loading by pointing the data tables to the SBDF files and have it linked to source to always fetch latest data from SBDF
Considerations:
- If you are using Microsoft SQL as the TIBCO Spotfire Server database, then there will be a 2GB limit on the size of the analysis (dxp) as imposed by the database. See KB 000028303 Size limit of items stored in the TIBCO Spotfire Library for more details.