Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

KPIs and how to hide them Expand / Collapse
Author
Message
Posted Thursday, September 17, 2009 8:32 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 21, 2014 8:35 AM
Points: 3,642, Visits: 72,429
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?




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #789682
Posted Thursday, September 17, 2009 9:13 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 21, 2014 8:35 AM
Points: 3,642, Visits: 72,429
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 then 1
WHEN ([Measures].[Average Loadtime]) > 2000 and ([Measures].[Average Loadtime]) <= 5000 then 0
WHEN ([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




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #789725
Posted Thursday, September 17, 2009 12:00 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 21, 2014 8:35 AM
Points: 3,642, Visits: 72,429
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.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #789884
Posted Monday, September 21, 2009 10:19 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 21, 2014 8:35 AM
Points: 3,642, Visits: 72,429
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?



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #791287
Posted Monday, January 14, 2013 2:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 21, 2013 2:54 PM
Points: 6, Visits: 56
Solution is to add the following to the beginning of your status expression:

CASE WHEN IsEmpty(KpiValue("MyKPI")) THEN NULL
Post #1406941
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse