cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Gourd_J Frequent Visitor
Frequent Visitor

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

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-r...

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

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)

View solution in original post

2 REPLIES 2
Super User
Super User

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

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)

View solution in original post

Gourd_J Frequent Visitor
Frequent Visitor

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

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

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 147 members 1,396 guests
Please welcome our newest community members: