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:
Can U please try to change back ground color or Text color........
ReplyDeleteUr Blogs are very good
ReplyDeletecan you change this background colour ??
this background makes text reading very very difficult..please change!
ReplyDeleteI think this is a very useful aspect of complex tableau operations and also useful to provide so many solutions to tough and complicated problems.
ReplyDeleteTableau Soap Connection