KPIs and how to hide them

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Business Intelligence » KPIs and how to hide them

KPIs and how to hide them

Rate Topic

Display Mode

Topic Options

Author

Message

mtassin

Posted Thursday, September 17, 2009 8:32 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 6:30 AM
Points: 3,226, Visits: 64,066

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

mtassin

Posted Thursday, September 17, 2009 9:13 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 6:30 AM
Points: 3,226, Visits: 64,066

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

mtassin

Posted Thursday, September 17, 2009 12:00 PM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 6:30 AM
Points: 3,226, Visits: 64,066

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

mtassin

Posted Monday, September 21, 2009 10:19 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 6:30 AM
Points: 3,226, Visits: 64,066

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

RollTide

Posted Monday, January 14, 2013 2:08 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 22, 2013 3:18 PM
Points: 6, Visits: 50

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 »

Permissions