Forum Stats

  • 3,873,457 Users
  • 2,266,576 Discussions
  • 7,911,542 Comments

Discussions

FDMEE Data load taking too long

User_W7KT0
User_W7KT0 Member Posts: 168 Blue Ribbon
edited Apr 23, 2018 3:52AM in Financial Data Management

Hi All,

We are loading 2 months (Jan and Feb) of data from Teradata .

No of rows: 387939

No of rows for Jan: 189295

No of rows for Feb: 198644

It suppose to be completed by 10 mins . But,dataload is taking 5+ Hrs.

If we restart the service and trigger again it takes 10 mins,but next run takes 5+ hrs again.

Attached log helps to identify which one is taking more time.

Usual Run:

Processing Mappings for Column 'UD1'

2018-03-05 11:08:15,747 INFO  [AIF]: Data Rows Updated by Location Mapping '99_All_Maps' (LIKE): 189242

2018-03-05 11:08:15,749 INFO  [AIF]:

Processing Mappings for Column 'UD10'

2018-03-05 11:08:15,854 INFO  [AIF]: Data Rows Updated by Location Mapping EXPLICIT: 0

2018-03-05 11:08:15,854 INFO  [AIF]:

Processing Mappings for Column 'UD3'

2018-03-05 11:08:15,941 INFO  [AIF]: Data Rows Updated by Location Mapping EXPLICIT: 0

2018-03-05 11:08:15,941 INFO  [AIF]:

Long run:

Processing Mappings for Column 'UD1'

2018-03-06 19:20:02,136 INFO  [AIF]: Data Rows Updated by Location Mapping '99_All_Maps' (LIKE): 189284

2018-03-06 19:20:02,138 INFO  [AIF]:

Processing Mappings for Column 'UD10'

2018-03-06 22:20:22,516 INFO  [AIF]: Data Rows Updated by Location Mapping EXPLICIT: 0

2018-03-06 22:20:22,735 INFO  [AIF]:

Processing Mappings for Column 'UD3'

2018-03-06 22:20:22,838 INFO  [AIF]: Data Rows Updated by Location Mapping EXPLICIT: 0

2018-03-06 22:20:22,838 INFO  [AIF]:

Please help to fix the issue.

Best Answer

  • User_W7KT0
    User_W7KT0 Member Posts: 168 Blue Ribbon
    edited Apr 23, 2018 3:52AM Answer ✓

    I would like to post the solution here.

    Created index but didnot help.We taken dbstats and could see TDATAMAP_T was 0. we locked the table with maximum counts and now its running with in 10 mins.

    Thanks all for your suggestion.

Answers

  • JohnGoodwin
    JohnGoodwin Member Posts: 30,471 Blue Diamond
    edited Mar 7, 2018 3:48AM

    If you turn up the FDMEE log level to 5 you should see the query that is being run against the FDMEE database, it will be an UPDATE statement against the TDATASEG_T

    It might be worth getting your DBA involved, you can provide the DBA with the SQL query and get them  to monitor to try and understand why the query is taking so long.

    Also refer to the following document - http://www.oracle.com/technetwork/middleware/bi-foundation/fdmee-tuning-1112x-2349440.pdf

    Cheers

    John

    User_W7KT0
  • User_W7KT0
    User_W7KT0 Member Posts: 168 Blue Ribbon
    edited Mar 7, 2018 7:44AM

    Thanks John!!! one of the update query taking too much time.

    below is the update query

    UPDATE TDATASEG_T SET (UD10X , UD10R, AMOUNTX, CHANGESIGN, VALID_FLAG) =

    ( SELECT tdm.TARGKEY , tdm.DATAKEY , AMOUNTX * CASE tdm.CHANGESIGN WHEN 1 THEN -1 ELSE 1 END , CASE tdm.CHANGESIGN WHEN 1 THEN CASE TDATASEG_T.CHANGESIGN WHEN 1

    THEN 0 WHEN 0 THEN 1 ELSE TDATASEG_T.CHANGESIGN END ELSE TDATASEG_T.CHANGESIGN END , CASE tdm.TARGKEY WHEN 'IGNORE'

    THEN 'I' ELSE TDATASEG_T.VALID_FLAG END FROM TDATAMAP_T tdm WHERE tdm.LOADID = TDATASEG_T.LOADID AND

    tdm.PARTITIONKEY = TDATASEG_T.PARTITIONKEY AND tdm.DIMNAME = :1 AND tdm.WHERECLAUSETYPE IS NULL AND

    tdm.SRCKEY = TDATASEG_T.UD10 AND tdm.RULE_ID IS NULL AND tdm.TDATAMAPTYPE <> 'MULTIDIM' ) , UD10F = :2 WHERE LOADID = :3 AND PARTITIONKEY = :4 AND

    CATKEY = :5 AND (UD10X IS NULL OR UD10X = '') AND EXISTS ( SELECT 1 FROM TDATAMAP_T tdm WHERE tdm.LOADID = TDATASEG_T.LOADID AND

    tdm.PARTITIONKEY = TDATASEG_T.PARTITIONKEY AND tdm.DIMNAME = :6 AND tdm.WHERECLAUSETYPE

    IS NULL AND tdm.SRCKEY = TDATASEG_T.UD10 AND tdm.RULE_ID IS NULL AND tdm.TD ATAMAPTYPE <> 'MULTIDIM' ) AND PERIODKEY = :7

  • User_W7KT0
    User_W7KT0 Member Posts: 168 Blue Ribbon
    edited Apr 23, 2018 3:52AM Answer ✓

    I would like to post the solution here.

    Created index but didnot help.We taken dbstats and could see TDATAMAP_T was 0. we locked the table with maximum counts and now its running with in 10 mins.

    Thanks all for your suggestion.

This discussion has been closed.