Following up on my Essbase ranking post and a question from OTN, I’d like to elaborate on the ranking and filtering of the rank results.
Again, we start with a basic report showing the top10 products by actual sales. We’re not using OBIEE functions due to the performance constraints mentioned in the first article.
The core MDX function retrieving the dimension members being:
EVALUATE('TOPCOUNT(%1.members,10,[Actual])',Product."Gen6,Product")
Business now wants to implement filters on Gen2 through Gen5 of the product hierarchy in order to be able to more specifically choose their top10 products. This normally means four “is prompted” filters and a dashboard prompt containing Gen2 through 5 as hierarchically constrained prompt column. Let’s keep it simple and try one of the generations as a fixed filter before creating the prompts and testing in a dashboard context.
As already stated, the grain of the query changes to the one of the filter and renders the whole request useless.
MDX without filter:
With
set [Account3] as 'Filter([Account].Generations(3).members, (([Account].CurrentMember.MEMBER_ALIAS = "Net Sales" OR [Account].CurrentMember.MEMBER_Name = "Net Sales")))'
set [Market3] as 'Filter([Market].Generations(3).members, (([Market].CurrentMember.MEMBER_ALIAS = "Europe" OR [Market].CurrentMember.MEMBER_Name = "Europe")))'
set [Time Period3] as 'Filter([Time Period].Generations(3).members, (([Time Period].CurrentMember.MEMBER_ALIAS = "Q4-09" OR [Time Period].CurrentMember.MEMBER_Name = "Q4-09")))'
set [Evaluate0] as '{TOPCOUNT([Product].Generations(6).members,10,[Actual]) }'
member [Scenario].[MS1] as 'AGGREGATE(crossjoin ({[Account3]},crossjoin ({[Market3]},{[Time Period3]})),Scenario.[Actual LY])', SOLVE_ORDER = 100
member [Scenario].[MS2] as 'AGGREGATE(crossjoin ({[Account3]},crossjoin ({[Market3]},{[Time Period3]})),Scenario.[Actual])', SOLVE_ORDER = 100
select
{ [Scenario].[MS1],
[Scenario].[MS2]
} on columns,
NON EMPTY {{[Evaluate0]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [MyCube_A.MyCube]
MDX with filter:
With
set [Account3] as 'Filter([Account].Generations(3).members, (([Account].CurrentMember.MEMBER_ALIAS = "Net Sales" OR [Account].CurrentMember.MEMBER_Name = "Net Sales")))'
set [Market3] as 'Filter([Market].Generations(3).members, (([Market].CurrentMember.MEMBER_ALIAS = "Europe" OR [Market].CurrentMember.MEMBER_Name = "Europe")))'
set [Product4] as 'Filter([Product].Generations(4).members, (([Product].CurrentMember.MEMBER_ALIAS = "SMU PF - Business Products" OR [Product].CurrentMember.MEMBER_Name = "SMU PF - Business Products")))'
set [Time Period3] as 'Filter([Time Period].Generations(3).members, (([Time Period].CurrentMember.MEMBER_ALIAS = "Q4-09" OR [Time Period].CurrentMember.MEMBER_Name = "Q4-09")))'
set [Evaluate0] as '{TOPCOUNT([Product].Generations(6).members,10,[Actual]) }'
member [Scenario].[MS1] as 'AGGREGATE(crossjoin ({[Account3]},crossjoin ({[Market3]},crossjoin ({[Product4]},{[Time Period3]}))),Scenario.[Actual LY])', SOLVE_ORDER = 100
member [Scenario].[MS2] as 'AGGREGATE(crossjoin ({[Account3]},crossjoin ({[Market3]},crossjoin ({[Product4]},{[Time Period3]}))),Scenario.[Actual])', SOLVE_ORDER = 100
select
{ [Scenario].[MS1],
[Scenario].[MS2]
} on columns,
NON EMPTY {{[Evaluate0]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [MyCube_A.MyCube]
Lets' forget about OBIEE filtering. This means that we have to do the filtering within the MDX and make that filter respond to the prompts. For this we will use presentation variables and a new MDX statement:
EVALUATE('TOPCOUNT(Intersect(%1.dimension.members,Descendants([@{vPromptedProdHier}{SMU}],[Product].[Gen6,Product])),10,[Actual])',Product."Gen6,Product")
And here's the prompt to go with it:
The multiple usage of the same presentation variable ensures that we will always have the lowest level currently chosen populated in the hierarchy, which will nicely adapt our MDX statement to constrain the results to the descendants of the chosen dimension member.
Family:
With
set [Account3] as 'Filter([Account].Generations(3).members, (([Account].CurrentMember.MEMBER_ALIAS = "Net Sales" OR [Account].CurrentMember.MEMBER_Name = "Net Sales")))'
set [Market3] as 'Filter([Market].Generations(3).members, (([Market].CurrentMember.MEMBER_ALIAS = "Europe" OR [Market].CurrentMember.MEMBER_Name = "Europe")))'
set [Time Period3] as 'Filter([Time Period].Generations(3).members, (([Time Period].CurrentMember.MEMBER_ALIAS = "Q4-09" OR [Time Period].CurrentMember.MEMBER_Name = "Q4-09")))'
set [Evaluate0] as '{TOPCOUNT(Intersect([Product].Generations(6).dimension.members,Descendants([SMU PF - Business Products],[Product].[Gen6,Product])),10,[Actual]) }'
member [Scenario].[MS1] as 'AGGREGATE(crossjoin ({[Account3]},crossjoin ({[Market3]},{[Time Period3]})),Scenario.[Actual LY])', SOLVE_ORDER = 100
member [Scenario].[MS2] as 'AGGREGATE(crossjoin ({[Account3]},crossjoin ({[Market3]},{[Time Period3]})),Scenario.[Actual])', SOLVE_ORDER = 100
select
{ [Scenario].[MS1],
[Scenario].[MS2]
} on columns,
NON EMPTY {{[Evaluate0]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [MyCube_A.MyCube]
Group:
With
set [Account3] as 'Filter([Account].Generations(3).members, (([Account].CurrentMember.MEMBER_ALIAS = "Net Sales" OR [Account].CurrentMember.MEMBER_Name = "Net Sales")))'
set [Market3] as 'Filter([Market].Generations(3).members, (([Market].CurrentMember.MEMBER_ALIAS = "Europe" OR [Market].CurrentMember.MEMBER_Name = "Europe")))'
set [Time Period3] as 'Filter([Time Period].Generations(3).members, (([Time Period].CurrentMember.MEMBER_ALIAS = "Q4-09" OR [Time Period].CurrentMember.MEMBER_Name = "Q4-09")))'
set [Evaluate0] as '{TOPCOUNT(Intersect([Product].Generations(6).dimension.members,Descendants([SMU PG - BP Video],[Product].[Gen6,Product])),10,[Actual]) }'
member [Scenario].[MS1] as 'AGGREGATE(crossjoin ({[Account3]},crossjoin ({[Market3]},{[Time Period3]})),Scenario.[Actual LY])', SOLVE_ORDER = 100
member [Scenario].[MS2] as 'AGGREGATE(crossjoin ({[Account3]},crossjoin ({[Market3]},{[Time Period3]})),Scenario.[Actual])', SOLVE_ORDER = 100
select
{ [Scenario].[MS1],
[Scenario].[MS2]
} on columns,
NON EMPTY {{[Evaluate0]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [MyCube_A.MyCube]
End of line.
Again, we start with a basic report showing the top10 products by actual sales. We’re not using OBIEE functions due to the performance constraints mentioned in the first article.
The core MDX function retrieving the dimension members being:
EVALUATE('TOPCOUNT(%1.members,10,[Actual])',Product."Gen6,Product")
Business now wants to implement filters on Gen2 through Gen5 of the product hierarchy in order to be able to more specifically choose their top10 products. This normally means four “is prompted” filters and a dashboard prompt containing Gen2 through 5 as hierarchically constrained prompt column. Let’s keep it simple and try one of the generations as a fixed filter before creating the prompts and testing in a dashboard context.
As already stated, the grain of the query changes to the one of the filter and renders the whole request useless.
MDX without filter:
With
set [Account3] as 'Filter([Account].Generations(3).members, (([Account].CurrentMember.MEMBER_ALIAS = "Net Sales" OR [Account].CurrentMember.MEMBER_Name = "Net Sales")))'
set [Market3] as 'Filter([Market].Generations(3).members, (([Market].CurrentMember.MEMBER_ALIAS = "Europe" OR [Market].CurrentMember.MEMBER_Name = "Europe")))'
set [Time Period3] as 'Filter([Time Period].Generations(3).members, (([Time Period].CurrentMember.MEMBER_ALIAS = "Q4-09" OR [Time Period].CurrentMember.MEMBER_Name = "Q4-09")))'
set [Evaluate0] as '{TOPCOUNT([Product].Generations(6).members,10,[Actual]) }'
member [Scenario].[MS1] as 'AGGREGATE(crossjoin ({[Account3]},crossjoin ({[Market3]},{[Time Period3]})),Scenario.[Actual LY])', SOLVE_ORDER = 100
member [Scenario].[MS2] as 'AGGREGATE(crossjoin ({[Account3]},crossjoin ({[Market3]},{[Time Period3]})),Scenario.[Actual])', SOLVE_ORDER = 100
select
{ [Scenario].[MS1],
[Scenario].[MS2]
} on columns,
NON EMPTY {{[Evaluate0]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [MyCube_A.MyCube]
MDX with filter:
With
set [Account3] as 'Filter([Account].Generations(3).members, (([Account].CurrentMember.MEMBER_ALIAS = "Net Sales" OR [Account].CurrentMember.MEMBER_Name = "Net Sales")))'
set [Market3] as 'Filter([Market].Generations(3).members, (([Market].CurrentMember.MEMBER_ALIAS = "Europe" OR [Market].CurrentMember.MEMBER_Name = "Europe")))'
set [Product4] as 'Filter([Product].Generations(4).members, (([Product].CurrentMember.MEMBER_ALIAS = "SMU PF - Business Products" OR [Product].CurrentMember.MEMBER_Name = "SMU PF - Business Products")))'
set [Time Period3] as 'Filter([Time Period].Generations(3).members, (([Time Period].CurrentMember.MEMBER_ALIAS = "Q4-09" OR [Time Period].CurrentMember.MEMBER_Name = "Q4-09")))'
set [Evaluate0] as '{TOPCOUNT([Product].Generations(6).members,10,[Actual]) }'
member [Scenario].[MS1] as 'AGGREGATE(crossjoin ({[Account3]},crossjoin ({[Market3]},crossjoin ({[Product4]},{[Time Period3]}))),Scenario.[Actual LY])', SOLVE_ORDER = 100
member [Scenario].[MS2] as 'AGGREGATE(crossjoin ({[Account3]},crossjoin ({[Market3]},crossjoin ({[Product4]},{[Time Period3]}))),Scenario.[Actual])', SOLVE_ORDER = 100
select
{ [Scenario].[MS1],
[Scenario].[MS2]
} on columns,
NON EMPTY {{[Evaluate0]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [MyCube_A.MyCube]
Lets' forget about OBIEE filtering. This means that we have to do the filtering within the MDX and make that filter respond to the prompts. For this we will use presentation variables and a new MDX statement:
EVALUATE('TOPCOUNT(Intersect(%1.dimension.members,Descendants([@{vPromptedProdHier}{SMU}],[Product].[Gen6,Product])),10,[Actual])',Product."Gen6,Product")
And here's the prompt to go with it:
The multiple usage of the same presentation variable ensures that we will always have the lowest level currently chosen populated in the hierarchy, which will nicely adapt our MDX statement to constrain the results to the descendants of the chosen dimension member.
Family:
With
set [Account3] as 'Filter([Account].Generations(3).members, (([Account].CurrentMember.MEMBER_ALIAS = "Net Sales" OR [Account].CurrentMember.MEMBER_Name = "Net Sales")))'
set [Market3] as 'Filter([Market].Generations(3).members, (([Market].CurrentMember.MEMBER_ALIAS = "Europe" OR [Market].CurrentMember.MEMBER_Name = "Europe")))'
set [Time Period3] as 'Filter([Time Period].Generations(3).members, (([Time Period].CurrentMember.MEMBER_ALIAS = "Q4-09" OR [Time Period].CurrentMember.MEMBER_Name = "Q4-09")))'
set [Evaluate0] as '{TOPCOUNT(Intersect([Product].Generations(6).dimension.members,Descendants([SMU PF - Business Products],[Product].[Gen6,Product])),10,[Actual]) }'
member [Scenario].[MS1] as 'AGGREGATE(crossjoin ({[Account3]},crossjoin ({[Market3]},{[Time Period3]})),Scenario.[Actual LY])', SOLVE_ORDER = 100
member [Scenario].[MS2] as 'AGGREGATE(crossjoin ({[Account3]},crossjoin ({[Market3]},{[Time Period3]})),Scenario.[Actual])', SOLVE_ORDER = 100
select
{ [Scenario].[MS1],
[Scenario].[MS2]
} on columns,
NON EMPTY {{[Evaluate0]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [MyCube_A.MyCube]
Group:
With
set [Account3] as 'Filter([Account].Generations(3).members, (([Account].CurrentMember.MEMBER_ALIAS = "Net Sales" OR [Account].CurrentMember.MEMBER_Name = "Net Sales")))'
set [Market3] as 'Filter([Market].Generations(3).members, (([Market].CurrentMember.MEMBER_ALIAS = "Europe" OR [Market].CurrentMember.MEMBER_Name = "Europe")))'
set [Time Period3] as 'Filter([Time Period].Generations(3).members, (([Time Period].CurrentMember.MEMBER_ALIAS = "Q4-09" OR [Time Period].CurrentMember.MEMBER_Name = "Q4-09")))'
set [Evaluate0] as '{TOPCOUNT(Intersect([Product].Generations(6).dimension.members,Descendants([SMU PG - BP Video],[Product].[Gen6,Product])),10,[Actual]) }'
member [Scenario].[MS1] as 'AGGREGATE(crossjoin ({[Account3]},crossjoin ({[Market3]},{[Time Period3]})),Scenario.[Actual LY])', SOLVE_ORDER = 100
member [Scenario].[MS2] as 'AGGREGATE(crossjoin ({[Account3]},crossjoin ({[Market3]},{[Time Period3]})),Scenario.[Actual])', SOLVE_ORDER = 100
select
{ [Scenario].[MS1],
[Scenario].[MS2]
} on columns,
NON EMPTY {{[Evaluate0]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [MyCube_A.MyCube]
End of line.
Posts
