Cube Data Sources
Cube data sources (also known as multidimensional or OLAP data sources) have certain characteristics that differentiate them from relational data sources when you work with them in Tableau. This topic describes these differences, and also identifies some Tableau features that are not available when you connect to a cube data source. In many cases there are alternative approaches that you can use to compensate for the unavailability of these features with cube data sources, but you may also have the option to connect directly to a relational database that was used as the source for a cube data source. Talk to your database administrator to find out if this is an option.
Note: Cube data sources are supported only for Tableau Desktop on Windows – not for the Mac.
What are cube data sources?
A cube data source is a data source in which hierarchies and aggregations have been created by the cube's designer in advance.
Cubes are very powerful and can return information very quickly, often much more quickly than a relational data source. However, the reason for a cube's speed is that all its aggregations and hierarchies are pre-built. These definitions remain static until the cube is rebuilt. Thus, cube data sources are not as flexible as relational data sources if the types of questions you need to ask were not anticipated by the original designer, or if they change after the cube was built.
The cube data sources supported in Tableau are
- Oracle Essbase
- Teradata OLAP
- Microsoft Analysis Services (MSAS)
- SAP NetWeaver Business Warehouse
- Microsoft PowerPivot
Create calculated members using MDX formulas
When working with a cube data source, you can create calculated members using MDX formulas instead of creating Tableau formulas. MDX, which stands for Multidimensional Expressions, is a query language for OLAP databases. With MDX calculated members, you can create more complex calculations and reference both measures and dimensions. A calculated member can be either a calculated measure, which is a new field in the data source just like a calculated field, or a calculated dimension member, which is a new member within an existing hierarchy. For details, see How to Create a Calculated Member.
Tableau features that are affected when you use a cube data source
When you use a cube data source, not all Tableau features work the same as with relational data sources, or are even available. The following table details the differences.
| Feature | Status for Cube Data Sources |
| Actions | With Microsoft Analysis Services connections, drill-down actions defined in the cube are not available in Tableau. Cube data sources do not accept actions from relational or other cube data sources. For example, suppose you have a workbook with a view that uses a MySQL data source, a second view that uses cube data source A, and a third view that uses cube data source B. Actions in the view using the MySQL data source won't affect the views using the cube data sources, and actions in the view for one cube data source won’t affect the other. But actions in the views using the cube data sources will affect the view using the MySQL data source. |
| Advanced analytics features | Level of detail expressions, trend lines, forecasting, and clustering are not supported for cube data sources. |
| Aggregate calculation functions | Cube data sources are pre-aggregated and thus do not support aggregation functions, such as SUM(), AVG(), and CNT().It may be possible to use Table Calculations to perform aggregation operations on the cell-level results from the cube in Tableau. |
| Aliases | Aliases for cube databases are created by the cube's designer and can be activated in Tableau by selecting the data source from the Data menu and then choosing Alias File. Talk to your database administrator to find out whether your database has aliases available. Aliases are not supported by Microsoft Analysis Services databases. By default, the alias for every member of every dimension is initially defined to be the original member name. |
| Bins | For cube data sources, the Create Bin command is not available for measures. You can, however, write a calculation that takes cube cell results and bins them. For example:
|
| Cube KPI data type | When connected to Microsoft Analysis Services, any KPI calculations defined in the cube are not available. However, you can write your own KPI calculations in Tableau. You can also use Tableau parameters to create highly flexible what-if KPI analysis. For more information, see Visualise Key Progress Indicators. |
| Cube lag functions | For cube data sources, you cannot use cube lag functions in the Tableau calculation editor. You can use Tableau Table Calculations to calculate certain percentages and totals instead. For more information, see Transform Values with Table Calculations. Alternatively, you can use an MDX Lag function directly in Tableau using a calculated member. For example:
|
| Custom multidimensional expression (MDX) statements | When connected to a cube, you cannot make a connection to a custom MDX statement. All sub-cube definitions need to be created on the server by implementing necessary cube perspectives, partitions, dimensions or cell security. |
| Data blending | Cube data sources can only be used as the primary data source for blending data in Tableau. They cannot be used as secondary data sources. For details, see Troubleshoot Data Blending. |
| Date dimensions | For cube data sources, date dimensions are typically organised into hierarchies that contain levels such as year, quarter, and month. In addition, some multidimensional data sources have time intelligence enabled, which makes it possible to look at data levels different ways, such as Months by Year, Months by Quarter, Weekends, etc. These levels are represented as attributes of the hierarchy. For details, see Dates and Times. |
| Data source filters | Data source filters are not available for cube data sources. All field values must be defined in the cube prior to analysis in Tableau. |
| Extracts | You cannot create extracts from most cube data sources. 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 in most cases. Starting in 10.4, you can create SAP BW extracts without the need for a special product key from Tableau. For information about support for and limitations of SAP BW extracts, see SAP NetWeaver Business Warehouse. |
| Filters | When you display a filter for a cube dimension, all levels of the hierarchy for that dimension are included in the filter. For example:
You can use cube attributes as filters, to show just a single level instead of a hierarchy. In the Data pane, attributes appear in the Dimensions section and are indicated by this icon: Slicing filters behave differently with a cube data source than with a relational data source. See Create Slicing Filters for details. Some dimension filter card options are not available. For example, single value (drop-down), multiple values (custom list), etc. Instead, dimensional filters shown in the view retain their hierarchical look and feel, and cannot be changed into specific list types. You can create a Tableau set containing certain values from the hierarchy and then use the set as a filter in the view with the expected filter options (right-click the set in the Data pane, and then click Show Filter). |
| Groups | You cannot create groups when working with a cube data source. Any concept of groupings should be pre-defined in the cube as dimensional attributes or cube sets. However, you can write multidimensional expressions (MDX) directly in Tableau using a calculated member to create a group. For example:
|
| Hierarchies | For cube data sources, hierarchies must be defined in the cube prior to analysis. |
| Parameters | For cube data sources, you cannot use parameter values to filter dimensions in an MDX calculation. |
| Publishing | Workbooks using cube data source can be published to Tableau Server, but do not support pass-through connections. This means that you cannot make a connection from Tableau Server using such a data source. It also means you cannot create a workbook using the data source in Tableau Server. For details, see Cube Data Sources(Link opens in a new window) in the Tableau Server help. Publishing a cube data source to Tableau Server gives you the ability to store the data source on the server. However, to use the data source, you must download the data source to Tableau Desktop and use it locally. Workbooks that use cube data source cannot be published to Tableau Cloud. |
| Sets | Cube data sources support hierarchical sets, which filter data to the selected members and all of their descendants. For details, see Examples of sets. |
| Tableau string functions | With cube data sources, dimensions are not available in the calculation editor. However, you can write MDX inside Tableau using a calculated member to manipulate dimensional values. For example:
|
| Type conversions | For cube data sources, some type conversion functions are not available in the calculation editor. As a rule, data type conversions should be defined in the cube prior to analysis. In particular, changing the data type of a cube dimension to a date in Tableau sometimes gives incorrect information. This feature is supported for some cube dimensions, depending on how the dates are formatted in the cube. 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:
|
