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

  • Anonymous
    April 24, 2016
    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 greatThanks
  • Anonymous
    April 24, 2016
    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.
  • Anonymous
    May 31, 2016
    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.
  • Anonymous
    August 11, 2016
    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
  • Anonymous
    August 26, 2016
    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?
  • Anonymous
    August 29, 2016
    Thanks for the info
  • Anonymous
    September 12, 2016
    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.
  • Anonymous
    October 17, 2016
    Thanks for posting this but unfortunately it didn't work. I am not sure CU2 has stopped it from working?
  • Anonymous
    January 18, 2017
    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.
    • Anonymous
      January 18, 2017
      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=sqlreportingservicesGood luck,Sam
      • Anonymous
        January 18, 2017
        Thanks Sam
  • Anonymous
    March 07, 2017
    Thanks for posting this great information.
  • Anonymous
    March 16, 2017
    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!
  • Anonymous
    June 27, 2017
    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
    • Anonymous
      March 05, 2019
      KPIs are ordered by name.Add numbers at the beginning of each KPI name, for example.