Writing MDX Queries

In This Section:

Introduction

Understanding Elements of a Query

Using Functions to Build Sets

Working with Levels and Generations

Using a Slicer Axis to Set Query Point-of-View

Common Relationship Functions

Performing Set Operations

Creating and Using Named Sets and Calculated Members

Using Iterative Functions

Working with Missing Data

Using Substitution Variables in MDX Queries

Querying for Properties

Introduction

MDX, the data manipulation language for Essbase, is a joint specification of the XML for Analysis founding members. See http://www.xmla.org.

To complete the exercises in this chapter, which are based on the Sample.Basic database, use the MaxL Shell. Before continuing, start Essbase and log in to the MaxL Shell. Additionally, be prepared to use a text editor to create the sample queries as presented in this chapter.

Note:

You can use the MDX Script Editor in Administration Services Console instead of the MaxL Shell. However, the instructions in this chapter use the MaxL Shell.

Understanding Elements of a Query

In this section you will create a template to use as a basis for developing simple queries.

Most queries can be built upon the following grammatical framework:

SELECT 
 {}
ON COLUMNS
FROM Sample.Basic

SELECT in line 1 is the keyword that begins the main body of all MDX statements.

The braces { } in line 2 are a placeholder for a set. In the above query, the set is empty, but the braces remain as a placeholder.

Exercise 1: Creating a Query Template

*  To create a query template:

  1. Create a folder to store sample queries that can be run against the Sample.Basic database. For example, create a folder called “queries” under the ARBORPATH/app/Sample/Basic directory of the Essbase installation.

  2. Using a text editor, type the following code into a blank file:

    SELECT {} ON COLUMNS FROM Sample.Basic

  3. Save the file as qry_blank.txt to your queries folder.

    Note:

    If you are using the MDX Script Editor in Administration Services instead of a text editor, save the query as qry_blank.MDX from the editor instead.

Introduction to Sets and Tuples

A set is an ordered collection of one or more tuples that have the same dimensionality (see Rules for Specifying Sets for an explanation of dimensionality).

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, as is (Jan, Sales), as is ([Jan],[Sales],[Cola],[Utah],[Actual]).

The member name can be specified in these ways:

  1. By specifying the actual name or the alias; for example, Cola, Actual, COGS, and [100]

    If the member name starts with number or contains spaces, it should be within brackets; for example, [100]. Brackets are recommended for all member names, for clarity and code readability.

    For attribute members, the long name (qualified to uniquely identify the member) should be used; for example, [Ounces_12] instead of [12].

  2. By specifying dimension name or any one of the ancestor member names as a prefix to the member name; for example, [Product].[100-10] and [Diet].[100-10]. This is a recommended practice for all member names; it eliminates ambiguity and enables you to refer accurately to shared members.

    Note:

    Use no more than one ancestor in the member name qualification. Essbase returns an error if multiple ancestors are included. For example, [Market].[New York] is a valid name for New York, and so is [East].[New York]. However, [Market].[East].[New York] returns an error.

  3. By specifying the name of a calculated member defined in the WITH section.

Recall that the braces { } in line 2 of your query template are a placeholder for a set. In this exercise, we will add a set to the query and run it.

Exercise 2: Running Your First Query

*  To run the query:

  1. Open qry_blank.txt.

  2. Because a set can be as simple as one tuple, add Jan as a set to the query template. Retain the braces (required for all set specifications except for sets that are produced by a function call).

    Type Jan inside the braces in line 2:

    SELECT 
     {Jan}
    ON COLUMNS
    FROM Sample.Basic
  3. Save the query as ex2.txt.

  4. Ensure that Essbase is started (the essbase.exe process is running).

  5. In order for Essbase to receive MDX statements, pass the statements to Essbase using either the MaxL Shell or MDX Script Editor in Administration Services. The examples in this chapter use the MaxL Shell.

    Start the MaxL Shell and log in with a valid user name and password. For example,

    essmsh -l admin passwd
  6. Copy and paste the entire SELECT query into the MaxL Shell, but do not press Enter yet.

  7. Enter a semicolon at the end, anywhere after Basic but before pressing Enter. The semicolon is not part of MDX syntax requirements, but it is required by MaxL Shell to indicate the end of a statement that is ready to execute.

    Note:

    If you are using the MDX Script Editor in Administration Services, do not terminate with a semicolon.

  8. Press Enter. You should see results similar to the following.

    Jan
     8024

Rules for Specifying Sets

As described previously, a set is an ordered collection of one or more tuples.

For example, in the following query, {[100-10]} is a set consisting of one tuple.

SELECT 
{[100-10]} 
ON COLUMNS 
FROM Sample.Basic

In the following query, {([100-10], [Actual])} is a also a set consisting of one tuple, though in this case, the tuple is not one member name. Rather, ([100-10], [Actual]) represents a tuple consisting of members from two dimensions, Product and Scenario.

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

When a set has more than one tuple, the following rule applies: In each tuple of the set, members must represent the same dimensions as do the members of other tuples of the set. Additionally, the dimensions must be represented in the same order. In other words, each tuple of the set must have the same dimensionality.

For example, the following set consists of two tuples of the same dimensionality.

{(West, Feb), (East, Mar)}

The following set breaks the dimensionality rule, because Feb and Sales are from different dimensions.

{(West, Feb), (East, Sales)}

The following set breaks the dimensionality rule, because although the two tuples contain the same dimensions, the order of dimensions is reversed in the second tuple.

{(West, Feb), (Mar, East)}

A set can also be a collection of sets, or it can be empty (containing no tuples).

A set must be enclosed in braces {} except in cases where the set is represented by an MDX function which returns a set.

Introduction to Axis Specifications

An axis is a specification determining the layout of query results from a database. Axes fit into MDX queries as follows:

SELECT <axis> [, <axis>...]
FROM <database> 

At least one axis must be specified in any MDX query.

Up to 64 axes may be specified, beginning with AXIS(0) and continuing with AXIS(1)...AXIS(63). Using more than three axes is uncommon. The order of axes is not important; however, when a set of axes 0 through n are specified, no axis between 0 and n should be skipped. Additionally, a dimension cannot appear on more than one axis.

The first five axes have keyword aliases:

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)

For example, in the query

SELECT {Jan} ON COLUMNS FROM Sample.Basic

the axis specification is {Jan} ON COLUMNS.

Exercise 3: Running A Two-Axis Query

*  To run a two-axis query:

  1. Open qry_blank.txt.

  2. Add a placeholder for a second axis, by adding ON ROWS:

    SELECT
     {}  
    ON COLUMNS,
     {} 
    ON ROWS
    FROM Sample.Basic

    Note:

    Remember to add the comma after ON COLUMNS.

  3. Save the new query template as qry_blank_2ax.txt.

  4. As the set specification for the column axis, enter the Product members 100-10 and 100-20. These member names contain special characters, so you must use brackets. For example, add the text shown in bold:

    SELECT 
     {[100-10],[100-20]} 
    ON COLUMNS, 
     {} 
    ON ROWS 
    FROM Sample.Basic 

    Note:

    The convention used here, to enclose all member names in brackets even if they do not contain special characters, is recommended.

  5. As the set specification for the row axis, enter the Year members Qtr1 through Qtr4.

    SELECT
     {[100-10],[100-20]}
    ON COLUMNS,
     {[Qtr1],[Qtr2],[Qtr3],[Qtr4]}
    ON ROWS
    FROM Sample.Basic 
  6. Save the query as ex3.txt.

  7. Paste the query into the MaxL Shell and run it, as described in Exercise 2: Running Your First Query.

    Results:

     

    100-10

    100-20

    Qtr1

    5096

    1359

    Qtr2

    5892

    1534

    Qtr3

    6583

    1528

    Qtr4

    5206

    1287

Exercise 4: Querying Multiple Dimensions on a Single Axis

*  To query multiple dimensions on a single axis:

  1. Open qry_blank_2ax.txt.

  2. On the column axis, specify two tuples, each of which is a member combination rather than a single member. Enclose each tuple in parentheses, because more than one member is represented in each tuple.

    SELECT
     {([100-10],[East]), ([100-20],[East])}
    ON COLUMNS,
     {}
    ON ROWS
    FROM Sample.Basic
  3. On the row axis, specify four two-member tuples, nesting each Quarter with Profit:

    SELECT
     {([100-10],[East]), ([100-20],[East])}
    ON COLUMNS,
     {
      ([Qtr1],[Profit]), ([Qtr2],[Profit]), 
      ([Qtr3],[Profit]), ([Qtr4],[Profit]) 
     }
    ON ROWS
    FROM Sample.Basic
  4. Save the query as ex4.txt.

  5. Paste the query into the MaxL Shell and run it, as described in Exercise 2: Running Your First Query.

    Results:

      

    100-10

    100-20

      

    East

    East

    Qtr1

    Profit

    2461

    212

    Qtr2

    Profit

    2490

    303

    Qtr3

    Profit

    3298

    312

    Qtr4

    Profit

    2430

    287

Cube Specification

A cube specification is the part of the query that determines which database is being queried. The cube specification fits into an MDX query as follows:

SELECT <axis> [, <axis>...]
FROM <database> 

The <database> section follows the FROM keyword and should consist of delimited or nondelimited identifiers that specify first an application name and a then database name; for example, the following specifications are valid:

  • FROM Sample.Basic

  • FROM [Sample.Basic]

  • FROM [Sample].[Basic]

  • FROM'Sample'.'Basic'

Using Functions to Build Sets

Rather than creating sets member-by-member or tuple-by-tuple, you can use a function that returns a set. MDX includes several functions that return sets and several functions that return other values. For a complete reference of MDX functions supported by Essbase, see the MaxL section of the online Oracle Essbase Technical Reference.

Exercise 5: Using the MemberRange Function

The MemberRange function returns a range of members inclusive of and between two specified members of the same generation. Its syntax is as follows:

MemberRange (member1, member2, [,layertype])

where the first argument you provide is the member that begins the range, and the second argument is the member that ends the range. The layertype argument is optional. See the Oracle Essbase Technical Reference.

Note:

An alternate syntax for MemberRange is to use a colon between the two members, instead of using the function name: member1 : member2.

*  To use the MemberRange function:

  1. Open qry_blank.txt.

  2. Delete the braces {}, which are unnecessary when you are using a function to return the set.

  3. Use the colon operator to select a member range of Qtr1 through Qtr4:

    SELECT 
     [Qtr1]:[Qtr4]
    ON COLUMNS 
    FROM Sample.Basic
  4. Paste the query into the MaxL Shell and run it, as described in Exercise 2: Running Your First Query.

    Qtr1, Qtr2, Qtr3, and Qtr4 are returned.

  5. Use the MemberRange function to select the same member range, Qtr1 through Qtr4.

    SELECT 
     MemberRange([Qtr1],[Qtr4]) 
    ON COLUMNS 
    FROM Sample.Basic
  6. Run the query. The same results should be returned.

  7. Save the query as ex5.txt.

Exercise 6: Using the CrossJoin Function

The CrossJoin function returns the cross product of two sets from different dimensions. Its syntax is as follows:

CrossJoin(set,set)

The CrossJoin function takes two sets from different dimensions as input and creates a set that is a cross product of the two input sets, useful for creating symmetric reports.

*  To use the CrossJoin function:

  1. Open qry_blank_2ax.txt.

  2. Replace the braces {} from the columns axis with CrossJoin().

    SELECT
     CrossJoin () 
    ON COLUMNS, 
     {}
    ON ROWS
    FROM Sample.Basic
  3. Add two comma-separated pairs of braces as placeholders for the two set arguments you will provide to the CrossJoin function:

    SELECT
     CrossJoin ({}, {})
    ON COLUMNS,
     {}
    ON ROWS
    FROM Sample.Basic
  4. In the first set, specify the Product member [100-10]. In the second set, specify the Market members [East], [West], [South], and [Central].

    SELECT
     CrossJoin ({[100-10]}, {[East],[West],[South],[Central]})
    ON COLUMNS, 
     {}
    ON ROWS
    FROM Sample.Basic
  5. On the row axis, use CrossJoin to cross a set of Measures members with a set containing Qtr1:

    SELECT
     CrossJoin ({[100-10]}, {[East],[West],[South],[Central]})
    ON COLUMNS, 
      CrossJoin ( 
        {[Sales],[COGS],[Margin %],[Profit %]}, {[Qtr1]} 
      ) 
    ON ROWS
    FROM Sample.Basic
  6. Save the query as ex6.txt.

  7. Paste the query into the MaxL Shell and run it, as described in Exercise 2: Running Your First Query.

    Results:

      

    100-10

    100-10

    100-10

    100-10

      

    East

    West

    South

    Central

    Sales

    Qtr1

    5731

    3493

    2296

    3425

    COGS

    Qtr1

    1783

    1428

    1010

    1460

    Margin %

    Qtr1

    66.803

    59.118

    56.01

    57.372

    Profit %

    Qtr1

    45.82

    29.974

    32.448

    24.613

When using CrossJoin, the order of arguments affects the order of tuples in the output.

Exercise 7: Using the Children Function

The Children function returns a set of all child members of the given member. Its syntax:

Children (member)

Note:

An alternate syntax for Children is to use it like an operator on the input member, as follows: member.Children. We will use the operator syntax in this exercise.

*  To use the Children function to introduce a shortcut in the first axis specification:

  1. Open ex6.txt.

  2. In the second set of the column axis specification, replace [East],[West],[South],[Central] with [Market].Children.

    SELECT 
     CrossJoin ({[100-10]}, {[Market].Children})
    ON COLUMNS, 
      CrossJoin ( 
        {[Sales],[COGS],[Margin %],[Profit %]}, {[Qtr1]}
      )
    ON ROWS
    FROM Sample.Basic
  3. Save the query as ex7.txt.

  4. Paste the query into the MaxL Shell and run it, as described in Exercise 2: Running Your First Query.

  5. You should see the same results as were returned for Exercise 6: Using the CrossJoin Function.

Working with Levels and Generations

In MDX, layer refers to generations and levels in an Essbase hierarchy.

In Essbase, generation numbers begin counting with 1 at the dimension name; higher generation numbers are those closest to leaf members in a hierarchy.

Level numbers begin with 0 at the leaf-most part of the hierarchy, and the highest level number is a dimension name.

A number of MDX functions take a layer you specify as an input argument and perform set operations based on the generation or level represented in the layer argument.

You can specify a layer argument in the following ways:

  • Generation or level name; for example, States or Regions.

  • The dimension name along with the generation or level name; for example, Market.Regions and [Market].[States].

  • The Levels function with a dimension and a level number as input. For example, [Year].Levels(0).

  • The Level function with a member as input. For example, [Qtr1].Level returns the level of quarters in Sample.Basic, which is level 1 of the Market dimension.

  • The Generations function with a dimension and a generation number as input. For example, [Year].Generations (3).

  • The Generation function with a member as input. For example, [Qtr1].Generation returns the generation of quarters in Sample.Basic, which is generation 2 of the Market dimension.

Note:

In the Sample.Basic database, Qtr1 and Qtr4 are in the same layer. This means that Qtr1 and Qtr4 are also in the same generation. However, in a different database with a ragged hierarchy, Qtr1 and Qtr4 might not necessarily be in the same level, although they are in the same generation. For example, if the hierarchy of Qtr1 drills down to weeks, and the hierarchy of Qtr4 stops at months, Qtr1 is one level higher than Qtr4, but they are still in the same layer.

Exercise 8: Using the Members Function

The Members function can be used to return all members of a specified generation or level. Its syntax when used with a layer argument:

Members (layer)

where the layer argument you provide indicates the generation or level of members you want returned.

Note:

An alternate syntax for Members is layer.Members.

*  To use the Members function:

  1. Open qry_blank.txt.

  2. Delete the braces {}.

  3. Use the Members function and the Levels function to select all level 0 members in the Market dimension of Sample.Basic:

    SELECT 
      Members(Market.levels(0))
    ON COLUMNS 
    FROM Sample.Basic
  4. Paste the query into the MaxL Shell and run it, as described in Exercise 2: Running Your First Query.

    All states in the Market dimension are returned.

  5. Save the query as ex8.txt.

Using a Slicer Axis to Set Query Point-of-View

A slicer axis is a way to limit a query to apply to only a specific area of the database. The optional slicer, if used, must be in the WHERE section of an MDX query. Furthermore, the WHERE section must be the last component of the query, following the cube specification (the FROM section):

SELECT {set}
ON axes
FROM database
WHERE slicer 

Use the slicer axis to set the context of the query; it is usually the default context for all the other axes.

For example, for a query to select only Actual Sales in the Sample.Basic database, excluding budgeted sales, the WHERE clause might look like the following:

WHERE ([Actual], [Sales])

Because (Actual, Sales) is specified in the slicer axis, you need not include them in the ON AXIS(n) set specifications.

Exercise 9: Limiting the Results with a Slicer Axis

*  To use the slicer axis to limit results:

  1. Open ex6.txt.

  2. Paste the query into the MaxL Shell and run it, as described in Exercise 2: Running Your First Query.

    Note the results in one of the data cells; for example, notice that the first tuple, ([100-10],[East],[Sales],[Qtr1]), has a value of 5731.

  3. Add a slicer axis to limit the data returned to budgeted values only.

    SELECT
     CrossJoin ({[100-10]}, {[East],[West],[South],[Central]})
    ON COLUMNS,
      CrossJoin (
        {[Sales],[COGS],[Margin %],[Profit %]}, {[Qtr1]}
      )
    ON ROWS
    FROM Sample.Basic
    WHERE (Budget) 
  4. Paste the query into the MaxL Shell and run it.

    Note that the results are different.

  5. Save the query as ex9.txt.

Common Relationship Functions

The following relationship functions return sets based on member relationships in the database outline:

Relationship Function

Description

Children

Returns the children of the input member.

Siblings

Returns the siblings of the input member.

Descendants

Returns the descendants of a member, with varying options.

The following relationship functions return a single member rather than a set:

Relationship Function

Description

Ancestor

Returns an ancestor at the specified layer.

Cousin

Returns a child member at the same position as a member from another ancestor.

Parent

Returns the parent of the input member.

FirstChild

Returns the first child of the input member.

LastChild

Returns the last child of the input member.

FirstSibling

Returns the first child of the input member’s parent.

LastSibling

Returns the last child of the input member’s parent.

For examples using relationship functions, see the MDX examples in the MaxL section of the Oracle Essbase Technical Reference.

Performing Set Operations

The following set functions operate on input sets without deriving further information from a database:

Pure Set Function

Description

CrossJoin

Returns a cross-section of two sets from different dimensions.

Distinct

Deletes duplicate tuples from a set.

Except

Returns a subset containing the differences between two sets.

Generate

An iterative function. For each tuple in set1, returns set2.

Head

Returns the first n members or tuples present in a set.

Intersect

Returns the intersection of two input sets.

Subset

Returns a subset from a set, in which the subset is a numerically specified range of tuples.

Tail

Returns the last n members or tuples present in a set.

Union

Returns the union of two input sets.

Exercise 10: Using the Intersect Function

The Intersect function returns the intersection two input sets, optionally retaining duplicates. Its syntax is as follows:

Intersect (set, set [,ALL])

Use the Intersect function to compare sets by finding tuples that are present in both sets.

*  To use the Intersect function:

  1. Open qry_blank.txt.

  2. Delete the braces {} from the axis, and replace them with Intersect ().

    SELECT
     Intersect ( 
    
     )
    ON COLUMNS
    FROM Sample.Basic
  3. Add two comma-separated pairs of braces to use as placeholders for the two set arguments you will provide to the Intersect function:

    SELECT
     Intersect ( 
     { },
     { }
     )
    ON COLUMNS
    FROM Sample.Basic
  4. Specify children of East as the first set argument.

    SELECT
     Intersect ( 
     { [East].children },
     { }
     )
    ON COLUMNS
    FROM Sample.Basic
  5. For the second set argument, specify all members of the Market dimension that have a UDA of “Major Market.”

    Note:

    For information about the UDA function, see the Oracle Essbase Technical Reference.

    SELECT
     Intersect ( 
     { [East].children },
     { UDA([Market], "Major Market") }
     )
    ON COLUMNS
    FROM Sample.Basic
  6. Paste the query into the MaxL Shell and run it, as described in Exercise 2: Running Your First Query.

    The results include all children of East that have a UDA of “Major Market”:

    New York

    Massachusetts

    Florida

    8202

    6712

    5029

  7. Save the query as ex10.txt.

Exercise 11: Using the Union Function

The Union function joins two input sets, optionally retaining duplicates. Syntax:

Union (set, set [,ALL])

Use the Union function to lump two sets together into one set.

*  To use the Union function:

  1. Open ex10.txt.

  2. Replace Intersect with Union.

  3. Paste the query into the MaxL Shell and run it.

    If you compare the results with the results of the Intersect function in the previous exercise, you see that while Intersect returns a set containing only those children of East that have a UDA of “Major Market,” Union returns {all children of east) + (all Market Members that have a UDA of “Major Market.”)

  4. Save the query as ex11.txt.

For more examples using pure set-operative functions, see the Oracle Essbase Technical Reference.

Creating and Using Named Sets and Calculated Members

Calculated members and named sets are logical entities in query that can be used multiple times during the life of the query. Calculated members and named sets can save time in lines of code written as well as in execution time. The optional WITH section at the beginning of an MDX query is where you define the calculated members and/or named sets.

Calculated Members

A calculated member is a hypothetical member that exists for the duration of the query execution. Calculated members enable complex analysis without necessitating adding new members to the database outline. Calculated members are a storage place for calculation results performed on actual members.

Use the following guidelines for calculated member names:

  • Associate the calculated member with a dimension; for example, to associated the member MyCalc with the Measures dimension, name it [Measures].[MyCalc].

  • Do not use actual member names to name calculated members; for example, do not name a calculated member [Measures].[Sales], because Sales already exists in the Measures dimension.

Setting the solve order for each calculated member is recommended when you use multiple calculated members to create ratios or custom totals. For more information about solve order, see the MDX section of the Oracle Essbase Technical Reference.

Exercise 12: Creating a Calculated Member

This exercise includes the Max function, a common function for calculations. The Max function returns the maximum of values found in the tuples of a set. Its syntax is as follows:

Max (set, numeric_value)

*  To create a calculated member:

  1. Open qry_blank_2ax.txt.

  2. On the row axis set, specify the children of Product.

    SELECT
     {}
    ON COLUMNS,
     {[Product].children}
    ON ROWS
    FROM Sample.Basic
  3. At the beginning of the query, add a placeholder for the calculated member specification:

    WITH MEMBER [].[]
     AS ''
    SELECT
     {}
    ON COLUMNS,
     {[Product].children}
    ON ROWS
    FROM Sample.Basic
  4. To associate the calculated member with the Measures dimension and name it Max Qtr2 Sales, add this information to the calculated member specification:

    WITH MEMBER [Measures].[Max Qtr2 Sales]
     AS ''
    SELECT
     {}
    ON COLUMNS,
     {[Product].children}
    ON ROWS
    FROM Sample.Basic
  5. After the AS keyword and inside the single quotation marks, define the logic for the calculated member named Max Qtr2 Sales. Use the Max function with the set to evaluate (Qtr2) as the first argument, and the measure to evaluate (Sales) as the second argument.

    WITH MEMBER [Measures].[Max Qtr2 Sales]
     AS '
      Max (
        {[Year].[Qtr2]},
        [Measures].[Sales]
      )'
    SELECT
     {}
    ON COLUMNS,
     {[Product].children}
    ON ROWS
    FROM Sample.Basic
    
  6. The calculated member Max Qtr2 Sales is defined in the WITH section. To use it in a query, include it on one of the axes in the SELECT portion of the query. Select the predefined calculated member on the columns axis:

    WITH MEMBER [Measures].[Max Qtr2 Sales]
     AS '
      Max (
        {[Year].[Qtr2]},
        [Measures].[Sales]
      )'
    SELECT
     {[Measures].[Max Qtr2 Sales]}
    ON COLUMNS,
     {[Product].children}
    ON ROWS
    FROM Sample.Basic
    
  7. Paste the query into the MaxL Shell and run it, as described in Exercise 2: Running Your First Query.

    Results:

     

    Max Qtr2 Sales

    100

    27187

    200

    27401

    300

    25736

    400

    21355

    Diet

    26787

  8. Save the query as ex12.txt.

Named Sets

You define named sets in the WITH section of the query. Doing so is useful because you can reference the set by name when building the SELECT section of the query. For example, the named set Best5Prods identifies a set of the five top-selling products in December:

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

Using Iterative Functions

The following functions loop through sets of data and perform search conditions and results that you specify.

Function

Description

Filter

Returns the subset of tuples in set for which the value of the search condition is TRUE.

IIF

Performs a conditional test and returns an appropriate numeric expression or set depending on whether the test evaluates to TRUE or FALSE.

Case

Performs conditional tests and returns the results you specify.

Generate

An iterative function. For each tuple in set1, returns set2.

Filter Function Example

The following query returns all Market dimension members for which the expression IsChild([Market].CurrentMember,[East]) returns TRUE; the query returns all children of East.

SELECT
 Filter([Market].Members,
        IsChild([Market].CurrentMember,[East])
 )
ON COLUMNS
FROM Sample.Basic

The Filter function in MDX is comparable to the RESTRICT command in Report Writer.

For more examples of Filter and other iterative functions, see the Oracle Essbase Technical Reference.

Working with Missing Data

When you are querying on a database, you can use the NON EMPTY keywords at the beginning of an axis specification to prevent cells containing no value from being included the result of the query.

The axis specification syntax including NON EMPTY:

<axis_specification> ::= 
       [NON EMPTY] <set> ON
       COLUMNS | ROWS | PAGES | CHAPTERS |
      SECTIONS | AXIS (<unsigned_integer>)

Including the optional keywords NON EMPTY before the set specification in an axis causes suppression of slices in that axis that would contain entirely #MISSING values.

For any given tuple on an axis (such as (Qtr1, Actual)), a slice consists of the cells arising from combining this tuple with all tuples of all other axes. If all of these cell values are #MISSING, the NON EMPTY keyword causes elimination of the tuple.

For example, if even one value in a row is not empty, the entire row is returned. Including NON EMPTY at the beginning of the row axis specification would eliminate the following row slice from the set returned by a query:

Qtr1

Actual

#MISSING

#MISSING

#MISSING

#MISSING

#MISSING

In addition to suppressing missing values with NON EMPTY, you can use the following MDX functions to handle #MISSING results:

  • CoalesceEmpty, which searches numeric value expressions for non #MISSING values

  • IsEmpty, which returns TRUE if the value of an input numeric-value-expression evaluates to #MISSING

  • Avg, which omits missing values from averages unless you use the optional IncludeEmpty flag

For more information, see the MDX section of the Oracle Essbase Technical Reference.

Using Substitution Variables in MDX Queries

Substitution variables act as global placeholders for information that changes regularly; you set the substitution variables on the server through Administration Services, MaxL, or ESSCMD and assign a value to each variable. You can change the value anytime. You must have the role of at least Database Manager to set substitution variables. See Using Substitution Variables for a comprehensive discussion on creating and changing substitution variables.

*  To use a substitution variable in an MDX expression, consider:

    • The substitution variable must be accessible from the application and database you are querying.

    • A substitution variable has two components: the name and the value.

    • The variable name can be an alphanumeric combination whose maximum size is specified in Limits. Do not use spaces, punctuation, or brackets ([ ]) in substitution variable names used in MDX.

    • At the point in the expression where you want to use the variable, show the variable name preceded by an ampersand (&); for example, where CurMonth is the name of the substitution variable set on the server, include &CurMonth in the MDX expression.

    • When you perform the retrieval, Essbase replaces the variable name with the substitution value, and that value is used by the MDX expression.

    For example, the expression is written showing the variable name CurQtr preceded with the &:

    SELECT 
     {[&CurQtr]}
    ON COLUMNS
    FROM Sample.Basic

    When the expression is executed, the current value (Qtr1) is substituted for the variable name, and the expression that is executed is:

    SELECT 
     {[Qtr1]}
    ON COLUMNS
    FROM Sample.Basic

    Querying for Properties

    In MDX, properties describe certain characteristics of data and metadata. MDX enables you to write queries that use properties to retrieve and analyze data. Properties can be intrinsic or custom.

    Intrinsic properties are defined for members in all dimensions. The intrinsic member properties defined for all members in an Essbase database outline are MEMBER_NAME, MEMBER_ALIAS, LEVEL_NUMBER, and GEN_NUMBER.

    MDX in Essbase supports two types of custom properties: attribute properties and UDA properties. Attribute properties are defined by the attribute dimensions in an outline. In the Sample.Basic database, the Pkg Type attribute dimension describes the packaging characteristics of members in the Product dimension. This information can be queried in MDX using the property name [Pkg Type].

    Attribute properties are defined only for specific dimensions and only for a specific level in each dimension. For example, in the Sample.Basic outline, [Ounces] is an attribute property defined only for members in the Product dimension, and this property has valid values only for the level 0 members of the Product dimension. The [Ounces] property does not exist for other dimensions, such as Market. The [Ounces] property for a non level 0 member in the Product dimension is a NULL value. The attribute properties in an outline are identified by the names of attribute dimensions in that outline.

    The custom properties also include UDAs. For example, [Major Market] is a UDA property defined on Market dimension members. It returns a TRUE value if [Major Market] UDA is defined for a member, and FALSE otherwise.

    Querying for Member Properties

    Properties can be used inside an MDX query in two ways.

    • You can list the dimension and property combinations for each axis set. When a query is executed, the specified property is evaluated for all members from the specified dimension and included in the result set.

      For example, on the column axis, the following query returns the GEN_NUMBER information for every Market dimension member. On the row axis, the query returns MEMBER_ALIAS information for every Product dimension member.

      SELECT
        [Market].Members
           DIMENSION PROPERTIES [Market].[GEN_NUMBER] on columns,
        Filter ([Product].Members, Sales > 5000)
           DIMENSION PROPERTIES [Product].[MEMBER_ALIAS] on rows
       FROM Sample.Basic

      When querying for member properties using the DIMENSION PROPERTIES section of an axis, a property can be identified by the dimension name and the name of the property, or by using the property name itself. When a property name is used by itself, that property information is returned for all members from all dimensions on that axis, for which that property applies. In the following query, the MEMBER_ALIAS property is evaluated on the row axis for Year and Product dimensions.

      SELECT  [Market].Members
           DIMENSION PROPERTIES [Market].[GEN_NUMBER] on columns,
        CrossJoin([Product].Children, Year.Children)
           DIMENSION PROPERTIES [MEMBER_ALIAS] on rows
       FROM Sample.Basic
    • Properties can be used inside value expressions in an MDX query. For example, you can filter a set based on a value expression that uses properties of members in the input set.

      The following query returns all caffeinated products that are packaged in cans.

      SELECT
           Filter([Product].levels(0).members,
                    [Product].CurrentMember.Caffeinated and
                    [Product].CurrentMember.[Pkg Type] = "Can")
                 Dimension Properties
                 [Caffeinated], [Pkg Type] on columns
        FROM Sample.Basic

      The following query calculates the value [BudgetedExpenses] based on whether the current Market is a major market, using the UDA [Major Market].

      WITH
        MEMBER [Measures].[BudgetedExpenses] AS
        'IIF([Market].CurrentMember.[Major Market],
         [Marketing] * 1.2, [Marketing])'
      
        SELECT  {[Measures].[BudgetedExpenses]} ON COLUMNS,
         [Market].Members ON ROWS
       FROM Sample.Basic
       WHERE ([Budget])

    The Value Type of Properties

    The value of an MDX property in Essbase can be a numeric, Boolean, or string type. MEMBER_NAME and MEMBER_ALIAS properties return string values. LEVEL_NUMBER, and GEN_NUMBER properties return numeric values.

    The attribute properties return numeric, Boolean, or string values based on the attribute dimension type. For example, in Sample.Basic, the [Ounces] attribute property is a numeric property. The [Pkg Type] attribute property is a string property. The [Caffeinated] attribute property is a Boolean property.

    Essbase allows attribute dimensions with date types. The date type properties are treated as numeric properties in MDX. When comparing these property values with dates, use the TODATE function to convert date strings to numeric before comparison.

    The following query returns all Product dimension members that have been introduced on date 03/25/2007. Because the property [Intro Date] is a date type, the TODATE function must be used to convert the date string “03-25-2007” to a number before comparing it.

    SELECT
     Filter ([Product].Members,
             [Product].CurrentMember.[Intro Date] = 
             TODATE("mm-dd-yyyy","03-25-2007"))ON COLUMNS
    FROM Sample.Basic

    When a property is used in a value expression, you must use it appropriately based on its value type: string, numeric, or Boolean.

    You can also query attribute dimensions with numeric ranges.

    The following query retrieves Sales data for Small, Medium, and Large population ranges.

    SELECT
     {Sales} ON COLUMNS,
     {Small, Medium, Large} ON ROWS
    FROM Sample.Basic

    When attributes are used as properties in a value expression, you can use range members to check whether a member's property value falls within a given range, using the IN operator.

    For example, the following query returns all Market dimension members with the population range in Medium:

    SELECT
      Filter(
           Market.Members, Market.CurrentMember.Population
           IN "Medium"
      ) 
    ON AXIS(0)
    FROM Sample.Basic

    NULL Property Values

    Not all members may have valid values for a given property name. For example, the MEMBER_ALIAS property returns an alternate name for a given member as defined in the outline; however, not all members may have aliases defined. In these cases A NULL value is be returned for those members that do not have aliases.

    In the following query,

    SELECT
      [Year].Members
       DIMENSION PROPERTIES [MEMBER_ALIAS]
    ON COLUMNS
    FROM Sample.Basic

    none of the members in the Year dimension have aliases defined for them. Therefore, the query returns NULL values for the MEMBER_ALIAS property for members in the Year dimension.

    The attribute properties are defined for members of a specific dimension and a specific level in that dimension. In the Sample.Basic database, the [Ounces] property is defined only for level 0 members of the Product dimension.

    Therefore, if you query for the [Ounces] property of a member from the Market dimension, as shown in the following query, you will get a syntax error:

    SELECT
        Filter([Market].members,
               [Market].CurrentMember.[Ounces] = 32) ON COLUMNS
    FROM Sample.Basic

    Additionally, if you query for the [Ounces] property of a non level 0 member of the dimension, you will get a NULL value.

    When using property values in value expressions, you can use the function IsValid() to check for NULL values. The following query returns all Product dimension members with an [Ounces] property value of 12, after eliminating members with NULL values.

    SELECT
      Filter([Product].Members,
             IsValid([Product].CurrentMember.[Ounces]) AND
             [Product].CurrentMember.[Ounces] = 12) 
    ON COLUMNS
    FROM Sample.Basic