KPI visual connected to SSAS MD datasets that are set to refresh are not refreshing.

Gourd_J — Fri, 19 Jul 2019 16:01:33 GMT

This is our first time setting up Power BI Report Server with KPIs. Current version is August 2018.

I've created 4 visual KPIs on the Power BI Report Server that use 3 different shared datasets (uploaded separately to the server), the datasets all use the same Data Source and are MDX queries. The Data Source is a SSAS MD database and we have set it to "Using the following credentials" and set a specific Windows user with necessary permissions to the cubes. Two of the datasets connect to one Cube, the third DS to a different cube. User access has been tested via SSMS\browse an SSAS database\cube.
NOTE: the SSAS MD Cubes are updated once a day usually around 7:00 AM (depending on ETL performance).

Here's a sample of the dataset MDX:

SELECT 
[Measures].[MYMEASURE] ON COLUMNS, 
[Event Date].[Fiscal Period Hierarchy].[Fisc Date].MEMBERS ON ROWS 
FROM 
(SELECT ( { StrToMember("[Event Date].[Fiscal Period Hierarchy].[Fisc Date].&[" + Format(now()-14, "yyyyMMdd") + "]")
: StrToMember("[Event Date].[Fiscal Period Hierarchy].[Fisc Date].&[" + Format(now(), "yyyyMMdd") + "]")} 
) ON COLUMNS 
FROM [MYCUBE] )
WHERE [Clinical Location Portfolio].[Organization Code].&[MYORG]

All datasets are setup with Caching settings of:

- Cache copies of this data set and use them when available

- - Cache Expires on a schedule

- - - Report-specific schedule = "Every 0 hour(s) and 15 minute(s), starting 7/17/2019 at 2:00 AM"

The "Cache refresh plans" section is blank.

In Report Server, running the dataset's "Load Preview" shows current\updated data.

The caching setup has created 3 SQL Agent Jobs on my SQL Server. If I go to SSMS and run this query in the BIReportServer database it shows that these schedules are running and updating as expected:

SELECT [job_id] as [SQL Agent Job ID]
,sj.[name] as SQLJob_Name
,cat.[Path]
,sched.[LastRunTime] as Schedules_LastRunTime
,sj.[enabled] as jobEnabled
,suser_sname([owner_sid]) as JobOwner_username
FROM [dbo].[Schedule] as sched
inner join [dbo].[Catalog] as CAT on cat.ItemID = sched.[EventData]
inner join [msdb].[dbo].[sysjobs] as sj on sj.name = CAST( sched.ScheduleID AS CHAR(36)) COLLATE SQL_Latin1_General_CP1_CI_AS 
left join msdb.dbo.syscategories as sc on sc.category_id =sj.category_id
left join msdb.dbo.sysoperators as op on op.id = sj.notify_email_operator_id
order by [Path]

My KPI properties are;
- Value = Dataset field > I click "..." and pick a column from one of the dataset described above.
- Goal = Not set
- Status = Set manually and is "0 (neutral)"
- Trend set = Dataset field > I click "..." and pick the same column as used in "Value".
- Visualization = I select the "line".
- Related content = I have a URL to a Power BI report that details this dataset.

I believe that all of the dataset setup is correct and works fine. My reasoning is that if I:
1) go into "manage" a KPI to change it's properties
2) click "..." on either "Value" or "Trend set" that I have pointed to a dataset, and re-select the dataset & column,
when I click OK and return to the KPI properties page, the preview of the KPI will now be updated with the new value and trend line!
Click "Apply" and return to the Power BI Report Server home page and the KPI is showing the updated values.

The SSAS Cubes are only updated once a day, and each day the KPIs are still displaying the values they had when I last modified a KPI property.

I read this post from a year ago "KPI s fail to refresh sometimes - BUG"
https://community.powerbi.com/t5/Report-Server/KPI-s-fail-to-refresh-sometimes-BUG/m-p/207249
which a microsoft employee said "We will report this issue internally later. ".
Following that post someone else posted on Jan 4, 2018 that they had the same issue and were wondering if it has been fixed. Maybe I have the same issue?

Maybe this is a similar issue here (this person's using Jan 2019 version) "Schedule Refresh is not working for newly published report after Report Server January 2019 upgrade"

https://community.powerbi.com/t5/Report-Server/Schedule-Refresh-is-not-working-for-newly-published-report-after/m-p/740755#M10992

Maybe I have missed a setting or service that needs to be enabled? I have not found any mention of this problem with KPIs anywhere else.

Hoping this detailed post will drum up a response (and fix?).

Thanks

Re: KPI visual connected to SSAS MD datasets that are set to refresh are not refreshing.

d_gosbell — Sun, 21 Jul 2019 11:52:36 GMT

The "trick" with KPIs is that you need to setup a Cache Refresh Plan for the underlying dataset (the bit you've left blank), just setting the expiry will not work. (this is hidden in the documentation somewhere, but I missed it the first time I setup a KPI too)

Re: KPI visual connected to SSAS MD datasets that are set to refresh are not refreshing.

Gourd_J — Tue, 23 Jul 2019 13:44:06 GMT

Thanks d_gosbell worked perfect. I took it a step further and setup a site level schedule to use per dataset :)

topic Re: KPI visual connected to SSAS MD datasets that are set to refresh are not refreshing. in Report Server

KPI visual connected to SSAS MD datasets that are set to refresh are not refreshing.

Re: KPI visual connected to SSAS MD datasets that are set to refresh are not refreshing.

Re: KPI visual connected to SSAS MD datasets that are set to refresh are not refreshing.