This section describes SQL support for SAP BW and its resource types—functions, tables, and ABAP queries.

TDV has made the interface between TDV and SAP BW as efficient and accurate as possible. However, due to some product limitations and proprietary interfaces, TDV and SAP BW results might differ, and performance issues might occur when accessing SAP BW data using TDV. The following sections provide additional information.

Queries in SAP BW using BEx are created differently in TDV. In TDV, SQL queries generate MDX. The process is different in SAP BW using BEx. This disparity can cause differences in results. Workarounds are described in this section. See Introspection Resources for information about how TDV interprets SAP BW resources in TDV.

Queries can require input values. To handle queries with input values in TDV, set the values using a WHERE clause and provide values for columns that begin with an underscore (_). For an example, see Variables.

BEx queries that contain filters might not return the same results in TDV as they do in BEx. To apply the filters in TDV, recreate them using a WHERE clause. This limitation is imposed by SAP BW; TDV does not have access to the filters.

BEx queries do not return the same columns in TDV as they do in BEx. For example, you might see 1,000 columns in TDV listing every property of every dimension in the query, even if you did not include those properties in BEx. This is because the SAP BW API does not give TDV access to all of the items selected for output in the query. TDV only gets a list of InfoObjects. From there, TDV expands each one to include all of its columns.

Performance of TDV’s SAP BW Adapter is slower and more resource-intensive on the SAP BW server than accessing SAP BW through native SAP tools, because SAP does not give TDV access to proprietary APIs.

When performance issues occur when using TDV with SAP BW, you can reproduce them outside of TDV by copying the MDX statements generated by TDV (by examining the TDV cs_server.log) and entering them into the MDX test environment (transaction MDXTEST in SAP GUI). This allows SAP BW administrators to optimize settings and improve performance of the MDX statements.

This section describes the details of introspecting SAP BW resources.

The organization of SAP BW resources in TDV mirrors SAP BW as much as possible. InfoAreas in SAP BW become folders and subfolders in TDV. Supported InfoObjects are organized into subfolders.

In the following screenshot of BeX Analyzer, nodes have been expanded in InfoAreas to reveal the InfoProvider Inventory cube (month) which contains the Query Stock (first and last aggregation).

The next section shows how the equivalent resources look in TDV.

As with BEx Analyzer, TDV displays only the objects that the user is authorized to see.

InfoAreas in BeX Analyzer are displayed as folders in Studio, and these folders are named after the InfoArea’s technical name or description. Moving the mouse over an item reveals a tool-tip with its technical name and type.

InfoProviders contain up to three subfolders to group resources by type, as described below.

The data structures present in multi-dimensional InfoProvider, Query, or Hierarchy objects are transformed into a flat namespace of columns in TDV. InfoProviders and Queries contain three types of data relevant to TDV: Characteristics, Attributes, and Key Figures. Queries may contain Variables as well. Hierarchies contain only Characteristics and Attributes. The next screen shot illustrates the column types in a typical Query.

Each Characteristic is represented by four columns containing Characteristic metadata, differentiated by naming conventions:

Following the Characteristic columns are Characteristic Attributes. Each Attribute is named using the Characteristic short description, a space, and the Attribute description. Its native type is the technical name of the Attribute. Its data type is VARCHAR, with the length determined by SAP BW metadata, defaulting to 255 characters if metadata is not available.

Each Key Figure is represented as two columns. One column contains the formatted value; the other contains the unformatted, numeric value. Both columns are named using the Key Figure name, but the numeric value field has an asterisk (*) suffix to differentiate it. The native type is the technical name of the Key Figure. The data type of the formatted Key Figure is a 128 character VARCHAR. The data type of the unformatted value is DOUBLE.

Variables only appear in Query objects. They can be identified by the single underscore before their names. The data type is VARCHAR of length determined by SAP BW metadata. The native type is the technical name of the variable.

Internal columns are created for use within TDV SQL and do not correspond to objects in SAP BW. Their names are prefaced with two underscores, and they appear at the end of the list of columns. Each Internal column has a unique function, as discussed in Query Mapping.

The InfoObjects in an ODS InfoProvider are transformed into a set of columns in TDV.

Each InfoObject becomes a column, named using the InfoObject’s short text. The native type is a concatenation of the InfoObject type and its SAP data type, for example “CHA C” is a Characteristic with SAP data type C. InfoObject types are listed below:

The data type of the column is determined by its SAP data type. The supported SAP data types and their corresponding TDV data types are:

Capabilities characterize the features and limitations of data sources. For example, an Oracle data source can execute subqueries, while SAP BW cannot. Capabilities are consulted when a query is processed so that data sources receive only the query processing work they support; otherwise, TDV performs the work itself.

This section includes the following topics:

The following table lists commonly used capabilities and whether they are supported in queries against SAP BW. Pushed indicates whether query processing can be passed to SAP BW. For efficient queries, minimize use of non-push capabilities.

The following table lists capabilities that apply to ODS Objects, whether they are supported against ODS Objects, and whether query processing can be pushed to SAP BW. For efficient queries, minimize use of non-push capabilities. Capabilities for ODS objects are:

This section describes how queries are mapped from SQL to MDX. This mapping is critical to content and performance.

Characteristics selected in SQL are projected onto the Query Axis ROWS (Axis 1). Multiple Characteristics are cross-joined, resulting in a Cartesian product of all selected Characteristics. This creates a rows-and-columns data set for TDV. Selecting many Characteristics could result in long-running queries and huge result sets.

A Characteristic projected without a filter adds its MEMBERS set to the cross-join. If the active Hierarchy contains more than two levels, the MDX DRILLDOWNLEVEL function is applied to produce a set containing all nodes of the Hierarchy. Additionally, the MDX HIERARCHIZE function is applied to order the members in their hierarchical order.

To restrict the Characteristic to specific members, apply a filter (WHERE clause) to the Characteristic or the Characteristic [Key] column. For example, the following query produces a cross-join of Company code, Division, and Sales organization, and restricts Sales organization to the one with key 2200:

If a member key cannot be found in the Hierarchy, a warning is logged but the query continues. The log is in the file cs_server.log, located in the subdirectory logs of <TDV_install_dir>.

If you do not know the member key, you can use the member text, also known as the “caption,” but note that member texts may not be unique. To avoid ambiguous queries, it is best to filter on member keys.

The following query accomplishes the same effect as the query above, but refers to Sales organization 2200 by its text designation (Paris):

Characteristics have the following functionality:

Attributes, also known as Dimension Properties, typically contain data related to the Characteristic. For example, the Characteristic “Company Code” contains an attribute named “Country (Name).”

Filter behavior for Attributes is similar to that of ordinary SQL queries:

Unlike in MDX, Attributes can be selected without their parent Characteristic. TDV selects the parent Characteristic automatically. This adds a Characteristic to the cross-join, which increases the load on SAP BW, the network, and TDV.

Key Figures, also known as Measures, contain numeric data that is aggregated based on the sets present in the query. They are projected on the Query Axis COLUMNS (Axis 0).

Both the formatted and unformatted (numeric) values are available as columns and behave identically in queries,.

Filter behavior for Key Figures is similar to that of ordinary SQL queries. The following filters are supported:

Ordering of data is accomplished with the ORDER BY clause, adapted to work with SAP BW in the following ways:

When a filter is placed on a column that is not projected, TDV treats the filter as a Slicer, placing it on the Slicer Axis. A Slicer qualifies tuples for each cell of the query, but does not affect the Query Axis. For example:

The above query returns the Sales Key Figure for the cross-join of Company Code and Division Characteristics, with cells restricted to tuples including the January 2001 member of the Calendar Month/Year Characteristic. This may result in many empty rows where Company Code/Division contains no Sales data, but these are removed by default. (To display empty rows, specify the query option includeEmptyRows. For more information, see the Query Options.)

Slicers have the following behavior:

Note: In BeX Query Designer, Slicers are called Filters.

Variables are features of SAP BW Queries. To provide a Variable value, add a filter to the SQL statement. Depending on the variable type, the value can be a member key (enclosed in square brackets), member text, or literal.

In the following example, the Variable Sales organization is assigned to members whose text matches the values USA Philadelphia or USA Denver.

The annotation of a Variable column includes guidance for how to use it within a query.

Rules for using Variables in queries are as follows:

The Internal column “__mdx” in InfoProvider and Query resources allows custom MDX to be sent directly to SAP BW, bypassing SQL-to-MDX query mapping in TDV. This helps when you need to access features of MDX without using SQL; but then MDX queries cannot be used with other queries. TDV has no visibility to tune them.

The following is an example of using custom MDX in a query. The MDX function TOPCOUNT is used to provide ranking and ordering which cannot be accomplished in SQL:

Developing MDX can be challenging. It is best to work within a specialized OLAP tool to develop the initial query, test it, and then paste it into Studio.

Additionally, a number of rules must be followed for the MDX to execute properly within TDV:

An internal column named “__options” (two underscores) is provided for every InfoProvider and Query resource. This column accepts a comma-delimited list of query options, which can modify the behavior of the SAP BW query in ways that cannot be expressed in SQL.

Valid options are the following:

The natural order of data from SAP BW may be important to preserve. Without an explicit ORDER BY clause, results from SAP BW are ordered hierarchically. Once the data is reordered due to a join or other SQL operation, it cannot be restored through ORDER BY or any other operator in TDV because it relies on the hierarchical order available only to SAP BW.

For this reason, an Internal column named “__rownum” (two underscores) exists for every SAP BW resource in TDV. Values in this column range from 0 to n, indicating the natural order of data as returned from SAP BW. This makes it possible to preserve the natural order of the data. This column can be selected, but not in a filter expression.

Hierarchy nodes and other data in SAP BW can change. By default, queries run using the current date. To set a different date, create a filter on the “__options” Internal column, using the keyDate option and a date in YYYY-MM-DD format.

In the following example, the query is executed with a key date of January 1, 2006:

Tracing logs every interaction with SAP BW to files so that data values can be examined and validated against output in TDV. Combined with the debug output in cs_server.log, this can be helpful for troubleshooting queries.

Note: Tracing affects performance and creates large files on disk, so be sure to change the sapbw.trace property to false and restart TDV Server when tracing is no longer needed.

If your developers work with queries that return large amounts of data, or depend on the amount of available memory, you might be able to improve performance by adjusting the batch size.

Typically, the batch size should not be changed. If the setting is too high, the server requires too much memory to process the results from SAP BW and can crash. If the setting is too low, query performance can suffer because the server might unnecessarily split up single queries into multiple queries. However, depending on other factors such a network latency and bandwidth, it is possible that increasing the batch size could result in improved performance on queries that return amounts of data larger than the batch size.

Queries against SAP BW are multidimensional, which introduces a new behavior to TDV that may be unexpected to users accustomed to querying relational databases.

The feature Show Contents available in Studio is not recommended on any SAP BW resource. Show Contents performs a SELECT * query, which results in a cross-join of every possible Characteristic in the SAP BW resource.

When creating a new view that uses an SAP BW data source in combination with other data sources, or when using multiple data sources of any type, use the New OLAP View command in Studio to create a new view.

If you are creating a view that calls only one SAP BW data source, you can create a New OLAP View in Studio. For more information about OLAP Views, see the TDV User Guide.

TDV Server makes a number of assumptions when optimizing queries, which can cause confusion. This is especially true when building Views that call other Views, or when using Views from outside of TDV using JDBC or ODBC. Because a cross-join is used to query SAP BW, when a Characteristic column is removed from a query due to optimization, the meaning of the underlying MDX query changes dramatically.

For example, if a View includes the Characteristics “Company Code” and “Division” and “Key Figure Sales,” an ODBC query selecting only “Division” and “Sales” produces a different data set than a query selecting all columns. To ensure the same base data are returned regardless of the query, wrap any Views using SAP BW in a Parameterized Query, and expose the Parameterized Query to query users. This prevents TDV from applying relational optimization rules to the multi-dimensional query.

You can filter on level columns (Dimension [Level]). For example, you can add a WHERE clause to the SQL statement that filters the [Level] field:

This would return Measure for the cross product of Dimension1 (members beginning from level 2 and below) and Dimension2 (all members).

TDV caches SAP BW metadata to improve query performance. These caches are automatically cleared after every TDV restart, but you might want to do this at other times.

For example, your SAP BW data source might become out of sync with the SAP BW database, even after re-introspecting the data. If the metadata is still out of sync after re-introspection, you can restart the TDV instance, or you can clear the metadata cache as described in this section.

Joins cannot be pushed to SAP BW. Executing joins in TDV can degrade performance, because a table scan would be required, and it would fetch every row of the joined tables. The technology that TDV uses to connect with SAP BW is not optimized for large data sets, so table scans should be avoided.

A semijoin is the best way to reduce the number of SAP BW rows retrieved and processed. To force a semijoin to occur in a query, add the option immediately before the table to be joined. Example:

Values of X.Key are collected and passed in a query to Y as the filter:

If X has many rows, queries against Y can be lengthy. If the queries against Y become too large, TDV automatically partitions them and reassembles the results into a unified set.

Put the table that returns the larger number of rows on the right side of the join whenever possible. When running a new query for the first time, activate the Execution Plan tab in the Studio and click Execute and Show Statistics. Examine each node’s row count and query after processing has begun to make sure that filters are pushed down to SAP BW. This is a good way to see the mechanics of a semijoin in action. If the interaction between TDV Server and SAP BW is still unclear and performance is poor, enable debug logging for the adapter as described in the TDV Installation and Upgrade Guide.