Planning Analytics

 View Only
  • 1.  MDX - Hierarchy Change by context

    Posted Mon September 16, 2024 10:03 AM

    Good evening everyone, 

    Maybe is a dumb question but I've hit a wall in trying to achieve the following behavior:

    I have a budget application with several scenarios (2024, 2025, etc.). Every scenario use a custom set of elements created in the scenario initialization, where the user define the aggregation for product element where input the budget (can be any attribute and it can choose moltiple level of aggregation).

    I've tried to manage the product dimension with different hierarchies, one for each scenario, and tried to dynamically change the hierarchy in the budget input view by defining the view by MDX.

    My idea was to define on rows the product as:

    StrToSet("[product].["+[scenario].currentMember.name+"].members")

    But unfortunately the compiler doesn't like this definition.

    Is it the right approach? Is there any other method to dynamically change the hierarchy based on context without using a TI?

    Thanks alot, 

    Andrea



    ------------------------------
    Andrea Bonelli
    ------------------------------


  • 2.  RE: MDX - Hierarchy Change by context

    IBM Champion
    Posted Tue September 17, 2024 06:57 AM

    I had a similar issue where the MDX compiler didn't like me trying to dynamically change the hierarchy for rows based on a context element of a different dimension.

    The MDX compiled ok but when changing the context element it reported a cell mismatch.

    In my case I was needing to show an FY hierarchy for some versions but a Calendar Year hierarchy for others; since I only had 2 hierarchies I was able to get around it by nesting both of them in the rows - then with the MDX I set the one that I "didn't want" for a given version to return a "Total" element only. It wasn't an ideal solution but it worked well enough for the given use case.

    If you have a lot of hierarchies that will continue to grow then that work-around would probably look awful for you with too many columns/rows being nested. But in the event that no one comes up with a proper solution it may give you some ideas for potential workarounds. 



    ------------------------------
    Declan Rodger
    Technical Director
    Spitfire Analytics
    ------------------------------



  • 3.  RE: MDX - Hierarchy Change by context

    Posted Fri September 20, 2024 09:56 AM

    Hi Declan,

    thank you for your Idea! I've tried and it's working, but unfortunately I want to achieve a more dynamic view since they can create a new scenario budget and it will have to appear in the mdx view automatically.

    But I will consider to use this approach for time hierarchies since they are more "fixed".

    Thanks again,

    Andrea



    ------------------------------
    Andrea Bonelli
    ------------------------------



  • 4.  RE: MDX - Hierarchy Change by context

    Posted Tue September 17, 2024 07:20 AM
    Edited by STUART KING Tue September 17, 2024 07:20 AM

    Hi Andrea,

    I'm not 100% sure I understand the question correctly.  I think you are asking if the set of members in one hierarchy (dimension) can be determined by the selection of a member in a different hierarchy (dimension).  I can provide a simple example of this.  Consider this view:

    The MDX is this used to render the view:

    SELECT 
       {
          TM1SubsetToSet([Measures].[Measures],"Default","public")
       } ON 0, 
       {
          FILTER(
             [Store].[Store].MEMBERS , [Store Locations].(
     
                [Store].[Store].CURRENTMEMBER,
                [Locations].[Locations]) = 1)
       } ON 1 
    FROM
       [Dynamic Sales Cube] 
    WHERE (
       [Locations].[Locations].[East], 
       [People].[People].[Sandy], 
       [Channels].[Channels].[Retail])
    As I change the Location, I will see a different set of stores on the row axis of the view.  For example (when I change Location from East to South):
    To make this work I use another cube called Store Locations that flags what stores belong to which location (some stores belong to two or more locations).  This is often attribute data instead of a separate cube in production TM1 models. 
    Let me know if this helps or if I didn't understand the problem you are trying to solve.



    ------------------------------
    Stuart King
    Product Manager
    IBM Planning Analytics
    ------------------------------



  • 5.  RE: MDX - Hierarchy Change by context

    Posted Fri September 20, 2024 09:52 AM


    Hi Stuart,

    Thank you somuch for your answer. Unfortunately, the approach you described reflects the way I am currently handling the issue, but the behavior I aim to achieve aligns more closely with what Declan has described.

    I am working with a product dimension that is used within a budget input cube. This dimension is recreated for each version of the budget with different aggregations:

    • Budget 2024 (which I intend to transform into the hierarchy [Product].[BDG2024]):
      • Commercial Group A
          • Product A
          • Product B
          • Product D
      • Commercial Group B
          • Product C

    • Budget 2025 (hierarchy [Product].[BDG2025].Members):
      • Technical Line A
          •  Product A
          •  Product D
      • Technical Line B
          • Product C
          • Product B

    As such, for each scenario, there will be a hierarchy in the product dimension, as the aggregation is reshuffled each time a new budget is created.

    Is there a way to automatically adjust the hierarchy in the rows of a cube viewer when the user selects a different element in the Scenario Budget dimension?

    I hope this clarifies my question..

    Thank you for your time and consideration.

    Best regards,
    Andrea



    ------------------------------
    Andrea Bonelli
    ------------------------------



  • 6.  RE: MDX - Hierarchy Change by context

    Posted Fri September 20, 2024 10:09 AM

    Hi Andrea,

    Few years ago, I have used a quick report with a macro wich change the MDX of a view.

    First with Pafe create an exploration and copy the MDX of this view.

    Create a quick report based on this view

    import the vba automation CognosOfficeAutomationExemple.bas and CognosOfficeMessageSuppressor.cls

    (ask to IBM support where to fin files and documentation)

    You are able to modify the MDX of the view with a macro like :

    Public Sub Replacer()

    '

    ' Replacer Macro

    '

    Dim MDX As String

    Dim IDReport As Integer

    MDX = "SELECT TM1SubsetToSet([month].[month], ""ss_mois_annee"") DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_NAME, MEMBER_CAPTION, LEVEL_NUMBER, CHILDREN_CARDINALITY ON 0, HEAD(TM1SubsetToSet([region].[region], ""ss_Mes_pays""), 500) DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_NAME, MEMBER_CAPTION, LEVEL_NUMBER, CHILDREN_CARDINALITY ON 1 FROM [PNLCube] WHERE ([actvsbud].[Actual] , [account2].[Sales]) DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_NAME, MEMBER_CAPTION, LEVEL_NUMBER, CHILDREN_CARDINALITY , [account2].[account2].[compte2]"

    Worksheets("RapRapide").Activate

    Cells(8, 2).Activate

    IDReport = Reporting.GetCurrentReport(ActiveCell).ID

    Reporting.QuickReports.Replace IDReport, MDX

    End Sub

    for sample change the hierarchy.

    When, on quick report, a user change the point of vue, an action button can throw the macro and the quick report is ..... perhaps .... as you want.

    Regards,

    Philippe



    ------------------------------
    Philippe CHAMPLEBOUX
    ------------------------------



  • 7.  RE: MDX - Hierarchy Change by context

    Posted Mon September 23, 2024 09:23 AM
    Edited by Cristian Serb Mon September 23, 2024 09:30 AM

    Hi Andrea,

    Try using a FILTER function like this:

    DESCENDANTS(FILTER(TM1SUBSETALL([Product].[Product]) , ([Product].[Product].CURRENTMEMBER.NAME = [Scenario].CURRENTMEMBER.NAME)))

    The above MDX example will parse the Product dimension to find the element (rollup) matching the Scenario name. Then it will simply show the Descendants of that Product rollup. For example, if you have a rollup called 'Budget 2024' in the Product dimension and the selected Scenario is 'Budget 2024', then the above MDX will return all descendants of 'Budget 2024' Product rollup.

    Alternatively, if the Product rollup is not entirely matching the Scenario name, you could potentially create an attribute on the Product dimension called 'Budget Version' that will be mapped with each Scenario. 

    In this case, the MDX will be slightly different, but the idea is the same:

    DESCENDANTS(FILTER(TM1SUBSETALL([Product].[Product]) , ([Product].[Product].CURRENTMEMBER.PROPERTIES("Budget Version") = [Scenario].CURRENTMEMBER.NAME)))

    Regards,

    Cristian



    ------------------------------
    Cristian Serb
    ------------------------------