Planning Analytics

 View Only
Expand all | Collapse all

PAW - How to make the selection widget show user-specific selections stored in a parameter cube

  • 1.  PAW - How to make the selection widget show user-specific selections stored in a parameter cube

    Posted Tue June 15, 2021 11:00 AM
    Hi,

    I have a selection widget for the organization dimension (4 levels) and a crosstab which should always show the zero level (cost centres). Meaning if I select a level 2 element in the organization dimension selection widget, the table should show all cost centres (leaf level) which belong to the selected level 2 element.

    I solved this with a selection widget and a TI process which stores the selection for each user in a parameter cube (using the new synchronize feature in action buttons). In the crosstab, a MDX is applied to the organization dimension which gets the selected element for the current user and shows all leaf elements.

    In the screenshot below I can select element N48 in the selection widget, click on run and the table updates and shows all leaf elements which belong to element N48. So far so good. 

    The problem I have is when I close and reopen the book, the selection widget shows the top element "Alla" although the table is filtered on element N48. This can be confusing for users and for usability reasons I would like the selection widget to show N48 instead. 

    I tried several solutions with MDX but nothing worked 100%. In TM1WEB sheets this could easily be solved in the SUBNMN function with the "IndexOrName" argument. So this functionality is basically what I am looking for in PAW selection widgets. 

    I know I could show the value from the parameter cube to inform the user what the latest selection is but I would like to avoid that and rather have the selection widget show the latest selection.

    Has anyone had the same issue and found a solution?







    ------------------------------
    Mario Hasler
    ------------------------------

    #PlanningAnalyticswithWatson


  • 2.  RE: PAW - How to make the selection widget show user-specific selections stored in a parameter cube

    Posted Thu June 17, 2021 05:58 AM
    Edited by System Fri January 20, 2023 04:47 PM
    Hi Mario,

    My usual workaround for things like this is to create an attribute against the client, lets call it "Client Selected Org", make that a picklist re a subset of the Organisation dimension, and then have the MDX reference this attribute. Then instead of the Dimension selector you actually make the selector the attribute cube intersection.
    This means it will always show the last value entered by the user. Of course you don't have to use the attribute cube, you could create a specific cube for this purpose and have the client as one of the dimensions.

    Having said this, it can come with certain anomalies, meaning that we really need the SUBNM type functionality you are asking for!

    I would say in general, how the subsets are updated in PAW needs some work. Often times they are not automatically recalculated/refreshed, which as you say is potentially confusing for end users.

    regards,

    Mark

    ------------------------------
    Mark Wragg
    ------------------------------



  • 3.  RE: PAW - How to make the selection widget show user-specific selections stored in a parameter cube

    Posted Thu June 17, 2021 11:10 AM
    Hi Mark,

    Thank you for your answer. Appreciate it! I have used the solution you suggested in other circumstances and it worked fine. The reason why I don't want to use it in this use case is that the organization dimension is quite big with several thousand elements and a hierarchy with several levels. Users do want to have possibility to "navigate" through the hierarchy in order to find the desired element (see screenshot below). With the "cell and picklist"-solution you only get a flat list of elements. So the users pretty much have to know exactly which element they want to select so they can search for it. 

    With the new button synchronize functionality which was launched I was really enthusiastic being able to use the selection widgets instead of cells and picklists. It is just this issue which I described which is frustrating because I already know users will (rightfully so) complain about that.

    SUBNM functionality would be great but ultimately it would be great if the user-specific selections in the selection widgets could be used in MDX on-the-fly without the need to store the selection in a cube or attribute. 

    Regards,
    Mario







    ------------------------------
    Mario Hasler
    ------------------------------



  • 4.  RE: PAW - How to make the selection widget show user-specific selections stored in a parameter cube

    IBM Champion
    Posted Thu June 17, 2021 11:48 AM
    Edited by System Fri January 20, 2023 04:20 PM

    HI Mario,

    We have some solutions that work via a combination of the 2 methods you have described. 

    The "trick" is to have the selection widgets be based off an MDX subset. That MDX subset is a union of the element that the user last selected (retrieved from a cube containing the }client dimension) and the existing hierarchy that you would have had in there anyway to make selections from. Just make sure that the union results in the first element in the subset being the one which they selected last time.

    I should also add that where we have this approach we are also using hidden websheets on the PAW book that are synchronized to take the selection widget selections, show them in a SUBNM - which is then sent into a cell against the }Clients dimension. I don't see any reason that you couldn't do this without the convoluted hidden websheet method though... we just use that because it helped us achieve a few other requirements in those particular applications.

    Thanks,

    Declan



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



  • 5.  RE: PAW - How to make the selection widget show user-specific selections stored in a parameter cube

    Posted Thu June 17, 2021 02:49 PM
    Hi Declan,

    Thank you for your reply. I did actually try the UNION approach before posting the question to the community but could not get it working. Now I wonder if I am missing something because you say it should work. 

    This is how the selection widget behaves with the union function:

    1. The top element "Alla" is selected (as a default for all clients the element "Alla" is prefilled in the cube which contains the user-specific selections)


    2. The user selects the element S2031. 

    • Element S2031 shows upp as the first element in the subset as expected with the UNION function. 
    • Issue: The hierarchy broke but that can be fixed by adding the "ALL" argument to the UNION statement in the MDX so that duplicates are kept. This just looks a bit strange when the top element is selected because it shows twice then as element 1 and 2. Maybe something the users could live with.

    3. I close the book and open it again.

    Unfortunately the element "Alla" is shown and not S2031 although it is the first element in the subset. It seems like the selection widgets always defaults to the element the book was initially saved with.

    Or am I missing something? 

    Regards,
    Mario

    ------------------------------
    Mario Hasler
    ------------------------------



  • 6.  RE: PAW - How to make the selection widget show user-specific selections stored in a parameter cube

    IBM Champion
    Posted Thu June 17, 2021 04:11 PM
    Edited by System Fri January 20, 2023 04:41 PM

    Hi Mario,

    Now you are making me think back and I didn't give our weird little websheet enough respect in my previous post. The websheet is what makes it all come together.

    The websheet contains a SUBNM to that MDX subset using index 1 to return the first element.
    It's that SUBNM which then send into the User Parameters cube.

    When a user first opens the PAW Book, the SUBNM is returning the last element that they selected and the DBSS formula sends it back into the cube which in turn drives the Selection Widgets... you actually notice the widget takes a split second to render - presumably as its still refreshing following the DBSS.

    It's only when the user clicks into the Selection Widget that Workspace recognises as it being time to trigger the synchronization which then overwrites the SUBNM in the websheet.

    I remember testing this by having the websheet visible and then I only hid it from view after proving that the order of steps was working correctly.

    It sounds nuts, now that I actually write it out but the users have no idea what's going on and it gives them the interaction they wanted. So it will do for me until there is a better alternative.
    By the sounds of it, like yourself we had key feedback from users being that they wanted to come back into a workback and have the selections exactly where they left them (which makes perfect sense.)

    We did see some problems with this the first time a user ever interacted with a book, because they didn't have selections already in place. But we just got around that by prefilling the cube for people with basic selections that run when the user security changes which we were doing via a process anyway. So if a user has access to 10 Business Units, it would prepopulate with the first one they had access to.

    Thanks,

    Declan



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



  • 7.  RE: PAW - How to make the selection widget show user-specific selections stored in a parameter cube

    Posted Tue June 29, 2021 05:27 AM
    Hi Declan, 

    Thank you for your reply! Sorry, it took me some time to test this but I still cannot get it to work the way you describe it.

    You wrote the following but we see a different behavior.
    "When a user first opens the PAW Book, the SUBNM is returning the last element that they selected and the DBSS formula sends it back into the cube which in turn drives the Selection Widgets... you actually notice the widget takes a split second to render - presumably as its still refreshing following the DBSS."

    In our case it seems to be the other way round, meaning the PAW selection widget is loaded first instead of the SUBNM.
    Let me try to describe the steps:

    1. A user opens the PAW book which was initially saved with element 'All' in the PAW selection widget.

    2. The user changes from element 'All' to element '123' in the PAW selection widget. The PAW selection widget element '123' gets synched with the SUBNM in the websheet and the DBSS formula sends '123' to the cube. So far so good and works as you described. 

    3. The user closes the book and opens it again.

    4. According to your description above, the element '123' should be shown in the PAW selection widget. However, in our case, the PAW selection widget is loaded before the websheet with the element 'All' (I guess because the book was initially saved with this element selected). As a consequence, the SUBNM is synched with element 'All' and the DBSS overwrites '123' with 'All' in the cube.

    I think if the websheet would load first, it would work as you describe. Is there a way to make the websheet load first in the PAW book or is there something else I am missing?

    Thank you,
    Mario


    ------------------------------
    Mario Hasler
    ------------------------------



  • 8.  RE: PAW - How to make the selection widget show user-specific selections stored in a parameter cube

    Posted Thu June 17, 2021 12:07 PM

    Mario-

    I love your solution, yet there may be a simpler way utilizing alternate hierarchies.

    In the following screen shot, the Item dimension (on the rows) is set to show the leaves and a Total (using an aggregation calculation).  The alternate hierarchy, called Category1 shows only the all member in the categories.  When the user makes any adjustment to the selectors Item leaves on the rows update automatically.



    Chris



    ------------------------------
    Chris Courim
    ------------------------------



  • 9.  RE: PAW - How to make the selection widget show user-specific selections stored in a parameter cube

    Posted Thu June 17, 2021 03:01 PM
    Hi Chris,

    Thank you for sharing your solution. Interesting approach - will test it.

    Regards,
    Mario

    ------------------------------
    Mario Hasler
    ------------------------------



  • 10.  RE: PAW - How to make the selection widget show user-specific selections stored in a parameter cube

    Posted Thu December 09, 2021 08:34 AM

    Excellent post, Chris! Thanks for sharing !

    Alternate hierarchies, combined with synchronizing the hierarchy in the exploration view may resolve some of the current limitations. It's absolutely brilliant it brings the leaf level elements when the subset on rows is set to show All leaves.
    Similar approach (without using alternate hierarchies, but the original hierarchy) defaults to just showing the selected element + immediate children. This may also be useful in some cases. I noticed PAW just uses the DRILLDOWNMEMBER function for it.

    Bottom line is we need either:

    1. A way for PAW to store currently selected member in a dimension selector / selector widget (without a need to create a user selection cube + flat pick list). Then have the ability to reference that element further in other MDX subsets. Maybe have a control cube }UserSelections having }Clients, }Dimensions and potentially a measure dimension that is able to read from PAW 
    2. Have the picklist showing the hierarchy indentation users are typically looking for

    Thanks !

     



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