MDX Subset filtered on cube data; Hierarchy issue

Post Reply
rturpyn
Posts: 7
Joined: Tue Apr 03, 2012 4:14 pm
OLAP Product: TM1
Version: 10.1.1
Excel Version: 2010

MDX Subset filtered on cube data; Hierarchy issue

Post by rturpyn » Thu Oct 03, 2019 9:18 pm

I've got an MDX subset that is filtered based on cube data. Before the introduction of hierarchies, this was working as expected. Now that I'm incorporating hierarchies, I'm running into some issues.

I'm filtering an employee list (PP-ResourceIDs) based on them being allowed to charge a project (PP-Projects). I have a control cube (PP-ProjectWorkforce) that stores employee availability for each project. The MDX subset references this cube.

This subset is then used in a data entry cube. The view used by end users is leveraging a hierarchy (OwningOrg) of the projects dimension in order to group projects for security purposes. Previously, this separate hierarchy didn't exist.

When I use the MDX subset in a view on the data entry cube with the non-default hierarchy (OwningOrg) the MDX subset isn't suppressing any elements (lower left view). The MDX subset does suppress elements that aren't available to ANY project which would seem to indicate an issue matching projects between the 2 cubes.

If I use the MDX subset in a view that uses the default hierarchy then the MDX subset correctly suppresses elements (upper left view).

If I use the MDX subset in a view on the control cube with the non-default hierarchy (OwningOrg) the MDX subset does correctly suppress elements (upper right view).
MDX Subset Issue.jpg
Views using MDX Subset
MDX Subset Issue.jpg (905.34 KiB) Viewed 3351 times

Previously, I didn't make any reference to the projects dimension in the MDX subset. The MDX essentially matched dimensions between the cube the subset was in and the cube the MDX referenced. However, with the introduction of hierarchies, I feel like I must have to explicitly tell the MDX subset which hierarchy to use for the projects dimension, but I can't figure out how to do that.

This is the MDX expression that is currently being used.
MDX Subset Issue (Old syntax).jpg
MDX Subset
MDX Subset Issue (Old syntax).jpg (46.08 KiB) Viewed 3351 times

While I realized I can't use the next MDX expression since currentmember isn't valid for a dimension other than the one the subset is a child too, this is the general idea.
MDX Subset Issue (new syntax).jpg
Pseudo MDX Subset
MDX Subset Issue (new syntax).jpg (64.93 KiB) Viewed 3351 times

Thanks for any help you can provide,

Ryan

Post Reply