Since the launch of SAP BW, users have demanded the ability to incorporate data from various SAP and non-SAP systems into SAP BW systems. SAP has consistently met their demand and has improved SAP BW’s ability to natively acquire data and metadata from disparate data sources.

SAP BW’s data acquisition capacity has grown to include a file interface for ASCII and CSV files, a SOAP interface for XML data, and the Staging Business Application Programming Interface (Staging BAPI), which several third-party vendors such as Ascential Software have adopted for ETL tools. In addition, SAP BW supports access to remote Relational Database Management System (RDBMS) tables via DBConnect functionality.

With the release of SAP BW 3.5 later this year, SAP adds yet another extraction interface to the SAP BW ETL services layer — Universal Data Connect (UDConnect). Figure 1 illustrates how UDConnect provides additional flexibility and extends SAP BW 3.5 native extraction capabilities beyond database platforms such as Oracle, Microsoft SQL, DB2/Informix, and MaxDB, which are all supported by the SAP Web Application Server (Web AS). With UDConnect, users can access data from a wide variety of sources including RDBMSs, which support the Java Database Connectivity (JDBC) Data Access API, as well as OLAP servers to support the OLE DB for OLAP (ODBO) and XML for Analysis (XMLA).

Figure 1

Overview of the staging interfaces provided by SAP BW 3.5

UDConnect is part of a more general concept in SAP BW 3.5 called Universal Data Integration (UDI), which uses the SAP NetWeaver J2EE engine to provide inbound and external connectivity to SAP BW for custom Java applications. UDI’s integration into custom Java applications is supported by SAP BI Java Software Development Kit (BI Java SDK), which is off-topic for this UDConnect discussion.

We will introduce the functionality of the UDConnect component. The architecture of the component is addressed along with details on the various connectors shipped with SAP BW 3.5. We will then offer an overview of the major steps of a common usage scenario, so you can understand what it will be like to leverage this new technology.

Key Concepts

Conceptually, UDConnect is similar to DBConnect in that it allows connection to remote databases; hence the similar naming scheme. Thanks to its support of open, industry-standard APIs, the UDConnect interface provides access to a wider variety of data sources using JDBC, ODBO, and XMLA.

UDConnect reads remote data dictionaries, replicates tables or views metadata in the local SAP BW metadata repository, and allows extraction from those tables and views. It is composed of three major components (Figure 2):

• BI Java Connectors are relational and multidimensional data connectors that plug into the J2EE Server.
• UDConnect is a J2EE Server component that links the BI Java Connectors to the ABAP side of SAP BW — e.g. the DataSource.
• DataSource is the inbound component on the SAP BW ABAP stack that receives data from the UDConnect J2EE component. Technically the DataSource is a generic function module that is automatically generated by SAP BW.

Figure 2

Architecture of UDConnect

BI Java Connectors are Java modules deployed onto the SAP J2EE engine. For application components installed on the server, they enable unified access to external data providers such as RDBMS, OLAP, or application servers. Four BI Java Connectors ship with SAP BW 3.5: the BI JDBC Connector, the BI SAP Query Connector, the BI ODBO Connector, and the BI XMLA Connector. We will discuss these connectors in more detail later.

The four connectors comply with the J2EE Connector Architecture (JCA). JCA defines a standard architecture for the integration of J2EE servers with Enterprise Information Systems (EIS). The standard sets the requirements a connector must meet to plug into an application server.

The UDConnect component is a J2EE application known technically as a “stateless session bean.” It consumes the metadata and query services provided by the BI Java Connectors, and couples them with the DataSource, which is implemented as an ABAP function module on the SAP Web AS.

UDConnect employs the SAP Java Connector (SAP JCo) for communication between the Java and ABAP components of the SAP Web AS supporting SAP BW. SAP JCo is represented in Figure 2 by the arrow pointing from the UDConnect component to the DataSource. In the UDConnect architecture, calls are directed from the DataSource (ABAP stack) to the UDConnect component (J2EE Java stack) via SAP JCo.

Employing the SAP J2EE engine for loading data and remote access has several advantages. With resource management tools, scalability options, and security solutions, the J2EE engine simplifies application development for accessing back-end databases. There is also significant industry support for J2EE standards and the technology is widely available. Sun’s Java developer Web site lists more than 200 drivers that support the JDBC Data Access API for data sources ranging from RDBMSs such as Teradata, Sybase, and Informix to Text and XML file formats.¹

UDConnect Architecture

To support the DataSource, functionality has been added to the InfoSource maintenance tool in the modeling area of the SAP BW Administrator Workbench. It generates the linkage between UDConnect and generic SAP BW data sources via metadata retrieved by the BI Java Connectors from the underlying remote database.

From this point onward in SAP BW, the UDConnect interface will employ well-established concepts for generic data sources via function modules. For all SAP BW constructs such as update rules and data targets “above” the DataSource, data retrieval details are transparent (Figure 3) and data flows up from an InfoSource to the data targets. Data flow is depicted in diagrams generated by the SAP BW Administrator Workbench and accessed by right-clicking on an InfoProvider, InfoSource, or DataSource, and selecting Show Data Flow in the menu.

Figure 3

The DataSource for UDConnect looks like a normal DataSource to SAP BW

A Closer Look at BI Java Connectors

The additional flexibility and enhanced extraction capabilities offered by the new SAP BW 3.5 system owes a lot to its four BI Java Connectors, which are relational and multidimensional data connectors that plug into the J2EE Server. The BI Java Connector for SAP Queries is a component of SAP Web AS that accommodates data acquisition in transactional systems such as R/3.

Extracting from an external database using JDBC is straightforward. Columns of the JDBC source table are mapped to fields in the transfer or communication structure and data types are mapped from Java types to matching ABAP data types.

SAP BW 3.5 offers two additional BI Java Connectors for multidimensional data sources: The BI ODBO Connector supports connectivity to OLAP servers such as Microsoft Analysis Services or SAS using the respective OLE DB for OLAP providers of these servers. This interface technology is restricted to a Microsoft Windows environment because native libraries are required. As a result, the BI ODBO Connector can only be deployed on a J2EE engine running on a Windows system.

The BI XMLA Connector supports connectivity to OLAP servers based on the XML for Analysis API. In contrast to ODBO, this API is platform-neutral, and hence can be used on any platform. As of the writing of this article, server products that support this standard have been released by Microsoft, Hyperion, and SAP. The full list of BI Java Connectors supplied with SAP BW 3.5 appears in Table 1.

Extracting from OLAP Sources

SAP BW extraction APIs support flat — or tabular — views of data. Because the multidimensional structure of an OLAP DataSource does not directly map into the extraction structures used by SAP BW interfaces, we will look more closely at extracting from OLAP sources.

Multidimentional structures exposed by an OLAP DataSource are transformed through a process called “flattening” into view-like tabular structures that map easily into a flat transfer structure. Figure 4 outlines the processes used by the BI Java Connectors to flatten a simple three-dimensional InfoCube.

Figure 4

The flattening process in UDConnect

Note that for each dimension, the lowest level is used from the dimension’s default hierarchy. Each of these levels results in one field per dimension for the flat target structure. In the example, this yields the Time, Store, and Product columns. Furthermore, one column is created in the target structure for each measure — Revenue and Sales Count in the example in Figure 4. The data types of the measure columns correspond to the data types of the measures in the InfoCube. The resulting flat table is shown at the bottom of Figure 4, which corresponds to the InfoCube fact table.

UDConnect in Practice

Now that you’re familiar with the architecture, let’s take a look at the major steps that make up a concrete usage scenario. A more detailed account of the scenario with technical instructions and additional resources is available for download at this link. Your Basis team will probably perform most of the steps in this section, but we have outlined them here to provide you with a better understanding of what’s going on.

The task we’d like you to consider is extracting transaction data from an orders table in a MySQL database. The examples we’ve provided are for the Microsoft Windows platform. We set the scenario on a MySQL database platform so readers can download the software and follow along with the example. The MySQL database can be downloaded for free at www.MySQL.com/downloads/index.html.

Before connecting to the source MySQL database from the SAP BW system, a few steps are necessary to set up the connection in the SAP J2EE Engine Administrator, which we’ll refer to as the Visual Administrator. The Visual Administrator is a GUI that allows you to administer relevant Java services and provides tools for remote monitoring and managing the services. It will be included on the SAP NetWeaver 2004 installation DVD along with all components of SAP BW 3.5.

MySQL provides a JDBC-compliant database driver that can be used with the UDConnect interface. The procedure we demonstrate here is similar for any other database or source that provides a JDBC driver.

To use the MySQL JDBC driver, the corresponding driver Java archives must be installed on the J2EE engine. In our example, the MySQL JDBC driver is in a single JAR file: mysql-connector-java-3.0.10-stable-bin.jar. This task is accomplished with the JDBC BI Connector.

Configuring the physical database connection is accomplished via the Connector Container services section of the Visual Administrator. It manages the overall connectivity to back-end resource systems and provides a run-time environment for resource adapters that enable connections to heterogeneous EISs. The default connection parameters of the BI_JDBC_CONNECTOR must be changed to access the sales database in the MySQL environment as shown in Figure 5.

Figure 5

Editing the Properties of the managed connection in the Connector Container

In addition to configuring the database- and driver-specific connection parameters, a user name and password is required to establish a physical connection with the database server. A loader reference must be added that points to the driver’s class libraries to the connector’s configuration, which allows the BI JDBC Connector to access the MySQL JDBC driver.

Create the DataSource and InfoSource

The final step in configuring this scenario calls for the creation of an InfoSource and the DataSource that accesses the orders table (Table 2) in the MySQL database. The process is nearly identical to the process used to create a SOAP-XML DataSource in SAP BW 3.0B and above. The maintenance of the InfoSource is done in the usual manner by maintaining the communication structure of the InfoSource and selecting the required InfoObjects. However, you’ll see that some new functionality has been added to make your life easier.

mysql> describe orders;
+————————+————————————-+———+————-+————-+———-+
| Field   | Type     | Null | Key  | Default | Extra |
+————————+————————————-+———+————-+————-+———-+
| OrderID  | varchar(5)   | YES |   | NULL |  |
| customerID    | varchar(20)  | YES |   | NULL |  |
| ProductID  | smallint(5) unsigned | YES |   | NULL |  |
| OrderDate  | date     | YES |   | NULL |  |
| UnitPrice  | decimal(6,2)   | YES |   | NULL |  |
| Quantity  | smallint(5) unsigned  | YES |   | NULL ||
| Discount  | decimal(6,2)   | YES |   | NULL |  |
+———————-+—————————————+———+————-+————-+———-+
7 rows in set (0.00 sec)

A DataSource can be generated to connect the InfoSource to the source database. This is a new feature provided as part of the UDConnect interface. From the Extras menu, select Create BW DataSource with UD Connect to access the Assigning UD Connect Source Object to BW DS screen (Figure 6). Enter the appropriate information in the RFC Destination, UD Connect Source, and UD Connect Source Object fields to identify the source database and table.

Figure 6

Assigning source fields in the communication structure

The system requests the column’s metadata for the orders table from the UDConnect component and populates the Source Object Elements list with the column names from the table. Basic mapping assignments are automatically proposed based on column names as well as data types.

When the assignment is complete, click on the Generate DataSource (for UD Connect) button shown at the bottom of Figure 6. This generates an ABAP extraction function module according to the rules for generic extractors as well as the required dictionary structures. The system prompts confirmation of the assignment of the generated DataSource to the InfoSource.

After changing some of the transfer rules such as setting the currency and unit InfoObject values to constants, the InfoSource can be activated and connected to the DataProvider of choice. The UDConnect interface allows the InfoSource to the access the source database dynamically or via persistent data staged as a basic InfoCube or an ODS. Let’s take a look at these options.

Create a remote InfoCube against an InfoSource simply by right-clicking on the InfoSource and selecting Create Remote Cube. A transient data staging scenario is created whereby data is accessed dynamically from the remote data source via the InfoSource. The BI Connector is used and the data is provided from the remote DataSource in real time.

The second option is a persistent data staging scenario where a basic InfoCube or ODS is created against the InfoSource. For this scenario, an InfoPackage must be created and the job scheduled, which causes the data to be physically transported and persisted in SAP BW.

In both cases, master data must be persistently stored on the SAP BW system if texts, attributes, and hierarchies are to be available for reporting. Regardless of the InfoProvider type chosen, once data is properly staged, Business Explorer (BEx) queries can be executed against this newly created InfoProvider.

1 See https://servlet.java.sun.com/products/jdbc/drivers for a current list of database platforms supported by JDBC.

Glen Leslie

Glen Leslie is a product manager for SAP’s Business Intelligence solution. Originally a data warehousing consultant, Glen has been working with SAP BW since the 1.2B release in a variety of environments.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.

Guido Schroeder

Guido Schroeder is the Director of Development at SAP Labs where he is leading the BI Advanced Technologies group. Before joining SAP Labs, he worked with the BW Regional Implementation Group in the US at SAP America, and began with the core BW development team at SAP AG in 1997.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.