SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


KPIs and how to hide them


KPIs and how to hide them

Author
Message
mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4854 Visits: 72519
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
mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4854 Visits: 72519
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
mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4854 Visits: 72519
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
mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4854 Visits: 72519
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
RollTide
RollTide
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 56
Solution is to add the following to the beginning of your status expression:

CASE WHEN IsEmpty(KpiValue("MyKPI")) THEN NULL
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search