Refreshing KPIs in SSRS 2016

  • Article

Refreshing KPIs in SSRS 2016 Refreshing KPIs in SSRS 2016 Refreshing KPIs in SSRS 2016

The introduction of KPIs in SQL Server Reporting Services 2016 gives us an ability to display key point indicators to allow for quick visual consumption of critical metrics. With the upcoming release of SSRS 2016, I spent some time diving into KPIs and found the default behavior for the refreshing of KPI data to be somewhat unintuitive. There are two great blog entries on KPI refresh from Chris Finlan and Patrick LeBlanc, but both included screenshots and instructions for the previous version of the SSRS portal. With the release of SSRS 2016 CTP3 and the new SSRS portal, the options and behavior are slightly different. Let’s take a look.

When you create a KPI, you have the option to link the value, goal, and status to fields in an existing data set. When you create the KPI, the values of these fields will get created based on the current data in the data sets at creation time. However, as the live data changes over time, the KPI value, goal, and status do not automatically get updated. The wording in the default option of “Always run this data set with the most recent data” gave me the impression that the KPI would be updating every time a user opened the SSRS portal showing the KPI. However, this is not the case due to the potential performance impact of having numerous KPIs, each potentially leveraging multiple data sets.

So how can we refresh this KPI data?

First, update the underlying datasets used by the KPI to enable caching –In order to enable datasets to be refreshed, you must enable caching by clicking on the radio button to “Cache copies of this dataset and use them when available”, then click “Apply”.

Refreshing KPIs in SSRS 2016 Enable Caching in SSRS 2016

After applying the change, you’ll see the link available to “Manage Refresh Plans”, where you can add a new cache refresh plans based on your KPI data freshness requirements.

Cache Refresh in SSRS 2016 Edit Caching in SSRS 2016 Schedule KPI Refresh in SSRS 2016

After creating the cache plan, the underlying datasets will be updated based on the schedule defined and the associated KPIs will be refreshed.

Note: A SQL Server Agent job is created for each cache plan. If you need to manually refresh the underlying cache data, one potential option is to execute the underlying Agent job associated with the KPI datasets. I’ve found this convenient for demos with the new SSRS portal.

I hope you enjoy the new SSRS 2016 and the powerful KPIs.

Thanks,
Sam Lester (MSFT)