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

Post Reply
jimhung777
Posts: 5
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 » Mon Aug 07, 2017 10:24 pm

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: 166
Joined: Fri Oct 17, 2008 2:40 am
OLAP Product: TM1
Version: 9.x
Excel Version: 200x
Location: Singapore

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

Post by kangkc » 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.

jimhung777
Posts: 5
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 » Tue Aug 08, 2017 7:12 am

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?

User avatar
tomok
MVP
Posts: 2335
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 » 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.

jimhung777
Posts: 5
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 » Wed Aug 09, 2017 1:07 am

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: 1538
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

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

Post by Wim Gielis » Wed Aug 09, 2017 2:09 am

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

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 103 TM1 articles and a lot of custom code
Newest blog article: TM1 message log analysis with Power Query

jimhung777
Posts: 5
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 » 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

Wim Gielis
MVP
Posts: 1538
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

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

Post by Wim Gielis » Thu Aug 10, 2017 6:22 am

Correct, a Tableau forum will probably give you the answers.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 103 TM1 articles and a lot of custom code
Newest blog article: TM1 message log analysis with Power Query

lotsaram
MVP
Posts: 2998
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TM1, CX
Version: TM1 10.2.2 PA 2.0x
Excel Version: 2010 2013 365
Location: Switzerland

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

Post by lotsaram » Thu Aug 10, 2017 6:49 am

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: 5
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 » Fri Aug 11, 2017 12:29 am

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: 549
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: 10.1.1 and 10.2.2
Excel Version: 2013

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

Post by paulsimon » Tue Aug 15, 2017 9:03 pm

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