SSAS Cube KPI slows down SSRS report
I've added a KPI to my SSAS cube. I want to add the KPI Goal to my SSRS report. However, as soon as I add the KPI Goal to my report, the report becomes unbearably slow.
The KPI in SSAS is not complicated, but I am employing a case statement in order to limit the scope of the KPI Goal to only the Production Order Number level. This KPI makes no sense in a global scale and only makes sense at the Production Order level.
Is there a way to improve the performance so I'm able to use the Goal in my report? Or is there another way to limit the scope of the KPI without the performance impact?
The KPI Value:
[Measures].[Labor Hours Per Cubic Meter]
The KPI Goal:
CASE
WHEN ([Fact Production Order].[Production Order Number].CURRENTMEMBER.LEVE
THEN ([Fact Production Order].[Production Order Number].[Production Order Number].CURRENTMEMBER,[Mea
ELSE NULL
END
I'm using SQL 2008 R2 Standard.
The KPI in SSAS is not complicated, but I am employing a case statement in order to limit the scope of the KPI Goal to only the Production Order Number level. This KPI makes no sense in a global scale and only makes sense at the Production Order level.
Is there a way to improve the performance so I'm able to use the Goal in my report? Or is there another way to limit the scope of the KPI without the performance impact?
The KPI Value:
[Measures].[Labor Hours Per Cubic Meter]
The KPI Goal:
CASE
WHEN ([Fact Production Order].[Production Order Number].CURRENTMEMBER.LEVE
THEN ([Fact Production Order].[Production Order Number].[Production Order Number].CURRENTMEMBER,[Mea
ELSE NULL
END
I'm using SQL 2008 R2 Standard.
ASKER
@Arif - I will give that a try. Can you explain why a calculated measure might perform better? I'm rather new to SSAS.
ASKER
@Arif - it is still super slow as soon as I hit the Production Order Number level.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Create separate calculated Measure for Goal and use it in KPI goal.
i.e.
You can create Calculated measure like "[KPIGoal]"
CASE
WHEN ([Fact Production Order].[Production Order Number].CURRENTMEMBER.LEVE
THEN ([Fact Production Order].[Production Order Number].[Production Order Number].CURRENTMEMBER,[Mea
ELSE NULL
END
and
use that directly in KPI Goal section when you define your KPI.
So it will be like.
The KPI Goal:
[Measures].[KPIGoal]