Thanks for the reply.
It would be great to see MDX variables used to their full potential, especially with the introduction of global variables.
Please count me in for this, I'm eagerly waiting to get hands-on with this feature.
Also, thank you for sharing these examples. Its really very useful.
Regards,
Original Message:
Sent: Tue February 25, 2025 04:44 AM
From: Paul C
Subject: PAW 2.0.101 - MDX Template a practical use case
That should be possible in the future. I know the developement team is working on "Global variables" as a next step. Currently these variables are "attached" and "restricted" to a given view. The idea with Global variables is to create variables that can be used everywhere. This has a couple of implications:
- You can use a variable from a dimension that is not part of the view: ie a dimension from a 1rst view/cube can drive the selection of a 2nd view/cube (and they might be different cubes)
- You can have several variables (from several selector widgets) for a given dimension: ie you don't have to use the multiple selectors in the example above, you can just create several selectors for a given dimension (and thus get several variables with different values)
It's also on the roadmap to bring variables to TI buttons parameters and Set editor widget. With these features, you should gain an excel-like freedom of modelisation in books.
In the case of Product Category and Product SKU, I expect that you can link both with global variables in the following manner:
- Create a "Slicer Variable" for Product Category (called <0>)
- In the view with Product SKU, transform that "Product Category" member into a Product SKU member: STRTOMEMBER("[Product SKU].[Product SKU].[" + <0>.PROPERTIES("MEMBER_NAME") + "]")
I actually wonder if the same would work for a set, the expression would have to contain a reference to a current member that the view on Product SKU shouldn't have access to. It's something we should try when that feature is released but I don't think that would work
GENERATE( <0> , {STRTOMEMBER("[Product SKU].[Product SKU].[" + [Product Category].[Product Category].CURRENTMEMBER.PROPERTIES("MEMBER_NAME") + "]")})
------------------------------
Paul C
Original Message:
Sent: Mon February 24, 2025 05:21 PM
From: Jitesh Parmar
Subject: PAW 2.0.101 - MDX Template a practical use case
Hi Paul,
Thank you for sharing this, it's extremely useful. I have recently done something similar for customer through TI and alias, but the new variable features present very useful options without requiring any data movement. I'm definitely looking forward to seeing more examples of how to use variables effectively.
I'm also wondering if this functionality allows us to synchronize two dimensions that share the same element ID. For example, consider two cubes that use different dimensions, such as Product Category and Product SKU. Currently, the synchronization model doesn't allow us to pass context to a different dimension. Could variables help us bridge that gap?
Thanks again for the insights!
Regards,
Jitesh
------------------------------
Jitesh Parmar
Original Message:
Sent: Mon February 24, 2025 07:01 AM
From: Paul C
Subject: PAW 2.0.101 - MDX Template a practical use case
Hello,
Sure here's a simple one, yet very difficult to build without MDX variables. Let's say you need to build a report that can compare two combination of Scenario/Year with a calculation that makes the difference. It's a simple enough view to create in PAW but it gets complicated as soon as you have to make things dynamic.

Here, there's no way to make that calculation dynamic. Once created, the "Var vs-1" stays hardcoded on "FY 2004 Forecast/2004" - "FY 2003 Budget/2003". There's also an issue with the prompting of Scenario and Year: we have an asymmetric view that takes in columns "Scenario 1 / Year 1" and "Scenario 2 /Year 2 & Var vs-1". Once that asymmetric view is set, you can't change members through the selectors without breaking the asymmetry. If you change one member, you'll go back to a symmetric view that makes the cartesian product of Scenario and Year

The only way you could build this report prior MDX variables was to build a selection cube but that comes with caveats:
- It's quite difficult to develop: inserting a reference to a cell value into a given view creates huge unmanageble MDX queries
- Performance is sub-par for obvious reasons: the system has to calculate "sub-views" on the lookup cube before starting to compute the main view
- Selections are reduced to picklist: there's no way to select a set containing several members and you lose the benefits of a full set editor (searching members, using sets etc...)
Now let's build that report with MDX variables. First we'll use multiple selection widgets that will allow us to create selections for each columns of the report (you'll have to use sync groups between the selector widgets and the view)

Then let's create the variables

and we'll insert them in the MDX

and now you have a dynamic report that lets you freely compare any Scenario/Year


This is a first initial release for MDX variables, I'll wait a bit to see what's next and I'll publish more examples
------------------------------
Paul C
Original Message:
Sent: Fri February 21, 2025 11:36 AM
From: Jitesh Parmar
Subject: PAW 2.0.101 - MDX Template a practical use case
Hi Everyone,
IBM has released a new feature in Planning Analytics Workspace (PAW) version 2.0.101 called MDX Template, which allows you to declare MDX variables as part of a cube MDX statement.
Does anyone have a practical use case for this feature, particularly in terms of view manipulation or query parameterization? I'm eager to learn how best to leverage this functionality.
It would also be great if the PA team could release a video showcasing a few examples of how this feature can be applied.
Thanks in advance for your insights.
Regards,
Jitesh
------------------------------
Jitesh Parmar
------------------------------