Skip to Content
avatar image
Former Member

Read Master Data + Routine in Transformation

Hello forum,

Within a transformation from PSA to an Info Cube, I would like to read master data (0CUSTOMER) using and Info Object within my PSA and if I find the record in there, assign this value (Customer #), otherwise, assign a dummy precalculated number.

I guess I would have to use a routine to achieve something like that but I have no ABAP experience at all. Any idea from you experts out there?

Thanks for your help.

P.s. BI 7.0

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Oct 21, 2008 at 03:52 PM

    Go to RSD1. Look at 0CUSTOMER. For simple existence checks, you can use the sid table - /BI0/SCUSTOMER. If the customer is in the master data, there will be a record in here. Double click on the sid table name to see its field name and types.

    DATA: l_customer TYPE /bi0/oicustomer.
    
    SELECT customer FROM /bi0/scustomer INTO l_customer
        WHERE customer EQ your_info_object_in_the_psa.
    IF sy-subrc IS INITIAL.
      result = l_customer.
    ELSE.
      result = 'some dummy value'.
    ENDIF.

    Note - this is simple, but not very good performance wise. It is probably a good to go on an ABAP course, and or buy one of the books. If you're going to be doing much ABAP, you need to get proper training. With two of my clients at the moment, they're even encouraging BI professionals to go on advanced ABAP training, like ABAP Objects. For them, it's key that these guys can program well. It can make the difference between a system that works, and one that's unusable.

    Add comment
    10|10000 characters needed characters exceeded

    • You have a choice. You can either buffer into an internal table (which is like an array but different!) all the records at the start. Or you can read from the buffer and if it isn't there, then read from the database, and put it into the buffer for next time. You can further improve performance by using the right kind of table. There are three kinds of internal table - HASHED, SORTED and STANDARD. For lookups, HASHED is usually the quickest.

      For what you are doing I'd choose the second kind of buffering, as you may actually only need 15 customer records and there could be thousands in the database table, and use a hashed table for lookup.

      In the start routine, in the global data, have the following:

      * Define line type of customer record
      TYPES: BEGIN OF customer_ty,
               customer TYPE /bi0/oicustomer,
               exists   TYPE flag,
             END OF customer_ty.
      
      * Define hashed internal table, keyed on customer
      DATA: th_customer TYPE HASHED TABLE OF customer_ty WITH UNIQUE KEY customer.

      In the routine do this:

      DATA: ls_customer TYPE customer_ty.
      
      READ TABLE th_customer INTO ls_customer
                             WITH TABLE KEY customer = your_info_object_in_the_psa.
      
      * Read from database if not found
      IF sy-subrc IS NOT INITIAL.
        SELECT SINGLE customer FROM /bi0/scustomer INTO ls_customer-customer
            WHERE customer EQ your_info_object_in_the_psa.
        IF sy-subrc IS INITIAL.
          ls_customer-exists = 'X'.
        ENDIF.
        INSERT ls_customer INTO TABLE th_customer.
      
      ENDIF.
      
      IF ls_customer-exists IS NOT INITIAL.
        result = ls_customer-customer.
      ELSE.
        result = 'some dummy value'.
      ENDIF.

      If you want to buffer the entire table do it like this:

      In the start routine, in the global data, have the following:

      * Define hashed internal table, keyed on customer
      DATA: th_customer TYPE HASHED TABLE OF /bi0/oicustomer WITH UNIQUE KEY table_line.

      In the start routine itself.

      SELECT customer FROM /bi0/scustomer INTO TABLE th_customer.

      Then in the routine:

      READ TABLE th_customer WITH TABLE KEY customer = your_info_object_in_the_psa
                            TRANSPORTING NO FIELDS.
      
      IF sy-subrc IS NOT INITIAL.
        result = 'some dummy value'.
      ELSE.
        result = your_info_object_in_the_psa.
      ENDIF.

      This applies to both 7.0 and 3.5, though the structure of the code surrounding it is different.

      matt

  • avatar image
    Former Member
    Oct 22, 2008 at 11:57 AM

    Thank you all, especially Matt for your help.

    Now I'll adjust my resume to mention that I'm an ABAP expert 😊

    Add comment
    10|10000 characters needed characters exceeded