TM1py and python integration with TM1

[amit_hhh]

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!

[David Usherwood]

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]

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...

[lotsaram]

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).

[scrumthing]

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.

[Wim Gielis]

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.

[Emixam]

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 !

[David Usherwood]

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)

[ykud]

Let's hijack this thread a bit

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?

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 .

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]

Let's hijack this thread a bit

Yeah, let us do this.

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 ))

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

[amit_hhh]

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?

[gtonkin]

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

[hermie]

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!

[ykud]

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