Essbase MDX

Understanding MDX with
BSO and ASO
mailto : aloo_a2@yahoo.com for all Hyperion video tutorial/Training/Certification/Material

MDX Introduction

MDX Introduction
Multidimensional Expressions (MDX) lets you query
multidimensional objects, such as cubes, and return
multidimensional cell sets that contain the cube's data.
In MDX, the SELECT statement specifies a result set that contains a subset
of multidimensional data that has been returned from a cube. To specify a
result set, an MDX query must contain the following information:
The number of axes or sets of hierarchies. You can specify up to 64(0-63)

axes in an MDX query.
The members from each dimension to include on each axis of the MDX
query.
The name of the cube that sets the context of the MDX query.
The members from a slicer axis on which data is sliced for members from
the query axes. For more information about slicer and query axes.

MDX Introduction

Examples
Simple Select
SELECT
{Jan}
ON COLUMNS
FROM Sample.Basic
Simple Select
SELECT
{([100-10], [Actual])}
ON COLUMNS
FROM Sample.Basic

ntroduction to Sets and Tuples
A tuple is a way to refer to a member or a member combination from any

number of dimensions.
For example, in the Sample Basic database, Jan is a tuple, and so is (Jan,
Sales), and so is ([Jan],[Sales],[Cola],[Utah],[Actual]).
et is an ordered collection of one or more tuples that have the same dimensional
In MDX Queries we specify AXIS to specify Data Access

Layout
ON COLUMNS can be used in place of AXIS(0)
ON ROWS may replace AXIS(1)
ON PAGES may replace AXIS(2)
ON CHAPTERS may replace AXIS(3)
ON SECTIONS may replace AXIS(4)

Examples
Select with Column and Row Axis
SELECT
{[100-10],[100-20]}
ON COLUMNS,
{[Qtr1],[Qtr2],[Qtr3],[Qtr4]}
ON ROWS
FROM Sample.Basic
Specify Tuple And Sets

SELECT
{([100-10],[East]), ([100-20],[East])}
ON COLUMNS,
{
([Qtr1],[Profit]), ([Qtr2],[Profit]),
([Qtr3],[Profit]), ([Qtr4],[Profit])
}
ON ROWS
FROM Sample.Basic

MDX Introduction

SELECT
{([100-10], [Actual])}
ON COLUMNS
FROM Sample.Basic

Specify Member Range
SELECT
{([100-10],[East]), ([100-20],[East])}
ON COLUMNS,
{
CrossJoin
({[Profit]},{[Qtr1],[Qtr2],[Qtr3],[Qtr4]})
}
ON ROWS
FROM Sample.Basic

Specify Member Range
SELECT SELECT
MemberRange([Qtr1],[Qtr4]) [Qtr1]:[Qtr4]ON COLUMNS
ON COLUMNS FROM Sample.Basic
FROM Sample.Basic
Attribute Dimension
SELECT {[Year].Children} ON COLUMNS,

Attribute ([Ounces_12]) ON ROWS FROM
Sample.Basic

Performing Set Operations
SELECT {[Measures].[Sales], [Measures].[Profit]} ON

COLUMNS, Except( [Market].Levels(0).Members, UDA
(Market, "Major Market") )
ON ROWS
WHERE {([Year].[Qtr1], [Scenario].[Actual])
SELECT
CrossJoin ({[100-10]}, {[East],[West],[South],[Central]})
ON COLUMNS,
CrossJoin (
{[Sales],[COGS],[Margin %],[Profit %]}, {[Qtr1]}
)
ON ROWS
FROM Sample.Basic

Level/Generation Generation
SELECT [Year].
SELECT [Qtr1].Generation.Members
Members(Market.levels(0))ON ON COLUMNS,
COLUMNS [Product].Generations(2).Mem
FROM Sample.Basic bers ON ROWS FROM
Sample.Basic
Filtering Using Slice
SELECT
CrossJoin ({[100-10]}, {[East],[West],[South],[Central]})
ON COLUMNS,
CrossJoin (
{[Sales],[COGS],[Margin %],[Profit %]}, {[Qtr1]}
)
ON ROWS
FROM Sample.Basic
WHERE (Budget)

Filtering Data
SELECT { Profit } ON COLUMNS,
Filter( [Product].levels(0).members, Profit < 0) ON ROWS
FROM Sample.Basic
Aggregation Queries
WITH MEMBER [Measures].[Max Qtr2 WITH

Sales] SET [Best5Prods] AS
AS ' 'Topcount (
Max ( [Product].members,
{[Year].[Qtr2]}, 5,
[Measures].[Sales] ([Measures].[Sales], [Scenario].
)' [Actual],
SELECT [Year].[Dec])
{[Measures].[Max Qtr2 Sales]} )'
ON COLUMNS, SELECT [Best5Prods] ON AXIS(0),
{[Product].children} {[Year].[Dec]} ON AXIS(1)
ON ROWS FROM Sample.Basic
FROM Sample.Basic

Member Functions
SELECT
Filter([Market].Members, SELECT {Parent
IsChild([Market].CurrentMember, ([100-10])} ON
[East]) COLUMNS FROM
) sample.basic
ON COLUMNS
FROM Sample.Basic
Conditional Access "IFF"
WITH MEMBER [Scenario].[Revised Budget] AS

'IIF ( [Product].CurrentMember.Caffeinated, Budget * 1.1, Budget )'
SELECT {[Scenario].[Budget], [Scenario].[Revised Budget]} ON
COLUMNS, [Product].Levels(0).Members ON ROWS FROM Sample.Basic
WHERE ([Measures].[Sales], [Year].[Qtr3])

Questions

Essbase MDX

Uploaded by

Copyright:

Available Formats

Essbase MDX

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Essbase MDX

Uploaded by

Copyright:

Available Formats

Understanding MDX with

BSO and ASO

mailto : aloo_a2@yahoo.com for all Hyperion video tutorial/Training/Certification/Material

mailto : aloo_a2@yahoo.com for all Hyperion video tutorial/Training/Certification/Material

The number of axes or sets of hierarchies. You can specify up to 64(0-63)

mailto : aloo_a2@yahoo.com for all Hyperion video tutorial/Training/Certification/Material

mailto : aloo_a2@yahoo.com for all Hyperion video tutorial/Training/Certification/Material

mailto : aloo_a2@yahoo.com for all Hyperion video tutorial/Training/Certification/Material

A tuple is a way to refer to a member or a member combination from any

In MDX Queries we specify AXIS to specify Data Access

mailto : aloo_a2@yahoo.com for all Hyperion video tutorial/Training/Certification/Material

Specify Tuple And Sets

mailto : aloo_a2@yahoo.com for all Hyperion video tutorial/Training/Certification/Material

mailto : aloo_a2@yahoo.com for all Hyperion video tutorial/Training/Certification/Material

mailto : aloo_a2@yahoo.com for all Hyperion video tutorial/Training/Certification/Material

mailto : aloo_a2@yahoo.com for all Hyperion video tutorial/Training/Certification/Material

SELECT {[Year].Children} ON COLUMNS,

mailto : aloo_a2@yahoo.com for all Hyperion video tutorial/Training/Certification/Material

SELECT {[Measures].[Sales], [Measures].[Profit]} ON

mailto : aloo_a2@yahoo.com for all Hyperion video tutorial/Training/Certification/Material

Filtering Using Slice

mailto : aloo_a2@yahoo.com for all Hyperion video tutorial/Training/Certification/Material

WITH MEMBER [Measures].[Max Qtr2 WITH

mailto : aloo_a2@yahoo.com for all Hyperion video tutorial/Training/Certification/Material

WITH MEMBER [Scenario].[Revised Budget] AS

mailto : aloo_a2@yahoo.com for all Hyperion video tutorial/Training/Certification/Material

mailto : aloo_a2@yahoo.com for all Hyperion video tutorial/Training/Certification/Material

You might also like