Denodo and SAP Best Practices

Goal

As an ERP software provider, SAP delivers software solutions that allow organizations to store data and records of different processes in a single unified system. Different ERP solutions based on diverse work areas such as human resources, financial accounting, payroll, and time management are developed by SAP.

Denodo offers different connectors to access the business components and the data managed by SAP. The goal of this document is to show the capabilities of this integration and best practices around them.

Denodo and SAP

Denodo offers two different connectors to access SAP business applications. Both connectors use JCo (SAP’s Java API) to connect with the SAP Server and provide the data to the Denodo Platform.

• BAPI connector for SAP ECC: The Enterprise Central Component provides the organization with great control over their key business processes. Denodo can consume the information provided by this SAP product through BAPI and RFC connectors.
  
• Multidimensional connector for SAP BW: SAP Business Warehouse is the SAP enterprise solution for Data Warehouse. It delivers reporting, analysis, and interpretation of business data. Denodo provides a Multidimensional database wrapper that generates MDX (MultiDimensional eXpressions) to query the cubes (BW Connector).
  

For details on how to install the JCo adapter, please refer to the Denodo Platform Installation Guide, section Installing the SAP JCo Connector.

In addition to these two adapters, there are also other options to directly access the underlying SAP database:

• Denodo’s RFC_READ_TABLE connector: this custom wrapper queries SAP tables invoking the standard RFC function module RFC_READ_TABLE. Instead of creating a BAPI data source and invoking the RFC_READ_TABLE BAPI through it, this specific adapter will translate SQL into the corresponding BAPI calls, offering a complete abstraction of the table access.
  
• JDBC adapter: for direct connection to the underlying database. This is the option that will offer the best performance, as it skips intermediate layers.

Accessing an ODS

ODS (Operational Data Stores) are persisted relational tables in the SAP underlying database. ODS Tables can be accessed using two different methods:

• Direct table access: ODS tables can be directly accessed by Denodo using the JDBC adapter and the RFC_READ_TABLE connector. To achieve good performance, Denodo recommends direct access to the underlying database.
• Access as cubes: SAP also exposes ODS tables as cubes, so it is also possible to query the tables using Denodo’s Multidimensional connector for SAP BW. However, since ODS uses traditional relational tables, a relational-to-multidimensional mapping is done by SAP. Since Denodo is based on a relational model, Denodo applies a multidimensional-to-relational mapping. In conclusion, data from an ODS will go through multiple layers of unnecessary processing.

Adding conditions to an SAP BW cube: slicing vs filtering

SAP allows to add conditions on dimensional data using two different approaches, slicing the dimension  and filtering by attributes. The behavior of SAP will be different depending on what technique is used, therefore it is important to understand how they work and how Denodo is using them.

• Slicing means filtering results by a particular value of the dimension hierarchy. You need to specify the member of the dimension you need to use via its internal technical name. It is a very fast operation.

• Attribute filtering allows you to filter based on any other attribute of the dimension. It is much slower.

Denodo, depending on what columns are used in the WHERE clause, generates an MDX “slice” or a “filter”:

• If the column that represents the member is used, Denodo will use slicing. It works both with the external name and with the member key.
  
• If a column that represents an attribute is used, Denodo will use filtering.
  

Slicing has certain limitations on SAP. The internal key has to be used and it only supports the EQUAL operator. Denodo has some built-in logic to overcome these limitations:

• End users do not have to deal with “internal keys”. If the “external name” is used, Denodo will automatically map that value with its corresponding internal key. To enhance performance, Denodo will cache internally the values for those maps.
  
• Denodo emulates the implementation of other operators: LIKE and <>. In this case, Denodo applies the operator to the “external to internal name” map in order to get the corresponding values that will be used in the slice.

These limitations do not apply for attributes and measures. Other operators like <, <=, >, >= work fine on these columns.