Uncategorized

Dynamic multi-level filtering with MDX

Greetings, programs!

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.

Related Posts

2 Comments to “Dynamic multi-level filtering with MDX”

  1. Brett
    · Edit

    Hello Christian

    I have been a fun of your blog for a long time. I have learned a lot in OBIEE by reading your blogs. Really appreciate it.. Right now, the combination of OBIEE and Essbase seems to be pretty popular, and it is currently the environment I am having at my project.. However, there are only a few resources online that talk about how to deal with essbase related troubleshootings on OBIEE.

    Typically, Essbase doesn't seem to be a relational database like oracle, data are stored as cube. One cube contains dimesion and fact attributes with hierarchical levels defined in essbase. For some reasons when OBIEE sends the query to essbase and get converted into MDX, something got lost in translations. When I apply any conversation functions or "SQL expression" values on date column filters or contents (such as cast(column as date), or filter on current_date on SQL Expressions) and create reports with measures, I get errors: [nQSError: 42043] An external aggregate is found in an outer query block. (HY000)

    A specifc case is that I am dealing with now is that I am trying to put a filter on the date column and filter between timestampadd(sql_tsi_day,-31,current_date) and current_date. when I put this date column (with the above filter condition) with measures and other columns then run report, I get a different error:
    State: HY000. Code: 10058. NQODBC SQL_STATE: HY000 nQSError: 10058 A general error has occurred. nQSError: 42043 An external aggregate is found in an outer query block. (HY000)
    SQL Issued: SELECT Product.”Brand Name” saw_0, “Up Level”.”Trial – level 12″ saw_1, “Year”.”Transaction Year” saw_2, “Year”.”Transactoin Month” saw_3, “Year”.”Transaction Date” saw_4, tranSale.count_of_transactions saw_5, Avg(tranSale.count_of_transactions by “Up Level”.”Trial – level 12″, “Year”.”Transactoin Month”, Product.”Brand Name”) saw_6 FROM tranSale_cube WHERE (”Up Level”.”Trial – level 12″ IN (’0′, ‘Product Upsell’)) AND (”Year”.”Transactoin Month” >= ‘2009 Feb’) AND (”Year”.”Transaction Date” BETWEEN timestampadd(sql_tsi_day,-31,current_date) AND current_date) ORDER BY saw_0, saw_1, saw_2, saw_3, saw_4

    So is there any trainings or related blogs you have that deals with this type of issues? I can't find anything online so I thought of coming to your blog. Any pointer will be deeply appreciated

    Thanks

    Keep up the good work

Leave a Comment