Connect Cognos TM1 to MS Power BI

Post Reply
jr34t6
Posts: 10
Joined: Tue Feb 15, 2022 11:33 am
OLAP Product: IBM Cognos
Version: 123
Excel Version: 123

Connect Cognos TM1 to MS Power BI

Post by jr34t6 »

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?
burnstripe
Regular Participant
Posts: 197
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: Connect Cognos TM1 to MS Power BI

Post by burnstripe »

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
jr34t6
Posts: 10
Joined: Tue Feb 15, 2022 11:33 am
OLAP Product: IBM Cognos
Version: 123
Excel Version: 123

Re: Connect Cognos TM1 to MS Power BI

Post by jr34t6 »

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?
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Connect Cognos TM1 to MS Power BI

Post by declanr »

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.
Declan Rodger
jr34t6
Posts: 10
Joined: Tue Feb 15, 2022 11:33 am
OLAP Product: IBM Cognos
Version: 123
Excel Version: 123

Re: Connect Cognos TM1 to MS Power BI

Post by jr34t6 »

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.
burnstripe
Regular Participant
Posts: 197
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: Connect Cognos TM1 to MS Power BI

Post by burnstripe »

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
jr34t6
Posts: 10
Joined: Tue Feb 15, 2022 11:33 am
OLAP Product: IBM Cognos
Version: 123
Excel Version: 123

Re: Connect Cognos TM1 to MS Power BI

Post by jr34t6 »

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.
Last edited by jr34t6 on Wed Feb 23, 2022 7:06 pm, edited 1 time in total.
ascheevel
Community Contributor
Posts: 286
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Re: Connect Cognos TM1 to MS Power BI

Post by ascheevel »

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.

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]
WHERE ([Currency].[USD], [Transaction Currency].[CAD])

edit: fixed extra "{" in second {TM1Su....
Last edited by ascheevel on Fri Feb 18, 2022 4:43 pm, edited 1 time in total.
burnstripe
Regular Participant
Posts: 197
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: Connect Cognos TM1 to MS Power BI

Post by burnstripe »

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.
jr34t6
Posts: 10
Joined: Tue Feb 15, 2022 11:33 am
OLAP Product: IBM Cognos
Version: 123
Excel Version: 123

Re: Connect Cognos TM1 to MS Power BI

Post by jr34t6 »

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.

Code: Select all

from TM1py.Services import TM1Service

with TM1Service(address="xxxxxxx", port=xxxxxx, user="xxxx", password="xxxxx", ssl=True) as tm1:
    data = tm1.power_bi.execute_view(cube_name="xFAP", view_name="View1", private=False)
data 
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.

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?
Last edited by jr34t6 on Wed Feb 23, 2022 2:56 pm, edited 3 times in total.
ascheevel
Community Contributor
Posts: 286
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Re: Connect Cognos TM1 to MS Power BI

Post by ascheevel »

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.

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]
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Connect Cognos TM1 to MS Power BI

Post by Wim Gielis »

jr34t6

Please remove sensitive information like usernames and passwords !
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
jr34t6
Posts: 10
Joined: Tue Feb 15, 2022 11:33 am
OLAP Product: IBM Cognos
Version: 123
Excel Version: 123

Re: Connect Cognos TM1 to MS Power BI

Post by jr34t6 »

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?
jr34t6
Posts: 10
Joined: Tue Feb 15, 2022 11:33 am
OLAP Product: IBM Cognos
Version: 123
Excel Version: 123

Re: Connect Cognos TM1 to MS Power BI

Post by jr34t6 »

Any suggestions? Maybe using 'get_members_under_consolidation'?
jr34t6
Posts: 10
Joined: Tue Feb 15, 2022 11:33 am
OLAP Product: IBM Cognos
Version: 123
Excel Version: 123

Re: Connect Cognos TM1 to MS Power BI

Post by jr34t6 »

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?
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Connect Cognos TM1 to MS Power BI

Post by lotsaram »

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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
jr34t6
Posts: 10
Joined: Tue Feb 15, 2022 11:33 am
OLAP Product: IBM Cognos
Version: 123
Excel Version: 123

Re: Connect Cognos TM1 to MS Power BI

Post by jr34t6 »

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:

Code: Select all

10
|_333
|_432
20
|_454
    |_5423
    |_4562
|_411
31
|_3432
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?
jr34t6
Posts: 10
Joined: Tue Feb 15, 2022 11:33 am
OLAP Product: IBM Cognos
Version: 123
Excel Version: 123

Re: Connect Cognos TM1 to MS Power BI

Post by jr34t6 »

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