It is quite a common requirement to load data from point A to point B in BW, while performing a lookup on a DSO to get a bunch of fields from there.
This is usually implemented as follows: a SELECT statement the transformation Start Routine picks up data from the DSO and fills up an internal table, and an end routine (or field-level routines) populates the target fields by reading the internal table.
In keeping with the general BW 7.3 theme of automating common scenarios, a new transformation rule type has been introduced to do this automatically.
To take this new feature out for a spin, I created a DSO with loosely based on the 0FIAR_O03 DSO. My DSO had the key fields Company Code, Customer (0DEBITOR), Fiscal Period, Fiscal Variant, Accounting Doc No, Item No and Due Date Item No. It also had the data fields Credit Control Area, Debit/Credit Amount, Local Currency, Credit Limit and Currency.
I created a Flat File DataSource , which did not contain any fields for Credit Limit and Currency. The objective was to derive these two fields in the transformation from the Credit Management Control Area Data DSO (0FIAR_O09). To begin with, this is what the transformation from DataSource to the custom DSO looked like.
To perform the lookup, first the key fields of the lookup DSO have to be identified. The key fields of the 0FIAR_O09 DSO are Credit Control Area and Customer Number (0C_CTR_AREA and 0DEBITOR). The lookup logic will search the 0FIAR_O09 DSO based on these two fields. In order to do this, the Credit Control Area and Customer from the DataSource should be mapped to the Credit Limit key figure in the target.
The first step in the Rule Details is to specify the DSO from which the field values will be picked up – in this case, 0FIAR_O09. Next, the “IOAssgnmnt” column must be manually filled up with the names of the InfoObjects. It is important that ALL the key fields of the lookup DSO are specified.
In a nutshell, the above screen tells the system to derive the value of the 0CRED_LIMIT (the target field) from the 0FIAR_O09 DSO (the lookup DSO) based on the C_CTR_AREA and DEBITOR values coming in from the DSO, which correspond to the 0C_CTR_AREA and 0DEBITOR InfoObjects of the lookup DSO.
The 0CURRENCY target field also needs to be similarly mapped.
This is how the transformation looks after we're done. Observe the "DSO" icon which appears next to the Credit Limit and Currency in the target of the transformation.
Once this is done, run the DTP. The transformation will perform perform the lookup and populate the values. Activate the data when the load completes.
Now to begin verifying the data. The Flat file contained the following values, which were loaded to the PSA. Observe that there is no Credit Limit data in this file.
In the 0FIAR_O09 DSO, the following values were present.
After the load, this is how the data in the DSO looks.
As the screenshot shows, the transformation rule has correctly picked up the Credit Limit from the 0FIAR_O09 DSO.
A few caveats are in order on this feature.
- All the key fields of the lookup DSO should be specified. If a partial key is specified (for instance, if we had mapped only 0DEBITOR in the source fields of the transformation rule) the system will assign the value from the first record it finds in the lookup DSO
- The InfoObject Assignment for the source fields should have exactly the same names as the corresponding InfoObjects in the lookup DSO. If the InfoObject in the lookup DSO was 0CRED_LIMIT and the target InfoObject of the transformation rule was 0VALUE_LC, this technique cannot be used as the InfoObjects differ
- The target InfoObject will be filled from the value of the InfoObject having the same name in the lookup DSO. In other words, 0CRED_LIMIT is filled up based on the value of 0CRED_LIMIT in 0FIAR_O09. If 0CRED_LIMIT did not exist in the lookup DSO, the system will throw an error during transformation activation
Essentially, this feature is most useful if you have simple lookups, for instance get Field X from DSO Y based on the lookup field Z and write it out in field X of the target. However, it may not be best solution if you have more complex requirements which involve
- Pulling multiple records from the lookup DSO and getting the first or the last found record in the set
- A lookup DSO in which the field you want has a different name
- SAP Managed Tags:
26 Comments
