By: Koen Verbeeck | Comments (5) | Related: > Reporting Services KPI
Problem
We have built a couple of key performance indicators (KPIs) using SQL Server Reporting Services (SSRS) 2016. Everything seems to be functioning, but when the data is refreshed in the data warehouse, the changes are not reflected immediately in the KPIs: they still show the old values. How is that possible and how can we solve this?
Solution
For an introduction on how to create KPIs in the new portal of SSRS in SQL Server 2016, check out the tips How to create a basic KPI in Reporting Services 2016 and Advanced KPI Configurations in Reporting Services 2016. In those tips, it is explained that a KPI uses a shared dataset as a source for its data. However, when you browse the KPIs in the SSRS portal, the data is not refreshed automatically. The reason for this is that if you have quite a lot of KPIs in the portal, suddenly refreshing all of the datasets behind the scenes could cause a performance issue. Therefore you have to take a few extra steps to make sure the KPIs stay up to date. This tip will walk you through the process.
Test Set-up
We're going to use the same KPIs constructed in the tips How to create a basic KPI in Reporting Services 2016 and Advanced KPI Configurations in Reporting Services 2016. Please refer to those tips for more details. As a quick recap, here's what they look like in the portal:
The green KPI is just a dummy KPI with some default sample data.
The data source for the KPIs is the Wide World Importers data warehouse. In order to change the data, we can simply add data up to the current date, as explained in the tip Generate more data for the Wide World Importers sample databases. When we run the two source queries after the data generation, the following results are returned:
The value for YTD orders has climbed to almost 50 million euros.
Refresh a KPIs
When we take a look at the KPIs in the portal, we can see that they still display the old values:
Refreshing the browser (even with CTRL-F5) doesn't help. There are no configuration settings of the KPI that influence this behavior, so we have to search someplace else. Let's take a look at the shared dataset settings.
For a dataset, you can configure if caching is applied. The default setting is to run the dataset always with the most recent data. However, as explained earlier, this setting is ignored for KPIs because of the potential performance impact. To resolve this, you have to enable caching on the dataset.
Be aware that in order to enable caching, the data source has to be configured with stored credentials. A warning will be displayed if this is not the case.
After clicking on Apply the caching is configured for the dataset. All that is left to do is creating a new cache refresh plan. This will make sure the KPI is periodically updated with up to date data from the source.
You have to specify a name and a schedule, which can be shared with other datasets.
This will create a SQL Server Agent Job, responsible for updating the cache of the dataset.
If needed, you can for example schedule this Agent job to run right after your ETL load. This will make sure your KPIs always display the latest data. After running the job, we can see that the KPIs have been updated:
However, the trend line in the second KPI is not updated, as its data is retrieved from another dataset. It's important to configure the cache refresh on every dataset associated with a KPI.
Conclusion
In this tip we have shown how you can keep a KPI in Reporting Services 2016 up to date by configuring a cache refresh schedule on the underlying dataset. By using such a schedule, we make sure that the KPI is periodically updated.
Next Steps
- Read more about KPIs in SSRS 2016 in the tips How to create a basic KPI in Reporting Services 2016 and Advanced KPI Configurations in Reporting Services 2016.
- The tip SQL Server 2016 Reporting Services Mobile Report goes into more detail about how to create mobile reports using the Datazen technology.
- For more SQL Server 2016 tips, you can use this overview.
About the author
Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
