Planning Analytics Integration With Google Sheets

Post Reply
BhunaTuna
Posts: 3
Joined: Mon Jul 19, 2021 12:58 pm
OLAP Product: Planning Analytics
Version: 209
Excel Version: 2065

Planning Analytics Integration With Google Sheets

Post by BhunaTuna » Mon Jul 19, 2021 2:30 pm

Hi there,

Within our business we would like to push some PA data into Google Sheets - I have found this TMV Gate (https://www.youtube.com/watch?v=WwH0Rq0-89M) which seems to do the job but an in-house solution would be ideal.

Has anyone done this - is it difficult etc? Just debating whether to start on this journey or is it easier to get the add-in?

Many Thanks,

User avatar
Elessar
Community Contributor
Posts: 262
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Location: Russia
Contact:

Re: Planning Analytics Integration With Google Sheets

Post by Elessar » Tue Jul 20, 2021 6:30 am

Hi,

It's can be done very easily using python and pandas:
  1. Use tm1py to load data from TM1 to a dataframe: example
  2. use gspread to load dataframe to google sheet: example
Best regards, Alexander Dvoynev

TM1 and Data Science blog: First article - Primary sales forecasting using PA, SPSS Modeler and python.

declanr
MVP
Posts: 1745
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: Planning Analytics Integration With Google Sheets

Post by declanr » Wed Jul 21, 2021 4:45 pm

How often do you need it to update?

The easiest and cheapest (free) solution would be to just export from TM1 to CSV on a schedule (with Chores) and then use the CSV Importer for Google Sheets which can also be scheduled. I should add you will need to get the CSV to somewhere with a URL before the import to Google Sheets, be that google drive or onedrive etc but the transfer from your server folder to most of those online storage solutions can be done with a small script which could be executed from TI also.
Declan Rodger

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

Re: Planning Analytics Integration With Google Sheets

Post by lotsaram » Thu Jul 22, 2021 7:23 am

You could do it with TM1Py
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

User avatar
scrumthing
Posts: 79
Joined: Tue Jan 26, 2016 4:18 pm
OLAP Product: TM1
Version: 11.x
Excel Version: MS365

Re: Planning Analytics Integration With Google Sheets

Post by scrumthing » Thu Jul 22, 2021 12:51 pm

lotsaram wrote:
Thu Jul 22, 2021 7:23 am
You could do it with TM1Py
I think you can and you should to it with tm1py. :-)

Most obvious solution from my point of view would be to use tm1py to put the data from tm1 into a pandas dataframe and push that dataframe into a google sheet. https://towardsdatascience.com/using-py ... e69422508f

If you trigger that script from within google sheets, you have something similar to an active form.
https://stackoverflow.com/questions/229 ... readsheets
There is no OLAP database besides TM1!

User avatar
Mike Cowie
Site Admin
Posts: 470
Joined: Sun May 11, 2008 7:07 pm
OLAP Product: IBM TM1/PA, SSAS, and more
Version: Anything thru 11.x
Excel Version: 2003 - Office 365
Location: Alabama, USA
Contact:

Re: Planning Analytics Integration With Google Sheets

Post by Mike Cowie » Mon Jul 26, 2021 9:37 pm

If you already have TM1 Excel reports that you've created with Perspectives or Planning Analytics for Excel (PAfE) and you'd like to generate/convert to nicely-formatted versions of the same in Google Sheets then have a look over at the "Commercial & Events" subforum for a recent post about ReportWORQ (or just reach out to me via private message).

The TM1Py => DataFrame => Google Sheets options will certainly work, too, but I wanted to mention this additional option if you hadn't already thought about leveraging existing TM1 Excel reports that you might already have.
Mike Cowie
QueBIT Consulting, LLC

Are you lost without Print Reports in Planning Analytics for Excel (PAfE)? Get it back today, for free, with Print Reports for IBM Planning Analytics for Excel!

MariusWirtz
Posts: 19
Joined: Sat Apr 08, 2017 8:40 pm
OLAP Product: TM1
Version: 10.2.2.6
Excel Version: 2016

Re: Planning Analytics Integration With Google Sheets

Post by MariusWirtz » Mon Aug 09, 2021 1:53 pm

Hi,

sorry for the late reply.
I just wanted to confirm that you can indeed build a lovely integration between Planning Analytics and Google Sheets with Python and TM1py.

I did this recently for a customer. We implemented read and write features and TI process executions.
Happy to share more details.

BhunaTuna
Posts: 3
Joined: Mon Jul 19, 2021 12:58 pm
OLAP Product: Planning Analytics
Version: 209
Excel Version: 2065

Re: Planning Analytics Integration With Google Sheets

Post by BhunaTuna » Wed Aug 25, 2021 9:46 am

Hi Everyone,

Thank you all for the replies, very helpful indeed and we managed to perform the Google Sheets integration!

Next project is to look at integrating with Tagetik our consolidation system. Apparently they have an O Data connection but has anyone got experience of trying to integrate (extract data to and from Tagetik) ???

Post Reply