Planning Analytics with Watson

Expand all | Collapse all

RestAPI Filter Cellset Error

  • 1.  RestAPI Filter Cellset Error

    Posted 18 days ago

     

    I have been working with the RestAPI to integrate our SOX Account Control System with TM1 and so far have been able to supply them with what they need to get TM1 Accounts and attributes for quarterly review.  They have asked if it is possible to filter for a single user in some cases so I tried to add a filter to my url for the returned cellset but Im getting "Bound functions are not currently supported"

     

    Working URL

    https://server:port/api/v1/ExecuteMDX?$expand=Cells($select=Value,Members;$expand=Members($select=Name,Attributes))

     

    MDX

    {

    "MDX": "SELECT {

    [}Groups].[}Groups].[ADMIN]

    } DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_NAME, MEMBER_CAPTION, LEVEL_NUMBER, CHILDREN_CARDINALITY ON 0,

    [}Clients].[}Clients].MEMBERS DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_NAME, MEMBER_CAPTION, LEVEL_NUMBER, CHILDREN_CARDINALITY,

    [}Clients].[}Clients].[SAMAccountName] ON 1

    FROM [}ClientGroups]"

    }

     

    Updated to add filter to Cells for a single name

    https://server:port/api/v1/ExecuteMDX?$expand=Cells($filter=Members/Name eq 'Admin';$select=Value,Members;$expand=Members($select=Name,Attributes))

     

     

    Is this a known issue or do I have my url wrong?  My workaround is modify the MDX to pull back a single member instead of the entire }Clients dimension.

     



  • 2.  RE: RestAPI Filter Cellset Error

    Posted 18 days ago
    Hi Robby,

    The interesting thing with querying information from a Cellset (in this case directly the cellset returned by the ExecuteMDX action) is that you are effectively querying information from the result of another query, one query expressed in MDX and the other using OData (select, expand, filter, orderby etc). What I'm really trying to say here that that might not be the most efficient way and that, if at all possible, I'd always use MDX in this case to express the complete query and only use OData queries to then either partially retrieve chunks of that result and/or augment that result by expanding navigation properties. In this case replacing [}Clients].[}Clients].MEMBERS with {[}Clients].[}Clients].[Admin]} would be way more efficient as well.

    Having said that though I'd really want to explain why the $filter doesn't work as well and apologize for the cryptic, albeit purely technically correct, error message. 

    Your filter expression, the filter expression to be applied to each and every cell in the Cells collection, is "Members/Name eq 'Admin'". Now I know your intention from your problem description and therefore immediately notice that's not what this expression does. You are asking the system to do here is to grab the Name property of the Members property and compare that to 'Admin'. The thing however is that 'Members' identifies the collection of all Members making up the cell, in your case a collection of two members, the first one representing a group and the second one representing a client. A collection itself doesn't have a property named 'Name' and TM1's implementation doesn't support functions to be executed against collections either while the OData standard leaves room for it, hence the cryptic error.

    If you really wanted to keep the MDX query as is I suppose what you wanted to do, and again I'm not suggesting you should (read: update the MDX instead;-), is filter based on the fact that one of the members has the name 'Admin' for which you could use a lambda function. And since a group can have the same name as client, and typically would in the case of 'Admin' I'd suggest you'd verify based on the unique name in that case as in:

    https://server:port/api/v1/ExecuteMDX?$expand=Cells($filter=Members/any(e:e/UniqueName eq '[}Clients].[}Clients].[Admin]');$select=Value,Members;$expand=Members($select=Name,Attributes))

    Hope that helps!

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



  • 3.  RE: RestAPI Filter Cellset Error

    Posted 17 days ago
    Hi 

    You should not try to filter on the rows. Instead use a WHERE clause eg 

    WHERE 
    [}Clients].[}Clients].[SAMAccountName]

    and that should do it.


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



  • 4.  RE: RestAPI Filter Cellset Error

    Posted 15 days ago
    Paul, WHERE clause only provides context for the actual data, and therefore would only, at best, have effect if the data were Null/Empty vs Non Empty and if Robby had a NON EMPTY clause on the axis, which he hasn't. On top of that, you can not use an attribute in the WHERE clause, SAMAccountName is an attribute, so that wouldn't work either.
    PS If it had been the SAMAccountName attribute he had been filtering on then the axis definition in the MDX should simply have gotten a FILTER function passing it the MEMBERS and then filtering the [}Clients].[}Clients].CurrentMember.Properties('SAMAccountName') by comparing that with the name he'd be after.

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



  • 5.  RE: RestAPI Filter Cellset Error

    Posted 15 days ago
    Hi Hubert

    I was confused by the question. I thought that SAMAccountName was a place marker for the client that he was trying to query rather than an attribute, as the question seemed to be about retrieving data for just one Client.

    Anyway, you seem to have resolved it. For some reason I did not see your reply before I posted mine.

    Regards

    Paul Simon

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