SAP Business Warehouse is an OLAP database that exposes data as cubes, which you query with multidimensional expressions (MDX). The add-in models these cubes in relational views that you can query with SQL-92. The following mapping is for the layout of the model:

• Catalog: Displayed in the add-in as a Catalog.
• Cube: Displayed in the add-in as a Schema.
• Measure: Available in the add-in under the special Measures view.
• Dimension: Each dimension is exposed as a view.
• Level: Each individual level of a hierarchy is exposed as a column on the appropriate dimension view.

Joining Measures and Dimensions

To retrieve measures per specific level value, issue a JOIN between the Measure view and any Dimension or set of dimensions. For example, running the following query retrieves the number of customers in each city:

SELECT m.[Customer Count], c.[City] 
FROM [AdventureWorksDW2012Multidimensional-SE].[Adventure Works].Customer AS c 
INNER JOIN [AdventureWorksDW2012Multidimensional-SE].[Adventure Works].Measures AS m

Note that there is no ON condition necessary. This is because tables are already related appropriately in SAP Business Warehouse. If you are using a tool that requires ON conditions, set IncludeJoinColumns to True. This appends a number of foreign key columns to each view, which will relate them to one another. These columns do not return data on their own, but can be detected automatically with tools to construct the ON conditions for JOINs where needed.

Aggregating Data

Data stored in SAP Business Warehouse is already aggregated. In many cases, attempting to retrieve an aggregate may be syntactically equivalent to not specifying anything. For example, the following query returns the exact same data as the previous:

SELECT SUM(m.[Customer Count]), c.[City] 
FROM [AdventureWorksDW2012Multidimensional-SE].[Adventure Works].Customer AS c 
INNER JOIN [AdventureWorksDW2012Multidimensional-SE].[Adventure Works].Measures AS m
GROUP BY c.[City]

The exception to this rule is when an aggregation of filtered results is requested. In such cases, a calculation will be requested from SAP Business Warehouse. For example, to calculate the sum and average of customers in France and Germany:

SELECT SUM(m.[Customer Count]), AVG(m.[Customer Count]), c.[Country] 
FROM [AdventureWorksDW2012Multidimensional-SE].[Adventure Works].Customer AS c 
INNER JOIN [AdventureWorksDW2012Multidimensional-SE].[Adventure Works].Measures AS m
WHERE c.[Country] IN ('France', 'Germany')
GROUP BY c.[Country]

Selecting Multiple Hierarchies

In SAP Business Warehouse, individual dimensions are made up of hierarchies, which may have one or more levels. For instance, the AdventureWorks Customers table has City, Country, and Gender. City and Country are part of the same hierarchy, while Gender is its own hierarchy.

When selecting multiple hierarchies, the method to support this is to cross join the values in MDX. While not obvious from a relational table model of the data as the add-in presents, this can cause very expensive queries to be executed. For example, executing the following returns 6 rows:

SELECT c.[Country], m.[Customer Count]
FROM [AdventureWorksDW2012Multidimensional-SE].[Adventure Works].Customer AS c 
INNER JOIN [AdventureWorksDW2012Multidimensional-SE].[Adventure Works].Measures AS m

However, selecting Gender as well returns 12 rows:

SELECT c.[Country], c.[Gender], m.[Customer Count]
FROM [AdventureWorksDW2012Multidimensional-SE].[Adventure Works].Customer AS c 
INNER JOIN [AdventureWorksDW2012Multidimensional-SE].[Adventure Works].Measures AS m

This is because Gender and Country are on different hierarchies. Thus, a CROSSJOIN is required in order to return both together. Each additional hierarchy added to the SELECT will multiply the total results by the number of available values in that hierarchy. Thus to get a count of how many rows to expect, you can execute the following:

SELECT (Count(c.[Country])*COUNT(c.[Gender])) AS totalrows
FROM [AdventureWorksDW2012Multidimensional-SE].[Adventure Works].Customer AS c 

Response Row Limit

When you select multiple hierarchies, the total number of result rows multiplies rapidly, which results in timeouts. The ResponseRowLimit connection property helps identify expensive queries and encourages best practices. When set, it calculates how many rows to expect before any query is executed. If the number of predicted rows exceeds the limit, an error is thrown indicating how many rows to expect back with the query.

It is recommended to select only the columns required or to apply a WHERE criteria. Both can significantly reduce the number of response rows, which has a significant impact on performance. If you are already familiar with the add-in and what queries may be expensive, ResponseRowLimit can be disabled by setting it to 0.

SAP Variables

The "SAP VARIABLES" clause is a specific customization of the SAP Business Warehouse MDX syntax. It allows variables to be defined at the cube level to dynamically control all downstream queries.

The SAPVARIABLES view allows for listing these variables as well as setting dynamic values for them at query time. An instance of this view is automatically added to all cubes listed via the add-in.

Listing SAP Business Warehouse variables

Selecting directly from the SAPVARIABLES view returns information about the variables available within the cube:

SELECT * FROM [Catalog].[Cube].[SAPVARIABLES]

Note the IsRequired and ValidValues columns. If IsRequired is True for a given variable, it must be set for all queries within this cube. If it is not given a value in this case, the add-in attempts to select a default value unless the AllowDefaultVariables property is set to False. The ValidValues column is used to indicate which values SAP Business Warehouse accepts when this variable is given an input during queries. These values most often correspond to the member captions of the ReferenceDimension levels.

Using SAP Business Warehouse variables during queries

Alongside the static columns listed below, each variable has its VariableCaption reported as an additional column. The VariableCaption columns always return null when selected directly, but serve as inputs for other queries. To use them, include the SAPVARIABLES view in your query via a JOIN and provide the variable input in the WHERE clause using the corresponding variable column. Where required, the add-in automatically translates the variable caption to the variable name, and the same is true of any hierarchies the variable value may correlate to. The syntax for providing input for a SAP Business Warehouse variable is the following:

SELECT [Level] FROM [Dimension] JOIN SAPVARIABLES s WHERE s.[Lifecycle Status] = 'closed'

Which will result in the following SAP VARIABLES clause being added to the generated MDX query:

SAP VARIABLES [!V000001] INCLUDING [2CSEPM_ISOLCSTTST].[C]

The SAP VARIABLES clause expects different input formats depending on the VariableSelectionType. The add-in translates the appropriate input formats where required, but some selection types require different methods for providing values to the initial SQL query. In particular, the "Interval" and "Complex" selection types expect multiple values to be specified in the same query. To accomplish this, an IN clause can be used when providing a value for the variable:

// Interval type
SQL: SELECT [Level] FROM [Dimension] JOIN SAPVARIABLES s WHERE s.[Lifecycle Status] IN ('Closed', 'New')
MDX: SAP VARIABLES [!V000001] INCLUDING [2CSEPM_ISOLCSTTST].[C]:[2CSEPM_ISOLCSTTST].[N]

// Complex type
SQL: SELECT [Level] FROM [Dimension] JOIN SAPVARIABLES s WHERE s.[Lifecycle Status] IN ('Closed', 'New')
MDX: SAP VARIABLES [!V000001] INCLUDING [2CSEPM_ISOLCSTTST].[C], [2CSEPM_ISOLCSTTST].[N]

Note: The "Interval" selection type supports multiple intervals being specified in the same query. To form these, an even number of values must be provided in the input list. If an odd number of values is provided, the add-in throws an exception indicating this.

By default, the add-in attempts to choose a default value for each required variable if one is not specified. This behavior can be disabled by setting AllowDefaultVariables to False.