Planning Analytics

Jitesh

------------------------------
Jitesh Parmar
------------------------------

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

Jitesh

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

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

Jitesh

------------------------------
Jitesh Parmar
------------------------------

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:

1. Create a "Slicer Variable" for Product Category (called <0>)
2. 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

------------------------------
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

Jitesh

------------------------------
Jitesh Parmar
------------------------------

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 unmanageable 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 multiple 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

Jitesh

------------------------------
Jitesh Parmar
------------------------------