How to reload on-demand data table for each user logging in TIBCO Spotfire Web Player when the analysis file is cached using Scheduled Updates

How to reload on-demand data table for each user logging in TIBCO Spotfire Web Player when the analysis file is cached using Scheduled Updates

On-demand data tables are data tables which have a dynamic input parameter which can be reloaded as needed, but when using Scheduled Updates for caching the analysis, the TIBCO Spotfire Web Player will cache the data in the on-demand data table based on the set of inputs available at the time of caching. 

For example: Suppose an on-demand data table is based on an Information Link which has %CURRENT_USER% defined as an input parameter value, which in case of Scheduled Updates will be "scheduledupdates", and that user has access to 70,000 records in the database table. In this case, the analysis will be cached with 70,000 records fetched from the database table in the on-demand data table. 

On-demand data table data will reload only when the values of the defined input parameter change (Edit > Data Table Properties > On demand data table > General > Settings > Data on demand > Input parameters defined). On-demand data tables do not have the option for setting "Reload the following data for each user" in Edit > Data Table Properties > Select your data table > Scheduled Updates tab. So if you have referenced %CURRENT_USER% as an input parameter for a stored procedure or Information Link used by your on-demand data tables, then it will not refresh automatically when user a logs in and access the cached report.

To summarize, it will work as below in the above scenario with Scheduled Updates:

1. %CURRENT_USER% is an input parameter in stored procedure element or Information Link
2. The Information Link is imported as on-demand data table and the analysis is saved
3. The report is added to Scheduled Updates. When Schedule Updates will open the analysis, it runs the Information Link/Stored Procedure with default Username = %CURRENT_USER% (value = scheduledudpates) input parameter value. This result will be cached. For example, if the scheduledudpates user does not have data access it will return 0 records and cache the analysis.
4. When a user who has data access tries to open the cached analysis in the Web Player, it will show 0 records initially due to Scheduled Updates cache.

So even though the on-demand data table may be set to "Load Automatically", the data table was not refreshed since the input parameter value as defined in the on-demand data table has not been changed. The username value underwent a change (from scheduledupdates to the current end user) but that change is in Information Link/Stored Procedure and not in the on-demand data table properties so there is no way on-demand data table will understand to reload the data for different user. In this case, to have the on-demand data table refreshed for the current user when the cached report is opened, you can perform the following steps:

1. Create one more Information Link containing only the value of the logged in user (%CURRENT_USER%). For example in a column named "Username"
2. Add a new data table based on this Information Link and check the option to "Reload the following data for each user" in the Data Table Properties "Schedule Updates" tab
3. Remove %CURRENT_USER% default value from your Information link/Stored Procedure, and instead put ?username
4. In the on-demand data table settings, configure the input parameter to use the "Username" column from your data table created in Step 2. Now your on-demand data table will have the dynamic username input parameter in the analysis
5. Save the analysis and refresh the cache using Schedule Updates

Now you will see that once the analysis is cached and when you open the analysis, the on-demand data table is loaded automatically as per the user who logged in.
  Doc: On Demand Data Tables

• https://docs.tibco.com/pub/sfire-analyst/7.11.0/doc/html/en-US/TIB_sfire-analyst_UsersGuide/add/add_on_demand_overview.htm

Doc: Scheduled updates to analyses

• https://docs.tibco.com/pub/spotfire_server/7.11.0/doc/html/TIB_sfire_server_tsas_admin_help/GUID-77D00E34-7A16-4984-B4E0-A7F404A24B70.html