Create Dynamic Asynchronous Views with MDX

Everyone who works with IBM Planning Analytics / TM1 is familiar with a cube view that you can create using a cube viewer. One column is generated for each element in the first dimension and then each of those columns are duplicated for each element in the second stacked dimension. As a result, if you stack more than 2 dimensions, the number of columns will grow exponentially. For example, if you want to stack 2 years, 3 versions, and 13 periods you will end up with 78 columns where you might only be interested in column 2 and 13. This creates a challenge in terms of visibility and usability as you strive to get a better grasp of your data.

>However, did you know that you can select, for each column, the combination of elements which are most relevant. This is called building an asynchronous view where each column and row has a unique combination of elements:

You can even take this one step further by creating a dynamic asynchronous view which means that the elements in the rows and columns will be driven by values in a cube. In this article, we’ll show you just how easy it is to create these Dynamic Asynchronous Views with MDX.

Asynchronous Views are very helpful for multiple different use cases, whether they are used in Excel with MDX Forms using Slice (Click here for more information on MDX forms) or used directly in the ARC cube viewer itself. The main advantage is that each column can have its own combination of elements. As you can see in the example below, column 1 could be (1, 2017, Year) and column 3 could be (vs, 2017, Year):

An asynchronous view needs to be built with MDX. Here is an example on how you can ‘stack’ the columns in an asynchronous way (We are using the MDX Lab that you can find in Arc to edit the MDX):

Column 1 – Getting the value for actuals, for the total year 2022

We just need to define in MDX the three elements which will be displayed in the column:

({[Version].[Version].[Actual]} * {[Year].[Year].[2017]} * {[Period].[Period].[Year]})

### Column 2 – Getting the value for budget, for the total year 2022 ###

({[Version].[Version].[Budget]} * {[Year].[Year].[2022]} * {[Period].[Period].[Year]}),

You can find the complete MDX below:

A dynamic asynchronous view is driven by global variables. The objective here is to ensure consistency across your user experience by using system-defined elements within your TM1 model. A dynamic asynchronous view brings the following benefits:

• Customise the user experience with minimal maintenance required from TM1 administrators.
• Handle multiple scenarios in theme view whilst adding leaf elements for data input.
• Define your columns irrespective of the element type (N or C level) in the dimensions.
• All other dimension elements can be pre-set with dynamic subsets or remain static.

In the example below, we will introduce a TM1 System Information cube to fetch the corresponding elements into the query. The global variables will be stored in a simple cube called System Info:

To retrieve this value in our MDX, we will use the StrToMember MDX function in our query (Click here for more information) to replace static elements such as the year. For example, to get the value 2018 we’re going to use the following MDX:

{StrToMember(“[Year].[Year].[” + [System Info].([System Info Parameter].[Financial Year],[System Info Measures].[String]) + “]”)}

You can insert your MDX query in the Arc Cube Viewer by just clicking the MDX expression icon.

So, our columns should now look like the following:

Column 1 – Getting the value for actuals, for the total year 2022

{({[Version].[Version].[Actual]} * {StrToMember(“[Year].[Year].[” + [System Info].([System Info Parameter].[Financial Year],[System Info Measures].[String]) + “]”)} * {[Period].[Period].[Year]}),

Column 2 – Getting the value for budget, for the total year 2022

({[Version].[Version].[Budget]} * {StrToMember(“[Year].[Year].[” + [System Info].([System Info Parameter].[Budget Year],[System Info Measures].[String]) + “]”)} * {[Period].[Period].[Year]}),

Please find the full MDX below:

Remember, you can use the StrToMember MDX function in conjunction with any TM1 cube that holds string parameters in your TM1 model to make MDX views dynamic. In addition, you also have the MDX Lab to make it easier to develop and test these sorts of queries.

Once you’re happy with your MDX, you can easily add it to an MDX Form as shown below:

By simply editing the value in the cube, the report gets updated the next time you refresh it:

This is a powerful feature that can make a big difference when you’re working with highly complex datasets. Leverage it and you can immediately transform how you process, analyse, and interpret your data.

• How to Get the Most out of MDX Forms
• Unpacking 3 new features of the DBRW in Slice
• Getting started with Slice in one minute