how to make use of a global internal table in SAP BW during transfer rules

[Former Member]

HI friends,

I am ABAP consultant working on some APO info cubes. I have an issue during the upload of planning area data into APO info cube.

Please help.

I am using a transfer routine to find the TECHWEEK from a data base table ZGC_CALWEEK based on the on the calender month and calender week.

Code I am writing is like below.

*----------------------------------------------------------------------*
*       FORM COMPUTE_/BIC/ZCALWEEK
*----------------------------------------------------------------------*
* Compute value of InfoObject ZCALWEEK
* in communication structure /BIC/CSZT6DPPA
*
* Technical properties:
*     field name      = /BIC/ZCALWEEK
*     data element    = /BIC/OIZCALWEEK
*     data type       = NUMC
*     length          = 000006
*     decimals        = 000000
*     ABAP type       = N
*     ABAP length     = 000006
*     reference field =
*----------------------------------------------------------------------*
* Parameters:
*  -->  RECORD_NO       Record number
*  -->  TRAN_STRUCTURE  Transfer structure
*  <--  RESULT          Return value of InfoObject
*  <->  G_T_ERRORLOG    Error log
*  <--  RETURNCODE      Return code (to skip one record)
*  <--  ABORT           Abort code (to skip whole data package)
*----------------------------------------------------------------------*
FORM COMPUTE_/BIC/ZCALWEEK
  USING    RECORD_NO LIKE SY-TABIX
           TRAN_STRUCTURE TYPE TRANSFER_STRUCTURE
           G_S_MINFO TYPE RSSM_S_MINFO
  CHANGING RESULT TYPE /BIC/OIZCALWEEK
           G_T_ERRORLOG TYPE rssm_t_errorlog_int
           RETURNCODE LIKE SY-SUBRC
           ABORT LIKE SY-SUBRC. "set ABORT <> 0 to cancel datapackage
*$*$ begin of routine - insert your code only below this line        *-*
* DATA: l_s_errorlog TYPE rssm_s_errorlog_int.
DATA: LV_WEEK         TYPE ZGC_CALWEEK-APOWEEK,
        LV_MONTH        TYPE ZGC_CALWEEK-APOMONTH,
        LV_TECH_WEEK    TYPE ZGC_CALWEEK-TECHWEEK.

  LV_WEEK   = TRAN_STRUCTURE-CALWEEK.
  LV_MONTH  = TRAN_STRUCTURE-CALMONTH.

  SELECT SINGLE TECHWEEK INTO LV_TECH_WEEK
    FROM ZGC_CALWEEK CLIENT SPECIFIED
    WHERE  MANDT = SY-MANDT
    AND  APOWEEK  = LV_WEEK
    AND   APOMONTH = LV_MONTH.
  IF SY-SUBRC IS INITIAL.
    RESULT = LV_TECH_WEEK.
  ELSE.
    RETURNCODE = 1.
    ENDIF.
*$*$ end of routine - insert your code only before this line         *-*
ENDFORM.

There are more than 50-80 million records that wil be transferred from planning area to info cube. The select statment is giving pathetic performance as this has to run 50-80 million times.

After adding the select statment to find the TECHWEEK it is taking 4 times the time that used to take before writing the select statment.

Is there a way that I can first fetch the data from ZGC_CALWEEK to one internal table and that internal table can be used using read statment during the transfer routine instead of writing select statement here.

Please help in this case?

[Former Member · Accepted solution]

Hi ,

Can you give me the structure of the table : ZGC_CALWEEK .

From your routine it seems like this table exists in BI side , right ?

We can think of building some secondary indexes on table ZGC_CALWEEK on fields APOWEEK & APOMONTH, if these are non-primary fields in the table ZGC_CALWEEK.

And also i think you have the option of creating a start routine in the transfer rules maintenance screen. This start routine is run for each data package after the data has been written to the PSA and before the transfer rules have been executed.

So your start routine will execute once as compared to your tranfer rules which is being executed 50-80 million times.

Try writing the same logic in start routine in the transfer rules .

Hope the above reply was helpful.

Kind Regards,

Ashutosh Singh

[Former Member · Accepted solution]

Hi Ashutosh,

Thanks for the reply,

The structure of the ZGC_CALWEEK is as below. I have already created a secondary index on the table for this table for the fields APO WEEK and APO MONTH. This didn't help much on the performance.

I am also planning to keep the ZGC_CALWEEK database table to be fully buffered and this may definitely improve the performance but I need to reduce the data base hits as less as possible.

MANDT MANDT CLNT 3 0 Client

TECHWEEK ZTECHWEEK NUMC 6 0 Technical Week

FROMDATE_TECH DATUM DATS 8 0 Date

TODATE_TECH DATUM DATS 8 0 Date

APOWEEK /BI0/OICALWEEK NUMC 6 0 Calendar year / week

FROMDATE_APO DATUM DATS 8 0 Date

TODATE_APO DATUM DATS 8 0 Date

APOMONTH /BI0/OICALMONTH NUMC 6 0 Calendar year/month

The table ZGC_CALWEEK is in APO system, where the transfer rules are being executed.

As you mentioned START ROUTINE, In the start routine Can I create an internal table let's say GT_CALWEEK with structure ZGC_CALWEEK and pull all the records (I have a max of 2000 records in this table) from ZGC_CALWEEK to GT_CALWEEK and Can I used the same internal table GT_CALWEEK in the transfer routine to read the TECHWEEK from internal table.

Thank you very much again for you reply. Any help regarding this would be greatly appreciated.

Best regards,

Siva

[Former Member · Accepted solution]

Hi,

Yes , whatever data you are pulling in start routine, we can use that data in individual transfer rules . Even the declarations that we maintained in start routines will be accessible here in individual transfer rules .

Hope the above reply was helpful.

Kind Regards,

Ashutosh Singh

[Former Member · Accepted solution]

Hi Ashutosh,

Thank you very much for the help.

I have used the start routine to fill the internal table and created the internal table as part of global declarations.

Best regards,

Siva

[Former Member · Accepted solution]

HI Ashutosh,

Now I am creating the internal table GT_CALWEEK with reference to ZGC_CALWEEK in global variables.

and I am filling the internal table GT_CALWEEK using ZGC_CALWEEK in the start routine.

and in the actual transfer routine I read the internal table GT_CALWEEK to fetch the TECHWEEK based on the APOWEEK and APOMONTH.

The transferring data is working perfectly fine. however, since the data in bw will get loaded in packets (pls correct me if I am wrong), for each packet the global internal table contents are getting cleared and I need to fetch the data once again from table ZGC_CALWEEK.

The code I have written in the startroutine is as follows

IF gt_calweek[] IS INITIAL.
   SELECT apoweek apomonth techweek INTO gt_calweek FROM zgc_calweek.
   IF sy-subrc IS INITIAL.
      SORT gt_calweek BY apoweek apomonth techweek.
   ENDIF.
ENDIF.

There are around 5 infopakages we have created for this, and each request containing say 10 to 15 million records, and the data packet size is around 5000 records. It is still resulting in 3000 database table hits (with full data load of zgc_calweek contianing 2500 records). I have tried this in our sandbox system for 6 lakh records, the performance is still not increased when compare the individual select statement in the transfer routine.

Any help on this would be greatly appreciated. Is there any way we can reduce the data base hits to 1 select statement into internal table.

Sorry for the so long information.