TM1py and python integration with TM1

Post Reply
amit_hhh
Posts: 28
Joined: Wed Jan 18, 2017 3:27 pm
OLAP Product: Cognos TM1
Version: 10.2.2, 11
Excel Version: Excel2010
Location: Bangalore, India

TM1py and python integration with TM1

Post by amit_hhh » Mon Jun 15, 2020 6:00 am

Hi Everyone,

This is a general question regarding TM1 and python integration. I have recently started exploring TM1py library and based on sample codes what I could understand is that it is used for creating dim, subsets, cube , perform CRUD operations etc. What is unclear to me is that when we can do all these operations from TM1 architect or PAW, what is the use of python integration? Maybe there is a broader picture of this which I am unable to see for now and would like your expertise on below questions:
1. Why use python with TM1?
2. What are the benefits we get which we are unble to get so far with existing TM1 tools?
3. How to best use python in existing tm1 models?
4. What is the future of TM1 and python integration?
5. Has anyone used ML on top of TM1 so far? If yes, could you please share some insights how we can start doing so?

Looking forward to hear from you!
Regards,
Amit Saxena
India

David Usherwood
Site Admin
Posts: 1419
Joined: Wed May 28, 2008 9:09 am

Re: TM1py and python integration with TM1

Post by David Usherwood » Mon Jun 15, 2020 8:13 am

My take on your questions:
1. Why use python with TM1?
To do something you can't do with the builtin tools

2. What are the benefits we get which we are unable to get so far with existing TM1 tools?
Integration:
  • With Python libraries for functionality which is not in TM1 - eg IRR, stats
  • With other products, via their Rest APIs
  • With multiple TM1 instances
3. How to best use python in existing tm1 models?
If you don't have a need, don't bother. All you need to do is to set /check the HTTPPortNumber in tm1s.cfg.

4. What is the future of TM1 and python integration?
Bright (IMO). Mariusz and Cubewise have done an excellent job and continue to support it (last update on 12 June 2020).

5. Has anyone used ML on top of TM1 so far? If yes, could you please share some insights how we can start doing so?
Don't know (assuming ML = Machine Learning?). Covered by 2 above I suspect.

scrumthing
Posts: 51
Joined: Tue Jan 26, 2016 4:18 pm
OLAP Product: TM1
Version: 2.0.9
Excel Version: too many

Re: TM1py and python integration with TM1

Post by scrumthing » Mon Jun 15, 2020 10:36 am

Just to show some use cases we had so far.

We had to do a license audit and needed to provide the users for all of our tm1 instances. We have about 80 instances on 30-40 servers. Utilizing tm1py I wrote a script that logged into every instance and returned a list of users and their type via the rest api. License audit done in about 5 minutes. Try doing that any other way!

Another use case I am currently working on. Provide tm1 data for our internal data lake. We could of course export a csv file to import into the data lake. But with tm1py the data lake team can use their existing knowledge of python to extract the data whenever they have a need for an update and do not rely on someone in the tm1 team to trigger the export for them. They can easily extract all necessary metadata with one or two lines of code.

Of course you can do machine learning and that kind of stuff. If you need further information try www.nicholasrenotte.com. He is a data scientist at IBM and does some awesome stuff with tm1, python and weird data science stuff. :-)

Hope that helps...
There is no OLAP database besides TM1!

lotsaram
MVP
Posts: 3429
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: TM1py and python integration with TM1

Post by lotsaram » Mon Jun 15, 2020 1:23 pm

scrumthing wrote:
Mon Jun 15, 2020 10:36 am
Another use case I am currently working on. Provide tm1 data for our internal data lake. We could of course export a csv file to import into the data lake. But with tm1py the data lake team can use their existing knowledge of python to extract the data whenever they have a need for an update and do not rely on someone in the tm1 team to trigger the export for them. They can easily extract all necessary metadata with one or two lines of code.
External system pulls can of course be a little risky without some rules of engagement as administrators (and users) of external systems are unlikely to be aware of (or care about) peak load times in the TM1 system(s) and often such external queries are by their nature large and resource intensive.

(Quite off the main topic here but I thought relevant to point out).
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

scrumthing
Posts: 51
Joined: Tue Jan 26, 2016 4:18 pm
OLAP Product: TM1
Version: 2.0.9
Excel Version: too many

Re: TM1py and python integration with TM1

Post by scrumthing » Mon Jun 15, 2020 4:29 pm

lotsaram wrote:
Mon Jun 15, 2020 1:23 pm
External system pulls can of course be a little risky without some rules of engagement as administrators (and users) of external systems are unlikely to be aware of (or care about) peak load times in the TM1 system(s) and often such external queries are by their nature large and resource intensive.

(Quite off the main topic here but I thought relevant to point out).
Not at all off topic but instead very important. We currently solved that by providing native view for the export. That way we can control the amount of data. Of course there is no way stopping all the million stupid ways people can circumvent that. :-)
But the amount of data can definitely be an issue. That is why I try to figure out a way of pagination. But that as well can only prevent some of the possible cases in that area.
So thanks @lotsofram for bringing that up. Was thinking about restricting the service user for accessing the export to certain hours during the night. Otherwise no read access. But I have to think more about that.
There is no OLAP database besides TM1!

Wim Gielis
MVP
Posts: 2529
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: TM1py and python integration with TM1

Post by Wim Gielis » Mon Jun 15, 2020 4:34 pm

scrumthing wrote:
Mon Jun 15, 2020 10:36 am
Just to show some use cases we had so far.

We had to do a license audit and needed to provide the users for all of our tm1 instances. We have about 80 instances on 30-40 servers. Utilizing tm1py I wrote a script that logged into every instance and returned a list of users and their type via the rest api. License audit done in about 5 minutes. Try doing that any other way!

Another use case I am currently working on. Provide tm1 data for our internal data lake. We could of course export a csv file to import into the data lake. But with tm1py the data lake team can use their existing knowledge of python to extract the data whenever they have a need for an update and do not rely on someone in the tm1 team to trigger the export for them. They can easily extract all necessary metadata with one or two lines of code.
In order to boost the adoption of tm1py and Python in cooperation with TM1, maybe it would be good to post some parts of what you used in the “Useful code, tips and tricks” subforum. Of course, anonymous data and names and such.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

Emixam
Posts: 76
Joined: Tue May 21, 2019 3:33 pm
OLAP Product: TM1
Version: PA 2.0.x
Excel Version: 2016
Location: The Internet

Re: TM1py and python integration with TM1

Post by Emixam » Mon Jun 15, 2020 6:54 pm

amit_hhh wrote:
Mon Jun 15, 2020 6:00 am
1. Why use python with TM1?
2. What are the benefits we get which we are unble to get so far with existing TM1 tools?
3. How to best use python in existing tm1 models?
4. What is the future of TM1 and python integration?
5. Has anyone used ML on top of TM1 so far? If yes, could you please share some insights how we can start doing so?
I just saw on Linked In that Cubewise are doing a Webinar about the Top 5 use cases of tm1py on June 18th, 2020. You should take a look !

https://app.livestorm.co/cubewise-austr ... s-of-tm1py

If you can't watch it live, simply register and you'll be able to watch it whenever you want.
Enjoy !
Sorry for my English :)

David Usherwood
Site Admin
Posts: 1419
Joined: Wed May 28, 2008 9:09 am

Re: TM1py and python integration with TM1

Post by David Usherwood » Mon Jun 15, 2020 10:03 pm

Wim Gielis wrote:
Mon Jun 15, 2020 4:34 pm
In order to boost the adoption of tm1py and Python in cooperation with TM1, maybe it would be good to post some parts of what you used in the “Useful code, tips and tricks” subforum. Of course, anonymous data and names and such.
I've discussed this somewhat with Mariusz and I believe his initial preference was for 'hints and tips' to be posted on the TM1PY GitHub as 'issues'. I am not myself convinced that this is sensible and indeed I posted something myself on this forum's hints and tips about using TM1PY to manage ODBCOutput passwords (which ykud more or less demolished, but hey :) ). Let's see how this goes forward.
My current 'hints and tips' would be:
  • Executing 'loose TI' statements actually creates/executes/deletes a process - no problem but you thus have to run it as admin;
  • I had a (python) memory error writing a large cellset and found a simple pythonic way to split the cellset:

    Code: Select all

    	Cellsetsize = len(Cellset)//2
            Cellset1 = dict(list(Cellset.items())[Cellsetsize:])
            tm1.cubes.cells.write_values(cubename,Cellset1,dimensions = dims)
                            
            Cellset2 = dict(list(Cellset.items())[:Cellsetsize])
            tm1.cubes.cells.write_values(cubename,Cellset2,dimensions = dims)

User avatar
ykud
Posts: 112
Joined: Sat Jan 10, 2009 10:52 am
Contact:

Re: TM1py and python integration with TM1

Post by ykud » Tue Jun 16, 2020 12:00 am

Let's hijack this thread a bit :)
scrumthing wrote:
Mon Jun 15, 2020 4:29 pm
Not at all off topic but instead very important. We currently solved that by providing native view for the export. That way we can control the amount of data. Of course there is no way stopping all the million stupid ways people can circumvent that. :-)
Have a look at tm1cmp if you haven't already (I need to write a post about it), it has an ability to define your view as MDX or a full spec in JSON file as well that you can re-use?
scrumthing wrote:
Mon Jun 15, 2020 4:29 pm
But the amount of data can definitely be an issue. That is why I try to figure out a way of pagination. But that as well can only prevent some of the possible cases in that area.
You can use the $top=n in Rest API to return up to n records. You can see this implemented in the execute_set_mdx method in tm1py .
David Usherwood wrote:
Mon Jun 15, 2020 10:03 pm
I am not myself convinced that this is sensible and indeed I posted something myself on this forum's hints and tips about using TM1PY to manage ODBCOutput passwords (which ykud more or less demolished, but hey :) ).
I didn't mean to :) I think tm1py-samples is indeed a reasonable repo for 'how to do X with tm1py' discussions and inclusion of some code samples, but I think there are a lot fewer people there than here, so maybe it should be the other way around :)

scrumthing
Posts: 51
Joined: Tue Jan 26, 2016 4:18 pm
OLAP Product: TM1
Version: 2.0.9
Excel Version: too many

Re: TM1py and python integration with TM1

Post by scrumthing » Tue Jun 16, 2020 7:15 am

ykud wrote:
Tue Jun 16, 2020 12:00 am
Let's hijack this thread a bit :)
Yeah, let us do this. :-)
Wim Gielis wrote:
Mon Jun 15, 2020 4:34 pm
In order to boost the adoption of tm1py and Python in cooperation with TM1, maybe it would be good to post some parts of what you used in the “Useful code, tips and tricks” subforum. Of course, anonymous data and names and such.
I will do when it is working properly. But the one for usermanagement is rather simple. Don't know if it is worth it.

Code: Select all

users =  tm1.security.get_all_users
for x in users():
    print(x._name + ';' + str(x._user_type )+ ';' + str(x._enabled ))
ykud wrote:
Tue Jun 16, 2020 12:00 am
You can use the $top=n in Rest API to return up to n records. You can see this implemented in the execute_set_mdx method in tm1py .
Yes but it was missing the SKIP parameter. But I already contacted Marius about it and (as always) he was so unbelievable fast in implementing it that you can already test it: https://github.com/cubewise-code/tm1py/pull/252
There is no OLAP database besides TM1!

amit_hhh
Posts: 28
Joined: Wed Jan 18, 2017 3:27 pm
OLAP Product: Cognos TM1
Version: 10.2.2, 11
Excel Version: Excel2010
Location: Bangalore, India

Re: TM1py and python integration with TM1

Post by amit_hhh » Sun Jun 21, 2020 6:52 pm

Thank you Everyone for your insights on this. I have been exploring TM1py samples and this has really given a better picture of python and TM1 integration and its capabilities.
Can you guys please suggest some use cases on TM1py which I can start working on that you have explored?

User avatar
gtonkin
MVP
Posts: 812
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.3
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: TM1py and python integration with TM1

Post by gtonkin » Sun Jun 21, 2020 6:59 pm

Have a look at some of Nick’s videos, some really exciting possibilities open up!

hermie
Posts: 3
Joined: Wed Jul 22, 2015 6:00 pm
OLAP Product: TM1
Version: PA 2.0.x
Excel Version: 2010

Re: TM1py and python integration with TM1

Post by hermie » Mon Jun 29, 2020 8:21 am

ykud wrote:
Tue Jun 16, 2020 12:00 am
Have a look at tm1cmp if you haven't already (I need to write a post about it), it has an ability to define your view as MDX or a full spec in JSON file as well that you can re-use?
I gave tm1cmp a test drive today. Impressed, will be really useful for automated and ad hoc tests/comparisons.
You should really write a post about it!

User avatar
ykud
Posts: 112
Joined: Sat Jan 10, 2009 10:52 am
Contact:

Re: TM1py and python integration with TM1

Post by ykud » Mon Jun 29, 2020 11:15 pm

hermie wrote:
Mon Jun 29, 2020 8:21 am
ykud wrote:
Tue Jun 16, 2020 12:00 am
Have a look at tm1cmp if you haven't already (I need to write a post about it), it has an ability to define your view as MDX or a full spec in JSON file as well that you can re-use?
I gave tm1cmp a test drive today. Impressed, will be really useful for automated and ad hoc tests/comparisons.
You should really write a post about it!
Thanks! Glad you like it, that post is high on my to-do list :(

Post Reply