OLAP vs. Relational Flashcards
Aggregate Calculation Fuctions
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.
Alternate Approach:
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.
Alternate Approach:
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.
Alternate Approach:
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.
Alternate Approach:
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.
Alternate Approach:
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.
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.
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.
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.
Alternate Approach:
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.
