At some point, I got labeled as a “Tableau cube expert.” The funny thing is, when I first acquired this label I just barely knew how to spell OLAP. But over the years, I’ve gained some experience working with Tableau and cubes. And I get questions almost weekly about how Tableau works with cubes and for some Tableau tips and tricks for working with cubes. Here are my thoughts:
The Cube Paradigm
Tableau works with cubes by translating VizQL (the Visual Query Language Tableau generates based on where you drag and drop fields in the view to visually encode the data) into MDX and issuing that as a query to the cube, getting back the results, and then rendering the visualization.
When cubes are well designed and when you’ve got a developer or team of developers who are pretty quick to turn-around requests for cube enhancements, it can be a fairly enjoyable experience to work with Tableau against a cube. The key to understand, is that cubes are designed to “pre-aggregate” the data, meaning that all the aggregations are defined already (so if it is an average in the cube — that’s what you get!) and the values are stored for each intersection of dimension values.
What this means is that the cube is where most of the analytical logic must take place. When you are working with relational data sources, Tableau offers a wealth of options for implementing business rules, logic, and analysis: calculations using dimensions, creation of new dimensions, groups, bins, LOD, Top N Filters/Sets using an aggregation of the dimension itself, custom aggregations of any row level field, complex calculated filters, data source filters, view underlying row-level data, etc…, etc…, etc…
But those are not options with a cube. Why? Because cubes are designed to handle all of that themselves. In fact, most of it is pre-calculated and stored in aggregate form — so you can’t simply create new slices of data or new custom aggregations. That takes away a lot of complexity from Tableau (so give a business executive a connection to a well-designed cube with business rules baked in and a license of Tableau and she’s pretty safe in the analysis she does), but it also takes away a lot of flexibility.
There’s still a lot you can do with Tableau — statistical functions are mostly there (sans forecasting), table calcs (sans referencing dimension fields – but I can’t think of a reason why the table calc shouldn’t be able to reference a dimension – hence: this idea for which you can vote) And, of course, you can visualize what’s in the cube.
But if you find yourself needing to do analysis using fields not in the cube or want to implement complex business rules or logic – then be prepared to implement that in the cube. Or use another data source (after all, the cube was built from something!)
Tips and Tricks for Using Tableau with Cubes
At the same time, I have discovered a few tips, tricks, and workarounds that may help you should you find yourself using Tableau with OLAP:
- You cannot create an extract from a cube, but you can create an offline cube file (.cub or .cube) and use that as a data source and even distribute it as a packaged workbook (.twbx).
- You can write calculated members using MDX. This can help you group dimensional values, among other things.
- Make sure that in addition to having a date hierarchy in the cube, you also have a date dimension with the actual value of the date. That will give you ultimate flexibility in choosing how you visualize dates.
- If you can’t change the cube and are stuck with just the date hierarchy, then create a secondary source with the date parts and the full date and then blend to get the exact date value.
- For that matter, use blending to get values not available in the cube. Frustrated that you can’t alias dimension values when using a cube? Just create a secondary data source with the actual values and desired aliases then blend and use the alias. Sad that you can’t create ad-hoc or calculated groups? Create a secondary source with the groupings and blend!
- Use table calculations to fake calculations that you can’t do. For example:
If you wanted to get the difference between Profit for Technology and Office Supplies, you can’t write the calculation:
IF [Category] = “Technology” THEN [Profit] END - IF [Category] = “Office Supplies” THEN [Profit] END
But using the view above, you could write:
LOOKUP([Profit], Last()) – LOOKUP([Profit], First())
7. Try to convince your boss to use a nice star schema data warehouse instead.
Hi Joshua
Great to see other tableau users writing about working with cubes. Where I work we are strong believers in using cubes together with Tableau. The marriage is not perfect, but when we deliver cubes to the business then we know that the business rules are implemented correctly, that hierarchies behave and naming conform to the standard (and we are creating a lot of cubes). For us te cubes take away a lot of the complexity the business users and analysts have to deal with when connection to relational data. And an added benefit is that the cubes can be used from tableau, excel, reporting services or a lot of other tools without changing anything.
Would love to exchange stories and experiences – are you going to tc15?
Cheers
Peter
Peter, I’ll be there! Definitely would love to catch up with you.
I am certainly having problems with cubes. Not with the simple views, but with crafting a viz a very specific way.
One of the requests was to add in “ranks”. I did that in the table calc layer, but I had to bring in all members into the view, have Tableau computer the ranks for them all, and then I hid the lower ones from the viz. This greatly slowed down the view in comparison to a SQL extract.
Are table calcs cached in any way? Because it seems like it re-draws the view every time!
I have a dimension Contact in my OLAP cube. There is a calculated member in my OLAP cube which needs to show value only when the Contact dimension has a value Y. Please suggest how can I integrate the Dimension value selection with the Calculated member in Tableau
Hi Joshua,
Please help out me,
How to write conditions against Cubes in Tableau?
I am sharing clearly here,
We have 1 Dimension Geography, It has hierarchy & Levels are Country, State, City & Zip Code.
I want to write condition as below if
If [State] = “Kerala” theh “KL”
else [State] end.
I hope, you understand my situation.
Thanks
Sriman
No, not exactly. But there are a couple of options:
1. write a calculated member (MDX) and that would allow you to group dimensions (see http://kb.tableau.com/articles/knowledgebase/grouping-cube-dimensions-using-mdx) and you can use the technique to group a single value (Kerala) under the name KL. That will give you a new member — but KL will still be there and totals will double count (unless you filter out KL). There are some strange behaviors that can happen with this approach (especially with filtering).
2. Create a list of states and aliases as a separate data source and then use blending to get the aliases from the secondary source. I like this approach because it is fairly easy to implement. You’ll have to maintain the list to make sure that if you add new states you don’t start seeing them as NULL values.
Best Regards,
Joshua Milligan
Hi Joshua Milligan,
Thanks for your patience to spend time on my query.
I came to know from your inputs , We have to use the second Database to solve this situation. It is hectic process Whichever is not possible from cube side, we have to prepare at Secondary Database.
I hope Tableau will increase the Cube functionality & make easier to use Cube in back end in upcoming releases.
If my words are hard, Requesting you forgive me with your kindness.
Thanks a lot
Sriman
Hi Joshua,
It might be useful in explaining to users why they can users lookups to replace the logic calc you demonstrated. The first question I am often faced with is “How can you guarantee this calc will always work or rather, how do you know this calc will be the same tomorrow?” This is a question of misunderstanding the coding of a cube and one that is swiftly explained as member position is defined in the cube so unless the BI team suddenly choose to alter the position, the calc will persist.
Steve
Hi
My name is Charlie and I have been a Cognos OLAP Developer for 20 years before being introduced to Tableau. Currently I am working on Tableau Reports that use SAP BW (CUBE) as a data source. The thing that is frustrating me right now, and that I have just sent a note to tableau Support about is, that I can not found any documentation on supported MDX functions for creating calculated fields within a report. In Cognos I could use MDX functions like Tuple, Children, Tail, etc to create dynamic selections of data… I am not seeing that with Tableau, does a list of supported MDX Functions/Syntax by Cube source exist?
Thanks
Charlie
I wanna calculate Share % in Tableau pointing to SSAS Cube. Against relational database I would use LOD which is not possible against SSAS Cube. Is there any alternate.?
Eg: I show Top 5, top 10 products sold for a week. Along with this I want to show how much % has the top 5 or 10 contributed for overall revenue for the week.
Yeah, OLAP gets difficult, because the cube wants to take all of the analytical processing to itself. Still, you should be able to use table calculations. The first approach that comes to mind is to create a running total that also checks to see the offset from first or last – using FIRST() or LAST(). Depending on how you sorted the products, you could see if you were within the first 5 products and add to the running total (otherwise add a 0). THat’s your total for the top 5. Then have another calculation that gives you the running total (or window sum) for everything. Then divide the results to get the percentage.
Hi,In the above post you mentioned that we an create a .cube file and connect to it to create a packaged workbook. Can you clarify if we can create an offline cube for Oracle Essbase as well?
Setha, I don’t really know right off for Essbase.
Hi Joshua,
I am trying to create a calculated member for a parameter and used this formula in the formula window..
CASE Switch_Time
WHEN ‘Weekly’ THEN [Fiscal Calender].[FISC_WEEL_VAL]
WHEN ‘Daily’ THEN [Fiscal Calender].[CLDR_DATE]
END
however when i click on check formula it shows this error …
“The dimension ‘[Switch_Time] was not found in the cube when the string , ‘[Switch_Time] was parsed” .
Hi Joshua,
I have created Calculated Group member in Cube but when I tried to use that as filter new created member’s are not filtering properly and i am getting Total Sales that is wrong. Pre member(Cube grouping) filtering works good but i have problem only with my calculated group members … is this normal behavior or is there any work around ?
Hi. I’m trying to find the top 10 within a category in the cube BUT my viz currently is parameterized to allow the user to select one of three different metrics AND to select a specific region. So for example, if I have store sales data by region, I want to find the top sales, returns or # customer visits. The metric would be controlled by a dropdown. I don’t understand how I can use calculated measures to actually get the top 10 within each region. What is happening is that I have a top 10 filter that gives me the top 10 stores. When I select a specific region from the dropdown, I get maybe 4 stores in one region, 2 in another, etc – it’s not giving me the top 10 stores for each region independently when it’s narrowed down. I hope that makes sense and that it’s doable with a cube source.
This is the kind of behavior that can usually be controlled with a context filter. That is, Top N filters are done in context: either the entire data set or in the context defined by context filters (e.g. add the Region filter to context to make sure you get the Top 10 within the filtered region instead of overall). What I can’t remember (and I don’t have an OLAP data source currently to test) is whether you can create context in cubes. If not, then you’ll likely have to filter using a calculation with the RANK() function (with Region defining the partition). Hope that helps!
Please help!! I need to use the underlying query that tableau generates to show the viz into a query that I can pull in Alteryx. Does anyone know how to do this??
Sam,
You can use the Performance Recorder (located under the Help menu) to record everything Tableau does (from query to rendering) which would give you insight into the actual queries Tableau generates. Also, you can find them in the log files.
Thanks for this article. I have a data cube in an odd format (Beyond2020 or .ivt). I need to somehow convert it into a .cub or something else Tableau can read.
Any ideas? Exporting it as a flat file is not an option because the subtotals do not equal the sum of their parts as the parts were subject to rounding and suppression.
Thanks!
Erin,
I don’t know specifically. Are the values rounded in the export to a flat file? Or is it at the cube level itself?
-Joshua
HI Joshua,
Any trick to add subtotals when u have a measure in filter.
Hello Joshua,
Thanks for this post – very useful. I wanted to know if you have any ideas on how to apply an “All” option to parameters being used against a cube? Would it still be creating a calculated field such as:
[Parameter]= 6 //where 6 is the int for the alias displayed as “All”
OR
[Parameter]=[Dimension]
For my dashboard, based on 3 date parameters chosen for year, month, and week, the parameter filter needs to bring back sales amounts for particular year, month and week based on the parameter. When the user chooses 6 for their week parameter, tableau should bring back the sum of sales for the entire month/year specified.
My calculated field used to filter based on the parameters:
[concatenated parameter YYYYMoWk] = [dimension YYYYMoWk] //show only the sales sum for the particular week in the month and year
OR
([dimension month] = [parameter Month] AND [parameter Week]=6 AND [dimension year] = [parameter Year])
//show the sum of all the weeks in the month and year
Thanks!
Its a great article. Can u please help on 2 concerns.
1) how to copy cube to .cub
2) dimension hierarchies are not shown at member level in tableau, but i could see in Excel/hyperion. How to get this done.
Thank you
Ram, I know it is possible to export to a .cub file using SSAS, but I don’t know about Hyperion. I’m also not positive about what specifically might be going on with your hierarchies. Any chance you have a screenshot?
-Joshua
I am having trouble connecting to a .cub file. It was my understanding that I only need the file and the correct driver installed to connect to the cube file but I get an error saying it can’t connect to the server. Do I need to install SQL Analysis Services to connect to the cube file?
I think your understanding is correct. Is there any additional information to the error?
I am unable to connect to an offline cube(.cub) file to tableau or even excel. Can you help me out with this whether i can directly connect it to tableau or use excel a an interface?