Refreshing KPIs in SSRS 2016


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)

 

Comments (14)

  1. George Walkey says:

    Excellent!
    Will we be able to create KPIs in each folder (organized by function or dept), not just on the main Web Portal?
    Thank would be great
    Thanks

  2. Hi George, yes, you can create KPIs in each folder, not just on the main portal page. In the KPI Manage pane, there’s a “Move” button that allows you to move the KPI to any existing folder. You can also “Favorite” KPIs from various folders to view them in the Favorites tab together.

  3. Ian Hall says:

    Great post… thank you so much for this!
    I was stuck on the “use most recent data” as well, but implementing the caching works great, and the step-by-step screen caps really helped.

  4. Chris Fournier says:

    Thanks for posting this step-by-step, Samuel.

    I found it to be very helpful when setting up automatic dataset refreshes in our production SSRS 2016 environment.

    I do wonder however if there is a method to have KPIs on your web page automatically update their values as the dataset updates as if they were on a dashboard? If not I wonder if there’s any chance MS would consider adding that functionality to a future update?

    Thanks again,
    -Chris

  5. I have an issue with the scheduled Cache Refresh when refreshing a query that has a filter by the domain username. Is there a way around this?

  6. Rakesh says:

    Thanks for the info

  7. The Great One says:

    My KPI does not seem to be refreshing even when I have the dataset to refresh every 2 minutes.
    Now if I go into the dataset and refresh, the KPI’s don’t budge.

  8. Lex Lee says:

    Thanks for posting this but unfortunately it didn’t work. I am not sure CU2 has stopped it from working?

  9. Hiruy T. Shita says:

    Is there a known issue where the KPI will not refresh when a parameter is passed in the stored procedure ?
    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.

    1. Hi Hiruy, I haven’t tried this specific scenario. My suggestion would be to post a question in the SSRS MSDN forum here:
      https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=sqlreportingservices

      Good luck,
      Sam

      1. Hiruy T. Shita says:

        Thanks Sam

  10. Aftab says:

    Thanks for posting this great information.

  11. David Grayston says:

    Maybe just me but I’m not clear on the Cache Expiration timing vs Cache refresh plan. In the example the plan refreshes once daily but you have expiration set at 30min. Confusion for me on how expiration and refresh plan differ or interact. Would love some additional explanation or examples. Thanks!

  12. Ravi Nair says:

    On the Report Manage Portal. How do I change sequence or Order of KPI’s. Whenever i create a new KPI, it automatically gets added to left of existing one. I want to change the order of squence after i have built all KPI’s

Skip to main content