TM1 Forum

I would like to connect our TM1 instance to Power BI. I was wondering whether someone has done this before and has any guiding tips for the process? I think about using the Python library TM1Py. Any other suggestions?

I would highly recommend using TM1PY for this. Once you have the syntax down on tm1py set up, it is really easy to pull in additional information.

Have a look at these web pages on cubewise. They should help you get started.

https://code.cubewise.com/tm1py-help-co ... connection

https://code.cubewise.com/blog/getting- ... with-tm1py

https://code.cubewise.com/tm1py-help-co ... o-power-bi

If you get stuck along the way come back here and I or someone else will nudge you on the right path

Step 1 > install python/anaconda
Step 2 > install tm1py
Step 3 > compose python script to use in power bi

Thanks for your response! I am familiar with Python so that's already something. I would like to create a local TM1 instance to run some tests. Do you know if it's possible to create this without the need to buy and install the IBM Cognos software?

jr34t6 wrote: ↑Wed Feb 16, 2022 10:44 am Thanks for your response! I am familiar with Python so that's already something. I would like to create a local TM1 instance to run some tests. Do you know if it's possible to create this without the need to buy and install the IBM Cognos software?

You would need to speak to your IBM or Business Partner Reseller representative for a definitive answer on this.
Generally speaking though, "development" instances need to be licenced - so assuming you are doing read only requests to pull data from TM1 I would expect connecting to your companies existing TM1 Dev server should be safe enough.

Although one other thing is that it might be worth contacting your rep anyway to see how they view presenting TM1 data through PowerBI from a licensing perspective.
Generally speaking there have been instances in the past of major vendors suggesting that having a "live" pull of data presented to users through a visualisation tool *should* require those users to be licenced in the source tool (SAP vs Diageo comes to mind).
Normally you can get around that by having it moved into an intermediary layer in-between (e.g. a Datawarehouse.)

I am by no means saying this would be IBM's stance, but its worth checking to avoid being hit with a big/unexpected bill down the line.

Thanks for your response! I am now trying to connect to the TM1 instance. I need a http port number and I am unsure where to find it. I have found that 9510 leads to the web application of TM1. I also tried 5898, 5895, 12345 and others since these seem to be default. If I understand correctly I should be able to find this in the tm1s.cfg file that is on the server. The problem is: I only have access to the server through Architect and the web application. Is there another way to get the port number? I have already asked the administrator, but it would be great if I would be able to find it on my own as well.

You should see httpportnumber in the tm1s.cfg

If its there that is the port you need, if it's create it and set it to a port not used and restart the service and open the port on your firewall (if needed)

If you have admin access to tm1 you can also check the port in use through the server message log in architect. When the service is started up the parameter will show in the log along with the port being used

I managed to create a connection to the TM1 database through TM1py!

Another question. I am trying to come up with a MDX query that will provide me with columns that have the dimensions as header. The dimension 'Product' would become the column 'Product' and would need to be joined with the other columns (in total there are 17 dimensions, so 16 other columns). This means the resulting table is unpivoted. Could anyone help out with the right query? I have included a screenshot of the structure.

Write your mdx and then use the tm1py function execute_mdx_dataframe. If you need help with the mdx, check out mdxpy. I haven't used it personally, but it'll make writing mdx easier. Otherwise, take a stab at writing your mdx and post back here if you're having issues. I won't write it for you, but below is a start; I didn't specify all dimensions, you should in yours.

edit: fixed extra "{" in second {TM1Su....

If you haven't already I'd recommend starting out by pulling a public or private cubeview into Powerbi first. It's easier to do, and would help you confirm you have the right python syntax for powerbi.

Once you have this move onto the mdx view and maybe try a practise cube first with 3 dimensions and then move onto your 16 dim cube.

Thank you for the recommendations! I have managed to import individual dimensions into Power BI. I have been following this tutorial: https://code.cubewise.com/tm1py-help-co ... o-power-bi

However, now I am struggling to get the Measures data into PBI. This view has the correct columns, but it seems to be too large too process. I get the error "SystemOutOfMemory". Is it possible to limit the amount of rows and process the view in batches? I am still not sure how I can else get the 'Measures' out of the cube.
Doing it like above gives me a syntax error. Which is strange as the syntax should be correct?

jr34t6 wrote: ↑Fri Feb 18, 2022 3:06 pm

Code: Select all

SELECT NON EMPTY
	{TM1SubsetToSet([Product], "TEC_FAPMonthlyCompanyDetails_ADM_Standard Export")}
	* {{TM1SubsetToSet([Cost Center], "TEC_FAPMonthlyCompanyDetails_ADM_Standard Export")} ON 0
FROM [FAP]

Doing it like above gives me a syntax error. Which is strange as the syntax should be correct?

There was an extra "{" in the mdx I had posted. I've edited my original post and pasted the updated below.

jr34t6

Please remove sensitive information like usernames and passwords !

Hi!

The link is successfully made, thank you for your help!. However, I am now struggling to transfer the consolidations of the TM1 database to Power BI. What would be the correct way to be able to drill down on the consolidations in Power BI?

Any suggestions? Maybe using 'get_members_under_consolidation'?

One option I now consider is to create a view with consolidated accounts and a view with unconsolidated accounts and use either for a specific purpose. However, it would be great if you could roll-up/drill-down in Power BI as you can in TM1.

In TM1, I can select a view that has all levels of subsets. For example, for the time period I could view:
2020, 2020Q1, 2020Q2, 2020Q3, 2020Q4, 202001, 202002, 202003...202012 simultaneously. But to create a drill-down structure in Power BI it needs to be transformed again, making it a not so elegant solution. Also, this transformation needs to be done manually, which is a lot of work for all the different accounts.

I could also 'filter by level' in the View, but then the consolidation levels disappear.

Would there be an elegant solution for transferring the roll-up structure from TM1 to Power BI?

jr34t6 wrote: ↑Wed Apr 20, 2022 8:11 am Would there be an elegant solution for transferring the roll-up structure from TM1 to Power BI?

You can certainly roll your own with tm1py but have you looked at TM1Connect? If you don't want to come up with your own logic of converting TM1 hierarchies into tabular star schema then this is the closest I have seen to an OOTB product approach to streaming TM1 data into a reporting tool built to understand relational as oposed to OLAP data sources.

I am not sure if a star schema is what I try to accomplish. I would like to drill down on aggregated accounts, so for example I would like to take all data from account '1', then drill down to account '2' and '3', which when summed are account '1'.

Example, list of accounts in TM1: Account 10, 20 and 31, are aggregate accounts here. Accounts 333, 432, 454 and 411 are on the same level. Also, 5423, 4562 and 3432 should be on the same level. Note that 3432 is not on the same level in TM1, but should be interpreted in Power BI on the same level as 5423 and 4562. Does this make sense?

Is there a way to export the sign that is used for a measure in a consolidation in TM1Py?