Is there a free way to move data from cube to Tableau?

Post Reply
jimhung777
Posts: 14
Joined: Mon Aug 07, 2017 5:39 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Is there a free way to move data from cube to Tableau?

Post by jimhung777 »

As the title, I have searched online and noticed there is a business solution to help this issue.

But if we want to do it manually, how could it possible to migrate data from tm1 cube to tableau without changing the structure.

The only way now I figure out is output data from cube to .csv and let tableau load .csv document. Is this valid?

Please kindly recommend the approaches.

Thanks in advance.
kangkc
Community Contributor
Posts: 206
Joined: Fri Oct 17, 2008 2:40 am
OLAP Product: TM1, PA , TMVGate
Version: 2.x
Excel Version: 36x
Location: Singapore
Contact:

Re: Is there a free way to move data from cube to Tableau?

Post by kangkc »

In the most simplistic way, yes. Export cube view and import into Tableau.
But things get slightly complicated if you need hierarchy structure. Most likely you will need to build TI that will export the hierarchy structure together with the data. Moreover this will be unique to a specific cube/cubes.
Tableau is tabular in nature which is different from TM1.
jimhung777
Posts: 14
Joined: Mon Aug 07, 2017 5:39 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: Is there a free way to move data from cube to Tableau?

Post by jimhung777 »

kangkc wrote: Tue Aug 08, 2017 1:27 am In the most simplistic way, yes. Export cube view and import into Tableau.
But things get slightly complicated if you need hierarchy structure. Most likely you will need to build TI that will export the hierarchy structure together with the data. Moreover, this will be unique to a specific cube/cubes.
Tableau is tabular in nature which is different from TM1.

Hi, thanks for your answer.

Yes. You got the point. If there is no hierarchy structure for the data, then that will simply like using Excel' PivotTable Methods to analyze the data.

And Tableau can provide that kind of function as well. However, when it comes to hierarchy structure, then I have no confidence to say the conversion and migration from tm1 cube to tableau will be completely successful.



How to import OLAP data to Tableau will be the issue.

How could we be possible to manipulate data in Tableau just like in Cube viewer?
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Is there a free way to move data from cube to Tableau?

Post by tomok »

Exporting data from TM1 is quite simple. You build the view you want to extract and then use ASCIIOutput to write to a text file. If you want the hierarchy for a dimension you can write a TI that dumps out the parent/child relationships. For answers on how you can import this into Tableau you should probably head over to a Tableau forum.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
jimhung777
Posts: 14
Joined: Mon Aug 07, 2017 5:39 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: Is there a free way to move data from cube to Tableau?

Post by jimhung777 »

tomok wrote: Tue Aug 08, 2017 12:48 pm Exporting data from TM1 is quite simple. You build the view you want to extract and then use ASCIIOutput to write to a text file. If you want the hierarchy for a dimension you can write a TI that dumps out the parent/child relationships. For answers on how you can import this into Tableau you should probably head over to a Tableau forum.
Thank you for your advice. Surely for the part of Tableau, perhaps asking in Tableau forum will be better. But since there are not so many ppl know about TM1 and its structure, I determine to ask questions here first. Thank you anyway. I will study on how to export the parent/child relationships. :)
Wim Gielis
MVP
Posts: 3104
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: Is there a free way to move data from cube to Tableau?

Post by Wim Gielis »

jimhung777 wrote: Wed Aug 09, 2017 1:07 am I will study on how to export the parent/child relationships. :)
If you want to avoid scripts in Turbo Integrator (even though it's not very hard) you can also right-click a dimension and choose to export to a flat file. This functionality exists out of the box.
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
jimhung777
Posts: 14
Joined: Mon Aug 07, 2017 5:39 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: Is there a free way to move data from cube to Tableau?

Post by jimhung777 »

Wim Gielis wrote: Wed Aug 09, 2017 2:09 am
If you want to avoid scripts in Turbo Integrator (even though it's not very hard) you can also right-click a dimension and choose to export to a flat file. This functionality exists out of the box.
Thanks a lot.

I have already read "Bedrock.Dim.Export" and tried to write dimension info to flat file by myself. However, I still don't know how to import hierarchy information to Tableau with programming. Since my company's structure is kind of complex, it's not possible to set it up manually every time. I need to write some scripts/code to let Tableau read the .csv and build the hierarchy automatically. Perhaps I could turn to ask questions in Tableau forum for the answer.

Thank you all again. :D
Wim Gielis
MVP
Posts: 3104
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: Is there a free way to move data from cube to Tableau?

Post by Wim Gielis »

Correct, a Tableau forum will probably give you the answers.
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
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: Is there a free way to move data from cube to Tableau?

Post by lotsaram »

jimhung777 wrote: Thu Aug 10, 2017 2:03 am
Wim Gielis wrote: Wed Aug 09, 2017 2:09 am
If you want to avoid scripts in Turbo Integrator (even though it's not very hard) you can also right-click a dimension and choose to export to a flat file. This functionality exists out of the box.
Thanks a lot.

I have already read "Bedrock.Dim.Export" and tried to write dimension info to flat file by myself. However, I still don't know how to import hierarchy information to Tableau with programming. Since my company's structure is kind of complex, it's not possible to set it up manually every time. I need to write some scripts/code to let Tableau read the .csv and build the hierarchy automatically. Perhaps I could turn to ask questions in Tableau forum for the answer.

Thank you all again. :D
I have played quite a bit with Tableau both stand alone and in conjunction with TM1 with 2 commercial products which you will find advertised if you look. Points of fact
  • Tableau doesn't seem interested in building a TM1 Connector themselves
  • the commercial products can work "in real time" although if data volumes are large then don't expect zippy performance
  • even using the connectors there is still quite some work to be done in both configuring the connector on the export side and on the Tableau side in order to have usable hierarchies in Tableau
  • if you want reports to perform in Tableau then the best option is to duplicate the TM1 data by exporting into relational tables
  • For "dimensional modeling" to work out of the box in Tableau requires a star schema in the DWH. In simplest terms think of "flattening" your TM1 dimension into a table where each column represents a level of the hierarchy. If you are writing your own export this is the format you need to come up with!
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
jimhung777
Posts: 14
Joined: Mon Aug 07, 2017 5:39 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: Is there a free way to move data from cube to Tableau?

Post by jimhung777 »

lotsaram wrote: Thu Aug 10, 2017 6:49 am
a table where each column represents a level of the hierarchy

Thanks a lot.

I think the thing is clear. We have to output the format suitable for Tableau to recognize the hierarchy and eat.

This point is very informative and useful. :D :D :D
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Is there a free way to move data from cube to Tableau?

Post by paulsimon »

Hi

Rather than writing .csv files for Tableau, you should also consider writing out to the relational database that Tableau uses. Just use ODBCOUTPUT

Regards

Paul Simon
Post Reply