Continuing my blog from last week, Creating KPIs in SQL Reporting Services, I am now going to explain how to configure scheduled data refresh for the KPI that was created in that blog post.
1. SQL Server Agent must be running
2 Credentials must be stored in the Data Source
3. Caching must be enabled on the Dataset
With all of these things configured, open the classic Report Manager and navigate to the dataset that acts as the source for the KPI. You could do this from the report also. Hover over the dataset and click the drop down arrow. Select Manage from the list of available options and the General Properties page will open.
If you haven’t already done so, enable Caching. Then select Cache Refresh Options from the left navigation section. Click New Cache Refresh Plan in the top header.
On the Cache Refresh Plan page supply a Description and configure a schedule. You can leverage an existing schedule or configure a new one. To configure a new schedule click the button labeled Configure.
Schedule the refresh to meet the requirements of your organization. Then click the button labeled OK. Next, if you have been following along, execute the following script:
SET AnnualSales = 381123.54
WHERE SalesYear = 2016
You can wait for the refresh job to run or you can run it manually. Remember at the beginning when I said the SQL Agent must be running? Well the reason is because a job is actually created and is executed at the intervals you specified in the schedule. Don’t’ believe me, open SQL Server Management Studio (SSMS) expand the SQL Server Agent and expand jobs. What do you see?
In addition, to any jobs that you created, you may also see several jobs with GUIDs as names. Hmmm. To refresh the KPI now, you can just execute the job that was created for that particular Cache Plan, but which job is it? Don’t worry I got you covered. Execute this query:
INNER JOIN ReportServer.dbo.Subscriptions s
ON c.ItemID = s.Report_OID
INNER JOIN ReportServer.dbo.ReportSchedule rs
ON c.ItemID = rs.ReportID
AND rs.SubscriptionID = s.SubscriptionID
In the list you will see your KPI name and a Job name. That Job name will correspond to one of the jobs in the list of agent jobs.
Once you have identified that job, start it. After it succeeds go back to the KPI in the new web portal and refresh the page, it should resemble the following:
Now instead of Red it should be Green and the value should reflect the updated value from the table. Pretty cool. As always, if you have any questions please feel free to email me at firstname.lastname@example.org.
Talk to you soon,
Data Platform Solution Architect