Currently, we have a file being created that we are moving for the data warehouse consumption and reporting purposes. We are currently looking to see if we could pull the data we want with the REST API. We have the API up and running and I am able to get results in Postman but... I can not find any documentation on formatting the data into a useful JSON format. Do any parameters exist that would allow me to pull the data in a flat format?
The only documentation I have seen show this query string for the api: $expand=Axes($expand=Hierarchies($select=Name),Tuples($expand=Members($select=Name))),Cells
I have used both the View and MDX execute to pull data.
Any help would be greatly appreciated.
Thanks
REST API - Pull data for data warehouse
- Steve Rowe
- Site Admin
- Posts: 2417
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
Re: REST API - Pull data for data warehouse
Hi,
I'm not going to able to help directly other than to suggest that you look at TM1PY, which provides a python wrapper around the RESTApi.
Assuming you have access to some python skills it may be more straightfoward than doing this in REST from the ground up.
Cheers,
I'm not going to able to help directly other than to suggest that you look at TM1PY, which provides a python wrapper around the RESTApi.
Assuming you have access to some python skills it may be more straightfoward than doing this in REST from the ground up.
Cheers,
Technical Director
www.infocat.co.uk
www.infocat.co.uk
- macsir
- MVP
- Posts: 785
- Joined: Wed May 30, 2012 6:50 am
- OLAP Product: TM1
- Version: PAL 2.0.9
- Excel Version: Office 365
- Contact:
Re: REST API - Pull data for data warehouse
Have a read about one of the IBM official manuals. You definitely can implement this by any programming languages.
https://www.ibm.com/support/knowledgece ... t_api.html
https://www.ibm.com/support/knowledgece ... t_api.html
-
- Posts: 2
- Joined: Thu Dec 17, 2020 2:52 pm
- OLAP Product: TM1/Planning Analytics
- Version: v2.0.9.1
- Excel Version: Office 365
Re: REST API - Pull data for data warehouse
Thanks for the reply.Steve Rowe wrote: ↑Thu Feb 04, 2021 7:48 pm Hi,
I'm not going to able to help directly other than to suggest that you look at TM1PY, which provides a python wrapper around the RESTApi.
Assuming you have access to some python skills it may be more straightfoward than doing this in REST from the ground up.
Cheers,
We are looking into TM1PY. We are currently spinning up a sandbox for our ETL tool to try it out there. I was just hoping I may have missed a parameter for the api that could format the response.
-
- Site Admin
- Posts: 1454
- Joined: Wed May 28, 2008 9:09 am
Re: REST API - Pull data for data warehouse
TM1PY is hands down the best way to go. But if you are insistent on doing it by hand then you could have a dig round the TM1PY library to find out what that does to format the response.
Watch out for volume issues - I've seen writeups by Paul Simon inter alia that Web Services aren't good for bulk data. I doubt that would be an issue on the TM1 side though, though you might have to split up the view for memory issues.
Watch out for volume issues - I've seen writeups by Paul Simon inter alia that Web Services aren't good for bulk data. I doubt that would be an issue on the TM1 side though, though you might have to split up the view for memory issues.
-
- MVP
- Posts: 2832
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: REST API - Pull data for data warehouse
I don't know what your volume of data is going to be but if it is anything more than a few thousand records or so this is definitely NOT the way to go. While the new REST API is great for standardized data access from the TM1 engine, there is a tremendous amount of overhead in it. If you are talking about basically a data dump out of a cube for importing into a DW then I would never use the REST API. A TI process that generates a flat file for uploading into the DW is going to be the way to go.