Planning Analytics

 View Only
  • 1.  PAW 2.0.101 - MDX Template a practical use case

    Posted 5 days ago
    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
    ------------------------------


  • 2.  RE: PAW 2.0.101 - MDX Template a practical use case

    Posted 2 days ago
    Edited by Paul C yesterday

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



  • 3.  RE: PAW 2.0.101 - MDX Template a practical use case

    Posted 2 days ago

    Thank you Paul, very helpful !



    ------------------------------
    Wim Gielis
    Senior Consultant
    Aexis International
    Sint-Stevens-Woluwe
    0496225001
    ------------------------------



  • 4.  RE: PAW 2.0.101 - MDX Template a practical use case

    Posted 2 days ago
    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
    ------------------------------



  • 5.  RE: PAW 2.0.101 - MDX Template a practical use case

    Posted yesterday
    Edited by Paul C yesterday

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



  • 6.  RE: PAW 2.0.101 - MDX Template a practical use case

    Posted 23 hours ago

    Hi Paul,

    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,

    Jitesh



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



  • 7.  RE: PAW 2.0.101 - MDX Template a practical use case

    Posted yesterday

    thank you Pau!!



    ------------------------------
    Vitalij Rusakovskij
    ------------------------------



  • 8.  RE: PAW 2.0.101 - MDX Template a practical use case

    Posted 4 hours ago

    Thank for that write up Paul, much appreciated. 


    I have managed to successfully replicate this on v11 on Cloud. However, trying to do the same on IBM PA aaS on v12, the reference to variables works fine, the asymmetric display of the variables works fine, but then defining a calculation (e.g. the variance) with reference to the variables fails. It appears that the MDX statement fails as it does not expect the comma after the first variable reference:

    I will raise with IBM, but was just wondering if anyone reading this has already given this a go on v12?



    ------------------------------
    Johann Kassier
    Enterprise Planning Architect
    Fusion 5
    ------------------------------