Cubetown!
Ever since my first Tableau experience with cube connections, they’ve left a bad taste in my mouth. So much so, that I often outright dismissed cubes as being a viable source for Tableau in ANY SITUATION! Recently I’ve been doing more work and research on cubes and how to make them work with Tableau and I’ve stumbled across some tips and tricks. I’m going to list them briefly here for you in the hopes that they help, even just a little :), with the pain of Tableau/Cube work.
(disclaimer - I’m specifically talking about SAP BW cubes here, but some of these are applicable to most cube types)
1.) Know the 3 different ways you can get BW cube data. Three ways you say! I thought you could only connect to BW cubes live?! Well...not exactly.
Option 1: Connect Live
This is the standard way to connect to BW cubes. Typically you can connect using BEx queries that have most likely already been built to serve business objects/Webi reporting. (Connection Instructions)
Benefits
The data is always up to date, hence live!
Hierarchies are built in and no data work is necessary
BEx queries can have prompts, which is a nice feature for filtering data pre-dashboard load
Limitations (not all inclusive)
Cannot create calculated fields for or using dimensions (only if you know MDX or modify the BEx query)
Limited filter functionality (some workarounds)
Cannot use LOD calcs
Option 2: Extract
Wait a minute…..you can EXTRACT? Yeah, so I didn’t realize this for a long time, but Tableau allows you to extract from BW cubes. If you ask they will give you a special key that extracts/flattens the data! (additional info)
Benefits
Data is flattened to look like a relational source, all regular Tableau functionality is restored!
The extracts can be scheduled on Tableau Server! Hooray!
Limitations
The extract only pulls the lowest level data, which allows for aggregation in Tableau, but also limits what can be shown at one time. You can edit the level shown for each field, but you can only show one option (example - i can show sku # or sku description if they are in the same hierarchy at different levels, not both)
You can’t switch back from extract to live, so keep a spare live connection in your workbook so you can make a duplicate and do your extracts.
If the data is too big or the BEx query is too complex, you can get a timeout error during extracting.
Option 3: Live to TDE (ok it's kinda like 2b, not 3)
Another option, which piggy backs off option 2 and basically has the same benefits and limitations is connecting live to the TDE generated from the extract process. You’d think this would be exactly the same, but its not.
Benefits
The one and only benefit to doing this is that when you connect live to the TDE, all levels of the cube hierarchy are available for use! So one of the limitations of the extract method was that you could only see the lowest level of the hierarchy - this will solve that issue.
Limitations
You can’t set this data source on an extract schedule on the server, that can only be done with the straight extract, which makes this a huge limiting factor that must be considered
Something not entirely crucial, but super annoying/time consuming is that the field names are not clean in this version. They will take on whatever weird system name they had pre-bex query and metadata cleansing.
2.) So you probably are wondering, How do I make the cube connections fast? Simply put, if you are connecting to a BW cube there are 3 ways to make the connections fast:
Option 1: Optimize your BEx query. Alot of people might say “that’s alot of work” or “It works fine with BOBJ/Webi, just not Tableau”. First off, this is really your best option working live - nothing else will improve performance more than trimming your BEx query to only pull the data you need to make your dashboard run. Second, if its slow in Tableau its slow in BOBJ/Webi too. Once you have Tableau-friendly queries, the BW cube connections should be pretty fast, especially if your BW cubes live on HANA.
Option 2: Extract it. Yeah, its duplication of data. Yeah, its inefficient. But if you want all of the functionality and speed that comes with a TDE, go the extract route. Maybe you can make a custom BEx query just for your extract options so that you can create data sources on the server that many users can use!
Option 3: Get with the times - move that data out of BW permanently. BW cubes might have been the Vanilla Ice of BI in the 90′s, but its 2016. If you want super fast performance and great self-service analytic functionality in Tableau, move that data to Hadoop or some in memory database that can be offered up to your analysts for consumption.
That’s it for this post - I know its not a fun dashboard (i’m working on one now to post soon!) but I feel like more and more I run into BW Cubes and Tableau and I wanted to shed some light on some options I found useful!
Dustin