September 17, 2009 at 8:32 am
So I'm coming along developing my SSAS cube. And I have a small problem.
I have a shared dimension that has every customer the company has in it.
My measures are for the performance of a single application that some of our customer use.
When I just pull the measures into the cube on the client I get just the customers who have reported values for the measures and customers not using said product don't show up.
But...
If I add my KPI to the display, then suddenly every customer of the company shows up.
How can I hide KPIs for customers who don't have any data at all?
September 17, 2009 at 9:13 am
I guess rather I should aski how to not perform KPI calculations when there is no data to perform them on. I can do that to a degree with a calculated measure.
i.e. I can give it non empty behavoir to follow. But there isn't an option for that on the KPI page.
My formulas look like this
Value Expression:
IIF(NOT ISEMPTY([Measures].[Average Loadtime]),[Measures].[Average Loadtime],Null)
Goal Expression:
IIF(NOT ISEMPTY([Measures].[Average Loadtime]),2000,NULL)
Status Expression:
CASE
WHEN (ISEMPTY([Measures].[Count of Instances]))
THEN NULL
WHEN ([Measures].[Average Loadtime]) 2000 and ([Measures].[Average Loadtime]) 5000 then -1
END
Trend Expression:
CASE
WHEN isempty(([Measures].[Average Loadtime])) then NULL when
([Measures].[Average Loadtime]) > (([Measures].[Average Loadtime]),[Time].[Calendar].Prevmember) then 1
WHEN
([Measures].[Average Loadtime]) = (([Measures].[Average Loadtime]),[Time].[Calendar].Prevmember) then 0
WHEN
([Measures].[Average Loadtime]) < (([Measures].[Average Loadtime]),[Time].[Calendar].Prevmember) then -1
END
But it's still leaving all the customers in the list who don't have values to measure the KPI against
September 17, 2009 at 12:00 pm
Ok the client is Excel 2007.
The trouble is that when I bring a KPI status or KPI trend onto the cube view it gives them for dimension members that have no data (and likely will never have data). Which means instead of the couple hundred or so customers who use this product we have 10,000 plus customers triggering a KPI calculation on non-existant data. I just want to suppress the call to get those calculations.
September 21, 2009 at 10:19 am
If I can't force SSAS to not calculate KPIs when there is no data, should I instead cut my customer's dimension down to just those that I have records for?
January 14, 2013 at 2:08 pm
Solution is to add the following to the beginning of your status expression:
CASE WHEN IsEmpty(KpiValue("MyKPI")) THEN NULL
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply