Usage of BW7.3 Transformation Rule Type “Read from DataStore”
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
Hi Suhas,
Thanks for this blog posting!
As you have investigated this new functionalitly, maybe you can throw some light on the issue we're having?
In a transformation from DSO B to DSO C we are reading from DSO A with this new feature.
But after some time many records are stored in the error stack, because they couldn't find a record in DSO A.
Some investigation pointed out that all these records were reversals (recordmode = 'R').
Also in DSO A these records were reversed and thus deleted from the active table.
I personally think that this is an issue in this new feature of BI7.3, because the generated program doesn't care about the recordmode and just tries to read the data.
Do you have any idea on how to solve this? Or is it the best way to create an OSS-message?
Thanks in advance,
Steven Groot
Hi Steven,
When specifying this rule type, there is a button that defines the behaviour when the record is not found. In the second screenshot in the blog, it is the button appearing to the right of the "InfoObj. from DSO" field.
There you can define whether an error is thrown or a Constant. Perhaps this may not be specified in the rule, that could be the reason for the errors.
Now whether DSO A has no records for the key or whether the records existed and got reversed/deleted at some point, both scenarios should be equivalent from the point of view of the lookup logic. So if the transformation continues to throw an error even after making the above setting, indeed it would be an issue with this feature of 7.3
Regards,
Suhas
Hi Suhas,
Thanks for the explanation! That was exactly the setting I was looking for.
Apparently the default setting is 'Error message', while I needed the setting 'Constant'.
Thanks for your help!
Regards,
Steven
Hi Suhas,
Thank you for sharing information. Really appreciate your efforts.
Some questions from my end-
1. When we read from DSO as a Rule type, it fires "Select" statement on DSO for each record?
2. Do you recommend to use this rule type if lookup DSO has millions of records?
Thank you in advance.
BR
Yogesh
Hi Yogesh,
I would still suggest you go with the traditional approach, that way we have control on the movement and as per the requirement to most extent. But this is a nice feature though.
Hi Aditya,
Thanks for your detailed explanation!! I would like to ask you about the traditional approach you are talking in above comment. Could you please let me know what is that approach. Could you send any links to get a deep understanding of that scenario?
Thanks in advance for your help!
Regards,
Rohit
Hi Suhas
I am trying to fix a problem with an Infocube that is loeaded using several "read from DSO" rules (a lot of them). Basically the porblem is that when the user tries to load it, he gets a dump, when I go to the st22 the description is that the field "x" is not in the table from where I am trying to read (but it is). I have reviwed each one of this rules I even have retired each rule one by one and I have identified the rule that causes the dump. The only different thing about that rule is that this one is reading from a different DSO. So I have 22 rules that read from one dso (yes, 22) and just 1 that reads from a different one, and this last one is the rule that is causing the dump. As far as I undestand this Infocube has been loaded before, the problem started recently, I think that the number of rules has something to do with the dump. Is there any kind of limitation wheter in number of rules or in number of different DSOs from where data can be read?
Thank you in advance
Gerardo
Good one!
Well Explained, Thank-You.
Thank you Suhas.. Great help.
Good Document
good one... 🙂
Regards,
SM
Really very good document.
BR
Prabhith
well explained!
Hi Suhas,
very excellent documentation of the new functionality "Read from DSO" from BW 7.3 upwards.
Thanks for sharing.
Regards,
Michael
Really Good. Well Explained.
Regards,
karthick.
Gosh, its good
Very Nice
Hi Suhas,
Excellent post 🙂 thanks for sharing the info.
Regards,
Siva
I've created the same Rule, but at DTP execution Error appears: CANNOT READ FROM DATASTORE OBJECT ZXREF3O1 FOR RECORD 1
Any ideas?
Thanks in advance.
Hi Mihmalis,
I would suggest you do the usual checks i.e. whether the DSO ZXREF3O1 is active, whether the field definitions match etc.
Also, check the settings in the transformation. I've explained it earlier in this comment.
Nice Post! Question - if your lookup fails is there a way to see what value it was trying to look up ? Thanks!
Hi Suhas,
Well Explained, Easily understandable, Thanks for the post...!! 🙂
Hello Suhas.
Great read.. Indeed, Very helpful... Awesome article.... This is a brilliant way to go.
Regards
Ashok
Hi Suhas,
This has helped me in avoiding the ABAP code.
Thanks for the post.
Regards,
Lakshmi
Nice feature and good article. But the field names in the target DSO and the look up DSO needs to be identical is a set back. SAP should have included another field below the 'Infoobject from DSO' field like 'Field from lookup DSO' to let the user select the field from lookup DSO that may have a different name . 🙂
Regards,
Usha.