cancel
Showing results for 
Search instead for 
Did you mean: 

Enhancement of the datasource is taking lot of time

Former Member
0 Kudos

Hi Experts,

we enhanced the datasource 0employee_attr almost 35 fileds but every thing fine when we are loading it taking almost 20hrs . these reports are using every day .how to reduce the loading time .iam not written any routines i thought its happened in R/3 side because lot of select statement.can any one suggest me on this issue.

Regards

sivaraju

Accepted Solutions (1)

Accepted Solutions (1)

former_member205352
Active Contributor
0 Kudos

Whats the time it takes when you check in RSA3 ???

You can trace using ST05 as to where its taking more time in the R3(source system) side.

Hope this helps.

Former Member
0 Kudos

Hi,

thank you very much for your replies . even if i executed in rsa3 taking lot of time almost 15min . let me check ST05 and code where it taking time.

Regards

sivaraju

Former Member
0 Kudos

Hi

If it's possible then create a generic data source with new fields and mapp it to the existing infosource in BW and create new infopack to load into master data table.

I think it's not idle to append ES with 35 fields.

Hope it helps..

Regards,

Chama.

Former Member
0 Kudos

Hi,

i dont know much about ABAP coding . can you pllz suggest me where i need to put indexes etc.

TABLES: PA0185,HRP1001,PA0001,PA0588, PA0587, PA0021, PA0006, PA0009, PA0003.

TABLES: HRMS_BIW_IO_OCCUPANCY.

TABLES: BWCO_MD_CCTR.

TABLES: BIW_KNA1_S,KNB1.

  • Internal table - Types -- Declaration for COSTCENTER_ATTR

TYPES : BEGIN OF T_PERNR,

PERNR TYPE PERSNO,

END OF T_PERNR.

DATA : IT_PERNR TYPE STANDARD TABLE OF T_PERNR.

DATA : VL_COUNT TYPE I.

  • data : s_area like pa0001-mstbr.

  • Declarations for 0EMPLOYEE_ATTR

DATA : I_W_DATA LIKE HRMS_BIW_IO_OCCUPANCY,

I_W_DATA_KOSTL LIKE BWCO_MD_CCTR,

L_ICNUM TYPE PA0185-ICNUM,

L_RMBEGDA TYPE PA0185-BEGDA,

L_RMENDDA TYPE PA0185-ENDDA,

L_PERNR TYPE PA0001-PERNR,

L_PERNR1 TYPE PA0001-PERNR,

L_PLANS TYPE SOBID,

L_PLANS2 TYPE SOBID,

L_SUBTY TYPE PA0185-SUBTY,

L_NAME TYPE PA0001-ENAME,

L_NAME1 TYPE PA0001-ENAME,

L_AMOUNT TYPE BETRG,

L_AMOUNT_OFF TYPE BETRG,

L_ORGUNITSVP TYPE PA0001-ORGEH,

L_ICNUM_RM TYPE PA0185-ICNUM,

L_ICNUM_TIER2 TYPE PA0185-ICNUM,

L_MSTBR TYPE PA0001-MSTBR,

L_ICNUM_21 TYPE PA0185-ICNUM,

L_ICNUM_20 TYPE PA0185-ICNUM,

L_ICNUM_24 TYPE PA0185-ICNUM,

L_ICNUM_18 TYPE PA0185-ICNUM,

L_ISSPL TYPE PA0185-ISSPL,

L_ESINO LIKE PA0588-ESINO,

L_EEBAS LIKE PA0587-TSTID,

I_W_DATA1 LIKE BIW_KNA1_S,

L_TLFXS LIKE KNB1-TLFXS,

L_NAME4 LIKE KNA1-NAME4,

  • new fields added by pandu

L_GBDAT TYPE GBDAT,

L_FAVOR TYPE PAD_VORNA,

L_NAME2 TYPE PAD_CONAM,

L_STRAS TYPE PAD_STRAS,

L_PSTLZ TYPE PSTLZ_HR,

L_ORT01 TYPE PAD_ORT01,

L_USRID TYPE SYSID,

L_XMETXT TYPE REBDXRO,

L_BANKL TYPE BANKK,

L_BANKN TYPE BANKN,

L_CNAME TYPE ZCNAME,

L_USRID1 TYPE SYSID,

L_MGRID TYPE SOBID,

L_INCDAT TYPE BEGDA,

L_LIFNR TYPE LIFNR,

L_RDATE TYPE BEGDA,

L_LWDAY TYPE BEGDA,

L_REDAT TYPE BEGDA,

L_SCHKZ TYPE SCHKN,

L_PRDAT TYPE PRDAT,

L_ABRDT TYPE LABRD,

L_ABRSP TYPE ABRSP,

L_BANKA TYPE BANKA,

L_SMDAT TYPE BEGDA.

  • l_ctc_amt type betrg.

*endchange

DATA: BEGIN OF TP_RGDIR OCCURS 0.

INCLUDE STRUCTURE PC261.

DATA: END OF TP_RGDIR.

***************************************

*Decleration for monthly salary

DATA : L_PA0008 LIKE PA0008.

DATA : V_AMOUNT TYPE PAD_AMT7S,

FLDNAM(30),

NUM(2) VALUE '01'.

FIELD-SYMBOLS: <FS> TYPE ANY.

FIELD-SYMBOLS: <FS1> TYPE ANY.

*****************************************

CASE I_DATASOURCE.

*Getting Customer branch in 0customer_attr

WHEN '0CUSTOMER_ATTR'.

LOOP AT I_T_DATA.

MOVE-CORRESPONDING I_T_DATA TO I_W_DATA1 .

*Getting Customer branch in 0customer_attr

SELECT SINGLE TLFXS INTO L_TLFXS FROM KNB1 WHERE KUNNR EQ I_W_DATA1-KUNNR.

*Getting Customer Name4 in 0customer_attr from table KNA1

SELECT SINGLE NAME4 INTO L_NAME4 FROM KNA1 WHERE KUNNR EQ I_W_DATA1-KUNNR.

I_W_DATA1-ZZTLFXS = L_TLFXS.

I_W_DATA1-ZZNAME4 = L_NAME4.

MODIFY I_T_DATA FROM I_W_DATA1.

CLEAR L_TLFXS.

CLEAR L_NAME4.

ENDLOOP.

  • Getting the RM for the employee and SVP using the ICNUM

WHEN '0EMPLOYEE_ATTR'.

LOOP AT I_T_DATA INTO I_W_DATA.

SELECT SINGLE ICNUM INTO L_ICNUM FROM PA0185

WHERE PERNR EQ I_W_DATA-PERNR

AND SUBTY = '23'

AND ENDDA GE SY-DATUM.

  • Getting the Joining date of RM

SELECT SINGLE BEGDA INTO L_RMBEGDA FROM PA0000

WHERE PERNR EQ I_W_DATA-PERNR

AND MASSN EQ '01'.

  • Getting the Resignation Date of RM

SELECT SINGLE BEGDA INTO L_RMENDDA FROM PA0000

WHERE PERNR EQ I_W_DATA-PERNR

AND MASSN EQ '14'.

*Getting the Tier II code and Name

SELECT SINGLE SOBID INTO L_PLANS FROM HRP1001

WHERE OTYPE EQ 'S'

AND OBJID EQ I_W_DATA-PLANS

AND RSIGN EQ 'A'

AND RELAT EQ '002'

AND ENDDA GE I_W_DATA-ENDDA

  • AND endda GE sy-datum

AND SCLAS EQ 'S'.

  • AND sobid EQ i_w_data-plans.

*

SELECT SINGLE PERNR INTO L_PERNR FROM PA0001

WHERE ENDDA GE SY-DATUM

  • WHERE endda GE i_w_data-endda

AND PLANS EQ L_PLANS.

SELECT SINGLE ENAME INTO L_NAME FROM PA0001

WHERE ENDDA GE SY-DATUM

AND PLANS EQ L_PLANS.

*Getting the TIER II branch

SELECT SINGLE ICNUM INTO L_ICNUM_TIER2 FROM PA0185

WHERE ENDDA GE SY-DATUM

AND PERNR EQ L_PERNR

AND SUBTY = '05'.

*Getting the Supervisor/HOD for Employee----


SELECT SINGLE MSTBR FROM PA0001 INTO L_MSTBR WHERE PERNR = I_W_DATA-PERNR AND

ENDDA GE SY-DATUM.

*Getting the Employee Product from infotype 185 styp 21.

SELECT SINGLE ICNUM INTO L_ICNUM_21 FROM PA0185

WHERE ENDDA GE SY-DATUM

AND PERNR EQ I_W_DATA-PERNR

AND SUBTY = '21'.

*Getting the Employee Branch Discription/Place of issue from infotype 185 styp 05.

SELECT SINGLE ISSPL INTO L_ISSPL FROM PA0185

WHERE ENDDA GE SY-DATUM

AND PERNR EQ I_W_DATA-PERNR

AND SUBTY = '05'.

*Getting the Employee Library no/business from infotype 185 styp 18.

SELECT SINGLE ICNUM INTO L_ICNUM_18 FROM PA0185

WHERE ENDDA GE SY-DATUM

AND PERNR EQ I_W_DATA-PERNR

AND SUBTY = '18'.

*Getting the Employee Department from infotype 185 styp 20.

SELECT SINGLE ICNUM INTO L_ICNUM_20 FROM PA0185

WHERE ENDDA GE SY-DATUM

AND PERNR EQ I_W_DATA-PERNR

AND SUBTY = '20'.

*Getting the Employee RBT Codefrom infotype 185 styp 24.

SELECT SINGLE ICNUM INTO L_ICNUM_24 FROM PA0185

WHERE ENDDA GE SY-DATUM

AND PERNR EQ I_W_DATA-PERNR

AND SUBTY = '24'.

*Getting the Employee ESI number from Infotype 588.

SELECT SINGLE ESINO INTO L_ESINO FROM PA0588

WHERE ENDDA GE SY-DATUM

AND PERNR EQ I_W_DATA-PERNR.

*Getting the Employee PF Eligibility no from Infotype 587.

SELECT SINGLE TSTID INTO L_EEBAS FROM PA0587

WHERE ENDDA GE SY-DATUM

AND PERNR EQ I_W_DATA-PERNR.

*Getting the Employee Basic salary amt from Infotype 0008.

NUM = '01'.

CLEAR V_AMOUNT.

SELECT SINGLE * INTO L_PA0008 FROM PA0008 WHERE PERNR EQ I_W_DATA-PERNR

AND ENDDA EQ '99991231'.

DO 30 TIMES.

IF STRLEN( NUM ) = 1.

CONCATENATE '0' NUM INTO NUM.

ENDIF.

CONCATENATE 'L_PA0008-LGA' NUM INTO FLDNAM.

ASSIGN (FLDNAM) TO <FS>.

CONCATENATE 'L_PA0008-BET' NUM INTO FLDNAM.

ASSIGN (FLDNAM) TO <FS1>.

IF <FS> = '1000'.

V_AMOUNT = <FS1>.

ENDIF.

CLEAR : <FS>, <FS1>.

NUM = NUM + 1.

ENDDO.

I_W_DATA-ZZBASPAY = V_AMOUNT.

  • endif.

  • HRMS_BIW_IO_OCCUPANCY-ZZMSTBR = s_area.

*----


  • select single mstbr into l_mstbr from pa0001

  • where endda GE sy-datum

  • and pernr eq l_pernr.

*Bringing RM branch

SELECT SINGLE ICNUM INTO L_ICNUM_RM FROM PA0185

WHERE ENDDA GE SY-DATUM

AND PERNR EQ I_W_DATA-PERNR

AND SUBTY = '05'.

  • new code for fields by pandu

*bringing date-of-birth

SELECT SINGLE GBDAT INTO L_GBDAT FROM PA0002 WHERE PERNR = I_W_DATA-PERNR.

  • bringing fathers name

SELECT SINGLE FAVOR INTO L_FAVOR FROM PA0021 WHERE PERNR = I_W_DATA-PERNR

AND SUBTY = '2'.

  • bringing address details

SELECT SINGLE * FROM PA0006 WHERE PERNR = I_W_DATA-PERNR

AND SUBTY = '2'

AND ENDDA GE SY-DATUM.

L_NAME2 = PA0006-NAME2.

L_STRAS = PA0006-STRAS.

L_ORT01 = PA0006-ORT01.

L_PSTLZ = PA0006-PSTLZ.

  • bringing communication dteails

SELECT SINGLE USRID INTO L_USRID FROM PA0105 WHERE PERNR = I_W_DATA-PERNR

AND SUBTY = '0010'

AND ENDDA GE SY-DATUM.

  • bringing location details

SELECT SINGLE XMETXT INTO L_XMETXT FROM PA9005 WHERE PERNR = I_W_DATA-PERNR

AND ENDDA GE SY-DATUM.

  • bringing bank details

SELECT SINGLE * FROM PA0009 WHERE PERNR = I_W_DATA-PERNR

AND ENDDA GE SY-DATUM.

L_BANKN = PA0009-BANKN.

L_BANKL = PA0009-BANKL.

  • bringing the bankname

SELECT SINGLE BANKA INTO L_BANKA FROM BNKA WHERE BANKL = L_BANKL.

  • bringing esop details

SELECT SINGLE CNAME INTO L_CNAME FROM PA9008 WHERE PERNR = I_W_DATA-PERNR

AND ENDDA GE SY-DATUM.

  • bringing vendor details

SELECT SINGLE LIFNR INTO L_LIFNR FROM LFB1 WHERE PERNR = I_W_DATA-PERNR.

SELECT SINGLE CTC_DATE INTO L_INCDAT FROM PB9007 WHERE REPORT = I_W_DATA-PERNR

AND ENDDA GE SY-DATUM.

*select single ctc_amt into l_ctc_amt from pb9007 where report = i_w_data-pernr

  • and endda ge sy-datum.

*l_incdat = pa9007-ctc_date.

*l_ctc_amt = pa9007-ctc_amt.

  • bringing resignation date

SELECT SINGLE BEGDA FROM PA0000 INTO L_RDATE WHERE PERNR = I_W_DATA-PERNR

AND ENDDA GE SY-DATUM

AND MASSN = '14'.

  • to get last working day

L_LWDAY = L_RDATE - 1.

  • to get reentry date

SELECT SINGLE BEGDA FROM PA0000 INTO L_REDAT WHERE PERNR = I_W_DATA-PERNR

AND ENDDA GE SY-DATUM

AND MASSN = '15'.

  • bringing workschedule details

SELECT SINGLE SCHKZ FROM PA0007 INTO L_SCHKZ WHERE PERNR = I_W_DATA-PERNR

AND ENDDA GE SY-DATUM.

  • bringing payroll status details

SELECT SINGLE * FROM PA0003 WHERE PERNR = I_W_DATA-PERNR

AND ENDDA GE SY-DATUM.

L_PRDAT = PA0003-PRDAT.

L_ABRDT = PA0003-ABRDT.

L_ABRSP = PA0003-ABRSP.

  • bringing paydate

SELECT SINGLE BEGDA INTO L_SMDAT FROM PA0008 WHERE PERNR = I_W_DATA-PERNR

AND ENDDA GE SY-DATUM.

*endadd

  • Getting wage type for the salaries calc

I_W_DATA-ZZRMID = L_ICNUM.

I_W_DATA-ZZICNUM = L_ICNUM_RM.

I_W_DATA-ZZRMBEGDA = L_RMBEGDA.

I_W_DATA-ZZRMENDDA = L_RMENDDA.

I_W_DATA-ZZEVPID = L_PERNR.

I_W_DATA-ZZEVPNAME = L_NAME.

I_W_DATA-ZZORGUTSVP = L_ICNUM_TIER2.

I_W_DATA-ZZMSTBR = L_MSTBR.

I_W_DATA-ZZICNUM_21 = L_ICNUM_21.

I_W_DATA-ZZICNUM_20 = L_ICNUM_20.

I_W_DATA-ZZICNUM_24 = L_ICNUM_24.

I_W_DATA-ZZICNUM_18 = L_ICNUM_18.

I_W_DATA-ZZESINO = L_ESINO.

I_W_DATA-ZZEEPF1 = L_EEBAS.

I_W_DATA-ZZISSPL = L_ISSPL.

  • code added by pandu

I_W_DATA-ZZGBDAT = L_GBDAT.

I_W_DATA-ZZFAVOR = L_FAVOR.

I_W_DATA-ZZNAME2 = L_NAME2.

I_W_DATA-ZZORT01 = L_ORT01.

I_W_DATA-ZZSTRAS = L_STRAS.

I_W_DATA-ZZPSTLZ = L_PSTLZ.

I_W_DATA-ZZUSRID = L_USRID.

I_W_DATA-ZZXMETXT = L_XMETXT.

I_W_DATA-ZZBANKL = L_BANKL.

I_W_DATA-ZZBANKN = L_BANKN.

I_W_DATA-ZZCNAME = L_CNAME.

I_W_DATA-ZZLIFNR = L_LIFNR.

I_W_DATA-ZZINCDAT = L_INCDAT.

  • i_w_data-zzctc_amt = l_ctc_amt.

I_W_DATA-ZZRDAT = L_RDATE.

I_W_DATA-ZZLWDAY = L_LWDAY.

I_W_DATA-ZZRDAT = L_REDAT.

I_W_DATA-ZZSCHKZ = L_SCHKZ.

I_W_DATA-ZZPRDAT = L_PRDAT.

I_W_DATA-ZZABRDT = L_ABRDT.

I_W_DATA-ZZABRSP = L_ABRSP.

I_W_DATA-ZZBANKA = L_BANKA.

I_W_DATA-ZZSMDAT = L_SMDAT.

*endchange

*Getting The details for EVP

CLEAR L_PERNR.

CLEAR L_NAME.

CLEAR L_ICNUM_TIER2.

CLEAR L_ICNUM_RM.

CLEAR L_RMBEGDA.

CLEAR L_RMENDDA.

CLEAR L_ESINO.

CLEAR L_EEBAS.

CLEAR L_ICNUM_21.

CLEAR L_ICNUM_20.

CLEAR L_ICNUM_24.

CLEAR L_ICNUM_18.

CLEAR L_MSTBR.

CLEAR L_ISSPL.

CLEAR L_PLANS.

*code added by pandu

CLEAR L_GBDAT.

CLEAR L_FAVOR.

CLEAR L_NAME2.

CLEAR L_ORT01.

CLEAR L_STRAS.

CLEAR L_PSTLZ.

CLEAR L_USRID.

CLEAR L_XMETXT.

CLEAR L_BANKN.

CLEAR L_BANKL.

CLEAR L_CNAME.

CLEAR L_LIFNR.

CLEAR L_RDATE.

CLEAR L_LWDAY.

CLEAR L_REDAT.

CLEAR L_PRDAT.

CLEAR L_SCHKZ.

CLEAR L_ABRDT.

CLEAR L_ABRSP.

CLEAR L_BANKA.

CLEAR L_SMDAT.

*endcode

SELECT SINGLE SOBID INTO L_PLANS2 FROM HRP1001

WHERE OTYPE EQ 'S'

AND OBJID EQ L_PLANS

AND RSIGN EQ 'A'

AND RELAT EQ '002'

AND ENDDA GE SY-DATUM

AND SCLAS EQ 'S'.

  • AND sobid EQ l_plans.

SELECT SINGLE PERNR INTO L_PERNR1 FROM PA0001

WHERE ENDDA GE SY-DATUM

AND PLANS EQ L_PLANS2.

SELECT SINGLE ENAME INTO L_NAME1 FROM PA0001

WHERE ENDDA GE SY-DATUM

AND PLANS EQ L_PLANS2.

I_W_DATA-ZZSRRMEVP = L_PERNR1.

I_W_DATA-ZZSRRMEVPNAME = L_NAME1.

***********************

*Monthly salary of Rm from PA0008

NUM = '01'.

IF I_W_DATA-ENDDA EQ '99991231'.

CLEAR V_AMOUNT.

SELECT SINGLE * INTO L_PA0008

FROM PA0008

WHERE PERNR EQ I_W_DATA-PERNR

AND ENDDA EQ '99991231'.

DO 30 TIMES.

IF STRLEN( NUM ) = 1.

CONCATENATE '0' NUM INTO NUM.

ENDIF.

CONCATENATE 'L_PA0008-BET' NUM INTO FLDNAM.

ASSIGN (FLDNAM) TO <FS>.

V_AMOUNT = V_AMOUNT + <FS>.

CLEAR <FS>.

NUM = NUM + 1.

ENDDO.

I_W_DATA-ZZMONSAL = V_AMOUNT.

ENDIF.

*************************************************

  • Getting salary monthly information

CLEAR : L_AMOUNT, L_AMOUNT_OFF,L_NAME1.

*CALL FUNCTION 'ZIBHR_EMP_INCENTIVE'

  • EXPORTING

  • S_PERNR = i_w_data-pernr

  • IMPORTING

  • INCENTIVE = l_amount

  • INCENTIVE_OFF = l_amount_off

  • .

*

  • i_w_data-zzmonsal_off = l_amount_off.

MODIFY I_T_DATA FROM I_W_DATA.

CLEAR L_ICNUM.

CLEAR L_PERNR.

ENDLOOP.

  • COst Center Enhancement for data source

  • Getting the employee count for a COST CENTER

WHEN '0COSTCENTER_ATTR'.

CLEAR : VL_COUNT.

LOOP AT I_T_DATA INTO I_W_DATA_KOSTL.

SELECT PERNR INTO TABLE IT_PERNR FROM PA0185

WHERE ICNUM EQ I_W_DATA_KOSTL-KOSTL

AND SUBTY = '05'

AND ENDDA EQ '99991231'.

DESCRIBE TABLE IT_PERNR LINES VL_COUNT.

MOVE : VL_COUNT TO I_W_DATA_KOSTL-ZZHEADCOUNT.

MODIFY I_T_DATA FROM I_W_DATA_KOSTL.

REFRESH : IT_PERNR.

ENDLOOP.

ENDCASE.

Former Member
0 Kudos

Next time please paste your code using the code parameter it will be easier to read

Answers (3)

Answers (3)

Former Member
0 Kudos

Hi Kolli,

I am facing a similar issue with my 0EMPLOYEE_ATTR extract (RSA3 times out also as taking too long). Did you manage to get your problem fixed - if so, what was the solution? Thanks in advance.

Former Member
0 Kudos

Is the time taking in the cmod? - check the job logs in R3 to check this or is it in the RFC scheduler

Have you traced the cmod code - are you going in on indexed fields?

Have you put extra indexes on the tables whereyou are doing non indexed selects

As with all BW implementations - we earn our money anticpating data volume problems before stuff goes into production

Former Member
0 Kudos

Hi,

it may be due to performance issue in data source 0EMPLOYEE_ATTR.

Check SAP Note Number: 1063469

Hope this helps you ..

Reagds,

shikha

Former Member
0 Kudos

Hi,

thank you very much for your reply . i gone through your suggested note what are the steps suggested in the note same thing in my system

BWMTK ACTIV 0employee_attr: Multitasking active

BWMTK GRNA2 0employee_attr: Name of Logon/Server Group

BWMTK GRNAM 0employee_attr: Name of Logon/Server Group

BWMTK MAXTK 1 0employee_attr: Maximum Number of Processes

BWMTK PKSIZ 1000 0employee_attr: Package Size

can you suggest me where i need to change the settings.

Regards

sivaraju

Regrds

Former Member
0 Kudos

i believe you are facing problems as you are using complex queries in R/3

the whole purpose of BIW is to reduce load on the OLTP(R/3).

What i would suggest you is do the calculations in BIW if possible by taking the required data for calclation in BIW ODS.

Then write routines in BIW.

Former Member
0 Kudos

HI,

its already in production almost 4 reports are using on this . if i go for redisgn they wont agree .do u have any other solution.

regards

sivaraju