Planning Analytics

Planning Analytics

Get AI-infused integrated business planning

 View Only
Back to discussions
Expand all | Collapse all

TI Data Source using MDX View on }ElementAttributes with Hierarchy

  • 1.  TI Data Source using MDX View on }ElementAttributes with Hierarchy

    Posted Fri September 06, 2019 01:49 PM
    Hi

    I want to read all attribute values from the consolidated members of a Named Hierarchy.

    I understand that the only way to create a source view for a TI process that references Named Hierarchies in one or more dimensions is to use an MDX View. 

    I can generate the MDX easily enough to show this in PAX. However, PAX subtly modifies the MDX behind the scenes. As well as adding a HEAD 500, it is changing the ON ROWS to ON 0. If I try ON 1, it again gets changed to on 0. In other words, if you try to put all Hierarchies/Dimensions ON ROWS, PAX is changing this to ON COLUMNS. However, even though it changes the MDX to say ON COLUMNS (ON 0), when it displays the data it does show it on rows. There is probably some fix in PAX to adjust for this since in Excel you will potentially run out of columns, way before you run out of rows.

    If I use TI CreateViewByMDX to create an MDX View using the same MDX as in PAX, and try to use this as a Data Source in TI, then the fix to get the data to rows is not happening, everything is on columns which is useless. I get every member of the Named Hierarchy crossed with every Attribute on columns. If I try changing the ON 0 to ON 1, the MDX fails and the view is not created.

    The usual way around this with any other cube is to use a measures dimension but that is not possible with an }ElementAttributes cube since the }ElementAttributes dimension is the last dimension in the cube

    Is there a way around this?

    I want to use a view so that I get the benefit of zero suppression.

    At present it seems that the only options I have are :

    a) Modify the process to call a sub process which passes in each Attribute in turn. The sub process can then create an MDX View with the single Attribute on columns and the subset of the Named Hierarchy on rows. Then I can read in values from this view and get the benefit of zero suppression from the MDX View. However, that seems a little tedious.

    b) Read every consolidated member of the Named Hierarchy using an MDX Subset which I can use as a data source, and then loop through all attributes and CellGet the value. This will work but I won't get any benefit from zero suppression.

    Is there another way?

    Regards

    Paul Simon




    ------------------------------
    Paul Simon
    ------------------------------

    #PlanningAnalyticswithWatson


  • 2.  RE: TI Data Source using MDX View on }ElementAttributes with Hierarchy

    Posted Mon September 09, 2019 07:29 AM
    Paul - Can you share the MDX statements that you are getting from PAX, and the MDX used as the data source for the Ti?  The data source for an Ti always needs to be a table of data.  I think it make sense both dimensions in the }ElementAttributes cube to be columns.  In this case the Ti should have three columns (Element Name, Attribute, Attribute Value) where the Element Names are only elements belonging to the named hierarchy.

    I'd love to try this for myself if you can share enough of the TM1 model so I can reproduce.  You are welcome to reach out at stuart.king@ca.ibm.com.


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

    Original Message


  • 3.  RE: TI Data Source using MDX View on }ElementAttributes with Hierarchy

    Posted Wed September 11, 2019 10:44 AM
      |   view attached
    Paul-

    Another option (although ugly) is to use the REST API to create a TI process using Postman.  That way you have control over the source MDX and the datasource variables.   I've attached a very rough sample.

    Chris

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

    Original Message


  • 4.  RE: TI Data Source using MDX View on }ElementAttributes with Hierarchy

    Posted Thu September 12, 2019 12:53 PM

    Hi Chris

     

    Thanks for the reply. My problem isn't so much writing the process. The problem is that what is being retrieved is a numeric 0 when it should be text. I have referred it over to IBM and I am waiting for a reply. This seems to occur specifically when a named hierarchy is used. I have tried variations on the MDX such as yours and one where the }ElementAttributes dimension is on the columns, and is then repeated on the rows nested within the dimension, which seems to be what PAX itself generates for this query, but I get the same result in both cases.

     

    Other priorities have come up, but if I don't get an explanation from IBM I will try your approach.

     

    Is there any front end tool that actually shows a named MDX View? PAX doesn't seem to show them.

     

    Regards

     

    Paul

    Paul Simon

    Success Cubed Ltd

    Tel: +44 7941 506 197

    www.successcubed.co.uk

    Skype Name: paulsimongb

    Skype Tel: +44 20 3287 4616

     




    Original Message


  • 5.  RE: TI Data Source using MDX View on }ElementAttributes with Hierarchy

    Posted Thu September 12, 2019 02:07 PM
    Hi Paul,

    I can reproduce the problem you reported with exporting from the attributes cube.  I'm investigating to determine if this is a product defect or we are missing something in the Ti script to make this work.  

    I will let you know once I know more.  Hang tight!

    One quick note, the problem doesn't seem to be specific to alt hierarchies.  I think the problem is specific to string data when using MDX as a data source.

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

    Original Message


  • 6.  RE: TI Data Source using MDX View on }ElementAttributes with Hierarchy

    Posted Fri September 13, 2019 09:06 AM
    Hi Stuart

    Many thanks for confirming that you have been able to reproduce the problem. Interesting that it doesn't necessarily only apply on named hierarchies but on any MDX based view. I guess I had never encountered it before because I only needed to create an MDX view because named hierarchies cannot be used in a native view.

    I suspect that the underlying cause is that MDX is designed for SQL Server Analysis Servers, and unless things have changed since I last used it, that can't store text, only numbers.

    I will wait to hear more. If it is going to take a while to resolve this then I will probably go with reading the full list of elements in the hierarchy and doing an ElementATTRSL for each Attribute.

    Regards

    Paul

    ------------------------------
    Paul Simon
    ------------------------------

    Original Message


  • 7.  RE: TI Data Source using MDX View on }ElementAttributes with Hierarchy

    Posted Mon September 16, 2019 04:09 PM
    Hi Paul,

    Our TM1 Server development team believes they identified a defect causing string data to not be accessed correctly when using MDX views as a data source.  We think this has never worked since we added support for MDX based views in Ti.  I will followup once we have confirmed we have a solution.  No APAR as this defect was logged internally (by myself).  If you want an APAR number to track please open a support ticket and point support to this post.  I have a testcase in hand so you shouldn't need to provide anything.

    I've requested this be fixed.  I don't think it will happen until after the next update the 2.0.9 release.


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

    Original Message


  • 8.  RE: TI Data Source using MDX View on }ElementAttributes with Hierarchy

    Posted Thu October 31, 2019 02:13 PM
    Hi Stuart

    Do you have any further information on when a fix for this is likely to arrive? It is becoming something of an issue for us here.

    Regards

    Paul Simon

    ------------------------------
    Paul Simon
    ------------------------------

    Original Message


  • 9.  RE: TI Data Source using MDX View on }ElementAttributes with Hierarchy

    Posted Thu October 31, 2019 11:06 PM
    Hi Paul,

    We have a fix ready for the 2.0.9.1 release.   2.0.9 is planned to be released within the next few weeks.  2.0.9.1 hopefully by the end of this year, maybe early next year.

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

    Original Message


  • 10.  RE: TI Data Source using MDX View on }ElementAttributes with Hierarchy

    Posted Sat November 02, 2019 03:08 PM
    Hi Stuart

    Thanks for letting me know. At least we can plan around that. At present we are putting in a temporary solution to read from the Dimension:Hierarchy ALL Subset and then use a WHILE Loop to get the Attributes. We will convert this to an MDX View when the ability to read text values is fixed.

    Regards

    Paul Simon

    ------------------------------
    Paul Simon
    ------------------------------

    Original Message