MDX-7 Calculated Members in MDX and Relative member functions

Calculated Members:

Calculated members, in short, allow us to define new members, based upon dimensions or measures that exist within the cube in which we create the calculated member. They are members whose values depend on an expression rather than the value of a cell within a cube. The potential uses of calculated members are limited only by the imagination and experience of the developer

To create a calculated measure, we will use the WITH clause, the syntax for which might resemble the following:

WITH MEMBER dimension.name AS 'Expression'

Dimension represents the dimension into which we are creating the new member. 

Name is my shorthand for the name of the member we are creating, and

expression is the expression from which it is created.

WITH 
    MEMBER [Measures].[Warehouse Margin] AS
    '[Measures].[Warehouse Sales]-[Measures].[Warehouse Cost]'
 
 SELECT
 
    {([Time].[Quarter].Members)} ON COLUMNS,
    {([Warehouse].[Country].USA)} ON ROWS
 
 FROM Warehouse
 
 WHERE ([Measures].[Warehouse Margin])

As we can see above, the WITH section contains the definition of the calculated member Warehouse Margin. The newly created Warehouse Margin is a member of the Measures dimension, and is composed of the total of Warehouse Sales less Warehouse Cost, intersecting all members of the remaining dimensions in the cube, but displaying the combined intersects with theUSA warehouses for purposes of our query. 

The returned dataset, therefore, represents theWarehouse Profit (which we called Warehouse Margin in our query) for the USA warehouses, by quarter (for the years 1997 and 1998, as stored in the Warehouse sample cube).

.CurrentMember, .PrevMember and .NextMember are the relative members:

.CurrentMember is actually the default operator, and its specification is, therefore, optional.

At any given point in the execution / evaluation process, the coordinates at which the process "currently" rests is made up of eachdimension in the cube and a current member (dimension1.CurrentMember, dimension2.CurrentMember, dimension3.CurrentMember, and so on).

Syntax
Let's look at an example to familiarize ourselves with the syntax.
 WITH 
    MEMBER [Measures].[Warehouse Margin] AS
    '([Time].CurrentMember , [Measures].[Warehouse Sales])-   
        ([Time].Currentmember, [Measures].[Warehouse Cost])'
 
 SELECT
 
    {(Time.CurrentMember)} ON COLUMNS,
 
    {([Warehouse].[Country].USA)} ON ROWS
 
 FROM Warehouse
 
 WHERE ([Measures].[Warehouse Margin])
The result dataset returned would appear as shown in Illustration 2 below:

MDX Part-5("Family" Member Functions)

I like to call one specific subset of the member functions and operators the "family" functions, because many of their names resemble those of family members. The functions that compose this group include:

• .Parent
• .Children
• Ancestor()
• Cousin()
• .FirstChild
• .LastChild
• .FirstSibling
• .LastSibling

The "family" metaphor is appropriate, because these functions perform operations on dimensions, hierarchies, and levels in a manner that mirrors movement about a "family tree." There are other "family" functions that return sets, but we will focus primarily on the member functions in this segment of the series.

The "family" functions, like other member functions, belong to two general groups, from the perspective of the result datasets they return. 

One group works within a "vertical" scope, traveling up or down between hierarchical levels, as we will see in the respective Practice section for each function. Examples include the following functions:

• Ancestor()
• .Children
• .Parent
• .FirstChild
• .LastChild.

The second general group of "family" functions operates within a "horizontal" scope of the hierarchy involved. These functions travel within the same level of the hierarchy ("across" versus "up and down"), and include:

• .Cousin()
• .FirstSibling
• .LastSibling

As we have intimated, the capability to perform operations within the vertical and horizontal scopes of the hierarchy can mean more efficient, simpler MDX queries. We will take a look at the "family" functions individually to obtain a good understanding of their workings in the sections devoted to each that follow.

-------------------------------------------------##-----------------------------------------

.Parent() Function:

The .Parent function returns the parent of a specified member using the syntax we describe below. The function is especially useful in calculated members.

The .Parent function is appended to the right of the member, as in the following illustration:
<member>.Parent

SELECT
{[Time].[Year].members} ON COLUMNS,
{[Booker].Parent} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])

Perhaps a look at the hierarchy for the source member (Booker) for the .Parent function above will make the illustration more meaningful. In fig above, we see that Booker is a member of the Milk sublevel of the Drinks level, within the Product Family hierarchy of the Product dimension.

Example:

Let's begin with a basic query to extract total Frozen Foods Units Shipped for 1997 and 1998 (the only years in our cube).

SELECT
{[Time].[Year].Members} ON COLUMNS,
{[Product].[Product Family].[Food].[Frozen Foods]} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])

A quick look at the hierarchy illustrates the relationships between the members and levels under consideration, as shown below:

SELECT
{[Time].[Year].Members} ON COLUMNS,
{[Product].[Product Family].[Food].[Frozen Foods].Parent} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])

The Results pane appears as shown below.
The result dataset displays the totals at the Food level, which is somewhat obviously the Parent of the Frozen Foods level.
We see that we have now obtained a summary at the Food level for the Units Shipped for the years 1997 and 1998, because we affixed the .Parentfunction to the Frozen Foods level.
The .Children Function:

Much like the .Parent function, the .Children function works within a "vertical" scope, moving, down between hierarchical levels from the member to which the function is applied. As its name implies, the .Children function returns the children of the source member, using the syntax we describe below. Again resembling the .Parent function, the .Children function is especially useful in calculated members.

The .Children function is appended to the right of the member, as in the following illustration:
<member>.Children
A simple illustration of the .Children function, using our first example above and replacing the.Parent function with the .Children function, follows:
 
SELECT
{[Time].[Year].members} ON COLUMNS,
{[Booker].Children} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])
The result dataset returned would appear as shown in Illustration 6 below:
 
Referring again to the hierarchical structure within which our source member lies, we can see that the .Children function has moved in the opposite direction to the .Parent function above, as depicted in the illustration below:



The Ancestor() Function:
The Ancestor() function retrieves the member, at the specified level, or at the specified distance,that is the ancestor .Like the .Parent and .Children functions, it travels within a "vertical" scope, movingup between hierarchical levels from the member to which it is applied.


The Ancestor() function is applied differently than the .Parent and .Children functions, in that it is not "appended" to the source member. The source member is placed within the parentheses to the right of the word Ancestor, as shown in the following illustration:

Ancestor(member, level)
Or
Ancestor(member, distance)

SELECT
{(Measures.[Warehouse Sales]), (Measures.[Warehouse Cost])} ON COLUMNS,
{(Ancestor([Time].[Year].[1997].[Q2].[4], [Time].[Quarter]))} ON ROWS
FROM [Warehouse]
The result dataset returned would appear as shown in the illustration below:

SELECT
{(Measures.[Warehouse Sales]), (Measures.[Warehouse Cost])} ON COLUMNS,
{(Ancestor([Time].[Year].[1997].[Q2].[4], 2))} ON ROWS
FROM [Warehouse]

MDX Part-3(MDX Members and member function)

Members represent an important Concept in an understanding of MDX. A member is, simply, an item in a dimension; members compose the values of the attributes that belong to a dimension. Keep in mind that measures are themselves dimensions, and are composed of members. 


MDX contains a set of functions, known as member functions that enable us to perform operations upon any member of a dimension. Member functions return a member or a zero. 


member functions allow us to perform operations, based upon a member's relative position, either up ("above" the member upon which the function is being performed) or down ("below" the member upon which the function is being performed) a dimensional hierarchy.


Working with Members and the .Members Operator:

SELECT
 [Measures].Members ON COLUMNS,
[Department].Members ON ROWS
FROM [HR]

All members of the Measures dimension (as columns);
All members of the Department dimension (as rows).

It is important to remember that the .Members operator must be applied at a level within a dimension where hierarchy is not ambiguous. If multiple hierarchies exist within a dimension, we must apply the .Members operator at or below the level of the "split," in a manner of speaking.
 If we attempt the operation we performed above, and multiple hierarchies exist within the dimension selected , the "membership" we are requesting is not precise, and our attempt will end with an error.

.Members operator is appended to the right of the level, hierarchy, or dimension

[Product].Members

[Product].[Product Family].Members
Note:
It is important to be aware that Calculated Members will not appear in the result dataset returned by the .Members function.
The.AllMembers function exist to include Calculated Members in our result datasets.

 Example 1:Let's say that an information consumer in the Human Resources department wants to know total organizational salaries for 1997 and 1998 (the two years captured in our corporate HR cube - and therefore the full "membership" of the Year level of the Time dimension).

Type the following simple query into the Query pane:
-- MDX04-1:  Tutorial Query No. 1

SELECT

{ [Time].[Year].Members } ON COLUMNS,

{ [Measures].[Org Salary]} ON ROWS

FROM [HR]
Example 2:
SELECT
  { [Measures].[Units Shipped], [Measures].[Units Ordered] } ON COLUMNS,

[Store].Members ON ROWS

FROM [Warehouse]   -- Remember to switch to the Warehouse cube!
The Results shown below.
The result dataset contains many empty members.
Empty cells occur in MDX statements when data for the intersection of multiple dimensions does not exist.
eliminate the empties, by adding the NON EMPTY keyword as follows:
SELECT
  { [Measures].[Units Shipped], [Measures].[Units Ordered] } ON COLUMNS,

NON EMPTY [Store].Members ON ROWS

FROM [Warehouse]   -- Remember to switch to the Warehouse cube!

MDX Part-3(MDX Operators:curled braces "{}", commas "," and colons ":")

Introduction to Basic Operators: 

curled braces "{}", commas "," and colons ":"

Curled Braces:

The way that MDX distinguishes sets: it surrounds members or tuples with curly braces (the { and } characters.) We are required to surround tuples with braces anytime the tuples are explicitly listed.

MDX uses the brace characters to "package" a set of components from a single dimension or a set of dimensions. The following example query makes use of the curly braces to specify the results we wish to see:

SELECT 
    {[Measures].[Units Shipped]} ON COLUMNS, 
    {[Store].[Store State].[CA], 

[Store].[Store State].[OR],

[Store].[Store State].[WA]} ON ROWS
FROM [Warehouse]

The expression above requests the total units shipped for the stores, as summarized specifically for the states of California, Oregon and Washington. It does this by selecting three single members of the Store dimension, the states in which they reside, and returns them on the rows of a result set that might be represented as in Table 1 below.

 Type the following simple query into the Query pane:

-- MDX03-1:  Tutorial Query No. 1
SELECT
{([Store].[All Stores].[USA],[Product].[All Products])} ON COLUMNS,
{([Time].[1997])} ON ROWS
FROM Warehouse
WHERE ([Measures].[Units Shipped])

Commas:

The comma operator separates tuples forming a set, particularly when we find it difficult to practically define a set using a range of component members (more on ranges later).

 MDX uses the comma operator to separate tuples, which define a slice of data from a cube. (Tuples are composed of an ordered collection of one member from each of one or more dimensions)

Example:Let's say that we have been asked to generate several measures for the California warehouse group by management for purposes of evaluating aggregate performance of the warehouses over the 1997 / 1998 measurement periods. These seven measures, used as critical success factors for the California warehouse group manager, are as follows:

• Store Invoice
• Supply Time
• Warehouse Cost
• Warehouse Sales
• Units Shipped
• Units Ordered
• Warehouse Profit

SELECT
  {[Measures].[Store Invoice],[Measures].[Supply Time],
      [Measures].[Warehouse Cost],[Measures].[Warehouse Sales], 
      [Measures].[Units Shipped],[Measures].[Units Ordered], 
      [Measures].[Warehouse Profit]} ON COLUMNS,
  { [Time].[1997], [Time].[1998] } ON ROWS 
FROM Warehouse
WHERE
([Warehouse].[All Warehouses].[USA].[CA])

Colons:
The colon operator provides us a means of leveraging the natural order of members to create a set. Order is important, because the levels within a dimension house their respective members either in member name or member key order. We can take advantage of the order of the members, and define sets based upon ranges within the order, by using the colon operator.

Example:
We can illustrate the syntax within which a colon operator is used with the following example, excerpted from our practice exercise in the immediately preceding section. The set of members specified in the set:
{[Measures].[Store Invoice],[Measures].[Supply Time],  [Measures].[Warehouse Cost],
[Measures].[Warehouse Sales],[Measures].[Units Shipped],
 [Measures].[Units Ordered],[Measures].[Warehouse Profit]}
can be retrieved with the following syntax:
{ [Measures].[Store Invoice]: [Measures].[Warehouse Profit]}
provided that the range specified by the colon takes into account the natural order of the members. Let's verify this point by putting it into action in the following exercise.

MDX Part-2(Tuples,Axes, and Sets)

Tuples, Axes, and Set are the  most basic and most common components in the MDX.


Tuples:A tuple is a collection of members, each of which is selected from a different dimension.
For example: (Sales, 1997) conceptually represents a tuple that is composed of members that might exist in an OLAP cube from two dimensions: measures (recall that measures are yet another dimension from a syntactical perspective) and time. 


The tuple (Non-Consumable, Sales, 1997) is composed of members of three different dimensions: product, measures, and time. The tuple is the basic unit for forming an axis, which we will discuss below.


A tuple can be composed of any and all dimensions, including,measures dimension.An important thing to remember when considering tuples is that, while they can be composed of one or more members from a number of dimensions, only one member from each dimension can exist in a given tuple.


[Dimension].[Member]------->or {[Dimension].[Member]}--->Single Member / Single Dimension


 Single members can be enclosed within parentheses, but are not required to be.


With square brackets "[ ]" serving as delimiting characters, MDX uses tuples to identify cell "addresses." 


Any cell value can be mapped using a tuple made up of one distinct member from each of a cube's dimensions. We can project dimensions to columns, rows, pages and other axes . Every intersection of multiple tuples results in yet another tuple.


Axes:
An axis is a group, or collection, of members from one or more dimensions, organized as tuples. 

An illustration might be a query that returns head count, by pay type and month, for a given department. In this case, three axes could be used in presenting the result of the query, with an axis representing each of the three dimensions (Pay Type, Time and Department).

([Time].[1998],[Department].[All Department].[HQ Finance and Accounting])} ON COLUMNS, 
{[Pay Type].[Pay Type].Members} ON ROWS

The ON COLUMNS and ON ROWS statements indicate the designated axes for the sets they follow.

A point we need to understand is that the ON COLUMNS and ON ROWS terms are aliases for axis names. why a single-axis report must include a COLUMNSaxis, but not a ROWS axis.

According to the documentation  a query can specify up to 63 axes, for which alias names exist only for the first few. Suffice it to say, however, that it is rare to use more than a couple of heading axes in a standard reporting scenario.

SET:

The set is an important component of MDX syntax. Sets are typically enclosed by curled "{ }" braces and often appear in the SELECT part of a query.

A set typically consists of a group of one or more tuples.

 The order of the tuples is significant, and the same tuple can be repeated in an MDX set. 

Braces are often required to make the syntax work correctly, but can sometimes be optional.

In above MDX we specified only one axis. Anytime we use single tuples, such as we do in specifying the "x" axis above (our only axis), we enclose the tuples in curled braces to signify that they are tuples contained within a set

Example: Suppose wants to provide total headcount for the years 1997 and 1998 for the entire company, but this time he wants us to break out the current pay types to show composition of the workers in our totals. Again, we are to provide the information in a two-dimensional grid, with the years as column headings. He states that he wants to see the pay types in the row.

SELECT {([Time].[1997]), ([Time].[1998])}ON COLUMNS, {[Pay Type].[Pay Type].Members} ON ROWS FROM HR WHERE ([Measures].[Count])

MDX Part-1

MDX emerged circa 1998, when it first began to appear in commercial applications. MDX was created to query OLAP databases


You can use Multidimensional Expressions (MDX) to query multidimensional data or to create MDX expressions for use within a cube.

Cube is organized based on measures, dimensions, and dimension attributes.


The output of an MDX query has the form of a cube itself.The query captures both the output specification and the dimensionality of the resulting cube.

Example1: We are asked information consumer to provide the total sales and total cost amounts for the years 1997 and 1998 individually for all USA-based stores (including all products). We are asked, moreover, to provide the information in a two-dimensional grid, with the sales and cost amounts (called measures in our data warehouse) in the rows and the years (1997 and 1998) in the columns.

1. Type the following query into the Query pane:
   

   --MDX01-1:  Basic Query
   SELECT
   {[Time].[1997],[Time].[1998]}ON COLUMNS,
   {[Measures].[Warehouse Sales],[Measures].[Warehouse Cost]}  ON ROWS
   FROM Warehouse
   WHERE  ([Store].[All Stores].[USA])
   

The diagram below labels the various parts of the query:

1. Two dashes (--) represent one of three typical ways to place a comment in MDX syntax, so that it is ignored when the MDX is parsed.


2.The cube that is targeted by the query (the query scope) appears in the FROM clause of the query. The FROM clause in MDX works much as it does in SQL.


3.The cube that is targeted by the query (the query scope) appears in the FROM clause of the query. The FROM clause in MDX works much as it does in SQL.


4. The output of an MDX query, which uses a cube as a data source, is another cube, whereas the output of an SQL query (which uses a columnar table as a source) is typically columnar.


5.It is important to realize that MDX's cube output allows us to place any dimension from the source cube onto any axis of the query's result cube. Many axes can exist, and it is often better to think in terms of "axes" than in "dimensions"  when designing an MDX query.


6.A query has one or more axes. The query above has two. (The first three axes that are found in MDX queries are known as rows, columns and pages.)


7. Keep in mind that columns always come before rows, and rows always precede pages, within the query.


8.Curled brackets "{}" are used in MDX to represent a set of members of a dimension or group of dimensions. The query above has one dimension each on the two query axes. The dimensions that appear are the Measures and Time dimensions.


9.We can display more than one dimension on a result axis. When we do this, an "intersection" occurs, in effect, and each cell appearing in the associated axis relates to thecombination of a member from each of the indicated dimensions. When more than one dimension is mapped onto an axis, the axis is said to consist of "tuples," containing the members of each of the mapped dimensions. 


We see the results below

Example 2:
Consumers have asked for a comparison between the total US warehouse sales for the first and second quarters of 1997.

SELECT

{[Time].[1997].[Q1],[Time].[1997].[Q2]}ON COLUMNS,

{[Warehouse].[All Warehouses].[USA]}  ON ROWS

FROM Warehouse

WHERE  ([Measures].[Warehouse Sales])

Because we have specified the Warehouse Sales measure in the WHERE statement, we have made it the slicer dimension. The slicer shows that we have picked only the Warehouse Sales measure from the measures dimension

The Basic MDX Query

A basic Multidimensional Expressions (MDX) query is structured in a fashion similar to the following example:

SELECT [<axis_specification>

       [, <axis_specification>...]]

  FROM [<cube_specification>]

[WHERE [<slicer_specification>]]

·         The breakdown of the <axis_specification> syntax is:

·         <axis_specification> ::= <set> ON <axis_name>

·         <axis_name> ::= COLUMNS | ROWS | PAGES | SECTIONS | CHAPTERS | AXIS(<index>)

·         Each axis dimension is associated with a number:

0 for the x-axis,

1 for the y-axis,

2 for the z-axis, and so on.

The <index> value is the axis number. For the first 5 axes, the aliases

COLUMNS------------------- AXIS(0)

ROWS ----------------------- AXIS(1)

PAGES----------------------- AXIS(2)

SECTIONS------------------- AXIS(3)

CHAPTERS------------------ AXIS(4)

An MDX query cannot skip axes.

Example1:A query cannot have a ROWS axis without a COLUMNS axis, or have COLUMNS axes without a ROWS axis.

·         However, you can specify a SELECT clause with no axes (that is, an empty SELECT clause). In this case, all dimensions are slicer dimensions, and the MDX query selects one cell.

 Basic MDX Syntax - SELECT Statement(SELECT and WHERE clause is mandatory):

In MDX, the SELECT statement is used to specify a dataset containing a subset of multidimensional data.

 To specify a dataset, an MDX query must contain information about:

• The number of axes. You can specify up to 128 axes in an MDX query.

but very few MDX queries will use more than 5 axes.

• The members from each dimension to include on each axis of the MDX query.
• The name of the cube that sets the context of the MDX query.
• The members from a slicer dimension on which data is sliced for members from the axis dimensions.

This information can be complex. As you will see in this topic, MDX syntax can provide such information in a simple and straightforward manner, using the MDX SELECT statement.

Basic MDX Query Example: MDX SELECT statement syntax:

SELECT

   { [Measures].[Unit Sales], [Measures].[Store Sales] } ON COLUMNS,

   { [Time].[1997], [Time].[1998] } ON ROWS

FROM Sales

WHERE ( [Store].[USA].[CA] )

The basic MDX SELECT statement contains a SELECT clause and a FROM clause(SELECT and FROM clause are mandatory)  WHERE clause is optional.

We can also write above query in this way

·        SELECT

·           { [Measures].[Unit Sales], [Measures].[Store Sales] } ON AXIS(0),

·           { [Time].[1997], [Time].[1998] } ON AXIS(1)

·        FROM Sales

·        WHERE ( [Store].[USA].[CA] )

The SELECT clause determines the axis dimensions of an MDX SELECT statement.

The FROM clause determines which multidimensional data source is to be used

The WHERE clause optionally determines which dimension or member to use as a slicer dimension; this restricts the extracting of data to a specific dimension or member.

The MDX query example uses a WHERE clause to restrict the data extract for the axis dimensions to a specific member of the Store dimension.

The syntax format of the MDX SELECT statement is similar to that of SQL syntax; however, you will note several obvious differences:

• MDX syntax distinguishes sets by surrounding tuples or members with braces (the { and } characters.)
• In the MDX query example, the COLUMNS and ROWS axis aliases are used. The MDX query could also have been written in the following fashion, using the ordinal position of each axis:

·        SELECT

·           { [Measures].[Unit Sales], [Measures].[Store Sales] } ON AXIS(0),

·           { [Time].[1997], [Time].[1998] } ON AXIS(1)

·        FROM Sales

·        WHERE ( [Store].[USA].[CA] )

• As with an SQL query, the FROM clause names the source of the data for the MDX query. However, unlike an SQL query, the FROM clause in an MDX query is restricted to a single cube.