ASO: Understanding MDX

Gary Crisci, Oracle Ace

www.odtug.com

ODTUG Kaleidoscope 2008

What is MDX?

www.odtug.com

ODTUG Kaleidoscope 2008

 MDX stands for Multi-Dimensional Expression

MDX is a language that allows you to query OLAP cubes in a way reminiscent of that in which SQL allows you to query relational databases.
Fast Track to MDX, 2004, Whitehorn, Zare, Pasumansky

Unlike some other OLAP languages, it is not a full report-formatting language. The results of an MDX query come back to a client program as data structures that must be processed in some way to look like a spreadsheet, a chart, or some other form of output. This is quite similar to how SQL works with relational databases and how its related application programming interfaces (APIs) behave.
MDX Solutions With Microsoft SQL Server Analysis Server 2005 and Hyperion Essbase Second Edition, 2006, Spofford, Harinath, Webb, Huang, Civardi

www.odtug.com

ODTUG Kaleidoscope 2008

How do we use MDX with Essbase?

There are two primary ways, at this time, to utilize MDX with Essbase.
Query data from an Essbase database
Aggregate Storage or Block Storage!

Construct member formulas

Aggregate Storage only

www.odtug.com

ODTUG Kaleidoscope 2008

SQL
SELECT Product, Sales, Margin FROM dbo.Transactions WHERE Year = Jan

MDX
SELECT {[Measures].[Sales], [Measures].[Margin]} ON COLUMNS, {[Product].levels(0).members} ON ROWS FROM [SAMPASO].[BASIC] Must Define axis WHERE [Year].[Jan]
specification to render multi-dimensional in a report.
www.odtug.com 5 ODTUG Kaleidoscope 2008

Nesting rows using AXIS

SELECT {[Measures].[Sales], [Measures].[Margin]} ON AXIS(0), {[Product].levels(0).members} ON AXIS(1), {[Scenario].children} ON AXIS(2) FROM [SAMPASO].[BASIC] WHERE [Year].[Jan]

Nesting columns is a little different

SELECT {CrossJoin({[Measures].[Sales], [Measures].[Margin]}, {[Scenario].children})} ON axis(0), {[Product].levels(0).members} ON axis(1) FROM [SAMPASO].[BASIC] WHERE [Year].[Jan]

www.odtug.com

ODTUG Kaleidoscope 2008

 Naming Conventions
The most obvious way to identify a member is to start with the name of the dimension and work downwards, specifying the members at each level in the hierarchy until we reach the required members - Fast Track to MDX, 2004, Whitehorn, Zare, Pasumansky [Jan] This works [Year].[Jan] This is better [Year].[Qtr1].[Jan] This is best practice * No difference in performance * Particularly important with Duplicate Member names * No quotes - Jan vs. [Jan]

www.odtug.com

ODTUG Kaleidoscope 2008

 Tuples
A tuple is defined as an intersection of exactly a single member from each dimension in the cube. For each dimension that is not explicitly referenced, the current member is implicitly added to the tuple definition. A tuple always identifies (or has the potential to identify) a single cell in the multi-dimensional matrix. That could be an aggregate or a leaf level cell, but nevertheless one cell and only one cell is ever implied by a tuple. - Fast Track to MDX, 2004, Whitehorn, Zare, Pasumansky ([Year].[Jan], [Measures].[Sales], [Product].[Cola], [Market].[East]) ([Year].[Jan], [Measures].[Sales]) ([Year].[Jan]) * Wrap tuples in parenthesis ( ) * Similar to a cross-dimensional operator (Jan->Sales) vs. ([Year].[Jan], [Measures].[Sales])

www.odtug.com

ODTUG Kaleidoscope 2008

 Sets
A set is a collection of tuples with the same dimensionality. It may have more than one tuple, but it can also have only one tuple, or even have zero tuples, in which case it is an empty set. - Fast Track to MDX, 2004, Whitehorn, Zare, Pasumansky {([Scenario].[Actual],[Measures].[Sales]), ([Scenario].[Actual],[Measures].[Margin])} {[Year].levels(0).members} {[Measures].[Sales]} Wrap sets in { }

www.odtug.com

ODTUG Kaleidoscope 2008

 MDX Expressions
WITH MEMBER [Measures].[Margin%] AS '[Measures].[Margin] / [Measures].[Sales]' SELECT {[Measures].[Sales], [Measures].[Margin], [Measures].[Margin%]} ON COLUMNS, {[Year].[Jan]} ON ROWS FROM [SAMPASO].[BASIC]

This is IT

www.odtug.com

10

ODTUG Kaleidoscope 2008

This is Essbase

www.odtug.com

11

ODTUG Kaleidoscope 2008

 We use MDX Expressions to define member formulas in the Essbase outline Same concept as member formulas in BSO Many functions can be translated from Essbase calc language to MDX
In EAS go to Help -> Information Map -> Technical Reference Go to MDX -> Aggregate Storage Topics -> MDX Outline Formulas
For version 7X go to MAXL -> MDX

www.odtug.com

12

ODTUG Kaleidoscope 2008

@AVG Calculator: @AVG(SKIPMISSING, @CHILDREN(East)); MDX: Avg([East].Children)

For SKIPNONE - Avg([East].Children,IncludeEmpty)

@CHILDREN Calculator: @CHILDREN(Market); MDX: Children([Market]) OR [Market].Children @ICHILDREN Calculator: @ICHILDREN(Market); MDX: Union({[Market]},{[Market].children}) @CURRMBR Calculator: @CURRMBR(Product); MDX: CurrentMember([Product]) or [Product].CurrentMember @LEVMBRS Calculator: @LEVMBRS(Product,0); MDX: [Product].levels(0).Members
www.odtug.com 13 ODTUG Kaleidoscope 2008

@LSIBLINGS Calculator: @LSIBLINGS(Qtr4); MDX: MemberRange([Qtr4].FirstSibling, [Qtr4].Lag(1)) @RSIBLINGS Calculator: @RSIBLINGS(Qtr1); MDX: MemberRange([Qtr1].Lead(1), [Qtr1].LastSibling) @PARENTVAL Calculator: @PARENTVAL(Market, Sales); MDX: ([Sales], [Market].CurrentMember.Parent).Value @REMOVE Calculator: @REMOVE(@CHILDREN(East),@LIST(New York,Connecticut)); MDX: Except({[East].Children}, {[New York], [Connecticut]}) @COUNT Calculator: @COUNT(SKIPMISSING, @RANGE(Sales, Children(Product)); MDX:NonEmptyCount(CrossJoin({[Sales]},{[Product].Children})) For SKIPNONE Count([Product].Children)
www.odtug.com 14 ODTUG Kaleidoscope 2008

@IDESCENDANTS Calculator: @IDESCENDANTS(Market); MDX: Descendants([Market]) @DESCENDANTS Calculator: @DESCENDANTS(Market,0); MDX: Descendants([Market], [Market].levels(0)) OR
Leaves([Market]) New in Sys 9

@ISICHILD Calculator: @ISICHILD(South); MDX: IIF(Is([Market].CurrentMember,[South]) OR IsChild([Market].CurrentMember,[South]), <True>,<False>)

www.odtug.com

15

ODTUG Kaleidoscope 2008

 Time Functionality
Leverage Analytic Dimensions

www.odtug.com

16

ODTUG Kaleidoscope 2008

 Period To Date Functions

[QTD] = SUM( PeriodsToDate( [Year].Generations(2), [Year].CurrentMember ), [View].[Per] ) [YTD] = SUM( PeriodsToDate( [Year].Generations(1), [Year].CurrentMember ), [View].[Per] )

www.odtug.com

17

ODTUG Kaleidoscope 2008

 Time Balancing
CASE WHEN IsUDA([Measures].CurrentMember, "TB_Last") THEN IIF(IsLeaf([Year].CurrentMember), [View].[Per], (ClosingPeriod ([Year].Levels(0), [Year].CurrentMember), [View].[Per]) ELSE [View].[Per] END Before

After

www.odtug.com

18

ODTUG Kaleidoscope 2008

 Time Balance Skip Missing Problem

www.odtug.com

19

ODTUG Kaleidoscope 2008

www.odtug.com

20

ODTUG Kaleidoscope 2008

Solution
CASE WHEN IsUDA([Measures].CurrentMember,"TB_Last") THEN IIF(IsLeaf([Year].CurrentMember) AND Not IsEmpty([Year].CurrentMember),[View].[Per], IIF(IsLeaf([Year].CurrentMember), IIF (NonEmptyCount (MemberRange(Head([Year].levels(0).members ).item(0).item(0), [Year].CurrentMember,LEVEL),[View].[Per])> 0, ([View].[Per],Tail(Filter(MemberRange( Head([Year].levels(0).members).item(0).item(0), [Year].CurrentMember, LEVEL), Not IsEmpty([View].[Per]))).Item(0).Item(0)), MISSING), IIF (NonEmptyCount(DESCENDANTS([Year].CurrentMember,10,LEAVES),[View].[Per]) > 0, ([View].[Per],Tail(Filter(DESCENDANTS([Year].CurrentMember,10,LEAVES), Not IsEmpty([View].[Per]))).Item(0).Item(0)), IIF (NonEmptyCount([Year].levels(0).members,[View].[Per]) > 0, ([View].[Per], Tail(Filter([Year].levels(0).members, Not IsEmpty([View].[Per]))).Item(0).Item(0)),MISSING)))) ELSE [View].[Per] END

www.odtug.com

21

ODTUG Kaleidoscope 2008

George Spofford Revised Solution

CASE WHEN IsUDA([Measures].CurrentMember, "TB_Last") THEN IIF(IsLeaf([Year].CurrentMember) AND Not IsEmpty([Year].CurrentMember),[View].[Per], IIF(IsLeaf([Year].CurrentMember), ([View].[Per],Tail(Filter (PeriodsToDate([Year].Generations(1), [Year].CurrentMember,), Not IsEmpty([View].[Per]))).Item(0).Item(0)), IIF (Not IsEmpty([View].[Per]),([View].[Per], Tail(Filter(DESCENDANTS([Year].CurrentMember,Year.Levels(0)), Not IsEmpty([View].[Per]))).Item(0).Item(0)), ([View].[Per],Tail(Filter([Year].levels(0).members, Not IsEmpty([View].[Per]))).Item(0).Item(0))))) ELSE [View].[Per] END

www.odtug.com

22

ODTUG Kaleidoscope 2008

Revised Period to Date Solution

CASE WHEN IsUDA([Measures].CurrentMember, "TB_Last") THEN IIF(IsLeaf([Year].CurrentMember) AND Not IsEmpty([Year].CurrentMember),[View].[Per], IIF(IsLeaf([Year].CurrentMember), ([View].[Per],Tail(Filter (PeriodsToDate([Year].Generations(1), [Year].CurrentMember), Not IsEmpty([View].[Per]))).Item(0).Item(0)), IIF (Not IsEmpty([View].[Per]),([View].[Per], Tail(Filter(DESCENDANTS([Year].CurrentMember,Year.Levels(0)), Not IsEmpty([View].[Per]))).Item(0).Item(0)), ([View].[Per],Tail(Filter([Year].levels(0).members, Not IsEmpty([View].[Per]))).Item(0).Item(0))))) ELSE SUM(PeriodsToDate([Year].Generations(1),[Year].CurrentMember),[View].[Per]) END

www.odtug.com

23

ODTUG Kaleidoscope 2008

www.odtug.com

24

ODTUG Kaleidoscope 2008

 Formula Precedence (Solve Order)

The mechanism that standard MDX uses for dealing with dimensional formula precedence is called solve order. Every calculated member has an associated solve order number, which is an integer that says what the calculation priority of the member is. When calculated members overlap on a cell, the member with the highest solve order number wins and is used to calculate the cell.
- MDX Solutions, 2006, Spofford, Harinath, Webb, Huang, Civardi

- Default value is 0, maximum value is 127

- Multiple members can have the same solve order

- Dimension solve order sets the default solve order for all members in the dimension, you can still edit individual members order if required - In addition to calculating the correct value, Solve Order can yield huge performance gains for calculated members

- Test, Test, Test!!!

www.odtug.com 25 ODTUG Kaleidoscope 2008

 Recap
MDX is a powerful query language, similar to SQL, that is used with multi-dimensional databases. MDX can be used to query both ASO and BSO Essbase cubes, although it is primarily used for member expressions in ASO models. Most Essbase calculator functions can be converted to MDX expressions refer to the technical document. You can leverage MDX expression in ASO cubes to simulate Dynamic Time Series (Period To Date) and Time Balancing functionality. Solve Order is extremely important, both for calculating accurate results and performance.

www.odtug.com

26

ODTUG Kaleidoscope 2008

Questions

www.odtug.com

27

ODTUG Kaleidoscope 2008