From time to time, clients create proof-of-concept dashboards in Excel or other relational data source and then hit a wall converting it to an OLAP cube. I’m never surprised to see it happen. What most people don’t realize is that Tableau behaves much differently when reading from a cube. This requires careful thought beforehand.
Unlike other data sources, an OLAP cube does all the aggregation. That speeds up your analysis and allows other analytical feats not ordinarily possible in Tableau. But it complicates things, too.
Things to consider:
1. The cube calculates many of the metrics that Tableau users usually calculate in Tableau. Tableau users love to pull data from a SQLServer or Excel and then go on to perform analytical back flips. New metrics needed? No problem. Create a calculated field, run a sub-query, or write some SQL. But in a cube, a back flip can hit a snag when a metric or hierarchy has not already been created. To create it, the user often has to go back to the cube, where it’s made via MDX. If you really can’t do without the cube’s speed, plan to work closely with the cube designers to ensure that the important metrics are in place from the start.
2. Users who know how to code in MDX can solve the limitations easily with calculated members. For example, calculated members allow users to interact with dimensions in calculated fields, allowing for continuous date fields and slider filters. MDX-skilled users can also create some new measures without rebuilding the cube. In many ways, calculated members are similar to SQL pass-throughs on a relational datasource, but are more vital to successful analysis.
3. Surprises will pop up, so extra time is necessary for experimentation and troubleshooting. During one of our last projects, we set up several action filters that ended up having problems. The first was on a table that was similar to a cross-tab, with rows of months and columns of regions, and click on a cell to filter against month and region. An action across two different dimensions is known as a “multi-dimensional slicing set,” and is not supported by Tableau. When you attempt to use the action, your charts go blank.
Despite these limitations, most of what you want to accomplish is possible with a bit of creativity and extra time to experiment. You can have the cube’s speed and other advantages and still do back flips in Tableau.
Further reading: Tableau’s list of differences