[TABLEAU] Functional Differences Between OLAP And Relational Data Source Connections

Functional Differences Between OLAP and Relational Data Source Connections

Tableau can connect to many relational data sources, and two OLAP/cube data sources: Oracle Essbase and Microsoft Analysis Services. There are functional differences when connecting to OLAP versus relational due to the underlying language differences between MDX and SQL, the respective query syntax for OLAP and relational data sources. As a result, some features are not available when connecting to OLAP data sources, but are available when connecting to relational data sources, and vice versa.

Specific Feature	Details	Alternative Approach
Aggregate Calculation Functions	When connected to a cube, aggregation functions such as SUM(), AVG() and similar are not available in the Tableau calculation editor. These features are available when connected to relational data sources.	N/A : When connected to OLAP, the aggregations have already occurred in the cube, and cannot be further aggregated. Note that you can also use Tableau "Table Calculations" to perform post-OLAP aggregation operations on the cell-level results from the cube.
Tableau "Groups"	When connected to a cube, the Tableau "create group" function is not available for dimensions. This feature is available when connected to relational data sources.	Alternative #1: Any concept of groupings should have been pre-defined in the cube as dimensional attributes or cube sets. Alternative #2 : You can write a custom "calculated (mdx) member" directly in Tableau which creates a group, the syntax is straight-forward, e.g.: [Customer].[Customer Geography].[France] + [Customer].[Customer Geography].[Germany] (see below for example workbook for working MDX).
Tableau "Bins"	When connected to a cube, the Tableau "create bin" function is not available for measures. This feature is available when connected to relational data sources.	Alternative : You can write a simple calculation which takes the cube cell results and bins them, e.g. str((INT([Internet Sales Amount]/1000)) * 1000). Note : This calculation cannot be placed onto the filter shelf.
Tableau "string manipulation" functions	When connected to a cube, many concepts related to string manipulation are not available in the calculation editor. This is because cube dimensions do not show up as available fields in the editor. These features are available when connected to relational data sources.	Alternative : You can write MDX calculated members directly inside of Tableau to manipulate dimensional values, e.g. LEFT([Product].[Product Categories].DataMember.MemberValue,LEN([Product].[Product Categories].DataMember.MemberValue)-5). Note : This is preferred to perform these types of manipulations directly in your cube prior to analysis.
Tableau "type conversion" functions	When connected to a cube, type conversion functions are not available in the Tableau calculation editor. These features are available when connected to relational data sources.	All data type definitions need to be defined in the cube prior to analysis, or via customer "calculated members" inside of Tableau.
Cube "KPI" data type	When connected to Microsoft Analysis Services, any "KPI" data types defined in the cube are not read nor available.	Alternative : You can write you own KPI calculations directly inside of Tableau. Further, you can use Tableau "parameters" to create highly flexible what-if KPI analysis.
Cube "actions"	When connected to Microsoft Analysis Services, drill-down actions defined in the cube are not available in Tableau.	Not relevant : You can create more and better actions in Tableau using the Tableau "actions" feature.
Tableau "Hierarchies"	When connected to a cube, you cannot create arbitrary hierarchies using the Tableau "create hierarchy" command. This feature is available when connected to relational data sources.	Not relevant : Hierarchies need to be defined in the cube prior to analysis.
Tableau "Custom SQL" Connection	When connected to a cube, you cannot make the connection a custom SQL or MDX statement. This feature is available when connected to relational data sources.	N/A : Because Tableau creates visual queries in real time when connected to a cube, Tableau uses optimized MDX queries during analysis. These queries cannot be customized during the connection process.
Tableau "quick filter" limitations	When connected to a cube, dimensions placed on the view as quick filters cannot be manipulated e.g. "show single value", "show compact list" or similar. Instead, they retain their hierarchical look and feel. These features are available when connected to relational data sources.	This is by design. Cube dimensional hierarchies used as filters will retain their hierarchical look and feel. Alternative : You can create a Tableau set of certain values from the hierarchy. This set can then be used as a quick filter with all of the regular quick filter options.
Tableau "extracts"	When connected to a cube, you cannot perform a Tableau "extract". This feature is available when connected to relational data sources.	This is by design: You cannot take an OLAP cube "offline" and all analysis must occur directly against the cube data source.
Tableau "aliases"	When connected to a cube, you cannot override dimensional cell values with the Tableau "alias" feature. This feature is available when connected to relational data sources.	This is by design: All alias values must be defined in the cube prior to analysis in Tableau.

 

The attached workbook contains a connection to the Microsoft Analysis Services "AdventureWorks" cube.  This is a sample cube that ships with Microsoft Analysis Services. To use this workbook, you simply need to change the Server from "scdemo-dbs" to the name of your MSAS server running AdventureWorks. For example: