29 Writing MDX Queries
MDX is a SQL-like language you can use to issue queries that retrieve data from Essbase. MDX is also used to define formulas on ASO cubes, query metadata, qualify member names, and delineate subsets of data or metadata. The best way to learn MDX is to write queries.
This section helps you learn MDX by writing queries against the Sample.Basic cube in a series of exercises.
Prerequisites for Writing MDX Queries
To complete the exercises, you will need:
-
a text editor, to write the MDX queries
-
Access to an Essbase instance with Sample Basic
If you need to get Sample Basic, follow the steps in Create a Sample Cube to Explore Outline Properties (just do the import, and skip setting the outline properties)
-
MaxL Client, to issue the queries to Essbase
Build an MDX Query Template
Learn the basic format of an MDX query, so you can get started using MDX with Essbase. Similarly to SQL statements, MDX queries usually start with SELECT.
In this section you will create a template to use as a basis for developing simple MDX 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 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: Create an MDX Query Template
To create a query template:
-
Create a folder to store sample queries that can be run against the Sample.Basic cube.
-
Using a text editor, type the following code into a blank file:
SELECT {} ON COLUMNS FROM Sample.Basic
-
Save the file as
qry_blank.txt
.
MDX Sets and Tuples
Write your first query, and run it in the MaxL Client to retrieve some data from the Sample Basic cube.
Sets
An MDX set can be empty, or be a collection of tuples, or a collection of sets.
For example, the following is an empty set.
{ }
A set must be enclosed in curly braces {}
, except in cases where the set is represented by an MDX function that returns a set (more about functions later).
The following is a set consisting of one tuple.
{[Cola]}
In the following query, {([Cola], [Actual])}
is also a set consisting of one tuple, though in this case, the tuple has more than one member name.
SELECT
{([Cola], [Actual])}
ON COLUMNS
FROM Sample.Basic
{([Cola], [Actual])}
is a tuple consisting of two members (Cola and Actual) from two dimensions (Product and Scenario).
Dimensionality Rule
When a set has multiple tuples, the members of each tuple must represent the same Essbase dimensions, in the same order. In other words, all the tuples must have the same dimensionality as the others.
-
OK: The following set consists of two tuples of the same dimensionality:
{(West, Feb), (East, Mar)}
-
Not OK: The following set breaks the dimensionality rule, because Feb and Sales are from different dimensions:
{(West, Feb), (East, Sales)}
-
Not OK: 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)}
Tuples and Member Names
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 cube, all of these are valid tuples:
Jan
(Jan, Sales)
([Jan],[Sales],[Cola],[Utah],[Actual])
The member names can be specified in these ways:
-
By specifying the actual name or the alias; for example:
-
Cola
-
Actual
-
COGS
-
[100]
If the member name starts with number or contains spaces, it should be within brackets; for example,
[100]
or[New York]
. However, the member name brackets are recommended for all member names, for clarity and code readability.If the member name starts with an ampersand (&) , it should be within quotation marks; for example,
["&xyz"]
. This is because the leading ampersand is reserved for substitution variables (see Variables in MDX Queries). You can also specify it asStrToMbr("&100")
.For attribute members, the long name (qualified to uniquely identify the member) should be used; for example,
[Ounces_12]
instead of[12]
. -
-
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 practice is recommended for all member names, as it eliminates ambiguity and enables you to refer accurately to shared members. See: Qualifying Members by Differentiating AncestorNote:
Do not use multiple ancestors in the member name qualification. Essbase returns an error if multiple ancestors are included. For example,
[Market].[New York]
and[East].[New York]
are valid names for New York; however,[Market].[East].[New York]
returns an error. -
By specifying the name of a calculated member defined in the WITH Section.
Exercise 2: Run Your First Query
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.
To run the query:
-
Open
qry_blank.txt
, the query template you created from Build an MDX Query Template. -
Because a set can be as simple as one tuple, add the tuple within the curly braces
{ }
that hold the set.Type
[Jan]
inside the{ }
braces in line 2:SELECT {[Jan]} ON COLUMNS FROM Sample.Basic
-
Save the query as
qry_first.txt
. -
Ensure that Essbase is running.
-
Start the MaxL Client and log in with a valid user name and password. For example:
login admin1 my_Pa55w0rD on "https://myserver.example.com:9001/essbase/agent";
-
Copy and paste the entire SELECT query into the MaxL Client, but do not press Enter yet.
-
Enter a semicolon at the end, anywhere after Basic but before pressing Enter. (The semicolon is not an MDX requirement, but required by the MaxL Client to indicate the end of a statement).
-
Press Enter to send the query to Essbase.
The results should be similar to the following:
Jan 8024
MDX Query Layout with Axes and Cube Specification
An MDX axis is an instruction shaping the grid layout of query results from an Essbase cube. ON COLUMNS and ON ROWS are axis keywords that describe where the results should appear. A cube specification includes the FROM keyword, and tells Essbase which cube to query.
MDX Axis
Axes fit into MDX queries after the Select:
SELECT <axis> [, <axis>...]
FROM <database>
In the following query, the axis specification is {Jan} ON COLUMNS
:
SELECT
{Jan} ON COLUMNS
FROM Sample.Basic
At least one axis must be specified in any MDX query.
Up to 64 axes may be indicated, 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 multiple axes.
The first five axes have keyword aliases, as listed in the following table:
Table 29-1 Axes Keyword Aliases
Axes Keyword Alias | Axes |
---|---|
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) |
MDX Cube Specification
A cube specification is the part of the query that determines which Essbase database is being queried. The cube specification fits into an MDX query as follows:
SELECT <axis> [, <axis>...]
FROM <cube>
The <cube> 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'
Exercise 3: Running A Two-Axis Query
To run a two-axis query:
-
Open
qry_blank.txt
, the query template you created in Build an MDX Query Template. -
Add a comma after
ON COLUMNS
; then add a placeholder for a second axis by addingON ROWS
:SELECT {} ON COLUMNS, {} ON ROWS FROM Sample.Basic
-
Save the new query template as
qry_blank_2ax.txt
. -
As the set specification for the column axis, enter the Product members 100-10 and 100-20. For example:
SELECT {[100-10],[100-20]} ON COLUMNS, {} ON ROWS FROM Sample.Basic
Because these member names contain special characters, you must use brackets. The convention used here, to enclose all member names in brackets even if they do not contain special characters, is recommended.
-
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
-
Save the query as
qry_2ax.txt
. -
Paste the query into the MaxL Client and run it, as described in the first exercise (in MDX Sets and Tuples).
The results of the query should look like the following:
Table 29-2 Results: Running A Two-Axis Query
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:
-
Open
qry_blank_2ax.txt
, the query template you created in the previous exercise. -
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 multiple members are represented in each tuple.
SELECT {([100-10],[East]), ([100-20],[East])} ON COLUMNS, {} ON ROWS FROM Sample.Basic
-
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
-
Save the query as
qry_1ax.txt
. -
Paste the query into the MaxL Client and run it, as described in the first exercise (in MDX Sets and Tuples).
The results should be similar to the following:
Table 29-3 Results: Querying Multiple Dimensions on a Single Axis
100-10 100-20 East
East
Qtr1
Profit
2461
212
Qtr2
Profit
2490
303
Qtr3
Profit
3298
312
Qtr4
Profit
2430
287
Use MDX Functions to Build Sets
Learn to use MemberRange and CrossJoin functions, by trying the exercises.
This introduction to MDX functions focuses on a few functions that generate sets. Rather than manually entering sets member-by-member or tuple-by-tuple into an MDX query, you can replace such enumerations with a simple function expression. MDX functions can return sets, as well as other values.
For example, Children is a set function. It returns the set of child members of the input member. Therefore, Children(Qtr1)
returns {Jan, Feb, Mar}
.
Exercises that follow below can help you learn to use MDX functions in simple queries. A complete reference of MDX functions supported by Essbase is listed at MDX Function List.
Exercise 5: Using the MemberRange Function
The MemberRange MDX 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.
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:
-
Open
qry_blank.txt
, the query template you created in Build an MDX Query Template. -
Delete the braces
{}
, which are unnecessary when you are using a function to return the set. -
Use the colon operator to select a member range of Qtr1 through Qtr4:
SELECT [Qtr1]:[Qtr4] ON COLUMNS FROM Sample.Basic
-
Paste the query into the MaxL Client and run it, as described in the first exercise (in MDX Sets and Tuples).
Qtr1, Qtr2, Qtr3, and Qtr4 are returned.
-
Use the MemberRange function to select the same member range, Qtr1 through Qtr4.
SELECT MemberRange([Qtr1],[Qtr4]) ON COLUMNS FROM Sample.Basic
-
Paste the query into the MaxL Client and run it.
-
Save the query as
gry_member_range_func.txt
.
Exercise 6: Using the CrossJoin Function
The CrossJoin function returns the cross product of two sets from different Essbase dimensions. Its syntax is as follows:
CrossJoin(set,set)
This function takes two sets from different dimensions as input, and creates a set that is a cross product of them. This is useful for creating symmetric reports.
To use the CrossJoin function:
-
Open
qry_blank.txt
, the query template you created in Build an MDX Query Template. -
Replace the braces
{}
from the columns axis withCrossJoin()
.SELECT CrossJoin () ON COLUMNS, {} ON ROWS FROM Sample.Basic
-
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
-
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
-
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
-
Save the query as
qry_crossjoin_func.txt
. -
Paste the query into the MaxL Client and run it.
As you experiment with using CrossJoin, notice that the order of arguments affects the order of tuples in the output.
Results of the query are shown below:
Table 29-4 Results: Using the CrossJoin Function
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 |
Note:
Consider using CrossJoinAttribute if the input sets are a base dimension and its attribute dimension.
Exercise 7: Using the Children Function
The Children function returns a set of all child members of the given member. Use this syntax:
Children (member)
Note:
An alternate syntax for Children is to use it as 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:
-
Open
qry_crossjoin_func.txt
, the query you built in in the previous exercise. -
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
-
Save the query as
gry_children_func.txt
. -
Paste the query into the MaxL Client and run it.
You should see the same results as those returned for the previous, CrossJoin exercise.
Referencing Levels and Generations with MDX
Some MDX functions perform set operations based on an input layer argument. The layer represents a generation or level of an Essbase dimension. Learn to reference a set using the Members function.
In MDX, the concept of a 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.
You can specify a layer argument in the following ways:
-
Generation or level name; for example,
States
orRegions
. -
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
Use the Members function to return all members of a specified generation or level. When used with a layer argument, the syntax is:
Members (
layer)
where the layer argument indicates the generation or level of members to return.
Note:
An alternate syntax for Members is layer.Members
.
To use the Members function:
-
Open
qry_blank.txt
, the query template you created in Build an MDX Query Template. -
Delete the braces
{}
, which are unnecessary when you are using a function to return a set. -
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
-
Save the query as
qry_members_func.txt
. -
Paste the query into the MaxL Client and run it, as described in the first exercise (in MDX Sets and Tuples).
Results: All states in the Market dimension are returned.
Use a Slicer Axis to Set MDX Query Point-of-View
A slicer axis is a way to limit an MDX query to consider only a specific area of the Essbase cube. Learn to use the slicer in the WHERE clause by trying the sample exercise.
The slicer, if used, must be in the WHERE section of an MDX query. Also, the WHERE section must be the last component of the query, following the cube specification (the FROM section):
SELECT {set}
ON axes
FROM cube
WHERE slicer
Use the slicer axis to set the context of the query; it is usually the default context for all the other axes.
To select only Actual Sales in the Sample.Basic cube, 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.
Note:
The same dimension cannot appear on other axes and the slicer axis. To filter an axis using criteria from its own dimension, you can use a sub select.
Exercise 9: Limiting the Results with a Slicer Axis
To use the slicer axis to limit results:
-
Open
gry_crossjoin_func.txt
, the query you built in Exercise 6 of Use MDX Functions to Build Sets. -
Paste the query into the MaxL Client and run it, as described in the first exercise (in MDX Sets and Tuples).
Note the results in one of the data cells; for example, notice that the first tuple,
([Cola],[East],[Sales],[Qtr1])
, has a value of 5731. -
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)
-
Paste the query into the MaxL Client and run it.
-
Notice that the value for tuple
([Cola],[East],[Sales],[Qtr1])
is now 5020. -
Save the query as
qry_slicer_axis.txt
.
Common MDX Relationship Functions
MDX relationship functions return sets or members based on hierarchical member relationships in the Essbase cube outline.
The following MDX relationship functions return sets.
Table 29-5 List of MDX Relationship Functions That Return Sets
Relationship Function | Description |
---|---|
Returns the children of the input member. |
|
Returns the siblings of the input member. |
|
Returns the descendants of a member, with varying options. |
The following MDX relationship functions return a single member rather than a set:
Table 29-6 List of MDX Relationship Functions That Return a Single Member
Relationship Function | Description |
---|---|
Returns an ancestor at the specified layer. |
|
Returns a child member at the same position as a member from another ancestor. |
|
Returns the parent of the input member. |
|
Returns the first child of the input member. |
|
Returns the last child of the input member. |
|
Returns the first child of the input member’s parent. |
|
Returns the last child of the input member’s parent. |
Exercise 10: Try Some Relationship Function Examples from the Documentation
To learn how relationship functions work:
-
Click on a link to a function in one of the tables above, or click Children.
-
Read the example, and copy the SELECT query to your clipboard.
-
Paste the query into the MaxL Client, add a closing semicolon, and run the query, as described in the first exercise (in MDX Sets and Tuples).
MDX Functions for Set Operations
Learn the difference between Intersect and Union functions by trying the exercises.
The following set functions operate on input sets without deriving further information from a cube:
Table 29-7 List of Pure Set Functions
Pure Set Function | Description |
---|---|
Returns a cross-section of two sets from different dimensions. |
|
Deletes duplicate tuples from a set. |
|
Returns a subset containing the differences between two sets. |
|
An iterative function. For each tuple in set1, returns set2. |
|
Returns the first n members or tuples present in a set. |
|
Returns the intersection of two input sets. |
|
Returns a subset from a set, in which the subset is a numerically specified range of tuples. |
|
Returns the last n members or tuples present in a set. |
|
Returns the union of two input sets. |
Exercise 11: Using the Intersect Function
The MDX Intersect function returns the intersection of two input sets, optionally retaining duplicates. Use it to compare sets by finding tuples that are present in both sets.
The syntax to follow is:
Intersect (set, set [,ALL])
-
Open
qry_blank.txt
, the query template you created in Build an MDX Query Template. -
Delete the empty set braces
{}
from the axis, and replace them withIntersect()
. Leave some room inside the Intersect braces for adding more code. For example:SELECT Intersect ( ) ON COLUMNS FROM Sample.Basic
-
Add two comma-separated pairs of braces to use as placeholders for the two set arguments you will provide to the Intersect function. For example:
SELECT Intersect ( { }, { } ) ON COLUMNS FROM Sample.Basic
-
Specify children of East as the first set argument. For example:
SELECT Intersect ( { [East].children }, { } ) ON COLUMNS FROM Sample.Basic
-
For the second set argument, specify all members of the Market dimension that have a UDA of “Major Market.” For example:
SELECT Intersect ( { [East].children }, { UDA([Market], "Major Market") } ) ON COLUMNS FROM Sample.Basic
-
Paste the query into the MaxL Client and run it, as described in the first exercise (in MDX Sets and Tuples).
All children of East that have a UDA of "Major Market" are returned. For example:
New York Massachusetts Florida 8202 6172 5029
-
Save the query as
qry_intersect_func.txt
.
Exercise 12: Using the Union Function
The MDX Union function joins two input sets, optionally retaining duplicates. Use it to combine two sets together into one set.
The syntax to follow is:
Union (set, set [,ALL])
-
Open
qry_intersect_func.txt
, the query you built in the previous exercise. -
Replace Intersect with Union.
-
Save the query as
qry_union_func.txt
. -
Paste the query into the MaxL Client and run it.
While Intersect returned a set containing only those children of East that have a UDA of Major Market, Union returns a bigger set. It includes all children of East, AND all Market members that have a UDA of Major Market.
(New York) (Massachusetts) (Florida) (Connecticut) (New Hampshire) (East) (California) (Texas) (Central) (Illinois) (Ohio) (Colorado) +---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+--------------- 8202 6712 5029 3093 1125 24161 12964 6425 38262 12577 4384 7227
Reusable Sets and Members: MDX WITH Section
Defining members and sets in the MDX query's WITH section helps you filter data without impacting the cube. A calculated member is a logical member that exists only within the query. A named set is a logical set that exists only within the query. Try a sample exercise.
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.
The following query uses a calculated member:
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
The following query uses a named set:
WITH SET [NewSet]
AS 'CrossJoin([Product].Children, [Market].Children)'
SELECT
Filter([NewSet], NOT IsEmpty([NewSet].CurrentTuple))
ON COLUMNS
FROM Sample.Basic
WHERE
{[Sales]}
Calculated Members
A calculated member is a hypothetical member that exists for the duration of the query execution. Calculated members enable complex analysis without the need to add physical members to the cube outline. Calculated members store calculation results performed on physical 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.
Named Sets
You define named sets using WITH SET keywords, before the SELECT portion of the query. Doing so is useful because you can reference the set by name when building the SELECT portion 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
Exercise 13: Creating a Calculated Member
This exercise uses the Max function, a common MDX function for calculations. It returns the maximum of values found in the tuples of a set.
The syntax to follow is:
Max (set, numeric_value)
-
Open
qry_blank_2ax.txt
, the query template you built in Exercise 3 of MDX Query Layout with Axes and Cube Specification.SELECT {} ON COLUMNS, {} ON ROWS FROM Sample.Basic
-
On the row axis set, specify the children of Product. For example:
SELECT {} ON COLUMNS, {[Product].children} ON ROWS FROM Sample.Basic
-
At the beginning of the query, add a placeholder for the calculated member specification. For example:
WITH MEMBER [].[] AS '' SELECT {} ON COLUMNS, {[Product].children} ON ROWS FROM Sample.Basic
-
To associate the calculated member with the Measures dimension and name it Max Qtr2 Sales, add this information to the calculated member specification. For example:
WITH MEMBER [Measures].[Max Qtr2 Sales] AS '' SELECT {} ON COLUMNS, {[Product].children} ON ROWS FROM Sample.Basic
-
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. For example:
WITH MEMBER [Measures].[Max Qtr2 Sales] AS ' Max ( {[Year].[Qtr2]}, [Measures].[Sales] )' SELECT {} ON COLUMNS, {[Product].children} ON ROWS FROM Sample.Basic
-
The calculated member Max Qtr2 Sales is defined in the WITH section. To use it in a query, reference it on one of the axes in the SELECT portion of the query. For example:
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
-
Save the query as
gry_calc_member.txt
. -
Paste the query into the MaxL Client and run it, as described in the first exercise (in MDX Sets and Tuples).
Results of the query are shown below:
Table 29-8 Results: Creating a Calculated Member
Max Qtr2 Sales 100
27187
200
27401
300
25736
400
21355
Diet
26787
Note:
Many more examples are available in the MDX reference documentation. See MDX With Section.
Iterative MDX Functions
Iterative MDX functions loop through sets of data in the Essbase cube, performing any search conditions that you specify to tailor the results. See an example that filters data based on a Boolean test.
Table 29-9 List of Iterative MDX Functions
Function | Description |
---|---|
Returns the subset of tuples in set for which the value of the search condition is TRUE. |
|
Performs a conditional test and returns an appropriate numeric expression or set depending on whether the test evaluates to TRUE or FALSE. |
|
Performs conditional tests and returns the results you specify. |
|
For each tuple in set1, returns set2. |
Filter Function Example
The following query uses the MDX Filter function to return 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.
Handling Missing Data with MDX
When querying an Essbase cube using MDX, you can use the NON EMPTY keywords on the axes to suppress cells containing no value. MDX functions that handle missing values include Avg, CoalesceEmpty, IsEmpty, NonEmptyCount, and NonEmptySubset. NONEMPTYMEMBER and NONEMPTYTUPLE properties help filter out empty values from large data sets.
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.
The following is the axis specification syntax with NON EMPTY:
<axis_specification> ::=
[NON EMPTY] <set> ON
COLUMNS | ROWS | PAGES | CHAPTERS |
SECTIONS | AXIS (<unsigned_integer>)
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:
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
The NonEmptyCount MDX function returns the count of the number of tuples in a set that evaluate to non-#Missing values. Each tuple is evaluated and included in the count returned by this function. If the numeric value expression is specified, it is evaluated in the context of every tuple, and the count of non-#Missing values is returned.
On aggregate storage cubes only, the NonEmptyCount function is optimized so that the calculation of the distinct count for all cells can be performed by scanning the cube only once. Without this optimization, the database is scanned as many times as the number of cells corresponding to the distinct count. The NonEmptyCount optimization is triggered when an outline member formula has the following syntax:
NONEMPTYCOUNT(set, measure, exclude_missing)
The exclude_missing parameter supports the NonEmptyCount optimization on aggregate databases by improving the performance of a query that queries metrics that perform a distinct count calculation.
The NONEMPTYMEMBER and NONEMPTYTUPLE optimization properties enable MDX to query on large sets of members or tuples while skipping formula execution on non-contributing values that contain only #MISSING data.
-
Use a single NONEMPTYMEMBER property clause at the beginning of a calculated member or formula expression to indicate to Essbase that the value of the formula or calculated member is empty when any of the members specified in nonempty_member_list are empty.
-
Use a single NONEMPTYTUPLE property clause at the beginning of a calculated member or formula expression to indicate to Essbase that the value of the formula or calculated member is empty when the cell value at the tuple given in nonempty_member_list is empty.
Given an input set, the NonEmptySubset MDX function returns a subset of that input set in which all tuples evaluate to non-empty. An optional value expression may be specified for the non-empty check. This function can help optimize queries that are based on a large set for which the set of non-empty combinations is known to be small. NonEmptySubset reduces the size of the set in the presence of a metric; for example, you might request the non-empty subset of descendants for specific Units.
Variables in MDX Queries
You can use predefined Essbase substitution variables in MDX to reference frequently changing information without changing your queries. To reference a variable in an MDX query or expression, enter the variable name preceded by an ampersand (&).
Substitution variables in Essbase act as placeholders for information that changes regularly. You set the substitution variables at the Essbase cube, application, or global level, 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.
To use a substitution variable in an MDX expression, consider:
-
The substitution variable must be accessible from the application and cube 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 Name and Related Artifact 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, enter 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 by the &:
SELECT
{[&CurQtr]}
ON COLUMNS
FROM Sample.Basic
When the expression is evaluated, the current value (Qtr1) is substituted for the variable name, so that the expression that is run is effectively:
SELECT
{[Qtr1]}
ON COLUMNS
FROM Sample.Basic
Querying for Properties in MDX
Properties describe certain characteristics of Essbase data and metadata. MDX enables you to write queries that retrieve and analyze data based on Essbase properties, which can be intrinsic or custom. You can invoke properties on the MDX query axes, or in a value expression.
Intrinsic and Custom 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
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, GEN_NUMBER, IS_EXPENSE, COMMENTS, and MEMBER_UNIQUE_NAME.
See MDX Intrinsic Properties for a description of each.
Custom Properties
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.
See also MDX Custom Properties.
Invoking Properties in a Query Axis
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
Invoking Properties in a Value Expression
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])
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/2018. Because the property [Intro Date] is a date type, the TODATE function must be used to convert the date string “03-25-2018” to a number before comparing it.
SELECT
Filter ([Product].Members,
[Product].CurrentMember.[Intro Date] =
TODATE("mm-dd-yyyy","03-25-2018"))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