Page 1 of 1

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

Posted: Mon Aug 07, 2017 10:24 pm
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.

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

Posted: Tue Aug 08, 2017 1:27 am
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.

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

Posted: Tue Aug 08, 2017 7:12 am
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?

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

Posted: Tue Aug 08, 2017 12:48 pm
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.

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

Posted: Wed Aug 09, 2017 1:07 am
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. :)

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

Posted: Wed Aug 09, 2017 2:09 am
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.

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

Posted: Thu Aug 10, 2017 2:03 am
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

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

Posted: Thu Aug 10, 2017 6:22 am
by Wim Gielis
Correct, a Tableau forum will probably give you the answers.

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

Posted: Thu Aug 10, 2017 6:49 am
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!

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

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

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

Posted: Tue Aug 15, 2017 9:03 pm
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