• Tableau and Cubes (OLAP)

    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:

    This represents what happens to my brain when I try to understand MDX

    This represents what happens to my brain when I try to understand MDX

    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.

    Tableau works with cubes using the same overall paradigm as any other data source.

    Tableau works with cubes using the same overall paradigm as any other data source.

     

    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:

    1. 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).
    2. You can write calculated members using MDX.  This can help you group dimensional values, among other things.
    3. 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.
    4. 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.
    5. 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!
    6. Use table calculations to fake calculations that you can’t do. For example:

    View

    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.

    Cube resistance is futile!

    Cube resistance is futile!

     

25 Responsesso far.

  1. Peter says:

    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

  2. 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!

  3. Surya says:

    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

  4. Mehar says:

    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

    • Joshua Milligan says:

      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

      • Sriman says:

        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

  5. Steve says:

    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

  6. Charlie says:

    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

  7. Harish Kumar says:

    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.

    • benmelek_admin says:

      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.

  8. Swetha says:

    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?

  9. Shariq says:

    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” .

  10. Swetha says:

    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 ?

  11. Yvette says:

    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.

    • benmelek_admin says:

      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!

  12. Sam says:

    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??

    • benmelek_admin says:

      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.

  13. Erin says:

    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!

    • benmelek_admin says:

      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

  14. Harsha says:

    HI Joshua,

    Any trick to add subtotals when u have a measure in filter.

  15. wendy says:

    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!

  16. Ram M says:

    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

    • Joshua Milligan says:

      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

Leave a Reply

Your email address will not be published. Required fields are marked *