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.

Prerequisites

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:

UPDATE dbo.AnnualSalesAndQuota

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:

SELECT

c.Name ReportName,

rs.ScheduleID JobName

FROM

ReportServer.dbo.[Catalog] c

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 patrick@sqllunch.com.

Talk to you soon,

Patrick LeBlanc

Data Platform Solution Architect