Key Performance Indicators (KPIs) are essential for monitoring and analyzing data in SQL Server Reporting Services (SSRS) 2016. However, you may encounter a situation where the KPIs do not reflect the latest data after refreshing the data warehouse. In this article, we will explore the possible reasons for this issue and provide a solution to keep your KPIs up to date.
The Problem
When you create KPIs in SSRS 2016, they use a shared dataset as a source for their data. However, the data in the KPIs is not automatically refreshed when you browse them in the SSRS portal. This is because refreshing all the datasets behind the scenes could cause performance issues, especially if you have a large number of KPIs.
The Solution
To ensure that your KPIs stay up to date, you need to take a few extra steps. Here’s how you can do it:
- Configure Caching: For each dataset associated with a KPI, enable caching on the dataset. By default, the dataset is set to always run with the most recent data, but this setting is ignored for KPIs. Enabling caching helps improve performance. Make sure the data source is configured with stored credentials to enable caching.
- Create a Cache Refresh Plan: After enabling caching on the dataset, create a cache refresh plan. Specify a name and a schedule for the refresh plan. This will create a SQL Server Agent Job responsible for updating the cache of the dataset. You can schedule this job to run after your ETL load to ensure that your KPIs always display the latest data.
By following these steps, you can keep your KPIs in SSRS 2016 up to date and ensure that they reflect the latest data from the source.
Conclusion
In this article, we have discussed how to keep KPIs up to date in SQL Server Reporting Services 2016. By configuring a cache refresh schedule on the underlying dataset and enabling caching, you can ensure that your KPIs are periodically updated with the most recent data. This helps you make informed decisions based on accurate and up-to-date information.