Tableau and cubes

我热爱Tableau，但是不喜欢用Tableau连接OLAP立方工作，因为Tableau各种最酷的功能都失效或者不尽人意。当然，我不把这个视为Tableau的失败，而只能归咎于OLAP。

I love Tableau, I do NOT however, love working with Tableau when it is connected to an OLAP cube (like Microsoft SQL Server Analysis Services).  I don’t enjoy working with cube data in Tableau because basically all the coolest parts of Tableau won’t work or won’t work in the ways you might expect.  I don’t see this as a failing of Tableau, I lay the blame on the OLAP cube.  The main issue with working against a cube in Tableau is that you talk to a cube with MDX, where we talk to almost every other data source with SQL.  MDX (or Mind Destroying Expressions as I think of them), are just a huge pain to work with.  As hard as it is for ME to write MDX, for Tableau it’s even harder. Here are some things that you should consider before committing to a Tableau project with Microsoft SQL Server Analysis Services as a data source

Top 10 things that are harder in Tableau with cubes

1. Aggregate Calculation Functions like SUM() and AVG() are not available.  These aggregations can be pre-calculated in the cube but this limits Tableau’s flexibility in creating some dynamic visualizations.
2. Custom Grouping is not available in Tableau, can be worked around with custom MDX statements.
3. Create Bins functionality is not available when connected with a cube, can be worked around with custom MDX statements.
4. Many string manipulation functions are not available when connected to a cube, can be worked around with custom MDX statements.
5. Tableau is not able to convert data types on data returned by cubes, all data types must be defined within the cube.
6. Cube KPI’s are not available within Tableau.  You can replicate this functionality within Tableau, but it can be complicated given Tableau’s other limitations when working with cubes.
7. Custom aggregations cannot be defined in Tableau; they must be predefined within the cube.
8. Quick filter display options are limited.  Quick filters retain the hierarchical look and feel of the cube, you cannot specify display options like ‘Show Single Value’ on these quick filters.
9. Tableau data extracts cannot be created from cubes.
10. Dimension aliases cannot be created within Tableau, alias values must be defined within the cube.

MDX to the Rescue!

Many of these limitations can be overcome with some custom MDX programming, but this is complex and requires additional time for development and testing.  When working with a cube as a data source for Tableau I strongly recommend that you have an MDX programming resource and/or an Analysis Services developer available to make adjustments to the cubes required to shape the data so that it can be used to create the required visualizations in Tableau.  Additional time should be added to the project plan for custom MDX coding, updates, reprocessing and retesting of Analysis Services cubes.

If you choose to connect directly to the fact and dimension tables used to create the Analysis Services cubes you won’t have any of the problems or limitations listed above.  The primary reason to use Analysis Services cubes is to pre-aggregate and pre-calculate large volumes of data to improve performance when reviewing that data.  Tableau’s extract engine can (in general) provide the same or better performance on medium to large sized data sets.  If you are working with very large data sets a column-oriented database  like Vertica or Amazon Redshift might offer superior performance to Tableau Extracts without the limitations listed above.

 Homework:

For more details and specific examples of how to work around some of the limitations noted above (using custom MDX queries) take a look at this the Cube Data Sources pages in the Tableau online help system.  They break down a lot of the issues described above and offer some guidance in how to work around them.