Combine Data from Multiple R/3 Tables into One BW Table

  • by Rich Oxley, Kenakore, Inc.
  • January 1, 2004
Having to combine data from multiple R/3 sources into one BW table is a common requirement. Fortunately, BW 3.x provides a means - flexible staging - to carry out this requirement. The author shows how to configure flexible staging using an real-world example.


Many of you have faced a business requirement similar to this one: You need to combine customer and contact master data from the enterprise level and sales area in R/3 into one table in BW. This new table then serves as a repository for all data related to a sales area’s customers and contacts. The new master data ODS object contains address information at the customer and contact enterprise level, credit information, and demographic information from the customer and contact sales data.

The challenge of this task is the design of the model and the process to update the data in BW. Designing the model can be challenging due to the different combination of data sets and determination of the key for the resulting data set. The update process can also be complex due to many data sets being combined into one record with a unique key.

To accomplish this, BW needs to denormalize all the R/3 master data. The master data for customer and contact are stored in the following manner in R/3. Each customer number has one unique record in table KNA1, and each contact has one unique record in table KNVK. Each customer can have zero to many contacts. The contact records have a unique key of contact number and have an attribute of customer number.

To denormalize the data, you need to combine the data from the customer with each contact record. If the customer has four contacts, R/3 would have one customer record and four contact records. A denormalized view of the data would have four records, one record for each customer/contact combination. By using the power of flexible staging (introduced in BW 3.0) and return tables in the update rules to an ODS object, you can easily denormalize data in BW.

Rich Oxley

Rich Oxley has more than six years of BW project experience and 15 years of IS experience. He has had many roles in the eight BW implementations he has taken part in, including BW architect (define requirements, design, model, and configure), technical lead (design, configure, and troubleshoot), project lead (project planning, estimation, and resource scheduling), and project team member. Rich works for Kenakore, Inc.

See more by this author


No comments have been submitted on this article. 

Please log in to post a comment.

To learn more about subscription access to premium content, click here.