consolidation issue for non aggregate measures
Posted: Wed Dec 18, 2013 1:36 pm
Hi,
We have a dimension 'Agent' , which consist of two levels ( All Agents -> children (agents) ) . In the Cube, on any day, agent will have multiple records each for different Operations (All Operaitons -> Operation) , Language dimension(All languages -> Language). There are measures which should be not be aggregated for Operations, Languages and need to be aggregated for other dimensions.
Below is the example scenario:
Below are the records for Agent X in the Cube:
Date Agent Operation Languages Value(Avaible Time)
2013-10-10 X Operation1 English 20
2013-10-10 X Operation2 English 20
2013-10-10 Y Operation2 English 10
2013-10-10 Z Operation1 English 40
So, the agent X will have same value for all Operations for any Language.
Requirement:
For the Context, All Operations, All Languages, the value (Avaible Time) for Agent X on 2013-10-10 should give as 20 and not as 60.
I have created a rule for Availble Time to pick up the maximum value of (Operation1 , English) , (Operation2 , English) for the Consolidated level.
with this we are getting Available time for X as 20, for Y as 10 which is correct but for 'All Agents' we are getting the Available time as 60 (i.e aggregated Operation1 value as 60, Operation2 as 30 a and giving the maximum value of these which is 60) but we need the value as X value+Yvalue+Zvalue= 20+10+40=70.
Any Idea how to restrict the measure aggregating for consolidation member for few dimensions and aggregating for other dimension. Can writing rule will help me to solve the issue or a TI process making use of the Cube View ???
We have a dimension 'Agent' , which consist of two levels ( All Agents -> children (agents) ) . In the Cube, on any day, agent will have multiple records each for different Operations (All Operaitons -> Operation) , Language dimension(All languages -> Language). There are measures which should be not be aggregated for Operations, Languages and need to be aggregated for other dimensions.
Below is the example scenario:
Below are the records for Agent X in the Cube:
Date Agent Operation Languages Value(Avaible Time)
2013-10-10 X Operation1 English 20
2013-10-10 X Operation2 English 20
2013-10-10 Y Operation2 English 10
2013-10-10 Z Operation1 English 40
So, the agent X will have same value for all Operations for any Language.
Requirement:
For the Context, All Operations, All Languages, the value (Avaible Time) for Agent X on 2013-10-10 should give as 20 and not as 60.
I have created a rule for Availble Time to pick up the maximum value of (Operation1 , English) , (Operation2 , English) for the Consolidated level.
with this we are getting Available time for X as 20, for Y as 10 which is correct but for 'All Agents' we are getting the Available time as 60 (i.e aggregated Operation1 value as 60, Operation2 as 30 a and giving the maximum value of these which is 60) but we need the value as X value+Yvalue+Zvalue= 20+10+40=70.
Any Idea how to restrict the measure aggregating for consolidation member for few dimensions and aggregating for other dimension. Can writing rule will help me to solve the issue or a TI process making use of the Cube View ???