Planning Analytics

 View Only
Expand all | Collapse all

REST API Data Load into IBM PA

  • 1.  REST API Data Load into IBM PA

    Posted Tue January 17, 2023 07:58 AM

    Our customer is evaluating API approach for loading and extracting data from IBM PA cloud. Currently we are loading data through csv files transferred to IBM FTP location. The idea is to  switch to REST API based integration.

    We are trying to understand;

    1. Is it feasible to to use REST api post method to update our sales cubes on a daily basis. Currently 3-4 csv files uploaded every day with avg size 1-2 mb , 15-20k rows maxing 300k rows (once in a while). How would be the performance with REST Api?

    2. Is "Cognos command center" the best choice for accessing API basd sources and orchestrating with other TI processes? Can we deploy Cognos command center to the same IBM PA cloud (without additional server) and access external REST Json sources and post it to IBM PA?

    Thanks in advance, would be great to hear your experience with REST based data integration on cloud.

    BR,
    James

    ------------------------------
    James Lavierre
    ------------------------------

    #PlanningAnalyticswithWatson


  • 2.  RE: REST API Data Load into IBM PA

    IBM Champion
    Posted Tue January 17, 2023 08:18 AM
    Edited by System Fri January 20, 2023 04:10 PM
    HI James,

    REST API will handle what you are looking to process. Have you had a look at TM1Py?
    This should do most of the heavy lifting for you and leaves you with the orchestration.

    In terms of orchestration, it depends. If you are on cloud, you already have CCC and could use it to perform various activities/actions.
    There are other options like Apache Airflow with significant options.

    I am sure others will be able to add further insights...

    ------------------------------
    George Tonkin
    Business Partner
    MCI Consultants
    Johannesburg
    ------------------------------



  • 3.  RE: REST API Data Load into IBM PA

    Posted Tue January 17, 2023 10:11 AM
    Hi George,

    Thanks for your answer. Have you experienced yourself with similar amount of daily data update using REST POST commands? Some says that putting all the data tuples into post body isn't feasible for that type of data. Is tm1PY better faster than REST API post action for cell update?

    BR,

    ------------------------------
    James Lavierre
    ------------------------------



  • 4.  RE: REST API Data Load into IBM PA

    IBM Champion
    Posted Tue January 17, 2023 10:17 AM

    Hi James,

    I do not have adequate use cases to give a definitive answer as my testing is largely in development environments.

    Ultimately whether TM1Py, Postman or some other language, you are always using a REST API post action.

    I am sure Marius Wirtz will add some commentary to this discussion as he has real-world experience and will likely be able to give you far more clarity than I can. We will both likely learn something!



    ------------------------------
    George Tonkin
    Business Partner
    MCI Consultants
    Johannesburg
    ------------------------------



  • 5.  RE: REST API Data Load into IBM PA

    IBM Champion
    Posted Tue January 17, 2023 10:57 AM
    TM1py has an optimized write mode that is faster than the current API functions. I'm using it the in the sample below.
    You can read more about the different write functions in TM1py here: https://cubewise-code.github.io/tm1py-tales/2021/write-with-tm1py.html

    Given that your dataset doesn't exceed 300k records, I wouldn't be concerned about performance or feasibility.

    Marius

    ------------------------------
    Marius Wirtz
    ------------------------------



  • 6.  RE: REST API Data Load into IBM PA

    IBM Champion
    Posted Tue January 17, 2023 10:51 AM
    Edited by System Fri January 20, 2023 04:14 PM
    Hi James,

    re 1, 
    IMHO REST is not just a viable approach, but perhaps the preferred choice.
     
    With TM1py updating 20k cells should be below 1 minute.
    Updating 300k cells shouldn't take more than a few minutes (of course it always depends on the cube).
     
    The biggest advantage of TM1py/REST is perhaps the features that Python brings to the table compared to Turbo Integrator when it comes to working with data (e.g. pandas) and connectivity (e.g. packages for GSheets, Salesforce, etc.).

    Here is a sample of how to connect and write a CSV to a cube in IBM Cloud with TM1py:

    write-to-tm1-cloud.py
    GitHub remove preview
    write-to-tm1-cloud.py
    You can't perform that action at this time. You signed in with another tab or window. You signed out in another tab or window. Reload to refresh your session. Reload to refresh your session.
    View this on GitHub >


     
    Disclaimer: I created the TM1py project.
     
    Marius


    ------------------------------
    Marius Wirtz
    ------------------------------



  • 7.  RE: REST API Data Load into IBM PA

    Posted Tue January 17, 2023 11:11 AM
    Hi Marius,

    Thanks for your response, the concern with tm1py is where to deploy it. As far as I know, IBM doesn't allow deploying 3rd party applications on PA cloud servers not even on rich clients . So I guess that force us to have additional server resides outside of IBM cloud environment that will host the app will be built on TM1py  (mainly because those processes will be scheduled to run, and to schedule, you would need an environment).

    BR,

    ------------------------------
    James Lavierre
    ------------------------------



  • 8.  RE: REST API Data Load into IBM PA

    IBM Champion
    Posted Tue January 17, 2023 11:26 AM
    Edited by System Fri January 20, 2023 04:17 PM
    There are different options for how to run TM1py with TM1 in the IBM Cloud.

    - you can package python + the TM1py script with PyInstaller and move the executable into the '/scripts' folder so that it can be called from TI through ExecuteCommand. Then schedule with Chores.
    - you can develop, deploy and schedule your TM1py script on IBM WatsonStudio. Alternatively, you can check out DataLore or Replit.
    - you can run your TM1py script on an on-premise server too and schedule with Task Scheduler

    I hope this helps

    Marius

    ------------------------------
    Marius Wirtz
    ------------------------------



  • 9.  RE: REST API Data Load into IBM PA

    Posted Wed January 18, 2023 03:11 AM
    Hi Marius !

    Are you sure we can move some executable file in the shared folder of PA Cloud ?

    I never succed to know if we are allowed (or not) to do that.

    In case of agree, it would be a solution for all fonctionnality like SFTP transfert, put and get file with sahrepoint and so on ( we can create powershell script  with all complements in our local environment and convert them to  exe file).

    Question : Are we allowed to move some executable files in shared folder ?

    Regards,
    Philippe

    ------------------------------
    Philippe CHAMPLEBOUX
    ------------------------------



  • 10.  RE: REST API Data Load into IBM PA

    IBM Champion
    Posted Wed January 18, 2023 05:54 PM
    Hi Philippe, 

    I can not tell you with certainty whether it's allowed or even approved by IBM.
    However, based on the conversations I have with TM1Py users, I can tell you with certainty that a lot of TM1Py users choose this approach because it works and it is easy and convenient to call the TM1py Script from TI.

    Of course, this approach has limitations. For once it is not future-proof in the long run because TM1 12 will not support ExecuteCommand (in its current shape).
    I'm hopeful that IBM will enable a more convenient way to execute Python from TM1/TI. In TM1 12 there will be a function to execute an HTTP request. With that function, you could substitute the ExecuteCommand to call a TM1py script that runs on a separate machine. Much more tidy IMO.

    There is an AMA session tomorrow, let's check with IBM :)

    ------------------------------
    Marius Wirtz
    ------------------------------



  • 11.  RE: REST API Data Load into IBM PA

    Posted Thu January 19, 2023 04:49 AM
    Hi Marius

    Thank for your answer but I AM AFFRAID ! you said "TM1 12 will not support ExecuteCommand (in its current shape)."

    Have you some informations, documentation, link I could read about the "future tm1 12 " ?
    Do you know where I ca find theses kind of informations ?

    best Regards,
    Philippe



    ------------------------------
    Philippe CHAMPLEBOUX
    ------------------------------



  • 12.  RE: REST API Data Load into IBM PA

    Posted Tue January 17, 2023 12:21 PM
    Hi @James Lavierre

    I will join George and Marius in voting for REST API / TM1py

    You will have no issues with 1-2 mb /15- 300k rows files.

    A few things I would like to bring here from my experience implementing the data upload functionality in TeamOne Google Sheets add-on:

    1. Does the data include any read-only / rule calculated values? Data quality \ error handling
    The REST API call will fail if trying to update read-only or rule calculated cells (TeamOne tm1import task has checkIfUpdatable option)
    You also have to think about error handling

    2. Who will be importing data (admins or regular users)?
    ​As you may know for IBM PA Cloud, you have to use non-interactive user accounts to connect through REST API. 
    So you may have to request additional accounts for users uploading the data

    This spring we will be launching a new cloud application that will include TM1py hosting and built-in integrations to read\write data from\to IBM PA, please let me know if you would like to hear more or become an early adopter

    ------------------------------
    Vlad Didenko
    Founder at Succeedium
    TeamOne Google Sheets add-on for IBM Planning Analytics / TM1
    https://succeedium.com/teamone/
    ------------------------------



  • 13.  RE: REST API Data Load into IBM PA

    IBM Champion
    Posted Tue January 17, 2023 03:30 PM
    @Vlad Didenko, @James Lavierre
    I agree that treating non-updateable cells needs to be considered. Same with nonexisting elements.
    I think you might want to consider both cases to be future-proof.

    If you choose a custom REST script for the integration, handling those edge cases will add a bit of complexity to your script.
    For TM1py you can use the optional skip_non_updateable argument in the write function. 

    Of course, you have to use this with care. It's in effect a bit like a "silent fail". if the whole CSV contained exclusively invalid records, it would go unnoticed if all write operations were to skip non-updateable cells.

    If the script runs as Admin some API operations will be faster as security doesn't need to be considered on every operation in the TM1 server.
    I'm assuming that would be a feasible option considering that the script is scheduled. 


    ​​

    ------------------------------
    Marius Wirtz
    ------------------------------



  • 14.  RE: REST API Data Load into IBM PA

    IBM Champion
    Posted Tue January 17, 2023 05:24 PM
    1) Yes! Performance is 'traditionally' slower over REST API than via the standard methods.
    2) It is a good choice as it is supported and supplied by IBM and it can be used to solve the challenge you have. If it suits your infrastructure and the skill base of your users/ administrators then it's a better choice

    It's a fairly recent area I've been investigating and the options I've come across so far are largely related to this post:

    https://community.ibm.com/community/user/businessanalytics/communities/community-home/digestviewer/viewthread?MessageKey=b6f428b5-3de6-4a11-a4a1-9ea19644e5c1&CommunityKey=8fde0600-e22b-4178-acf5-bf4eda43146b&tab=digestviewer

    The shorthand being
    1) POST to an existing ViewName via tm1.Execute

    2) PATCH to an existing CellSetId

    3) POST to a Cube via tm1.Update/ tm1.UpdateCells

    The other alternative I've been looking into is to generate the CellPutN/ IncrementN code via my language of choice and run the update via an Unbound process. The theory here is that the performance impact is removed. However, there are line limits to TI processes (16,384 as per @Christoph Hein and @Vlad Didenko - https://www.linkedin.com/pulse/limits-tm1-christoph-hein/) which would not make it feasible for a lot of use cases.

    What particularly interests me, is getting connected to the Excel API so the CSV can be read direct from the network​/ OneDrive/ Sharepoint etc.. and then pulled/ pushed to IBM Planning Analytics and thereby removing the need for FTP

    Working with Excel in Microsoft Graph:
    https://learn.microsoft.com/en-us/graph/api/resources/excel?view=graph-rest-1.0

    Almost any language can be utilised to use the REST API; @George Tonkin has a few IBM Community Blog posts using the REST API with Excel VBA​. I have some using Javascript. @Hubert Heijkers has an implementation using Go. You have already seen Marius and Vlad's offerings ​in this thread and I'm sure there's many more I've missed which I hope will be added to the thread

    ------------------------------
    Edward Stuart
    ------------------------------



  • 15.  RE: REST API Data Load into IBM PA

    Posted Tue January 17, 2023 06:52 PM
    Hi @Edward Stuart, those are great points!

    A few notes from my experience. I'm using both tm1.UpdateCells and Unbound process in TeamOne Google Sheets add-on.
    Of course the Unbound process is way faster and you can easily workaround the process line limits by generating & executing multiple processes. Moreover, I apply this technique to avoid potential performance issue (as TeamOne does a lot of processing directly in the user's browser).
    Moreover you can add embed your custom code to do your custom things (i.e. CellIsUpdateable)

    The only downside is regular users cannot run Unbound processes,  but if @James Lavierre needs some system \ scheduled uploads, then he can use an admin account.

    ------------------------------
    Vlad Didenko
    Founder at Succeedium
    TeamOne Google Sheets add-on for IBM Planning Analytics / TM1
    https://succeedium.com/teamone/
    ------------------------------



  • 16.  RE: REST API Data Load into IBM PA

    IBM Champion
    Posted Thu January 19, 2023 06:01 AM
    There would be no issue creating "Bound" processes which can be run by any user from any interface and having a clean-up process once those processes have been run. i.e. the final line in each TI updates a flag in a Process Control cube, when the flag is set then the clean-up process Deletes the process(es)

    ------------------------------
    Edward Stuart
    ------------------------------



  • 17.  RE: REST API Data Load into IBM PA

    Posted Thu January 19, 2023 12:21 PM
    Hi @Edward Stuart, when creating an "Unbound" you loop though your data (file lines) and compile the required CellPutN\S statements.

    A few issues with "bound"​ processes:
    1. How can you pass your data (file lines) to the pre-created bound process?
    2. Since the processes are executed with ADMIN rights, users will be able to upload any data even if you have some security restricting WRITE access


    ------------------------------
    Vlad Didenko
    Founder at Succeedium
    TeamOne Google Sheets add-on for IBM Planning Analytics / TM1
    https://succeedium.com/teamone/
    ------------------------------



  • 18.  RE: REST API Data Load into IBM PA

    IBM Champion
    Posted Fri January 20, 2023 07:49 AM
    I see them as advantages rather than issues. Model integrity must be maintained and by bringing the dataload into the model you are ensuring business processes and security requirements are being applied.

    1) The process can be created on the fly by posting to "api/v1/Processes" and in the body update the "PrologProcedure" with the CellPutN/ S etc.. lines. If the data load is regular (daily/ weekly etc..) then a pre-created process can be created and the same PATCH call can update with the new values.

    2) If Security on Process(es) is set/ required then the TI process generated can also include these details

    ------------------------------
    Edward Stuart
    ------------------------------



  • 19.  RE: REST API Data Load into IBM PA

    Posted Fri January 20, 2023 08:22 AM

    Here is a link to a document on the TM1 Rest API https://ibm.box.com/s/56yeauz4chc4qsf5a9pkqxvhu3v6umvj



    ------------------------------
    paul YOUNG
    ------------------------------