How to create MDX expressions

According to the dimension and your business need, dimensions may be large.

You may want to limit the data shown in the report or input schedule.

You can create customized categories or subsets of dimensions for your report or input schedule.  Customized subsets are utilized to speed the selection of just the values necessary to view in reports or saved for later use.

There are two types of subsets:

Static– subsets that remain constant until the next selection is made in the editor, even when the dimension structure is modified.

  • Existing subsets will not reflect a new account until you request to see all elements for that dimension in the editor

Dynamic Subsets– subsets that do change and are updated over time or as the dimension is modified.

  • Are created by assigning an MDX expression to the subset.
  • The expression will run each time the subset is called by the TM1 server.
  • You can record actions performed in the Subset Editor in Architect to simplify the creation of an expression in a subset.
  • Use the Record Expression and Stop Recording from the Tools menu to capture actions for your expression.
  • You can type MDX statements in the editor.

What is MDX

As MDX (Multi-Dimensional eXpressions) is the query language used to define dynamic subsets.  MDX is an industry-standard query language for multi-dimensional databases like Planning Analytics, although Planning Analytics only supports a certain category of the entire language and adds a few unique features of its own as well.

When you define a subset using MDX instead of a standard static subset, Planning Analytics stores the MDX definition rather than the resulting set.

This means the definition – or query – is re-run every time a user accesses the subset – requiring no further action by the user or administrator.

If the database, specifically the dimension, has changed from the last time the subset was accessed, then you may get different results.

Syntax and Layout

When writing the MDX statement in Architect, you can break up a query into multiple lines to make it more readable and to correct any syntax errors, such as forgetting to close a parenthesis.

You can prefix the dimension name of specific members.  It is optional, if the element name Bikes is unique within the entire server, then you do not need to prefix it with the dimension name, Products.

For example:

  • {{[Products].[Bikes].CHILDREN}}

This query will return all children of the Bikes element, which is found in the Products dimension.

If the element is not unique and you do not use the dimension name as a prefix, you will receive an error, such as:

“Level or member name “[Retail]” ambiguous: found in dimensions…”

The use of square brackets can sometimes seem a bit arbitrary when reading examples of MDX queries.

The reason is due to an Online Analytical Processing OLAP object name.  For example, the cube name, dimension name, member name, must be enclosed in square brackets, only if it contains a space, starts with a number or is an MDX reserved word (e.g. Select).

It can be simpler to decide to always use brackets.  Similar, queries can be compared side by side more easily.

The exact definition of a member in Planning Analytics is almost always expressed as [Dimension Name].[Member Name] and no more.

MDX and Planning Analytics are not case sensitive.

Demonstration:  Creating a Dynamic subset

Objectives

In this demonstration, we will:

  • create and save dynamic and static subsets
  • utilize the “Record Expression” command to create a subset for all years for 201?
  • create a static subset of 201? Years.
  • run a TI process to add a new year for 2018
  • evaluate differences between a dynamic and static subset

Results after creating a dynamic subset

Results after running a TI Process to add a new year

Demonstration:  Step-by-Step

  1. Open Planning Analytics Architect.
  2. Expand and sign into a TM1 Server, such as AdventureWorks2017.
  3. Expand the server, Cubes, and double-click the Financials cube.

  1. Create the following view and save it as a public view called Profit and Loss:

  1. Click on the Period dimension to open the subset editor.
  2. From the View menu, select Expression Window.

  1. From the Tools menu, select Record Expression.

  1. From the toolbar, click the All

Important Note

The All button is the most important button to select when recording an expression because it searches through the TM1 server for the dimension you are viewing and retrieves all elements you can see based on your security privileges.

You can also have the ALL function appear, when you select a parent element and drill down or up the hierarchy, using other MDX functions.

  1. If necessary, select All Time and expand All Time.
  2. From the toolbar, click the Filter by Wildcard
  3. In the Expression box, type “201?”

  1. Click OK.

The results appear below:

Important Note

The ALL function is surrounded by the DRILLDOWNMEMBER function.  The MDX expression will first analyze if there are any new elements within the Period dimension.  Then, it will filter by wildcard for any periods that start with the characters ‘201’.

  1. From the Tools menu, select “Stop Recording”.

  1. Click Yes to attach the expression to the subset.

  1. From the Subset menu, choose Save As.
  2. In the Select or Enter Subset Name field, type “All Years”.
  3. Uncheck Private and make certain Save Expression is checked.

  1. Click OK.

Run a TI Process to add a year

  1. Navigate to the Server Explorer – IBM Cognos Planning Analytics Architect
  2. Expand Processes and select the Period – Add 1 Year to Time Dimension

  1. Right-click and choose Run.
  2. Expand the window and the Prompt Question column:
  3. In the Default Value column, make certain you modify the Default Value as what is pictured below:

  1. Click OK to run the process.
  2. Click OK on the Process Completed Successfully message window.
  3. Navigate back to the Profit and Loss
  4. From the toolbar, deselect Zero Suppression.

  1. Open the private Profit and Loss Static view and deselect Zero Suppression.

  1. Double click the Period dimension to open the Subset Editor.
  2. Pull down the Subset drop-down, select the All Years subset from the list.

The results appear below:

  1. Pull down the Subset drop-down, select All Years Static subset from the list.

Notice how one subset has the new year, while the other All Years Static subset does not contain 2018.  It is recommended that the administrator/developer of the model create public dynamic subsets for dimensions that change over time.

This concludes this demonstration.

Demonstration:  Summary

In this demonstration, we:

  • created dynamic and static subsets
  • after adding a new year by running an existing process, we compared the differences between the dynamic versus the static subset