The interaction of element security and subset MDX on IBM PA on AWS (v 12.4.6) appears to have changed from older versions (e.g. 11.8.13). In the older version, it appears that the MDX logic was applied first, and the element security thereafter. On the new version, it appears that the element security is applied first, and thereafter the MDX, and as a result the MDX can fail for non-admin users that don't have access to all elements in the dimension.

For example, using the following Cost Centre dimension:

Assume that a user is granted access to element 100 and its children, but has no access to All Cost Centres.

A Workspace view reports data by cost centre, with the subset MDX reflecting all descendents of "All Cost Centres". MDX would be:
{DISTINCT({DESCENDANTS([CostCentre].[CostCentre].[All Cost Centres])})}

or even the older:
{TM1DRILLDOWNMEMBER({[CostCentre].[CostCentre].[All CostCentres]} , ALL , RECURSIVE)}

When the workbook with the view is opened by the non-admin user on the old v11 version, the MDX is applied, and thereafter the element security, and the view successfully reports the following cost centre structure:

However, when opened on the new version (v12.4.6), it appears that the element security is applied first. As a result, the "All Cost Centres" element is not found and the MDX that references this element, fails and returns no elements at all. 

Seems to be similar to a change made and then reversed back in 2020:
https://www.ibm.com/support/pages/node/6226890

Anyone know if this is intentional, or if this is a bug?

There are work-arounds that do work, e.g. using static subsets (then requires methodology to maintain these), or alternatively modifying the MDX in the view to return all members and then excluding roll-ups that are not required, but both options are not as simple as using the standard MDX generated by the subset editor. 

Hi Johann,

Thank you for posting us on the differences v11 vs v12. 

The change is going to be properly documented for the current supported behavior soon.

Best regards,

The interaction of element security and subset MDX on IBM PA on AWS (v 12.4.6) appears to have changed from older versions (e.g. 11.8.13). In the older version, it appears that the MDX logic was applied first, and the element security thereafter. On the new version, it appears that the element security is applied first, and thereafter the MDX, and as a result the MDX can fail for non-admin users that don't have access to all elements in the dimension.

For example, using the following Cost Centre dimension:

Assume that a user is granted access to element 100 and its children, but has no access to All Cost Centres.

A Workspace view reports data by cost centre, with the subset MDX reflecting all descendents of "All Cost Centres". MDX would be:
{DISTINCT({DESCENDANTS([CostCentre].[CostCentre].[All Cost Centres])})}

or even the older:
{TM1DRILLDOWNMEMBER({[CostCentre].[CostCentre].[All CostCentres]} , ALL , RECURSIVE)}

When the workbook with the view is opened by the non-admin user on the old v11 version, the MDX is applied, and thereafter the element security, and the view successfully reports the following cost centre structure:

However, when opened on the new version (v12.4.6), it appears that the element security is applied first. As a result, the "All Cost Centres" element is not found and the MDX that references this element, fails and returns no elements at all. 

Seems to be similar to a change made and then reversed back in 2020:
https://www.ibm.com/support/pages/node/6226890

Anyone know if this is intentional, or if this is a bug?

There are work-arounds that do work, e.g. using static subsets (then requires methodology to maintain these), or alternatively modifying the MDX in the view to return all members and then excluding roll-ups that are not required, but both options are not as simple as using the standard MDX generated by the subset editor. 

Hi Johann,

Thank you for posting us on the differences v11 vs v12. 

The change is going to be properly documented for the current supported behavior soon.

Best regards,

The interaction of element security and subset MDX on IBM PA on AWS (v 12.4.6) appears to have changed from older versions (e.g. 11.8.13). In the older version, it appears that the MDX logic was applied first, and the element security thereafter. On the new version, it appears that the element security is applied first, and thereafter the MDX, and as a result the MDX can fail for non-admin users that don't have access to all elements in the dimension.

For example, using the following Cost Centre dimension:

Assume that a user is granted access to element 100 and its children, but has no access to All Cost Centres.

A Workspace view reports data by cost centre, with the subset MDX reflecting all descendents of "All Cost Centres". MDX would be:
{DISTINCT({DESCENDANTS([CostCentre].[CostCentre].[All Cost Centres])})}

or even the older:
{TM1DRILLDOWNMEMBER({[CostCentre].[CostCentre].[All CostCentres]} , ALL , RECURSIVE)}

When the workbook with the view is opened by the non-admin user on the old v11 version, the MDX is applied, and thereafter the element security, and the view successfully reports the following cost centre structure:

However, when opened on the new version (v12.4.6), it appears that the element security is applied first. As a result, the "All Cost Centres" element is not found and the MDX that references this element, fails and returns no elements at all. 

Seems to be similar to a change made and then reversed back in 2020:
https://www.ibm.com/support/pages/node/6226890

Anyone know if this is intentional, or if this is a bug?

There are work-arounds that do work, e.g. using static subsets (then requires methodology to maintain these), or alternatively modifying the MDX in the view to return all members and then excluding roll-ups that are not required, but both options are not as simple as using the standard MDX generated by the subset editor. 

Svetlana-

Does this mean the element security behavior relative to MDX in V12 will be configurable (as described in the linked text note)?  

Chris

Hi Johann,

Thank you for posting us on the differences v11 vs v12. 

The change is going to be properly documented for the current supported behavior soon.

Best regards,

The interaction of element security and subset MDX on IBM PA on AWS (v 12.4.6) appears to have changed from older versions (e.g. 11.8.13). In the older version, it appears that the MDX logic was applied first, and the element security thereafter. On the new version, it appears that the element security is applied first, and thereafter the MDX, and as a result the MDX can fail for non-admin users that don't have access to all elements in the dimension.

For example, using the following Cost Centre dimension:

Assume that a user is granted access to element 100 and its children, but has no access to All Cost Centres.

A Workspace view reports data by cost centre, with the subset MDX reflecting all descendents of "All Cost Centres". MDX would be:
{DISTINCT({DESCENDANTS([CostCentre].[CostCentre].[All Cost Centres])})}

or even the older:
{TM1DRILLDOWNMEMBER({[CostCentre].[CostCentre].[All CostCentres]} , ALL , RECURSIVE)}

When the workbook with the view is opened by the non-admin user on the old v11 version, the MDX is applied, and thereafter the element security, and the view successfully reports the following cost centre structure:

However, when opened on the new version (v12.4.6), it appears that the element security is applied first. As a result, the "All Cost Centres" element is not found and the MDX that references this element, fails and returns no elements at all. 

Seems to be similar to a change made and then reversed back in 2020:
https://www.ibm.com/support/pages/node/6226890

Anyone know if this is intentional, or if this is a bug?

There are work-arounds that do work, e.g. using static subsets (then requires methodology to maintain these), or alternatively modifying the MDX in the view to return all members and then excluding roll-ups that are not required, but both options are not as simple as using the standard MDX generated by the subset editor. 

Hi Johann and Chris!

The clarification is indeed necessary as in v12 that is the next version there is no optional configuration for security for MDX/tm1. That is going to work only as described in this example relative to v12 version:

• {DISTINCT({DESCENDANTS([CostCentre].[CostCentre].[All Cost Centres])})}
• {TM1DRILLDOWNMEMBER({[CostCentre].[CostCentre].[All CostCentres]} , ALL , RECURSIVE)}

• {DISTINCT({DESCENDANTS([CostCentre].[CostCentre].members)})}
• {TM1DRILLDOWNMEMBER({[CostCentre].[CostCentre].members} , ALL , RECURSIVE)}

I'd see if the v12 documantation would include the explanation.

Best regards,

Svetlana-

Does this mean the element security behavior relative to MDX in V12 will be configurable (as described in the linked text note)?  

Chris

Hi Johann,

Thank you for posting us on the differences v11 vs v12. 

The change is going to be properly documented for the current supported behavior soon.

Best regards,

The interaction of element security and subset MDX on IBM PA on AWS (v 12.4.6) appears to have changed from older versions (e.g. 11.8.13). In the older version, it appears that the MDX logic was applied first, and the element security thereafter. On the new version, it appears that the element security is applied first, and thereafter the MDX, and as a result the MDX can fail for non-admin users that don't have access to all elements in the dimension.

For example, using the following Cost Centre dimension:

Assume that a user is granted access to element 100 and its children, but has no access to All Cost Centres.

A Workspace view reports data by cost centre, with the subset MDX reflecting all descendents of "All Cost Centres". MDX would be:
{DISTINCT({DESCENDANTS([CostCentre].[CostCentre].[All Cost Centres])})}

or even the older:
{TM1DRILLDOWNMEMBER({[CostCentre].[CostCentre].[All CostCentres]} , ALL , RECURSIVE)}

When the workbook with the view is opened by the non-admin user on the old v11 version, the MDX is applied, and thereafter the element security, and the view successfully reports the following cost centre structure:

However, when opened on the new version (v12.4.6), it appears that the element security is applied first. As a result, the "All Cost Centres" element is not found and the MDX that references this element, fails and returns no elements at all. 

Seems to be similar to a change made and then reversed back in 2020:
https://www.ibm.com/support/pages/node/6226890

Anyone know if this is intentional, or if this is a bug?

There are work-arounds that do work, e.g. using static subsets (then requires methodology to maintain these), or alternatively modifying the MDX in the view to return all members and then excluding roll-ups that are not required, but both options are not as simple as using the standard MDX generated by the subset editor. 

Hi Johann and Chris!

The clarification is indeed necessary as in v12 that is the next version there is no optional configuration for security for MDX/tm1. That is going to work only as described in this example relative to v12 version:

• {DISTINCT({DESCENDANTS([CostCentre].[CostCentre].[All Cost Centres])})}
• {TM1DRILLDOWNMEMBER({[CostCentre].[CostCentre].[All CostCentres]} , ALL , RECURSIVE)}

• {DISTINCT({DESCENDANTS([CostCentre].[CostCentre].members)})}
• {TM1DRILLDOWNMEMBER({[CostCentre].[CostCentre].members} , ALL , RECURSIVE)}

I'd see if the v12 documantation would include the explanation.

Best regards,

Svetlana-

Does this mean the element security behavior relative to MDX in V12 will be configurable (as described in the linked text note)?  

Chris

Hi Johann,

Thank you for posting us on the differences v11 vs v12. 

The change is going to be properly documented for the current supported behavior soon.

Best regards,

The interaction of element security and subset MDX on IBM PA on AWS (v 12.4.6) appears to have changed from older versions (e.g. 11.8.13). In the older version, it appears that the MDX logic was applied first, and the element security thereafter. On the new version, it appears that the element security is applied first, and thereafter the MDX, and as a result the MDX can fail for non-admin users that don't have access to all elements in the dimension.

For example, using the following Cost Centre dimension:

Assume that a user is granted access to element 100 and its children, but has no access to All Cost Centres.

A Workspace view reports data by cost centre, with the subset MDX reflecting all descendents of "All Cost Centres". MDX would be:
{DISTINCT({DESCENDANTS([CostCentre].[CostCentre].[All Cost Centres])})}

or even the older:
{TM1DRILLDOWNMEMBER({[CostCentre].[CostCentre].[All CostCentres]} , ALL , RECURSIVE)}

When the workbook with the view is opened by the non-admin user on the old v11 version, the MDX is applied, and thereafter the element security, and the view successfully reports the following cost centre structure:

However, when opened on the new version (v12.4.6), it appears that the element security is applied first. As a result, the "All Cost Centres" element is not found and the MDX that references this element, fails and returns no elements at all. 

Seems to be similar to a change made and then reversed back in 2020:
https://www.ibm.com/support/pages/node/6226890

Anyone know if this is intentional, or if this is a bug?

There are work-arounds that do work, e.g. using static subsets (then requires methodology to maintain these), or alternatively modifying the MDX in the view to return all members and then excluding roll-ups that are not required, but both options are not as simple as using the standard MDX generated by the subset editor.