Excel, TM1 and soccer

MDX statements

August 29, 2017

Introduction

MDX statements are what SQL queries are to relational databases, but instead acting on OLAP (multidimensional)
databases. In the case of TM1, we will use them to query a dimension and its members. It is possible to
make a set of dimension elements based on filters. Or just the lowest level elements. Or … a lot is
possible. Read this high-quality overview
for more details. In addition, this Microsoft link is interesting too:
click here.

MDX use cases

As a TM1 developer, we mainly use MDX statements in the following cases:

to make dynamic subsets rather than static subsets. For example, a cubeview showing
the sales of the customers who bought last month as well (and only those customers);

to create custom views in a TI process. We will often zero out (part of) a cube, this is clearing
cells at the lowest level elements in certain dimensions. This list of elements can be generated with
no need to loop through the dimension ourselves.

Active forms in Excel have dynamic row subsets, you can expand and contract consolidations. The list
of elements in the rows can again be an MDX statement, even quite complex statements.

Mind the syntax, it is crucial to get that straight. If not, TM1 won‘t like it… Some of the
statements can be generated in the Subset Editor: turn on the Properties Window (menu View), then choose
Tools > Record Expression. Record the expression while you do the steps manually. Stop recording when you are
finished.

Often used MDX statements

Below, I will present the MDX statements I need most of the time. This page will therefore
serve as a kind of code library: we can copy/paste and adjust only dimension names and element names. You will
notice [] around dimension names and element names. They could be left out, unless (for instance) the name
contains a space. Then we must enclose the name with []. The expressions are case-insensitive.
Except for function names, the expressions are also spase-insensitive.

1 hard-coded element (dimension name and element name follow):{[Project_type].[All types]}or:{[FIN_Year].[2016]}

In a TI process, we often use a parameter for the year or scenario:SubsetCreatebyMDX('Subset_Years', '{[FIN_Year].[2016]}');becomes:SubsetCreatebyMDX('Subset_Years', '{[FIN_Year].['|pYear|']}');

The function EXPAND in Turbo Integrator can be used to change variable names to their values:
SubsetCreatebyMDX('Subset_Years', EXPAND('{[FIN_Year].[%pYear%]}'));

An element and its descendants all the way down the hierarchy, but omitting level 0:{Except( Descendants([Customer].[Total Customer]), {TM1FilterByLevel( Descendants([Customer].[Total Customer]), 0 )} )}

Leaf-level descendants of a given consolidated member, that consolidation included:{Union( {FIN_Account.[EBIT]}, TM1FilterByLevel( Descendants(FIN_Account.[EBIT]), 0) )}or:{FIN_Account.[EBIT], TM1FilterByLevel( Descendants(FIN_Account.[EBIT]), 0) }Note: The Union function can be shortened to the + operator (an element is listed only once):{FIN_Account.[EBIT]} + {TM1FilterByLevel( Descendants(FIN_Account.[EBIT]), 0)}or, now EBIT is at the bottom of the list, the Order of the elements in the expression is retained left to right:{{TM1FilterByLevel( Descendants(FIN_Account.[EBIT]), 0)}, FIN_Account.[EBIT] }

Next to Union, Intersect is also supported.
Sidenote: if you want to avoid the removal of duplicate elements, use a comma instead of a plus sign. Example:
[Fct_Product].[A], [Fct_Product].[B], [Fct_Product].[C], TM1FilterByLevel( Descendants([Fct_Product].[C]), 0)}

The immediate children (leaf level or consolidated) of a given element:{[Project_type].[All Types].Children}

An element together with its immediate children (leaf level or consolidated):{DrilldownLevel( {[Project_type].[All Types]} )}

Sometimes we require a consolidated element with all its Descendants, together with a different consolidation with only that one's immediate children:{ Descendants([Fct_Assumption].[Total Assumption]) } + { DrilldownLevel( [Fct_Assumption].[Subtotals]) }

Similar to DrilldownLevel, but you limit the list to the first x members:{DrilldownLevelTOP( [Project_type].[All Types], 3)}

DrilldownLevelBOTTOM is similar to this function.

FIRSTCHILD gives us the first child of the member:{[Month].[Q2].FirstChild}

… will return Apr. LASTCHILD gives us Jun.

FIRSTSIBLING gives us the first child of the shared parent:{[Month].[Q2].FirstSibling}

… will return Q1. LASTSIBLING gives us Q4.

HEAD gives us the first element of a set:{HEAD( {[Month].[Q1].Children}

… will return Jan. TAIL gives us Mar.

LAG returns the member that is a specified number of positions along the member’s level:{[Month].[Q3].Lag(2)}

… will return Q1.

LEAD is the inverse of the function LAG:{[Month].[Q3].Lead(1)}

… will return Q4.

Lag(1) is equivalent to Lead(-1), since the functions are inverse.

PREVMEMBER is equal to LAG(1) and NEXTMEMBER is LEAD(1):{[Month].[Q3].PrevMember}

… will return Q2.

We have also functions to go roll up from elements to their parent(s), for example Parent,
Ancestors or TM1Rollup:{TM1ROLLUP( {[Month].[Jan]}, {[Month].[Jan]} )}

returns the parents of the month of January in the Month dimension.
This can be similar to a loop over Elpar from 1 to ElparN.

LASTPERIODS returns the n elements preceding and including the selected member along the member’s level{LASTPERIODS( 3, [Month].[Q3])}

… will return Q1, Q2, Q3.

LEVEL returns the members at the same level as the chosen member:{[Month].[Q3].Level.Members}

… will return Q1, Q2, Q3, Q4.

MEMBERS allows us to select a range of contiguous members (following dimension indexes) from the same level specifying the first and last member of the set with a colon between them:{[Month].[Jan]:[Month].[Oct]}

… will return Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct.

Please note the 2 following requirements:

Both elements should be at the same level in the dimension

Index-wise, no dimension elements at a different level, can be between the 2 listed elements

LOOKUPCUBE allows to filter on members following information in a cube:{TM1FilterByPattern( TM1SubsetAll([Account_PL]), LookUpCube ( "Prm_Account", "([Prm_Account_dim1].[Pattern], [Prm_Account_Msr].[Text])" ) )}

… will filter all elements in the Account_PL dimension following the pattern mentioned in the lookup cube Prm_Account.

Or, getting the reporting year for Actuals from a parameter cube:{StrToMember("[Rp_Year].["+[Rp_Irp_Parameter].([Rp_Scenario].[Actual],[Rp_Irp_Parameter].[Reporting Year],[Rp_Irp_Parameter_Msr].[Value])+"]") }

Going further, a way to filter elements in a dimension based on cube data, where the definition of the cube cells is stored in a parameter cube:{Filter( TM1FilterByLevel( TM1SubsetAll( [Period] ), 0), [Periods 2D].( StrToMember("[Year].[" + [System].([System_Prm].[Year], [System_Msr].[Text]) + "]")) > 0 )}

Hereby, we filter elements in the dimension 'Period' based on data in the cube 'Periods 2D'. In that cube, we select cells based on a year that is stored in the cube 'System'.

The same result could be achieved with a Property:{Filter( TM1SubsetAll( [FIN_Account] ), [FIN_Account].CurrentMember.Properties("Type") = "PL")}

The lookup value for the attribute can also be stored in a cube:{Filter( TM1SubsetAll( [FIN_Account] ), [FIN_Account].[Type] = [Prm_Account].([Prm_Account].[Parameter_Value],[Prm_Account_Measures].[Account type]))}

A very useful trick involving Instr is the following. You can apply it to views in the Cube viewer, Planning Analytics, Active forms, and so on. Well, you can filter (e.g.) accounts in the rows of a view in the cube viewer, when the account prefix equals the company name. Company is another dimension in the cube:{Filter( TM1SubsetAll( [St_Account] ), Instr( [St_Account].CurrentMember.Name,
[Mp_Std_Company].([Mp_Std_Company_Msr].[Company]) ) = 1 )}

St_Account is the dimension of accounts in the rows

Mp_Std_Company is the cube where the companies are mapped (a 2D cube with companies and a measure called "Company").
Below, China Guangzhou is mapped in the lookup cube to "C010", which filters the prefix in the accounts)

Selecting elements based on values in a cube (we do not have to specify every dimension in the cube):{Filter( TM1FilterByLevel( TM1SubsetAll( [PL_Account] ), 0), [BalanceSheet].([FIN_Year].[2016],[FIN_Period].[P10],[FIN_Scenario].[Actual],[BS_Measures].[Amount])<>0)}

If we do not specify an element in a dimension of the cube, TM1 will use the element at index 1 in the dimension. So watch out and make sure this is what you need.

Generate can be tricky but useful. It allows to apply an operation on every element in a first
selection. Like in: select all level 1 customers (consolidated) and for each of them, find the customer with the highest revenue:{Generate(
{TM1FilterByLevel( {TM1SubsetAll( [Customers] )}, 1)},
TopCount(Descendants([Customers].CurrentMember, 1), 1, Act_Sales.([Act_Sales_Msr].[Revenue])))}

A second example, list all managers of employees in a subset. Manager names are part of the same dimension
and are entered against the employee name in a 3D-cube called "Employee Planning"
(StrToMember will be discussed later on):{GENERATE( FILTER( TM1SubsetAll( [Employee] ),
[Employee Planning].([Scenario].[RP 2016],[Employee Planning Measures].[Manager ID]) <> ""),
{StrToMember("[" + [Employee Planning].([Scenario].[RP 2016],[Employee].CurrentMember,[Employee Planning Measures].[Manager ID]) + "]")})}

When you look at the link I mentioned above, you will find a lot more functions. Including functions for
information on the dimension structure:
FirstSibling,
Parent,
Children,
FirstChild / LastChild,
Ancestors, and so on. Many other options for creating dynamic subsets are explained
on that webpage. It is a must see!

Temporary subsets

Needless to say, but these subset definitions using MDX syntax can be used in the function SubsetCreatebyMDX.
That function can also create temporary subsets as of TM1 10.2.2 FP4 (see here).

Caveats

A few caveats apply. If you create a dynamic subset in TI (the function is called
SubsetCreatebyMDX('NAME OF THE SUBSET', 'MDX EXPRESSION'),
make sure you always have at least 1 element that matches. If not, the process errors out. Unless you use TM1 10.1 or later,
where an undocumented 3rd parameter to the function equal to the dimension name, will avoid the error in case no elements satisfy the expression.
As an alternative, in that case you should loop over the dimension elements and insert the matching elements in a static subset
SubsetCreate('DIMENSION NAME', 'SUBSET NAME') and
SubsetElementInsert('DIMENSION NAME', 'SUBSET NAME', 'ELEMENT NAME', POSITION).
The latter technique will always work (unless you are stuck in an endless loop like me on some Monday mornings),
whichever you will use depends on your liking and coding skills.

Please do NOT use the function TM1SubsetBasis() (unless you know what you are doing). Even though the MDX expression recorder
generates such code, it is in fact not to be used. It refers to a relative selection of elements ('at the time of using the function') rather than an absolute
selection of elements ('it will always be the same selection/result, all else being equal').

Excel

In Excel spreadsheets, we can work with MDX queries to return a subset: for example in active forms. There, you
could store the MDX query in a (hidden) cell. The query expression could be chopped up to include cell references.
For instance, the user can choose a Product family in cell H10, and the active form returns the data on the
lowest-level children of that Product family. Mind that there could be a 256 characters limit for the
MDX expression for older versions of TM1. You COULD leave out the dimension names BUT then the element
names must be unique throughout the ENTIRE TM1 model (over all dimensions).