Pax - Dynamic Subset possible?

Post Reply
chewza
Posts: 57
Joined: Tue Aug 17, 2010 11:51 am
OLAP Product: TM1
Version: 9.5
Excel Version: 7

Pax - Dynamic Subset possible?

Post by chewza » Tue Aug 14, 2018 2:03 pm

Hi there

Can you create a dynamic subset in Pax, similar to how you can record them in Perspectives/Architect?

Many thanks!!

Regards
Chris

User avatar
paulsimon
MVP
Posts: 648
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: 10.1.1 and 10.2.2
Excel Version: 2013

Re: Pax - Dynamic Subset possible?

Post by paulsimon » Sat Aug 18, 2018 11:12 pm

Hi

If you try Edit Set in PAX you will find that the default mode is to record the MDX.

To try this create an Exploration View. With a dimension in the Row or Column position, right click and select Edit Set. Then click the X on the RHS pane to clear any existing selection, and then in the LHS Pane click on a consolidation and select something like Insert Member with Leaves. Then Save the Subset with a Name as a Public Set, just as you would do in Perspectives. then Apply and Close. At that point if you right click on the Axis symbol, not the dimension, then you can select Edit MDX. This will show the MDX that was recorded.

The reason that you go to the Axis (Row or Column) to see the MDX rather than the Dimension is because in PAX you can have Asymmetric Axes, ie it is possible to nest one dimension inside another and to expand a consolidation in the inner dimension for just one element of the outer dimension. By comparison in say Perspectives if you expand a consolidation in the inner dimension then it will expand within every member of the outer dimension. Asymmetric mode means that you can therefore sometimes achieve enough in an Exploration View to meet a requirement, without the overhead of resorting to the more traditional formula based approach. The reason that the MDX has to be at the Axes level is that if you are expanding within some elements and not others then this needs special MDX. The default CROSSJOIN won't do.

The new PAX is heavily MDX based so it is worth reading some books on MDX. Most are based on Analysis Services but the code is transferable and of course there are the extra TM1 extensions to MDX. Unlike Analysis Services TM1 has the advantage that it consolidates immediately.

Regards

Paul Simon

Post Reply