Planning Analytics

 View Only
  • 1.  Workspace - Dynamic MDX in dimension dragged outside Cube View

    Posted Mon January 25, 2021 09:49 AM

    Hi, 

    I have run into an issue in PAW and wondered if anyone had a workaround or if IBM were planning any work to address the issue. 

    The issue is as follows:

    I have created 2 attributes for the Version dimension, these attributes are called Start Year and End Year. I can reference these attributes in MDX for the Year dimension, I can then have a view in workspace and when a user selects a version it will only return the years between the version Start Year and End Year.

    The MDX is as follows:

    {TM1FILTERBYLEVEL({FILTER( {TM1SUBSETALL([1002.00 Year])}, left([1002.00 Year].currentmember.name,4)>= Left([}ElementAttributes_0001.00 Version].([0001.00 Version].currentmember, [}ElementAttributes_0001.00 Version].[Start Year]),4) and left([1002.00 Year].currentmember.name,4) <= Left([}ElementAttributes_0001.00 Version].([0001.00 Version].currentmember, [}ElementAttributes_0001.00 Version].[End Year]),4))},0)}

     

    If I add a cube view to the workspace sheet and then update the Year dimension with the above mdx this works fine, the years change depending on the version selected.

    The following image shows a version with a start year of 2019/2020 and an end year of 2021/2022

     

    The problem occurs if I want to drag the version and year dimensions outside of the cube view, when this happens the year no longer appears to know which version is the current member, so it seems I am not able to use this MDX in these situations. This is really limiting as I would like to hide the dimensions from the user. Synchronising the dimensions also doesn't work.

    The below image shows the version and year dragged outside the cube view: 


    As can be seen from the image above the years do not show correctly. 

    Does anyone have any workaround for this? Does PAW already deal with this and I am missing something?

    regards,

    Mark



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

    #PlanningAnalyticswithWatson


  • 2.  RE: Workspace - Dynamic MDX in dimension dragged outside Cube View

    Posted Thu February 11, 2021 12:27 PM
    Hey Mark,
    Have you enabled dimension synchronization for that that viewer? Depending on what version you are running the sync settings are in the properties menu on the right fly out when you select that view. 
    Cheers
    Ryan

    ------------------------------
    Ryan Clapp
    ------------------------------



  • 3.  RE: Workspace - Dynamic MDX in dimension dragged outside Cube View

    Posted Thu February 11, 2021 12:54 PM
    Hi Ryan,

    Yes I tried synchronisation (i mentioned that in my post above) but it appears as soon as the dimension is dragged outside the cube viewer it no longer references the cube, which means that mdx which uses for example, currentmember no longer works. At least this is how it appears to me.

    Now I have found a workaround with the following MDX but it seems like an hack and requires a few more objects to be created (we have a cloned client dimension, which also complicates the mdx a little):

    {{{TM1FILTERBYLEVEL({FILTER( {TM1SUBSETALL([1002.00 Year])},left([1002.00 Year].currentmember.name,4)>= Left([}ElementAttributes_0001.00 Version].(STRToMEMBER("[0001.00 Version].[0001.00 Version].[" + [}ElementAttributes_9009.00 Clients].(STRTOMEMBER("[9009.00 Clients].[9009.00 Clients]." + MEMBERTOSTR(STRTOMEMBER("[}Clients].[" + USERNAME + "]"))), [}ElementAttributes_9009.00 Clients].[Version for Move]) + "]"), [}ElementAttributes_0001.00 Version].[Start Year]),4) and left([1002.00 Year].currentmember.name,4) <= Left([}ElementAttributes_0001.00 Version].(STRToMEMBER("[0001.00 Version].[0001.00 Version].[" + [}ElementAttributes_9009.00 Clients].(STRTOMEMBER("[9009.00 Clients].[9009.00 Clients]." + MEMBERTOSTR(STRTOMEMBER("[}Clients].[" + USERNAME + "]"))), [}ElementAttributes_9009.00 Clients].[Version for Move]) + "]"), [}ElementAttributes_0001.00 Version].[End Year]),4))},0)}}}

    I also have other examples where I might want MDX based on a cube value, as soon as I drag the dimension from the view the mdx gets complicated and other objects need to be built.

    I drag dimensions from the cube view purely for user presentation and I do not feel it should lose the link the cube it was dragged from.

    regards,

    Mark

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



  • 4.  RE: Workspace - Dynamic MDX in dimension dragged outside Cube View

    Posted Thu February 11, 2021 01:32 PM

    Sorry I must have missed that. 2 things.

    • I have found that sync is not really that great at doing these kind of things. Ever since the initial release it has been really lacking.
    • One thing you could try is dragging the tile onto the workspace from a different view. Maybe duplicate your view drag the tile on to the page from one then delete that and see if the other one works.

     

     






  • 5.  RE: Workspace - Dynamic MDX in dimension dragged outside Cube View

    Posted Wed March 10, 2021 10:29 AM
    Hi Mark,

    I did a bit of digging into this behavior.  I can confirm the behavior you are seeing is expected.   We can open a new enhancement request for this use case. 

    In short, when the selector is inside the cube viewer it is being updated as a result of the view being refresh.   Refreshing the cube viewer does cause data changes in the view, as well metadata on the view (such as the member displayed in the title position) based on the use of MDX based sets.  

    When a selector is dragged out of a cube viewer it becomes it's own widget that is optionally synchronized with the cube viewer the selector was sourced from.   However, the synchronization is triggered only on specific gestures.  These gestures include making a selection of a member or subset in one of the selector widgets, and drilling on a member in the row or column position of a view.  Making a change to data in the cube viewer does NOT trigger a synchronization, even if it causes a change in the members that are displayed on axes of the view.   

    What we need is a enhancement to our synchronization capabilities that will trigger synchronization when members are changed on the axis of the cube viewer as a result of refresh on that view.   The Workspace team is currently looking at enhancements to synchronization capabilities (e.g the action button TI parameter sync in 2.0.62).   I would love to have this use case prioritized for 2021.   Let me know if this makes sense.



    ------------------------------
    Stuart King
    IBM Planning Analytics Offering Manager
    ------------------------------



  • 6.  RE: Workspace - Dynamic MDX in dimension dragged outside Cube View

    Posted Wed March 10, 2021 11:05 AM
    Hi Stuart,

    really appreciate the feedback.

    I may have to read what you said a few times for it to make total sense :)

    But it would be great if an enhancement could be opened for this and that your desire for this to be included in 2021 came to fruition.

    For me it would make the tool a lot more flexible and powerful,

    regards,

    Mark

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



  • 7.  RE: Workspace - Dynamic MDX in dimension dragged outside Cube View

    Posted Thu March 11, 2021 03:54 AM
    Hi Mark,

    Might I add, that what I'm expecting is the root problem is that the set expression (the MDX you are using for the set of elements for that Year dimension),  doesn't have a context it can draw from if used as such. You are, as Stuart implies, 'lucky' because PAW, when part of the view, executes that expression in the context of the view in which it will pass a WHERE clause that includes your Version.

    What I'm guessing that is happening here is that the server, not having a member for the Version dimension in context, will substitute it with the default member for the version dimension, which is what happens in a SELECT query as well.

    That said, there are obviously ways to convey the context, and I think that's what's implied when you move dimensions to the worksheet and set them up to be synchronized, in and MDX and still just retrieve the content of such set, unfortunately for you though you can't make that happen, the PAW developers will have to take that into account the way things are today, that's presuming they agree that is/was the intention. 

    Cheers,

    ------------------------------
    Hubert Heijkers
    ------------------------------



  • 8.  RE: Workspace - Dynamic MDX in dimension dragged outside Cube View

    Posted Thu March 11, 2021 08:58 AM
    Hi Hubert,

    We did setup a testcase to investigate the behavior reported on this post.  The root of the problem is that Workspace does not trigger synchronization when the selected member in a title axis of a view is changed as a result of the view being refreshed.  It only triggers a sync when the user actually clicks on the selector in the title axis of the view and changes the selected member.   Basically, we need the sync to be driven based on changes of data on the grid.  This will be especially useful with picklist cells and the single cell widget in Workspace.  We are going to determine the level of effort to make this happen.

    Consider the above case where a change in data in the cube viewer causes a selector in the cube viewer to change the selected member, but does not change the selected member in the selector that was dragged out of the book.  If you close that book and then reopen it, the selector that was dragged out of the cube viewer will also show the expected member (that matches the selected member in the selector that is in the cube viewer).



    ------------------------------
    Stuart King
    IBM Planning Analytics Offering Manager
    ------------------------------