Essbase® Analytic Services Database Administrator's Guide | | Update Contents | Previous | Next | Print | ? | |
Information Map | |
This chapter describes MDX, the data manipulation language for Analytic Services.
MDX is a joint specification of the XML for Analysis founding members. For more information about XML for Analysis, please visit http://www.xmla.org. For more details on the syntax and grammar of MDX, refer to the MaxL section of the Technical Reference.
The goal of this chapter is to familiarize you with MDX and develop simple queries based on the Sample Basic database.
To complete the exercises in this chapter, use the MaxL Shell. Before continuing, please start Analytic Services, 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.
This chapter contains the following sections:
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 curly braces {} in line 2 are a placeholder for a set. In the above query, the set is empty, but the curly braces remain as a placeholder.
Essbase\Apps\Sample\Basic
directory of the Analytic Services installation.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.
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, and so is (Jan, Sales), and so is ([Jan],[Sales],[Cola],[Utah],[Actual]).
The member name can be specified in the following ways:
Cola
, Actual
, COGS
, and [100]
.
If the member name starts with number or contains spaces, it should be within braces; for example, [100]
. Braces 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 just [12]
.
[Product].[100-10]
and [Diet].[100-10]
This is a recommended practice for all member names, as it eliminates ambiguity and enables you to refer accurately to shared members.
Note: Use no more than one ancestor in the member name qualification. Analytic Services 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.
Recall that the curly 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.
qry_blank.txt
.Jan
as a set to the query template. We must retain the curly braces, because these are required for all set specifications (except for sets that are produced by a function call).Type Jan inside the curly braces in line 2, as shown:
SELECT {Jan} ON COLUMNS FROM Sample.Basic
ex2.txt
.essbase.exe
process is running).Start the MaxL Shell and log in with a valid user name and password. For example,
essmsh -l admin passwd
Note: If you are using the MDX Script Editor in Administration Services, do not terminate with a semicolon.
As described in the previous section, 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 a single member name. Rather, ([100-10], [Actual]) represents a tuple consisting of members from two different 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, and it can also be empty (containing no tuples).
A set must be enclosed in curly brackets {} except in some cases where the set is represented by an MDX function which returns a set.
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>
There must be at least one axis specified in any MDX query.
Up to 64 axes may be specified, beginning with AXIS(0) and continuing with AXIS(1)...AXIS(63). It is uncommon to use more than three axes. 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:
SELECT {Jan} ON COLUMNS FROM Sample.Basic
the axis specification is {Jan}
ON
COLUMNS
.
qry_blank.txt
.qry_blank_2ax.txt
.
SELECT
{[100-10],[100-20]
}
ON COLUMNS,
{}
ON ROWS
FROM Sample.Basic
Note: In this chapter, the convention will be to enclose all member names in braces, even if they do not contain special characters. This convention is recommended.
SELECT {[100-10],[100-20]} ON COLUMNS, {[Qtr1],[Qtr2],[Qtr3],[Qtr4]} ON ROWS FROM Sample.Basic
ex3.txt
.You should see results similar to the following.
qry_blank_2ax.txt
.SELECT {([100-10],[East]), ([100-20],[East])} ON COLUMNS, {} ON ROWS FROM Sample.Basic
SELECT {([100-10],[East]), ([100-20],[East])} ON COLUMNS, { ([Qtr1],[Profit]), ([Qtr2],[Profit]), ([Qtr3],[Profit]), ([Qtr4],[Profit]) } ON ROWS FROM Sample.Basic
ex4.txt
.You should see results similar to the following.
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 non delimited identifiers that specify an application name and a database name.
The first identifier should be an application name and the second one should be a database name. For example, all of the following are valid cube specifications:
As an alternative to 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 also several functions that return other values. For a complete reference of MDX functions supported by Analytic Services, see the MaxL section of the online Technical Reference.
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 and will not be addressed here, for more information see the 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.
qry_blank.txt
.{}
. Curly braces are not necessary when you are using a function to return the set.SELECT [Qtr1]:[Qtr4] ON COLUMNS FROM Sample.Basic
SELECT MemberRange([Qtr1],[Qtr4]) ON COLUMNS FROM Sample.Basic
ex5.txt
.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. This is useful for creating symmetric reports.
qry_blank_2ax.txt
.{}
from the columns axis, and replace them with CrossJoin()
.SELECT CrossJoin () ON COLUMNS, {} ON ROWS FROM Sample.Basic
SELECT CrossJoin ({}, {}) ON COLUMNS, {} ON ROWS FROM Sample.Basic
[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
SELECT CrossJoin ({[100-10]}, {[East],[West],[South],[Central]}) ON COLUMNS, CrossJoin ( {[Sales],[COGS],[Margin %],[Profit %]}, {[Qtr1]} ) ON ROWS FROM Sample.Basic
ex6.txt
.You should see results similar to the following.
When using CrossJoin, the order of arguments has an effect on the order of tuples in the output.
The Children function returns a set of all child members of the given member. Its syntax is as follows:
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.
ex6.txt
. You will use the Children function to introduce a shortcut in the first axis specification.[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
ex7.txt
.In MDX, the term layer is used to refer to generations and levels in an Analytic Services hierarchy.
In Analytic Services, generation numbers begin counting with 1 at the dimension name; higher generation numbers are those that are 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:
States
or Regions
.Market.Regions
and [Market].[States]
.[Year].Levels(0)
.[Qtr1].Level
returns the level of quarters in Sample Basic, which is level 1 of the Market dimension.[Year].Generations
(3)
.[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 even though 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, then Qtr1 is one level higher than Qtr4, but they are still in the same layer.
The Members function can be used to return all members of a specified generation or level. Its syntax when used with a layer argument is as follows:
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.
qry_blank.txt
.{}
.SELECT Members(Market.levels(0)) ON COLUMNS FROM Sample.Basic
ex8.txt
.A slicer axis is a way of limiting a query to apply only to 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
axesFROM
database WHEREslicer
The slicer axis is used to set the context of the query, and is usually the default context for all the other axes.
For example, if you want 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, it is not necessary to include them in the ON AXIS(n) set specifications.
ex6.txt
.
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.
SELECT CrossJoin ({[100-10]}, {[East],[West],[South],[Central]}) ON COLUMNS, CrossJoin ( {[Sales],[COGS],[Margin %],[Profit %]}, {[Qtr1]} ) ON ROWS FROM Sample.Basic WHERE (Budget)
ex9.txt
.The following relationship functions return sets based on member relationships in the database outline:
Relationship Function |
Description |
---|---|
The following functions are also relationship functions, but they return a single member rather than a set:
Relationship Function |
Description |
---|---|
Returns a child member at the same position as a member from another ancestor. |
|
For examples using relationship functions, see the MDX examples in the MaxL section of the Technical Reference.
The following set functions are characterized by the fact that they operate on input sets without deriving further information from a database:
The Intersect function returns the intersection two input sets, optionally retaining duplicates. Its syntax is as follows:
Intersect (set, set [,ALL])
You can use the Intersect function to compare sets by finding tuples that are present in both sets.
qry_blank.txt
.{}
from the axis, and replace them with Intersect
()
.SELECT Intersect ( ) ON COLUMNS FROM Sample.Basic
SELECT Intersect ( { }, { } ) ON COLUMNS FROM Sample.Basic
SELECT Intersect ( { [East].children }, { } ) ON COLUMNS FROM Sample.Basic
Note: To learn more about how the UDA function works, see the Technical Reference.
SELECT Intersect ( { [East].children }, { UDA([Market], "Major Market") } ) ON COLUMNS FROM Sample.Basic
The results will be all children of East that have a UDA of "Major Market":
ex10.txt
.The Union function joins two input sets, optionally retaining duplicates. Its syntax is as follows:
You can use the Union function to lump two sets together into one set.
ex10.txt
.If you compare the results with the results of the Intersect function in the previous exercise, you will 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.")
ex11.txt
.For more examples using pure set-operative functions, see the Technical Reference.
Calculated members and named sets are logical entities in query which 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.
A calculated member is a hypothetical member existing for the duration of the query execution. Calculated members enable you to perform complex analysis without the necessity of adding new members to the database outline. Essentially, calculated members are a storage place for calculation results performed on real members.
You can give a calculated member any name you want, with the following guidelines:
[Measures].[MyCalc]
. [Measures].[Sales]
, because Sales already exists in the Measures dimension.Whenever you use multiple calculated members to create ratios or custom totals, it is good practice to set the solve order for each calculated member. For more information about solve order, see Grammar Rules > With Specification in the MDX section of the Technical Reference.
This exercise will include 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)
qry_blank_2ax.txt
.SELECT {} ON COLUMNS, {[Product].children} ON ROWS FROM Sample.Basic
WITH MEMBER [].[] AS '' SELECT {} ON COLUMNS, {[Product].children} ON ROWS FROM Sample.Basic
WITH MEMBER [
Measures].[
Max Qtr2 Sales] AS ''
SELECT {} ON COLUMNS, {[Product].children} ON ROWS FROM Sample.Basic
WITH MEMBER [
Measures].[
Max Qtr2 Sales] AS '
Max ( {[Year].[Qtr2]}, [Measures].[Sales] )'
SELECT {} ON COLUMNS, {[Product].children} ON ROWS FROM Sample.Basic
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
You should see results similar to the following.
ex12.txt
.A named set is a set specification just like those you would define in the SELECT axes, except you define the sets in the WITH section of the query, and associate them with a name. This is useful because you can reference the set by name when building the SELECT section of the query.
For example, a named set called 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
The following functions loop through sets of data and perform search conditions that you specify, with results that you specify.
The following query returns all Market dimension members for which the expression IsChild([Market].CurrentMember,[East])
returns TRUE; in other words, 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 Technical Reference.
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 is shown next:
<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 the tuple to be eliminated.
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:
For more information, see the MDX section of the Technical Reference.
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 Analytic Services database outline are MEMBER_NAME, MEMBER_ALIAS, LEVEL_NUMBER, and GEN_NUMBER.
MDX in Analytic Services 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.
Properties can be used inside an MDX query in two ways. In the first approach, 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 will return 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 just 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 both 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
In the second approach, 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 of an MDX property in Analytic Services 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.
Analytic Services allows attribute dimensions with date types. The date type properties are treated as numeric properties in MDX. When comparing these property values with dates, you need to 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/1996. Since the property [Intro Date] is a date type, the TODATE function must be used to convert the date string "03-25-1996" to a number before comparing it.
SELECT Filter ([Product].Members, [Product].CurrentMember.[Intro Date] = TODATE("mm-dd-yyyy","03-25-1996"))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
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.
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
![]() |