SAP BW is a powerful Data Warehouse that allows you to build Cubes that can return information very quickly, often much more quickly than a relational data source. However the reason of these speeds are mostly caused because of the aggregations and hierarchies that are pre-build within SAP BW. These are static so that means that they are not updated until the Cube are rebuild. This makes it a lot less flexible than relational data source when the question you are trying to answer aren’t anticipated by the original cube designer, or if they change after the cube is build.
MDX Layer
Tableau connects to the MDX layer of SAP BW, which means that instead of using the Tableau formulas it will use the MDX formulas. MDX stands for Multidimensional Expressions and is a query language for OLAP databases. This means that you can create more complex calculations and reference both measures and dimensions with MDX calculated Members.
So what will be affected when you are using the MDX formulas and not Tableau formulas?
When you use a SAP BW data source, not all Tableau features work the same as with relational data sources, or are even available. This is because the Data sets are pre build cubes and can’t be changed without rebuilding a cube.
Tableau Extracts:
SAP BW is one of the only Cube data bases that is able to be extracted into Tableau. This features has been added since Tableau 10.4.
When you create an extract from an SAP BW data source, Tableau will convert the data from cube into a relational extract. This conversion can take some time depending on the size of the cube. Also most of the cube metadata cannot be represented in the relational data model. This means that there are limitation on working with an extract.
The following features are included in a SAP BW extract.
- The behaviour of empty user filters has changed to be consistent with the behaviour on other data sources. Using an empty user filter no longer hides the dimension.
- You can use Hide to hide dimensions before you extract data or refresh an extract. This reduces the size of the extract and the amount of time to perform an extract.
- Hiding a dimension takes priority over creating a user filter on the dimension.
- You can rename dimensions and measures in the data grid before extracting data.
- Tableau modifies the extract query slightly to fix some of the [cube contains no data to transform] errors.
The following limitation are in place when using SAP BW Extract.
- Tableau supports only infocubes, or InfoProviders that contain only infocubes. A composite source that contains other BW object types is not supported.
- For large extracts, Tableau automatically partitions the query if the query fails or times out.
- BEx queries that contain more than 49 dimensions are not supported by SAP BW.
- You can’t switch between a live connection and the extract. When you connect to an SAP BW data source, you’ll have the option to Connect Live or Import the data. If you choose to Connect Live, the Extract options that are typically available for a connection are not an option. Further, if you choose to Import the data and create an extract, you cannot switch back to the live connection using the Use Extract command.
- You can’t replace an extract with a live connection to SAP BW.
- Incremental extracts are not supported.
- When creating the extract, you can add filters to limit the data included in the extract, but these filters can be based only on hierarchies.
- Tableau doesn’t support extracting the following SAP BW objects:
- Calculated sets
- Custom formatting and cell properties, except aliases.
- User hierarchies. Hierarchies will collapse.
- Pre-aggregated data.
- Exception aggregation.
- Compounding attribute.
Data Display difference extract vs live connection.
In addition to the limitation, you may notice there are some differences between an extract and live connection. You will mostly notice this when connecting to SAP BW directly, the dimensions, hierarchies and levels are represented in the Data pane. But after you create an extract, all the fields will be shown in a flat list.
| Live connection | Extract connection |
 |
 |
As you can see when connecting to an extract the hierarchy has been removed and all the dimensions are shown as a flat structure. However when you connect to an extract you might also see extra fields that represent the alias options. All the aliases are included in an extract and are normally hidden. But you can hide them again by right click on the alias you don’t want to see and select “Hide”.
| Aliases hidden | Aliases showing |
 |
 |
Affected features:
When you use SAP BW, not all Tableau features work the same as with relational data sources, or are even available. The following table details the differences.
| Feature | Status |
|
Data blending |
SAP BW can only be used as the primary data source for blending data in Tableau. They cannot be used as secondary data sources. |
| Date dimensions | 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. |
| 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 Sets |
| Sets | Cube data sources support hierarchical sets, which filter data to the selected members and all of their descendants. |
| 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 support by Microsoft Analysis Services databases.
By default the alias for every member of every dimension is initially defined to be the original member name. For example, the figure below shows a bar chart built from an Essbase database. By default, the original member names are displayed (example on the left). As you can see, these names are not very intuitive. By selecting Data > Aliases File and selecting an appropriate alias file set up by the database administrator, meaningful names are displayed in the headers. |
| 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: [Customer].[Customer Geography].[France] + [Customer].[Customer Geography].[Germany] |
| Advanced analytics features | Level of detail expressions, trend lines, forecasting, and clustering are not supported for cube data sources. |
| 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. 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 Online. |
| Actions | Cube data sources do not accept actions from relational or other 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. |
| Bins | 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: str((INT([Internet Sales Amount]/1000)) * 1000) |
| Type conversions | 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: CDATE([Date].[Date].CurrentMember.MemberValue) |
| Custom multidimensional expression (MDX) statements | 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 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 can create an extract from SAP BW Cubes. But 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. |
| Hierarchies | For cube data sources, hierarchies must be defined in the cube prior to analysis. |
| Cube lag functions | 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: Avg ( { [Date].[Calendar].CurrentMember.Lag(4) : [Date].[Calendar].CurrentMember } , [Measures].[Internet Sales Amount] ) |
| Tableau string functions | 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: LEFT([Product].[Product Categories].DataMember.MemberValue,LEN([Product].[Product Categories].DataMember.MemberValue)-5) |
| Parameters | For cube data sources, you cannot use parameter values to filter dimensions in an MDX calculation. |
I hope this blog I shared can be useful for you! If you have any more questions don’t hesitate to contact us.
In need of further explanations and help? Check out our trainings page and find the one that suits your needs best. We also offer consultancy.
Header image from: pixabay.com. Screenshots from tableau.com