Changes to Element Security for MDX Subsets from v2.0.9.1

Update on Cubewise testing of TM1 release 2.0.9.1

IBM have since released a note on this topic which can be read here.

There are two main applications of MDX when we discuss TM1 dimension subsets. The first is a ‘dynamic subset’ where an MDX query statement is saved with the subset to ensure that the returned elements automatically reflect changes to the dimension structure (e.g. a new element added to a dimension) or changes to the cube data (e.g. Top 5 performing accounts). 

An example would be the following MDX showing all elements under the consolidation ‘World’.

{TM1DRILLDOWNMEMBER( {[Region].[World]}, ALL, RECURSIVE )}

For a user with access to all elements this would look like the example below:

When element security is assigned to a dimension, an end user would only see those elements that met the MDX selection criteria AND for which the user has minimum READ access to. In this example we have a security group with access restricted to South America (as defined in the element security cube as below) 

For a user in this security group – with only access to South America – it would result in the following – only those elements that the user has access to:

In TM1 release 2.0.9.1 the interpretation of element security and MDX queries has changed. Where previously it appeared to the be applying the security layer after the MDX was generated it is now applied before. 

Note that it is not strictly true that security was applied after the MDX in prior releases, but the effective result was a list of elements the user had access to that met the MDX criteria. It’s just easier in terms of explanation to say security was applied as a filter after generating the set.

If we take the example above we need to understand what is happening with the MDX a bit more. The MDX statement is first returning the element ‘World’ and then all of the elements underneath it – right down to leaf level elements.

{TM1DRILLDOWNMEMBER( {[Region].[World]}, ALL, RECURSIVE )}

The new interpretation would be that as the user does not have access to the ‘World’ element the MDX would return no elements. This is true both of dynamic subsets and MDX subset expressions used in client applications (for example a row definition in an Active Form or Dynamic Report)

The case stated above is true if we use the ‘World’ consolidation but if we were to use a direct parent of elements the end user has access to the behaviour changes once again

{TM1DRILLDOWNMEMBER( {[Region].[Americas]}, ALL, RECURSIVE )}

Using the interpretation we would expect that as the user does not have access to the ‘Americas’ element the MDX would return no elements, however as we can see this is not the case. This is most likely due to the difference between ‘members’ in MDX and ‘elements’ in TM1 dimensions. If you want to know more on this subject please check out the blog over at Cubewise CODE https://code.cubewise.com/blog/the-future-of-tm1-time-dimensions-in-the-world-of-hierarchies

When upgrading to 2.0.9.1 TM1 administrators should take care to fully test both saved dynamic subsets and end user applications for MDX statements on dimensions with element security. MDX subset statements can be quite complex when using more advanced functions and cube data filters and so this change in the execution order of MDX statement and security layer may result in empty subsets and reports for users if left unchecked.