With the release of SQL Server 2016 CTP 3.3 and the latest version of the Mobile Report Publisher Preview both now available, I thought it would be a good time to review how data updates work for Mobile Reports and KPI’s in Reporting Services.
As I reviewed in a previous post, you need to create a shared dataset to use in your mobile report or KPI that you save on the server. This is similar to what you would do in Datazen, where you’d create a query against a data source on the server and then use it for either a dashboard or a KPI. However, the way data is updated for KPI’s using a shared dataset works a little differently than it does with Mobile Reports.
Let’s review the Mobile Reports scenario first – In Reporting Services, by default, the shared dataset will always be up-to-date with the latest data.
They’re live!
So if I create a query against a SQL Server table for my dataset, every time I run a mobile report, that query will execute and bring back the latest data. This might not be ideal for performance reasons if hundreds/thousands of users are potentially using that report frequently. So Reporting Services has an option to cache the dataset on a periodic basis – that way, the data is saved periodically on the Report Server and isn’t always going back against the live data source.
You need to setup caching in the old Report Manager in CTP 3.3
It’ll make sure everyone who runs the report is getting consistent results and response times are potentially faster. If you do this for your mobile reports, you won’t see any updates to your data until the cache expires and the query then executes against the underlying dataset again.
With KPI’s, this doesn’t work the same way – for your KPI’s to be updated with the latest data from your shared dataset, you must have caching turned on AND also have a cache refresh schedule enabled to preload the cache with fresh data.
Why does it work differently than Mobile Reports? Well, imagine if you have forty KPI’s on the home page of your portal. When you first load the site, if it worked the way it did for mobile reports, it would execute at least forty queries all at once to load the data for each KPI. And since you can setup multiple datasets for different elements of a KPI, you’d potentially be running over 100 separate queries just by going to the home page. Multiply that by several users doing it at the same time, well, you get the idea. So make sure you setup a cache refresh plan
Hopefully this helps you understand what your options are around refreshing your data for your mobile reports and KPI’s in SQL Server 2016. Thanks for reading!
Christopher Finlan 