Planning Analytics Changes to MDX Subset Evaluation in 2.0.9

By: Walter Coffen & Michael Cowie

June 16, 2020

Planning Analytics Changes to MDX Subset Evaluation in 2.0.9

July 2020 UPDATE: IBM has reverted the change described in this article in release 2.0.9.2 of Planning Analytics (PA), which is now available to Local and IBM Cloud customers. Additional background and information about this issue, and the fix by IBM, can be found in this tech-note article: https://www.ibm.com/support/pages/node/6226890

We recommend that customers upgrading to PA 2.0.9 install the 2.0.9.2 release or newer to ensure they are not impacted by any MDX subset element security issues that have since been fixed by IBM.

Planning Analytics (PA) 2.0.9, released in December 2019, and PA 2.0.9.1, released in May 2020, changed how security and MDX are handled by PA. PA will now attempt to apply a user’s element security to an MDX statement before fully evaluating the MDX statement. The change in sequence can potentially impact a user’s interaction with PA, in some cases producing errors when attempting to access PA data from web or Excel-based applications. IBM has indicated that this change was made for security purposes that needed to be addressed but has not produced a technote yet. Customers may therefore need to review their use of MDX expressions as part of an upgrade, including those in “Dynamic Subsets”, to ensure all users have an experience consistent with earlier PA versions. This article covers some of the specific MDX and security changes that may impact your applications.

Background
For review, an MDX query is a dynamic way to query and build a list of elements that can be used as an edge to a view or a selection list for a template. As an example, an MDX query could return all the financial accounts under a rollup such as “Net Operating Income” inclusive of that rollup. Another MDX query based on that rollup might produce a list of all “Net Operating Income” leaf-level constituent accounts.

Element security is designed to limit the elements to what the user is authorized to see based on the TM1 security groups they are in, the group element security configuration, and the elements for which they have at least “read” access. For example, a user can be granted access to a smaller list of expense accounts that roll up into “Net Operating Income” but not have the ability to see the “Net Operating Income” total.

Changes in PA 2.0.9/2.0.9.1
The 2.0.9/2.0.9.1 change to MDX evaluation and element security is as follows: PA now attempts to perform the security check on the MDX query first, so that if a user has no access to an element in the MDX expression itself, then that part of the MDX statement evaluates to nothing. The impact of this change is that some dynamic subsets and reports that rely on MDX expressions that refer to specific elements may not return data for users who are limited by element security, in some cases showing an error when the report is opened.

In previous PA versions, the MDX statement was fully evaluated even if a hidden-from-the-user element was included in the expression in order to build a list of elements that the user potentially has limited rights to see. A restricted user not allowed to even see “Net Operating Income” who was still able to see all accounts under say “Operating Expenses” from an MDX based expression would have built a list of all accounts under “Net Operating Income”, without regard for security, then would trim away the elements that the user is not allowed access to after MDX evaluation.

The processing sequence change in PA 2.0.9 was made for REST API based requests, namely those originating in Planning Analytics Workspace (PAW) or Planning Analytics for Excel (PAfE). In PA 2.0.9.1, the same change in MDX evaluation was made for C API based requests, which impacts legacy tools such as Architect, Perspectives and TM1Web.

The change does not seem to apply to all MDX element references, however. While the PAW/PAfE subset editor has very limited MDX generation capabilities when compared to Architect/Perspectives Server Explorer, the MDX it generates seems to be the most impacted by the security change. In our testing it appears the MDX statements that are affected are limited to those using the standard MDX set function “DESCENDANTS”. There may be other functions impacted, too, but the emphasis of our analysis is on the MDX statements that come from the “Default member insertion” options in the latest PAW or PAfE subset editor. If the MDX contains “TM1DRILLDOWNMEMBER”, “DRILLDOWNMEMBER”, “CHILDREN”, “TM1FILTERBYLEVEL” and “TM1SUBSETALL”, as it might when recorded in the Architect subset editor, those do not seem to have been impacted: they still seem to operate with MDX evaluated first, security applied second. Please see “Detailed MDX Expression Test Results” below for more information.

Conclusions: What to do if you are Affected?
The recent change is a bit unsettling because changing the order of MDX expression evaluation to apply element security first potentially makes MDX in subsets a bad choice in designing reports and templates, despite MDX being an incredibly powerful reporting and analysis feature in PA. Users who are impacted may not be able to navigate their templates or may not be able to see all the data they need to do their jobs. Changing production systems may have significant governance requirements that need to be met in order to fix MDX expressions or security profiles as part of the upgrade. In models built before the REST API was available, MDX expressions developed in Architect via the “Record expression” feature may, at least for now, not be impacted by these changes to PA.

It is important to keep this change in mind as part of any upgrade project or for any near-term maintenance projects to get prepared. If your PA application has been affected by this change in behavior the safest resolution is to convert any MDX-based expressions to static list equivalents. This is something that can be done using Turbo Integrator and scheduled as part of normal data update processes to ensure that these static subsets stay up to date and “dynamic”.

If you are considering an upgrade and would like assistance in detecting and resolving problems resulting from this change in PA 2.0.9 and above please contact us at info@quebit.com.

Detailed MDX Expression Test Results
Testing for this article was applied using PA 2.0.9.1/PAW 2.0.53/PAfE 2.0.53. A restricted PA user ID was created with no access to the “Net Operating Income” summary account in the “plan_chart_of_accounts” dimension for the “Planning Sample” PA database that ships with PA. The following documents tests with specific MDX functions and properties that can refer to elements and which could be subject to the change in element security filtering.

Descendants Insertion (DESCENDANTS and TM1DRILLDOWNMEMBER Functions)

As the functionality change indicates, a basic get all descendants of “Net Operating Income” MDX request should fail since our restricted test user does not have access to “Net Operating Income”, and DESCENDANTS does not return any elements.

{DISTINCT(DESCENDANTS([plan_chart_of_accounts].[plan_chart_of_accounts].[Net Operating Income]))}

This MDX expression returns no members for a view that contains the dynamic subset in Architect, TM1Web, PAfE, and PAW if the user is not authorized to see “Net Operating Income”. This MDX was generated in PAfE by the latest subset editor using “Descendants” as the “Default member insertion” setting. This MDX works for a user that has at least “read” access to “Net Operating Income”.

Alternatively, using the following Architect-recorded MDX, the list of all descendants is generated when using TM1DRILLDOWNMEMBER even when the user has no access to “Net Operating Income” referenced in the MDX expression.

{TM1DRILLDOWNMEMBER( { [plan_chart_of_accounts].[plan_chart_of_accounts].[Net Operating Income] }, ALL, RECURSIVE ) }

This MDX returns all elements below “Net Operating Income” but not “Net Operating Income” itself for a view that contains the subset that can be opened in Architect, TM1Web, PAfE, and PAW by the restricted test user.

Children Insertion (DRILLDOWNMEMBER and CHILDREN Functions)

Selecting “Children” in the “Default member insertion” options in the PAW or PAfE subset editor produces the following MDX:

{DRILLDOWNMEMBER({[plan_chart_of_accounts].[plan_chart_of_accounts].[Net Operating Income]},   {[plan_chart_of_accounts].[plan_chart_of_accounts].[Net Operating Income]}, RECURSIVE)}

This MDX expression still works for the restricted user even though “Net Operating Income” is not available to the restricted user. A more simplified version of this MDX expression MDX also works.

{ [plan_chart_of_accounts].[plan_chart_of_accounts].[Net Operating Income].Children }

In either case the MDX is evaluated before security is applied.

Leaf Node Insertion (DESCENDANTS, TM1FILTERBYLEVEL, TM1SUBSETALL Functions)

Selecting “Leaf nodes” in the “Default member insertion” options produces the following MDX:

{DISTINCT({[plan_chart_of_accounts].[plan_chart_of_accounts].[Net Operating Income], DESCENDANTS([plan_chart_of_accounts].[plan_chart_of_accounts].[Net Operating Income], 99 , LEAVES)})}

The above MDX expression follows the new rule and returns nothing for the restricted user since they are not allowed to see “Net Operating Income”. But a couple of quick edits using MDX produced by Architect produces the following:

{TM1FILTERBYLEVEL( {TM1SUBSETALL( [plan_chart_of_accounts] )}, 0)}

This MDX expression still works for the restricted user and shows all leaf nodes under “Net Operating Income”, but exclusive thereof.

QueBIT’s Analytics Online Instructor-led Training Courses

  • IBM Planning Analytics Developer
  • IBM Planning Analytics Advanced Reporting
  • IBM Planning Analytics Advanced Developer

Visit learn.quebit.com

QueBIT’s Analytics Online Instructor-led Training

  • IBM Cognos Analytics Authoring Reports Fundamentals
  • IBM Cognos Analytics Authoring Reports with Multidimensional Data
  • IBM Cognos Analytics Authoring Reports Fundamentals

Visit learn.quebit.com

Subscribe to QueBIT Notifications

Can’t find what you’re looking for? →

Contact Help Desk

This form collects your contact information so that we can correspond with you. For more information on how we are committed to protecting and respecting your privacy, please review our Privacy Policy.

I consent to QueBIT collecting my contact information.