September 25, 2009 at 10:10 am
Ok I keep running into KPI's causing my cube performance to just suck.
Here's the deal 4000 customers... data across several years...
They submit program modification requestions. Some of these are defects, some are enhancement requests and some are regulatory compliance needs.
So I have a modification requestion dimension with those three categories.
When I bounce it against the customers in a layout with time as the columns and customers x modification request type as the rows and bring up a count of modification requests only when there are modification requests do I get results back.
Then I bring in the KPIs. When I bring them in, they calculate where there is no data. i.e. even if a customer never sent in a program regulatory change request it calculates KPIs for that category for every customer.
How do I keep it to just calculate the KPIs when there's actual data to calculate them on?
My calculations look like this:
Value Expression:
IIF(ISEMPTY([Measures].[Submitted PMRs]),NULL,[Measures].[Submitted PMRs])
Status Expression:
CASE
WHEN ISEMPTY([Measures].[Submitted PMRs]) THEN NULL
WHEN [Measures].[Submitted PMRs] <= 3 THEN 1
WHEN [Measures].[Submitted PMRs] >3 AND [Measures].[Submitted PMRs] <= 9 THEN 0
WHEN [Measures].[Submitted PMRs] >9 THEN -1
END
Trend Expression:
CASE
WHEN ISEMTPY([Measures].[Submitted PMRs]) THEN NULL
when
([Measures].[Submitted PMRs]) > (([Measures].[Submitted PMRs]),[Time].[Calendar].Prevmember) then 1
WHEN
([Measures].[Submitted PMRs]) = (([Measures].[Submitted PMRs]),[Time].[Calendar].Prevmember) then 0
WHEN
([Measures].[Submitted PMRs]) < (([Measures].[Submitted PMRs]),[Time].[Calendar].Prevmember) then -1
END
[/code]
But it's still calculating them for every possible combination. How do I stop that?
September 25, 2009 at 6:06 pm
It's really hard to say for sure, because so much depends on the actual structure of your cube.
I think that you're probably going to need to reference the .currentmember of one of your dimensions. If you can rephrase your question in terms of the AdventureworksDW cube, you might be able to get a better answer.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 28, 2009 at 6:53 am
Actually,
I managed to resolve this late on Friday...
When I basically coded an ISEMPTY result for the goal field that returned Null when the referenced field was empty, all the calculations on empty results went away.
Thanks though.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply