Skip to Content

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.

Tr1.png

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.

Tr2.png

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.

TR3.png

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.

TR4.png

In the 0FIAR_O09 DSO, the following values were present.

/wp-content/uploads/2012/05/tr5_100328.png

After the load, this is how the data in the DSO looks.

/wp-content/uploads/2012/05/tr6_100329.png
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
To report this post you need to login first.

26 Comments

You must be Logged on to comment or reply to a post.

  1. Steven Groot

    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

    (0) 
    1. Suhas Karnik Post author

      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

      (0) 
      1. Steven Groot

        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

        (0) 
  2. Yogesh Patil

    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

    (0) 
    1. Aditya Sai

      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.

      (0) 
      1. Rohit Ayinaparthy

        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

        (0) 
  3. Avila Gerardo

    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

    (0) 
  4. Michael Rau

    Hi Suhas,

    very excellent documentation of the new functionality “Read from DSO” from BW 7.3 upwards. 

    Thanks for sharing.

    Regards,

    Michael

    (0) 
  5. Mihmalis Aivars

    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.

    (0) 
    1. Suhas Karnik Post author

      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.

      (0) 
  6. Kevin Skroch

    Nice Post!    Question – if your lookup fails is there a way to see what value it was trying to look up ?      Thanks!

    (0) 
  7. Usha Marudaipillai

    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.

    (0) 

Leave a Reply