Getting data out of IBM Cognos TM1 into Tableau for Visualisation

by James Wakefield

Many organisations have TM1 and many now have Tableau too. So, I get asked almost on a weekly basis by people, “How do I get TM1 data into Tableau?”.

Like any good consultant, my response it always the same, “It Depends”.

The below is a good guide of the many options available:

  1. If it’s just one TM1 cube you want data from then just setup a TM1 view and export that view for consumption in Tableau.
  2. If you have lots of data or data from multiple cubes then consider writing a TM1 Turbo Integrator Process to export the data either to a csv file or to your database Tableau has as a connected Datasource
  3. Use http://tm1connect.com/ to expose your TM1 cubes to Tableau through an ODBC bridge
  4. Tridant have developed in Alteryx a Macro to extract TM1 data via MDX/API and push directly into Tableau Server
  5. Tridant are developing a TM1 connection through the Tableau Extension API

However, there are a number of considerations that I would suggest you think through before rushing into any of the above:

  • Think about the cubes and data you are exposing/exporting from TM1. Ideally you want to use a reporting cube so that the data is cached, static and approved
  • Tableau uses the concepts of Measures on data so that the user can control aggregations, Discrete vs Continuous, Data Types etc. Sometimes the Measures in TM1 are not the same usage depending on how/who built the cube and you should think exactly what needs exporting/showing on the rows/columns e.g. If exporting GL cube data then I would suggest you probably want your GL Accounts as Measures in Tableau
  • You may have created hierarchies and attributes in TM1 that you want to use in Tableau and thus you might need to use multiple
  • If creating a live connection to TM1 then don’t just give them the entire cube to drag/drop and create visualisations on. Think about the right versions and time periods
  • Finance data is a specific data reporting type with focus on cross tabs, waterfalls, kpi’s etc. Make sure your Tableau centre of excellence setup has structured training for finance people so they get to understand Tableau and how best to use Tableau on finance data
  • Make sure you have setup Tableau specific settings like the “Fiscal Year Start”
  • Consider your security profiles since TM1 Security groups will be about Read/Write scenarios
  • TM1 is an excellent aggregation engine. Be mindful however that de-aggregating and re-aggregating that data as it moves from TM1 into Tableau will have testing and reconciliation costs. For some of our clients, 0.1 represents millions of dollars! For Finance, accuracy is paramount.
  • Be mindful of data security. A benefit of using something like IBM Cognos over TM1 is that it naturally inherits your TM1 CAM security structure. Exporting the data out into Tableau may require a second maintenance point to keep your security and governance consistent.

Tridant have helped many organisations bridge the gap between their FP&A Budgeting and Forecasting needs with their BI Strategy and tools.

It is often a good start to defining what is best practice for financial reporting. Please read an overview from Tridant here: https://www.tridant.com/connecting-financial-and-operational-data-through-the-power-of-your-dashboards/

Our Tableau consultant Ana Yin also has a series of Tableau tips for Exec Dashboarding: https://www.tridant.com/lessons-from-executive-dashboarding-3/ 

Finally, we recently discussed how Anaplan integrates directly with Tableau and how Tableau use Anaplan themselves to link FP&A with Visualisation: https://www.tridant.com/a-magic-combination-anaplan-tableau/

For further information or to find out how Tridant can help you on your journey, please don't hesitate to contact us at info@tridant.com.

Share on ...

DataViz, FP&A Budgeting and Forecasting, IBM Cognos TM1, Industry News, James Wakefield, Tableau & TM1, Technical Articles, Tridant News, BI Strategy, Staff Blogs, Tableau

Tableau Data Day Out, Tableau Prep, Role Based Pricing & Much More Interview with Alteryx's Chief Data Scientist Dr Dan Putler

Recent Articles

Blog Categories

See all

Search