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.
Would you like to see this full item?