Planning Analytics

 View Only
  • 1.  REST API call against Transaction Log

    Posted Tue November 15, 2022 09:44 AM
    Edited by System Fri January 20, 2023 04:33 PM
    Hello Community,

    It's possible to query the transaction log like this:

    .../v1/TransactionLogEntries?$orderby=TimeStamp asc&$filter=(TimeStamp ge 2022-01-01T00:00:00Z) and (Cube eq 'My Cube') and (Tuple/any(t: t eq 'My Cost Center'))&$top=10

    This returns top 10 transaction log results, sorted in ascending order by TimeStamp, where TimeStamp is at/after Jan 1, 2022 0:00, from cube [My Cube], where one of the elements of the tuple (i.e. one of the cells that defines the result) is [My Cost Center].

    100% clear to me, and works in nearly all use cases.

    What happens if the members have identical names across dimensions?
    May be the case if the dimensionality is to use Boolean indicators, i.e. true/false.

    Therefore tuple can be something like
    ["true","true","true","false","false"]

    If I filter on 'true', it'll match any record that has any 'true' element.
    But what if I would like to only test the 3rd element in the above tuple?
    How can I filter based on the name of the dimension, hierarchy?

    Thank you!

    CC to @Hubert Heijkers​​

    ------------------------------
    Adam
    ------------------------------
    #PlanningAnalyticswithWatson


  • 2.  RE: REST API call against Transaction Log

    Posted Wed November 16, 2022 04:04 AM

    Hi Adam,

    The short answer is that currently I don't have a solution for that.

    Not only is it a bit unfortunate that the Tuple is just a collection of strings (had it been elements you could have looked at the dimension/hierarchy the element came from), but because TM1 doesn't support the syntax, defined in the OData v4.01 specification - TM1 was build based on the v4.0 spec, that would allow you to zoom into that one particular item from such collection.

    Given that we have started to adopt some of the newer, OData v4.01, specification we could extend the use of the substring function allowing it to take [ordered] collections. However that would still result in Tuple being a collection, of 1 in that case presumably, so the lambda would still be required to check for the value (which makes me realize a simple 'item' function would be nice - I'll create an enhancement request for the OData TC for that).
    In other words, given the latest specification, we could get to what you'd want, filtering the 3rd element (2, zero based) and it would look something like:

    .../v1/TransactionLogEntries?$orderby=TimeStamp asc&$filter=(TimeStamp ge 2022-01-01T00:00:00Z) and (Cube eq 'My Cube') and (substring(Tuple,2,1)/any(t: t eq 'true'))&$top=10

    That doesn't look too bad actually now does it;-). I'll create an enhancement request for TM1 then as well and have a peek at how easy or hard t would be to extend the substring function according to the latest specification (despite the fact we don't claim v4.01 support yet, we have adopted more compatible extension already;-).

    Cheers,



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



  • 3.  RE: REST API call against Transaction Log

    Posted Wed November 16, 2022 04:36 AM
    Edited by System Fri January 20, 2023 04:15 PM

    Hi Adam,

    While doing my [OData TC] homework I found/realized there is already away to request an individual member of an ordered collection, however that, being an addition to the v4.01 spec as well, isn't supported in TM1 just yet either.

    Having had a quick peek at the improvement that would be required for subset, I'd say that is more likely to happen sooner then the extensions to the syntax and parsing that would be required to support this addressing of individual member of an ordered array but, I just wanted to let you know that there is already syntax for this in OData v4.01 (and there not being any new enhancement request required) and once TM1 would support this your request could look like:

    .../v1/TransactionLogEntries?$orderby=TimeStamp asc&$filter=(TimeStamp ge 2022-01-01T00:00:00Z) and (Cube eq 'My Cube') and (Tuple/2 eq 'true')&$top=10

    Even nicer not;-?



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



  • 4.  RE: REST API call against Transaction Log

    Posted Wed November 16, 2022 09:12 PM
    Thanks so much Hubert! Appreciate it, glad this is on the horizon.

    ------------------------------
    Adam
    ------------------------------