Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Refresh the Data for a KPI in SQL Server Reporting Services 2016


By:   |   Read Comments (5)   |   Related Tips: > Reporting Services KPI

Attend these FREE MSSQLTips webcasts >> click to register


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:

KPI with old values

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:

new data

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:

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.

caching

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.

caching enabled

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.

stored credentials

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.

create cache plan

You have to specify a name and a schedule, which can be shared with other datasets.

create cache plan bis

This will create a SQL Server Agent Job, responsible for updating the cache of the dataset.

agent job

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:

up to date KPI

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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, January 19, 2017 - 2:47:54 AM - Koen Verbeeck Back To Top

 Hi Hiruy,

I think it's indeed a known issue.

Take a look here:

https://support.microsoft.com/en-us/help/3211220/fix-kpis-bound-to-a-dataset-together-with-parameters-are-not-updated-during-cache-refresh

This is fixed in SQL Server 2016 SP1 CU1.


Wednesday, January 18, 2017 - 1:39:29 PM - Hiruy Shita Back To Top

Is there a known issue where the KPI will not refresh when  the stored procedure of the dataset uses a parameter to filter the resultset ? The same Kpi refreshes fine when I am not using a parameter in the sproc . I assigned a default value in the data set and was able to see that value when creating the cache refresh plans and the kpi. However, whenever there is an update in the database the kpi wont refresh unlike the kpi which is dataset that doesn’t have a parameter.

 


Tuesday, January 17, 2017 - 11:02:21 AM - Hiruy Shita Back To Top

 Hi, 

It finally worked, the details are as follows

The cache of the shared data was set to expire after 10 minutes and the cache refresh schedule is set to refresh every 20 mins.

the only thing i did diffrently before it started working is that i changed the syquence of development.

1- i dropped the KPI

2- recreated the cache refresh plan 

3 - created a new KPI of off this data set 

--- some how it works 

 

Thanks 

 

 


Monday, January 16, 2017 - 1:30:41 PM - Koen Verbeeck Back To Top

Hi Hiruy Shita,

is the cache of the shared data set expired?


Monday, January 16, 2017 - 12:56:25 PM - Hiruy Shita Back To Top

 I followed every step you mentioned to check if the kPI's are designed correctly, and It is so. unfortunately, the KPI is still not refreshing. I checked the SQL Server Agent checked the job history and it shows that it had been refreshing per schedule. I came back and checked the data preview on the shared data set properties, every time there is a change in the database the result set in the data preview also changes. It seems like it is only the KPI that is not updating.  is there anyother issue or update i need to be aware of ?

 

 


Learn more about SQL Server tools