This series will introduce you to MDX views in Planning Analytics. It is aimed not only at those that have not worked with MDX views but also at those who may want to learn some additional skills to fully benefit from the power MDX views bring to Planning Analytics.

What is MDX

Multidimensional Expressions (MDX) is a powerful query language used in TM1/Planning Analytics to not only select members, tuples and sets for dimensions but also to retrieve data from the cubes.

MDX allows users to delve into their data to facilitating insights into sales trends, comparisons between actuals and budgets as well as ranking and showing top and bottom performers.

MDX views are used not only to show tabular data in the classic 2 dimensional matrix but is a powerful and versatile tool for reporting and visualisations like charts.

What is the difference between Tuples and Sets

Most TM1/Planning Analytics developers and advanced users would have been exposed to MDX whether through Architect/Perspectives when recording expressions or opening dynamic sets where someone else has created a set containing MDX.

A Tuple is an ordered collection of one or more members from different dimensions and refers to a single data point in a cube e.g.

[Sales].([Scenario].[Actuals], [Period].[2021-JUL], [Customer].[ADX001], [Product].[600394], [Sales Measures].[Units])

Alternatively, as a view in Planning Analytics Workspace (PAW) cube viewer:

Tuples are typically enclosed in round brackets with the member references within the brackets pointing to the cell. Think of the tuple as the address of the cell.

Sets are a collection of members in a single or the same dimension. These could be numeric, consolidated or string members, or any combination of these.

A set is typically collection of one or more members from the dimension to be used for a particular purpose e.g. the current period, live planning scenarios, top n products based on sales revenue for a given period etc.

It is possible to have sets with no members. These may not be useful for viewing data but may be of use when comparing two sets of member for example to look for members in one set but not in the other. An example of this may be where you have multiple rollups and need to ensure that all members are part of each.

An example of a set of related Sales Measures is defined as the following in my model:

{ [Sales Measures].[Sales Measures].[Units], [Sales Measures].[Sales Measures].[Sales Revenue], [Sales Measures].[Sales Measures].[Cost of Sales] }

When looking at the above members save to my model as a set called Monthly Reporting, we see the following in the PAW set editor:

Note that PAW adds the braces ( {…} )when you save the set and are not shown in the above example but should be there to make a valid set in MDX.

What are the differences between Sets and Views

We have seen that Sets contain zero or more members in a specific dimension and a Tuple relates to a specific cell but what then is a view?

A view is simply one or more Tuples or cells of data, a collection of cells if you want, or cell sets. For the most part users work with cell sets in a 2 dimensional view, in an X and Y style matrix. This is analogous to sheets in Excel where members may be listed on the rows and columns and at their intersection, a value is seen.

In TM1/Planning Analytics, this goes further as we are dealing with cubes where we typically work with cubes that contain more than 2 dimensions. To represent the cells within a cube in a 2 dimensional world, we need to query the data using an MDX expression.

The query allows us to define what we would like to see on the rows, columns and in the context area which is used as a filter on specific members. Dimensions are not limited to one on rows and another on columns but can be nested as required.

In Architect and Perspectives, users typically opened a native view and then dragged dimensions between the rows, columns and context areas, nesting and stacking dimensions as needed. Once a suitable view was obtained, that view could be saved and reused at a later date.

If the user reopened their view and any dimensions were linked to sets that were dynamic or based on MDX, the MDX would update and potentially the user would see different results. If for example the view was linked to a set on the Period dimension called Current Period and this contained the current month, opening the view and thus set in the following month, one would expect to see the results differ due to this change.

An MDX View is similar to the native view with respect to being able to save and reopen these at a later date. Both can be linked to sets, dynamic or static, ensuring that when the view is reopened, members shown within each dimension are updated.

MDX and native views are similar in many respects e.g. being able to save them, alter them, use them as sources for imports, exports etc.

MDX views however give some more flexibility that native views do not:

1)      Calculations can be added into MDX views to create new derived members e.g. a variance between Actual and Budget or a Gross Margin %, where these do not exist in the cube as members and are only required for analysis. More on this later but you can also add data from other cubes e.g. attributes or add in synthetic members that are notes or whitespace.

2)      Additional filtering could be added to a base set being used in the view with additional MDX around the underlying set. Using a native view would require you to create a new set and save it.

3)      Asymmetrical views can be thought of as unbalanced. Where you have multiple dimensions nested, typically members from the first dimension are shown then members from the next dimension are repeated for each member in the first dimension.
Asymmetrical views allow you to choose which member combinations you would want to see with repeating or showing irrelevant members and values to users.

4)      MDX views can be programmatically generated using MDX code. Native views require assigning sets to each dimension and then assigning those dimensions and sets to rows, columns and context areas.

5)      View ordering can be configured by specifying the necessary MDX to nest the dimensions on rows, column and context. When extracting data from an MDX view, this order is retained whereas extracting from a native view, the cube's dimension order is used. More on this in the future.

Constructing a basic MDX view

A basic MDX view typically has the below syntax with one on columns, another on rows, a cube directive and a filter relating to the members in each of the other dimensions to be used in the view:

SELECT
{Set 1} ON 0,
{Set 2} ON 1
FROM [cube]
WHERE <filter tuple for other dimensions>

It is possible as mentioned previously to nest dimensions on columns and rows but we will look at this in later examples.

The MDX view relating to my earlier Sales view with my Units sold in 2021-JUL would be the following:

SELECT
NON EMPTY {[Sales Measures].[Sales Measures].[Units]} ON 0,
NON EMPTY {TM1SubsetToSet([Product].[Product],"All Products","public")} ON 1
FROM [Sales]
WHERE (
  [Scenario].[Scenario].[Actual],
  [Period].[Period].[2022.FY^2021-JUL],
  [Customer].[Customer].[ADX001]
)

Note in the above that NON EMPTY is added in my view to suppress zeroes on both columns and rows.

For those curious as to what the ON 0 and ON 1 relate to, these are the various axes. They are beyond the scope for now but we may look at their application in further segmenting views in later articles.

MDX supports up to 128 axes but anything greater than 4 is just referred to by number e.g. Axis(5)

Nesting dimensions in an MDX View

Nesting dimensions allows you to see combinations of members from two or more dimensions, on the same axis. If I wanted to look at the quantity of Products sold to Customers, I would need to nest Products within Customer. Using my example above, this would be on rows, or axis 1.

The MDX is similar to what we had previously but instead of having one Customer selected within the filter tuple, we now have a set of Customers on the rows with Products nested:

SELECT
NON EMPTY {[Sales Measures].[Sales Measures].[Units]} ON 0,

NON EMPTY {TM1SubsetToSet([Customer].[Customer],"All Customers","public")} *   {TM1SubsetToSet([Product].[Product],"All Products","public")} ON 1

FROM [Sales]
WHERE ([Scenario].[Scenario].[Actual], [Period].[Period].[2021-JUL])

The above MDX would return the below cube view:

To enter MDX or change the MDX, simply click on the MDX button on the toolbar.

Nesting is simply seen as multiplying one set by the other, similar to a cross join combining members from both sets to create a Cartesian product. However, this is only a basic guide and once you introduce roll-ups/hierarchies into the sets, you may need to think along branches or tree structure, especially where you are suppressing zeroes.

Similarly, we may want to see the quantity sold, revenue and cost of sales for each Period and each Product. The following MDX nests the Sales Measures within the Periods:

SELECT
NON EMPTY {TM1SubsetToSet([Period].[Period],"_S-Current Period","public")} *
{TM1SubsetToSet([Sales Measures].[Sales Measures],"Monthly Reporting","private")} ON 0,

NON EMPTY {TM1SubsetToSet([Product].[Product],"All Products","public")} ON 1

FROM [Sales]
WHERE ([Scenario].[Scenario].[Actual], [Customer].[Customer].[ADX001])

The cube view below shows the value for each Products and for each Sales Measure within each Period.

Below is a summary of the MDX keywords used in this article:

In the next part we will look at adding calculated or derived members to our MDX views.

Please let me have your feedback so that it helps set the direction for future articles. 

Also, I have tried to keep things simple rather than rewriting textbooks.If there is something not clear or accurate, please let me know so that I can address this.

Future topics include but not limited to: Read-only values in views, Ranking results, Joining or stitching views, Notes or commentary alongside values, Sorting and sequencing a views for export or processing, Query Scoped Members.

See Part 2 for working with Calculated Members.

Further Reading

MDX Query Fundamentals

TM1/Planning Analytics - MDX Reference Guide

Working with Time Related MDX Functions

Discovering MDX Intrinsic Members

Some of the above may require you logging in to the IBM Community site to read.

#IBMChampion