未加星标

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

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二04 | 时间 2016 | 作者 红领巾 ] 0人收藏点击收藏

By:Koen Verbeeck || Related Tips: > 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:


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

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:


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

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:


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

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.


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

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.


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

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.


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

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.


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

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


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

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


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

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:


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

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 thisoverview. Last Update: 10/14/2016

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLKPISQL Server
分页:12
转载请注明
本文标题:Refresh the Data for a KPI in SQL Server Reporting Services 2016
本站链接:http://www.codesec.net/view/483563.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(23)