KNOWLEDGE BASE

Understanding Functional Differences Between OLAP and Relational Data Source Connections


Product(s): Tableau Desktop, Tableau Server
Version(s): 8.3, 8.2, 8.1, 8.0
Last Modified Date: 17 Oct 2016

Article Note: This article is no longer actively maintained by Tableau. We continue to make it available because the information is still valuable, but some steps may vary due to product changes.


When connecting to an OLAP (cube) data source, such as Oracle Essbase or Microsoft Analysis Services (MSAS), some Tableau features may not be available. This article describes some alternative approaches to features that are unavailable when connected to a cube. Examples of many of the listed features below can be found in the attached workbook, which 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 must change the server value from scdemo-dbs to the name of your MSAS server that has the Adventure Works multidimensional data source. For example:

Specific Feature

 

DetailsAlternative Approach
Cube ActionsWhen connected to Microsoft Analysis Services, drill-down actions defined in the cube are not available in Tableau.Not relevant: You can create actions in Tableau using Tableau actions.
Aggregate Calculation FunctionsWhen connected to a cube, aggregation functions, such as SUM(), AVG() and CNT(), are not available in the Calculated Field formula editor. These features are available when connected to relational data sources.N/A: Aggregations are defined in the cube, and cannot be changed to a different aggregation type. You can, however, use Table Calculations to perform aggregation operations on the cell-level results from the cube in Tableau.
Tableau AliasesWhen 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.
Tableau BinsWhen 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. For Example:

str((INT([Internet Sales Amount]/1000)) * 1000)

For an example of how to bin measures in cubes, see Creating Bins in Cubes.

Tableau Change Data Type to a Date

When connected to a cube, changing the data type of a cube dimension to a date using the Change Data Type feature in Tableau sometimes gives incorrect information. This feature is available for certain cube dimensions, depending on how the dates are formatted in the cube.

 

Alternative: You can write Multidimensional Expressions (MDX) directly in Tableau using a calculated member to change the data type of a cube dimension to a date. For example:

CDATE([Date].[Date].CurrentMember.MemberValue)

Tableau Type Conversion FunctionsWhen connected to a cube, some type conversion functions are not available in the Tableau Calculated Field formula editor. These features are available when connected to relational data sources.N/A: All data type conversions need to be defined in the cube prior to analysis, or via custom calculated members inside of Tableau.
Tableau Custom MDX ConnectionWhen connected to a cube, you cannot make a connection to a custom MDX statement. This feature is available when connected to relational data sources.N/A: All sub-cube definitions need to be created on the server by implementing necessary cube perspectives, partitions, dimension and/or cell security.
Tableau Edit Data Source FiltersWhen connected to a cube, the Tableau Edit Data Source Filter option is not available. This feature is available when connected to relational data sources.This is by design: All field values must be defined in the cube prior to analysis in Tableau.
Tableau ExtractsWhen connected to a cube, you cannot perform a Tableau extract. This feature is available when connected to relational data sources. Note: Starting with Tableau 8.1, SAP BW connections support extracts. For more information, see SAP BW Extract Limitations.This is by design: Cube and relational data sources have incompatible data structures, which makes extracting data from a cube and storing it in a relational data source, such as the data engine, impossible.
Tableau GroupsWhen 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 be pre-defined in the cube as dimensional attributes or cube sets.

Alternative #2: You can write MDX directly in Tableau using a calculated member to create a group. For example:

[Customer].[Customer Geography].[France] + [Customer].[Customer Geography].[Germany]

For an example of how to group cube dimensions using the formula above, see Grouping Cube Dimensions by Using MDX.

 

Tableau HierarchiesWhen 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.N/A: Hierarchies need to be defined in the cube prior to analysis.
Cube KPI Data TypeWhen connected to Microsoft Analysis Services, any KPI calculations defined in the cube are not available.Alternative: You can write your own KPI calculations directly inside Tableau. You can also use Tableau parameters to create highly flexible what-if KPI analysis. For more information on how to create KPIs in Tableau, see Creating Key Progress Indicators (KPIs).
Cube Lag FunctionsWhen connected to a cube, using a Lag function in the Calculated Field formula editor is not necessary. You can use Tableau Table Calculations to calculate certain percentages and totals instead. For more information on Table Calculations, see Table Calculations in the Tableau Desktop Help.

Alternative #2: You can use an MDX Lag function directly in Tableau using a calculated member. For example:

Avg

( { [Date].[Calendar].CurrentMember.Lag(4) : [Date].[Calendar].CurrentMember }

, [Measures].[Internet Sales Amount]

)

For some examples of how to use the formula above, see Using an MDX Lag Function in Cubes.

Tableau Quick Filter LimitationsWhen connected to a cube, some dimension quick filter options are not available. For example, single value (dropdown), multiple values (custom list), etc. Instead, dimensions placed on the quick filters shelf retain their hierarchical look and feel, and cannot be changed into specific list types. These quick filter options are available when connected to relational data sources.

This is by design: Cube dimension quick 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 String Manipulation FunctionsWhen connected to a cube, many concepts related to string manipulation are not available. This is because cube dimensions do not show up as available fields in the Calculated Field editor. Dimensions are available in the Calculated Field editor when connected to relational data sources.

Alternative: You can write MDX directly inside of Tableau using a calculated member to manipulate dimensional values. For example:

LEFT([Product].[Product Categories].DataMember.MemberValue,LEN([Product].[Product Categories].DataMember.MemberValue)-5)

For more information on how to create string manipulations in cubes using the formula above, see String Manipulations in Cubes.

 

 

 

 

Alternate Search Terms:Information Data Sources, MDX, OLAP, Cube, Relational
Did this article resolve the issue?