Planning Analytics

Planning Analytics

Get AI-infused integrated business planning

 View Only

  • 1.  REST API (Native) View - manipulating title selection

    Posted 09/09/21 04:51 PM
    REST API (Native) View - manipulating title selection

    Hi everyone!

    I am struggeling with the TM1 REST API, especially when I try to "set" a new title element, to retrieve data from a view.

    "The whole story":
    I have created a view, belonging to cube and I am successfull in getting the structure and the cells using a call like:

    POST https://localhost:8881/api/v1/Cubes('Cube_1')/Views('View_1')/tm1.Execute?$expand=Axes($expand=Hierarchies($select=Name),Tuples($expand=Members($select=Name))),Cells

    But what I like to do now is, I want to change the selection of a title element, so that I get appropriate values depending on that selection.

    Same way, like in the architect, where you can select new values in a title an click on re-calculate.

    Is this possible using the REST API?

    What I learned from the metadata: "tm1.Execute" is a post request, which accepts a payload, where parameters like "View", "Titles", "SuppressEmptyColumns", "SuppressEmptyRows" can be handed over.


    <Action Name="Execute" IsBound="true">
       <Annotation Term="Core.Description">
          <String>Runs a view against the model.</String>
       </Annotation>
           [...]
       <Parameter Name="Titles" Type="Collection(tm1.Element)">
          <Annotation Term="Core.Description">
             <String>The values of the titles in the title axis that you are slicing the view against.</String>
          </Annotation>
          <Annotation Term="Core.LongDescription">
             <String>You must specify all titles, even though you may only want change one.</String>
          </Annotation>
       </Parameter>
          [...]
       <ReturnType Type="tm1.Cellset" Nullable="false"/>
    </Action>

    My assumption was, that I could use the "Titles" parameter, to do the selection change. Looking again into the description of the metadata it says, that "all titles must be specified, even though you may only want change one" and the parameter has the type "tm1.Element".

    And here is the point, where I am stuck:

    I was not able the get the right payload format, to handle over the titles. The two main results of playing around with the format is:

    1. CellValues are always the initial ones 
    2. Error code 278 - "Could not find entity reference in payload."

    I did my test with the sample cube "SData" using the "Region" view - but unfortunately I was not lucky, too.



    Can someone give me a hint, what I am missing?

    Here is one example payload trying to change actuals to budget:

    { "Titles": [
       {
         "@odata.etag": "W/\"2fd97ea3ed7fc6f6626cdec0e0ef5ec423a381e8\"",
         "Name": "Budget",
         "UniqueName": "[actvsbud].[actvsbud].[Budget]",
         "Type": "Numeric",
         "Level": 0,
         "Index": 2,
         "Attributes": {
           "Caption": "Budget",
           "实际值VS预算": "预算",
           "Presactivo": "Presupuesto",
           "bilattivo": "Bilancio",
           "実数と予算": "予算",
           "datenart": "Plan",
           "réelebud": "Budget"
         }
       },{
         "@odata.etag": "W/\"a6de0dd08535c6b1f043753050597d85c76136a7\"",
         "Name": "S Series 1.8 L Sedan",
         "UniqueName": "[model].[model].[S Series 1.8 L Sedan]",
         "Type": "Numeric",
         "Level": 0,
         "Index": 13,
         "Attributes": {
           "Caption": "S Series 1.8 L Sedan",
           "型号": "S 系列 1.8 L 轿车",
           "modelo": "Sedán Serie S 1.8 L",
           "modelli": "Serie S Berlina 1.8 L",
           "モデル": "S シリーズ 1.8 L セダン",
           "modell": "S Klasse 1.8 L Limousine",
           "modèle": "Berline 1,8L Série S"
         }
       },{
         "@odata.etag": "W/\"2f1348bc71c1dec2f273a77d4a8b48b664024d81\"",
         "Name": "Sales",
         "UniqueName": "[account1].[account1].[Sales]",
         "Type": "Numeric",
         "Level": 0,
         "Index": 4,
         "Attributes": {
           "Caption": "Sales",
           "帐目1": "销量",
           "cuenta1": "Ventas",
           "conto1": "Vendite",
           "アカウント1": "売上",   
           "wertart1": "Umsatz",
           "compte1": "Unités"
         }
       }
    ]}



    Any help is welcome! :-)


    Thank you very much in advance,

    M. Spyker


    ------------------------------
    Marco Spyker
    ------------------------------

    #PlanningAnalyticswithWatson


  • 2.  RE: REST API (Native) View - manipulating title selection

    Posted 09/10/21 09:39 AM
    Hi Marco,

    You need to POST or PATCH the native view in order to change a selected element on the title axis.  This basically saves the view back to the TM1 server.  I'm pretty sure here is no way to adjust the selected element on the title axis of a native view without persisting the native view back to TM1.  Here is an example of writing a native view back to TM1 with selected elements on the title axis.

    140051588200192 [49] DEBUG 2021-09-10 09:31:36.724 TM1.HttpRequest POST /api/v1/Cubes('plan_BudgetPlan')/Views
    140051588200192 [49] DEBUG 2021-09-10 09:31:37.675 TM1.HttpRequestBody {
    "@odata.type": "#ibm.tm1.api.v1.NativeView",
    "Name": "View1",
    "Rows": [
    {
    "Subset": {
    "Hierarchy@odata.bind": "Dimensions('plan_chart_of_accounts')/Hierarchies('plan_chart_of_accounts')",
    "Elements@odata.bind": [
    "Dimensions('plan_chart_of_accounts')/Hierarchies('plan_chart_of_accounts')/Elements('41101')",
    "Dimensions('plan_chart_of_accounts')/Hierarchies('plan_chart_of_accounts')/Elements('42201')",
    "Dimensions('plan_chart_of_accounts')/Hierarchies('plan_chart_of_accounts')/Elements('Revenue')",
    "Dimensions('plan_chart_of_accounts')/Hierarchies('plan_chart_of_accounts')/Elements('Other Expenses')",
    "Dimensions('plan_chart_of_accounts')/Hierarchies('plan_chart_of_accounts')/Elements('61060')",
    "Dimensions('plan_chart_of_accounts')/Hierarchies('plan_chart_of_accounts')/Elements('65022')",
    "Dimensions('plan_chart_of_accounts')/Hierarchies('plan_chart_of_accounts')/Elements('65035')",
    "Dimensions('plan_chart_of_accounts')/Hierarchies('plan_chart_of_accounts')/Elements('Adv & Marketing')",
    "Dimensions('plan_chart_of_accounts')/Hierarchies('plan_chart_of_accounts')/Elements('Operating Expense')",
    "Dimensions('plan_chart_of_accounts')/Hierarchies('plan_chart_of_accounts')/Elements('Net Operating Income')"
    ]
    }
    }
    ],
    "Columns": [
    {
    "Subset@odata.bind": "Dimensions('plan_time')/Hierarchies('plan_time')/Subsets('plan_time_2004_qtrs_and_month')"
    }
    ],
    "Titles": [
    {
    "Selected@odata.bind": "Dimensions('plan_department')/Hierarchies('plan_department')/Elements('105')",
    "Subset@odata.bind": "Dimensions('plan_department')/Hierarchies('plan_department')/Subsets('All Departments')"
    },
    {
    "Selected@odata.bind": "Dimensions('plan_business_unit')/Hierarchies('plan_business_unit')/Elements('10110')",
    "Subset@odata.bind": "Dimensions('plan_business_unit')/Hierarchies('plan_business_unit')/Subsets('All Business Units')"
    },
    {
    "Selected@odata.bind": "Dimensions('plan_exchange_rates')/Hierarchies('plan_exchange_rates')/Elements('local')",
    "Subset@odata.bind": "Dimensions('plan_exchange_rates')/Hierarchies('plan_exchange_rates')/Subsets('local exchange rate')"
    },
    {
    "Selected@odata.bind": "Dimensions('plan_source')/Hierarchies('plan_source')/Elements('input')",
    "Subset@odata.bind": "Dimensions('plan_source')/Hierarchies('plan_source')/Subsets('input')"
    },
    {
    "Selected@odata.bind": "Dimensions('plan_version')/Hierarchies('plan_version')/Elements('FY 2004 Forecast')",
    "Subset@odata.bind": "Dimensions('plan_version')/Hierarchies('plan_version')/Subsets('All Versions')"
    }
    ],
    "SuppressEmptyColumns": false,
    "SuppressEmptyRows": false,
    "FormatString": "0.#########"
    }

    Consider translating the native view to MDX in your client.  You can execute MDX without the need for the view object in TM1.



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



  • 3.  RE: REST API (Native) View - manipulating title selection

    Posted 09/10/21 03:59 PM
    Hi Community,
    hi Stuart,

    thank you very much for your answer and explanations.

    I understand now how to create / modify a view using the REST API, but at the same time I am a little bit confused:
    If it is not possible to change the selected element on the title axis of a view, what are the parameters in action "tm1.Execute" good for and what do they do in a call like:

    https://localhost:8010/api/v1/Cubes('SalesCube')/Views('Region')/tm1.Execute?$expand=Cells


    And just to be sure: Is there a difference between a "view" and a "native view"?

    If the REST API is not able change selected elements, could the TM1 Java API fullfill that requierement?


    Thank you very much in advance,

    Marco

    ------------------------------
    Marco Spyker
    ------------------------------

    Original Message


  • 4.  RE: REST API (Native) View - manipulating title selection

    Posted 09/10/21 04:13 PM
    tm1.Execute executes the view to product a cellset.  The cellset is the object you need to interact with to read and update values of cells in cubes.  Cellsets contains both the axis of the view and the cells in the view.

    There are two types of views that can be stored under the cubes in a TM1 database  

    1 - Native views.  These are the ones you see in Architect.  The logging I previously shared shows a native view being written to TM1.
    2 - MDX views.   This is really just an MDX statement.  These views are not displayed in Architect/Perspectives, but you can see them in PAW and PAfE.  You can use these views as a data source for TI processes (which is important if you are using alternate hierarchies).

    You can also generate and interact with a cellset without a view by using the ExecuteMDX action (https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=metadata-actions#restapi_v1_csdl_actions_ExecuteMDX_String_notbound).






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

    Original Message


  • 5.  RE: REST API (Native) View - manipulating title selection

    Posted 09/10/21 04:22 PM
    I just re-read your last post and took a look at this doc:  https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=metadata-actions#restapi_v1_csdl_actions_Execute_NativeView_bound

    That does seem to indicate you can set the selected element, and set suppression, as you execute the view.  I've never used these parameters before and I don't think PAW or PAfE use these either.   I will see if I can find an example of how these parameters are used.

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

    Original Message


  • 6.  RE: REST API (Native) View - manipulating title selection

    Posted 09/10/21 04:31 PM
    Hi Stuart,

    yes it would be great, if you find and share an example of how these parameters are used!

    Thx,

    Marco

    ------------------------------
    Marco Spyker
    ------------------------------

    Original Message


  • 7.  RE: REST API (Native) View - manipulating title selection

    Posted 09/15/21 03:18 AM
    Hi Stuart,

    any news on how to use the parameters for "tm1.Execute" on a a (native) view?

    Thank you very much in advance,

    Marco

    ------------------------------
    Marco Spyker
    ------------------------------

    Original Message


  • 8.  RE: REST API (Native) View - manipulating title selection
    Best Answer

    Posted 09/16/21 11:09 AM
    Yes, The body of the POST should look something like this:

    {
    "Titles@odata.bind": 
    [
    "Dimensions('dim1')/Hierarchies('hier1')/Elements('elementA')",
    "Dimensions('dim2')/Hierarchies('hier1')/Elements('elementB')"
    ],
    "SuppressEmptyColumns": false,
    "SuppressEmptyRows": false
    }

    The above example assumes you have two dimensions in the title axis of your view.

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

    Original Message


  • 9.  RE: REST API (Native) View - manipulating title selection

    Posted 09/16/21 02:40 PM
    Hi Stuart,

    thank you very, it is working as designed! :-)


    Thank your very much for your help, support and time.

    Best regards,

    Marco

    ------------------------------
    Marco Spyker
    ------------------------------

    Original Message