![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
Thousands of companies around the world have made substantial investments in SAP - the leading supplier of packaged client/server applications. Organizations use SAP R/3 to manage their operational areas, including financial accounting, human resources, manufacturing, logistics, sales, and distribution. However, for a variety of reasons, these organizations have critical data stored in other business applications spread throughout the enterprise. And in most of the cases, these applications reside on heterogeneous platforms and in dissimilar database technologies than those driving the SAP R/3 system.
Today, successful companies recognize that to realize the full benefit of their SAP investment and to maintain a competitive edge, they need to incorporate business intelligence solutions that allow them to leverage the wealth of data captured in their SAP applications with the data on business legacy systems, or other systems. Examples of data that can be leveraged with SAP data include:
- Census data
- Spatial data (location-based data)
- Dun & Bradstreet data
- Other corporate data
To satisfy the business requirement to use the wealth of information stored in SAP/R3 system, Connector for SAP R/3 was introduced as an enhancement to DB2® Warehouse Manager V7.2. Connector for SAP R/3 can be used to bring SAP business objects that are stored in SAP R/3 systems into a DB2 data warehouse. Business objects provide an object-oriented view of R/3 business data and functions. You can then use the power of DB2 and DB2 Warehouse Manager for data transformation, data analysis, or data mining.
DB2 Universal Database® offers the Data Warehouse Center, a component that automates data warehouse processing. You can use the Data Warehouse Center to define the processes that move and transform data for the warehouse. Then, you can use the Data Warehouse Center to schedule, maintain, and monitor those processes. Figure 1 shows the architectural view of DB2 Warehouse Manager with its connector for SAP R/3.
Figure 1: Architectural view of DB2 Warehouse Manager V7.2
Data Warehouse Center is part of DB2, which contains a default configuration. The DB2 Warehouse Manager is a separately priced feature that enhances the scalability, manageability, and accessibility of Data Warehouse Center. The DB2 Warehouse manager is required to use agents on platforms other than Windows NT. Connectors are the independent components of Data Warehouse Manager, which facilitate data extraction from different application sources. Connector for SAP R/3 comes in a separate CD for Warehouse Connectors, and it is a priced feature of Data Warehouse Manager.
After you install Connector for SAP R/3, the only additional requirement is to install RFC Runtime modules from the Presentation CD (which comes with SAP Client software) to the client machine where SAP Connector is installed. Optionally, you can install the SAPGUI (SAP Client software) for troubleshooting or verification testing. Connector for SAP R/3 needs to be installed where the Warehouse Agent is installed.
There is no additional configuration required on SAP R/3 server to make Connector for SAP R/3 work. Connector for SAP works with SAP R/3 version 4.6C. Connector can be used to export data from an SAP R/3 system, irrespective of the operating system on which SAP R/3 is installed. Also, Connector for SAP works with all the databases supported by SAP R/3 software. Currently, Connector for SAP supports English-only installations of SAP R/3 server.
SAP R/3 components used by the connector
Connector for SAP R/3 uses the BAPI interface (associated with SAP Business Objects) to extract data from an SAP R/3 system. Connector for SAP interacts with the following components of SAP R/3:
- Business Object Repository (BOR)
- Business Object (BO)
- Business Application Programming Interface (BAPI)
2. Accessing SAP R/3 data with BAPIs
SAP has introduced object-oriented technology into the R/3 System by making R/3 processes and data available in the form of the SAP Business Objects. Examples of business objects include Employee, SalesOrder, PurchaseOrder, among others.
BAPIs are the method calls associated with each of those business objects to access their data. GetList is an example of a BAPI for an Employee business object. GetList returns a list of all employees that are defined in the SAP R/3 system. The SAP Business Objects and their BAPIs provide an open, object-oriented view of business processes and data in an R/3 System.
All the Business Objects and their BAPIs are defined and stored in SAP's Business Object Repository (BOR), which is a metadata store for SAP business objects and their BAPIs.
Defining an SAP Source using Connector for SAP R/3
After bringing up the Data Warehouse Center (Figure 3), right-click Warehouse Sources => Define => SAP, which brings up the window shown in Figure 4.
Figure 3. Choose SAP as a warehouse source
Figure 4. Define warehouse source
The first two tabs are generic to all the warehouse sources in which you specify the name and description for your new source and designate the agent site from which an SAP data extract step can be run.
The third tab (Figure 5 and Figure 6) is specific for defining the SAP source. The top half collects the Server information and bottom half collects the user-specific information.
There are two different ways to be able to connect to SAP system:
- By an application server, which requires that you specify an application server name and system number.
- By specifying a group of servers, which requires that you specify an SAP system ID, a message server name, and a group name.
Figure 5. Defining and SAP source using an application server configuration
Figure 6. Defining an SAP Source using a group of servers configuration
User information
The Client number, User ID and Password are mandatory to connect to the SAP system.
Business objects tab
After you have entered all the required connection information on the SAP Source tab for the SAP system, Connector for SAP R/3 connects to the SAP System and reads all the business object names from the SAP Business Object Repository (BOR) as shown in Figure 7 and presents that information as available business objects.
Figure 7. Displaying available and selected business objects
At this point, select the business objects for which you want to pull data into your warehouse and move those to the right-hand side window.
To save the newly defined Warehouse SAP Source, click OK. While saving the SAP source, Connector for SAP R/3 also reads the additional information for the selected business objects from the BOR and stores that information in the Control database of DB2 Warehouse Manager.
Displaying the properties of SAP business objects
After you have defined the SAP source, it will be listed under Warehouse sources. Expand Warehouse Sources => New SAP Warehouse Source => Business Objects. On the right-hand side, you will see all the selected business objects for the defined SAP source. To see the properties of a Business Object, select and right-click on the business object, then click on Properties, which displays the information shown in Figure 8.
Figure 8: Properties of a SalesOrder business object
The Source Business Objects tab displays the basic information and description about the business object that was extracted from the SAP BOR.
The Parameters tab displays the metadata information (read-only) for the parameters of the object such as data type, length, precision, whether it's a key, and whether it's mandatory.
The Parameter Mapping tab, as the name suggests, is where you map the input and output parameters that are needed to extract the detailed information for the object. This tab is enabled only if detailed information is available for the object.
If you want detailed information for the business object, you must map the key fields for the business object to export parameters from the business object's GetList method (part of Business Application Programming Interface). You must map all key fields, but not all of the export parameters must be mapped. Figure 9 shows an example of the parameter mapping information for a PurchaseOrder business object.
Figure 9. Mapping parameters for the PurchaseOrder business object
Extracting SAP business objects
Use the SAP Data Extract Step to extract SAP business objects from an SAP/R3 system and import them into Data Warehouse Center. The advantage of extracting data from Business Objects is that SAP has already wrapped up the data in form of real world objects, such as an employee or a sales order.
SAP Connector provides the business content extractions and also has the capability to define the target DB2 tables for the extracted data. This makes SAP Connector more attractive than other vendor tools in which someone has to manually pre-define all the tables on the target system. You can use Default Target Table generation to create target tables for the business objects, or you can use the pre-defined existing tables as target tables.
The SAP tab displays the name, description and user notes about the SAP data extract step.
The Input Parameters tab displays the input parameters for method calls used to extract data for business objects. Basically, you can use these parameters to specify the selection criterion for the output data. Specifying these parameters is similar to specifying the WHERE clause in an SQL statement.
Figure 10. Input parameters for an SAP data extract step
The Output Parameters tab displays the output parameters for method calls used to extract data for business objects. You should select and move over only those output parameters that you want to store in your warehouse. Selecting these output parameters is similar to specifying the columns in a SELECT clause of a SQL statement.
Figure 11. Output parameters for an SAP data extract step
The Column Mapping tab (Figure 12) is where you can map the output parameters (which you selected on the Output Parameters tab) to columns of the target table. The left bucket shows the output parameters selected from the previous tab. If you haven't created or selected the target table yet, you can use the Default Target Table Generation button to create a target table, and this process will automatically map the output parameters to the target columns.
If you are using a pre-existing target table, the right bucket displays the columns of the target table. Now you can either manually map all the output parameters to the target columns, or you can use the Add Columns and Maps button to map them automatically.
Figure 12. Column mapping between source object and target table
The Processing Options tab (Figure 13) lets you specify information about how you want the data extraction to occur:
- The population type field indicates whether data in the target table should be replaced or appended to.
- The commit count determines how many SQL INSERT statements the step issues before issuing a COMMIT statement. The default is 0, which means that the step will insert all records in one unit of work before issuing the COMMIT statement. You should set the commit count to some number greater than zero to commit the records at regular intervals.
The Retry count is used only if the step fails.
Figure 13. Specifying processing options for extracting SAP data
Example: Using order details for business analysis and forecasting
Let's go through an example to understand how SAP Connector can be used in a customer environment. Our customer is a nationwide furniture company, which has branch offices in all 50 states and almost 400 retail stores in different cities. The financial department, headquartered in Chicago, wants to track, analyze, and forecast the sales revenue across geographies on a periodic basis for all products sold. In different states, sales and customer data resides on disparate platforms in heterogeneous sources including DB2 and IMS on OS/390, Oracle on Windows NT, and SAP R/3 on a UNIX platform.
To achieve the objective of analyzing data and to predict future trends for sales, the company has decided to build a data warehouse using IBM's DB2 Warehouse Manager. Although it is not possible in this limited space to describe all of the features of DB2 Warehouse Manager, we can say that it is a distributed, heterogeneous infrastructure for designing, building, maintaining, governing, and accessing highly scalable, robust DB2 data warehouses. It helps in moving data directly from source to target and controls the server on which transformations take place by using distributed warehouse agents. DB2 Warehouse Manager also provides pre-built data cleansing and statistical transformations, which can be used for quick warehouse deployment.
Let's use the Data Warehouse Center to define a Subject called Sales & Distribution Subject area, and a process called Order Details that contains SAP steps for SalesOrder, Customer, and Material business objects.
- First, define an SAP system as a Warehouse Source and select the following business objects from which to import the data:
- SalesOrder
- Customer
- Material
- Define a subject called Sales & Distribution Subject area. Then define a process called Order Details within that subject.
- Open the Process Model for Order Details process.
- Click on the Add Warehouse Source & Target icon and then drop it on the canvas. Drill down to the business object level for the defined SAP source. Highlight the SalesOrder, Customer, and Material business objects, move them to the right-hand side bucket and click OK.
- Open the Properties window for the Material business object and go to the Parameter Mapping tab. Map the key field Material to the export parameter called MatnrList.MATERIAL.
- Similarly, open the Properties window for the Customer business object. Map the key field CustomerNo to the export parameter AddressData.CUSTOMER.
- Define three separate SAP data extract steps for the three business objects.
- Open the Properties window for each of the steps one by one, accept the default values on the Input Parameter tab, move over the required parameters on the Output Parameters tab, and use the option Generate Default Target Table to create the target tables. If you have the target tables predefined, you can use them instead.
- On the Processing options tab of Step Properties window, select a population type, either Replace or Append depending on whether you want to replace and create a table or want to append data to an existing table.
- Promote the steps to Production mode and populate the target tables. Figure 14 shows what this looks like in the Process Modeler.
Figure 14. The DB2 Warehouse Center process model for three SAP data extraction steps
Within the Data Warehouse Center, the Warehouse Schema Modeler is a specialized tool for generating and storing schema associated with a data warehouse. Any schema resulting from this process can be passed easily as metadata to the DB2 OLAP Integration Server.
Using these three target tables created above and couple of other tables extracted from various sources, you can define a star schema like the one shown in Figure 15.
Figure 15. Star Schema for Order_Detail example
The example in this article shows that many businesses have valuable data in SAP R/3; at the same time, they also maintain critical information in other databases such as DB2, Oracle, Sybase and Informix - data sources to which they have regular, unrestricted access. In conjunction with DB2 Warehouse Manager, Connector for SAP R/3 enables you to combine external demographic or legacy data sources that need to be integrated with SAP data to provide a complete customer profile.
The author would like to thank the following people for their contributions to this article by providing technical input and/or reviewing the article: Madhu Kochar, Michio Kikuchi, and Kathryn Zeidenstein.
Rohit Agarwal is an Advisory Software Engineer in Silicon Valley Laboratory in San Jose, California. He was one of the original team members responsible for the development of Connector for SAP R/3. He has been working as a DB2 Database Administrator interacting with different ERP software prior to joining IBM Silicon Valley Lab. You can reach Rohit at arohit@us.ibm.com.
Table of contents
Next steps from IBM
Tags
Use the slider bar to see more or fewer tags.
For articles in technology zones (such as Java technology, Linux, Open source, XML), Popular tags shows the top tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), Popular tags shows the top tags for just that product zone.
For articles in technology zones (such as Java technology, Linux, Open source, XML), My tags shows your tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), My tags shows your tags for just that product zone.
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
