Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    laod is taking more than 17 hours
Go
New
Search
Notify
Tools
Reply
  
laod is taking more than 17 hours
 Login/Join
 
Member
posted
Loading 4,00,000 records take more than 16 hours into the fact table.can anyone help me out to tune this to run more faster.

I have also pasted the text view of the data flow.
-*DM_JOB_TYPE=1
-*DM_USERID=VRajamuthaiah

-***************************************************

-:START_PRC
SET PANEL=9999
SET MORE=OFF
SET 2PARTNAME=ON
-RUN

-*[Variables to Control Request]
-SET &&CM__TARGET = 'f_customer';
-SET &&CM__AUTHOR = 'VRajamuthaiah';
-SET &&CM__REQUEST = '&FOCFEXNAME.EVAL';
-SET &&CM__RETURN = 0;
-SET &&CM__FOCCPU = &FOCCPU.EVAL;
-SET &&KILL_RPC = 'N';
-DEFAULT &DBMSERROR = 10000000
-DEFAULT &STARTAT = 0
-DEFAULT &STOPAT = 1000000000

-TYPE (ICM18122) Request - &FOCFEXNAME (Owner: VRajamuthaiah) submitted.
-GOTO Big GrinEP_0;

-Big GrinEP_MAIN
-TYPE (ICM18742) f_wtc_customer type MS SQL Server Existing target
-*[SCD] - Slow Changing Dimensions load type

SET CASESTAT=EXTENDED
SQL SET UPCASE=OFF; END

LOAD MASTER stg_facttable_customer_cube
EX -lines 5 EDAPUT MASTER,stg_facttable_customer_cube,A,MEM,
DEFINE In_Product_Row_ID/I11=DB_LOOKUP(d_product, PRODUCTKEY, PRODUCT_KEY, PRODUCT_ROW_ID); $
DEFINE In_Timeperspective_Row_ID/I11=DB_LOOKUP(lkp_time_perspective, TIME_PERSPECTIVEKEY, TIME_PERSPECTIVE_KEY, TIME_PERSPECTIVE_ROW_ID); $
DEFINE In_Month_Key/I6=DB_LOOKUP(d_month, MONTHKEY, FY_MONTH_ID, MONTH_KEY); $
DEFINE In_Versions_Row_ID/I11=DB_LOOKUP(d_versions, VERSIONSKEY, VERSIONS_KEY, VERSIONS_ROW_ID); $

-RUN

-SET &&CM__RETURN = &FOCERRNUM;
-IF (&&CM__RETURN NE 0) GOTO :ENDJOB;
-TYPE (ICM18429) Issuing PREPARE

SQL PREPARE SQLIN FROM
SELECT
CASE
WHEN T3.CUSTOMER_KEY IS NULL THEN -1
ELSE T3.CUSTOMER_ROW_ID END AS CUSTOMER_ROW_ID ,
CASE
WHEN T5.ACCOUNTS_KEY IS NULL THEN -1
ELSE T5.ACCOUNTS_ROW_ID END AS ACCOUNTS_ROW_ID ,
T6.UPDATEDETAILS ,
SUM(T6."VALUE" ) AS "Value" ,
'Stg_FactTableWTC_Customer' AS In_SRC_Name ,
'df_' || '&&CM__TARGET' AS In_DW_Created_By ,
CURRENT_TIMESTAMP(3 ) AS In_DW_Created_Date ,
CASE
WHEN T6.In_Product_Row_ID = 0 THEN -1
ELSE T6.In_Product_Row_ID END AS Product_Row_Id ,
CASE
WHEN T6.In_Timeperspective_Row_ID = 0 THEN -1
ELSE T6.In_Timeperspective_Row_ID END AS Timeperspective_Row_Id ,
CASE
WHEN T6.In_Month_Key = 0 THEN -1
ELSE T6.In_Month_Key END AS Month_Key ,
CASE
WHEN T8.VERSIONS_KEY IS NULL THEN -1
ELSE T8.VERSIONS_ROW_ID END AS VERSIONS_ROW_ID
FROM
((SELECT
T7.VERSIONS_ROW_ID ,
UCASE(T7.VERSIONS_KEY ) AS VERSIONS_KEY
FROM
d_versions T7
WHERE
T7.SOURCE_VERSION_NAME = 'Versions'
) T8
RIGHT OUTER JOIN
((SELECT
T4.ACCOUNTS_ROW_ID ,
UCASE(T4.ACCOUNTS_KEY ) AS ACCOUNTS_KEY
FROM
d_accounts T4
WHERE
T4.SOURCE_ACCOUNT_NAME = 'Customer_GL_Accounts'
) T5
RIGHT OUTER JOIN
((SELECT
T2.CUSTOMER_ROW_ID ,
UCASE(T2.CUSTOMER_KEY ) AS CUSTOMER_KEY
FROM
d_customer T2
WHERE
T2.DW_UPDATED_DATE IS NULL
) T3
RIGHT OUTER JOIN
(SELECT
T1.STG_FACTTABLEWTC_CUSTOMER_CUBE_KEY ,
UCASE(T1.UPDATEDETAILS ) AS UPDATEDETAILS ,
T1.TIME_PERSPECTIVEKEY ,
UCASE(T1.VERSIONSKEY ) AS VERSIONSKEY ,
T1.MONTHKEY ,
UCASE(T1.CUSTOMER_ACCOUNTSKEY ) AS CUSTOMER_ACCOUNTSKEY ,
T1.PRODUCTKEY ,
UCASE(T1.CUSTOMERKEY ) AS CUSTOMERKEY ,
T1."VALUE" ,
T1.CREATED_DATETIME ,
T1.CREATED_BY ,
T1.MODIFIED_DATETIME ,
T1.MODIFIED_BY ,
T1.In_Product_Row_ID ,
T1.In_Timeperspective_Row_ID ,
T1.In_Month_Key ,
T1.In_Versions_Row_ID
FROM
stg_facttablewtc_customer_cube T1
) T6
ON
T3.CUSTOMER_KEY = T6.CUSTOMERKEY )
ON
T5.ACCOUNTS_KEY = T6.CUSTOMER_GL_ACCOUNTSKEY )
ON
T8.VERSIONS_KEY = T6.VERSIONSKEY )
GROUP BY
CASE
WHEN T3.CUSTOMER_KEY IS NULL THEN -1
ELSE T3.CUSTOMER_ROW_ID END ,
CASE
WHEN T5.ACCOUNTS_KEY IS NULL THEN -1
ELSE T5.ACCOUNTS_ROW_ID END ,
T6.UPDATEDETAILS ,
CASE
WHEN T6.In_Product_Row_ID = 0 THEN -1
ELSE T6.In_Product_Row_ID END ,
CASE
WHEN T6.In_Timeperspective_Row_ID = 0 THEN -1
ELSE T6.In_Timeperspective_Row_ID END ,
CASE
WHEN T6.In_Month_Key = 0 THEN -1
ELSE T6.In_Month_Key END ,
CASE
WHEN T8.VERSIONS_KEY IS NULL THEN -1
ELSE T8.VERSIONS_ROW_ID END
END
-RUN
-SET &&CM__RETURN = IF &FOCERRNUM EQ 14104 THEN 0 ELSE &FOCERRNUM;
-IF (&&CM__RETURN NE 0) GOTO :ENDJOB;

-IF (&SQLAPT EQ 'APT') GOTO :SKIPHOLD;

-TYPE (ICM18440) Request will process data via NON-Pass Through (NON-APT)
-TYPE (ICM18451) HOLD file will be created for output file named: SQLIN.

SQL EXECUTE SQLIN;
TABLE ON TABLE HOLD AS
SQLIN
FORMAT DATREC
IF RECORDLIMIT EQ &STOPAT
END
-RUN
-SET &&CM__RETURN = &FOCERRNUM;
-IF (&&CM__RETURN NE 0) GOTO :ENDJOB;

-:SKIPHOLD

-TYPE (ICM18743) Starting Load

SET MSG=ON

-*SCD: &&CM__SCDATE is begin date (today)
-*SCD: &&CM__SCDEADT is end date for active records (future)
-*SCD: &&CM__SCDEIDT is end date for inactive records (yesterday)
-SET &TMP1 = HGETC(8, 'HYYMDs');
-SET &CURDT = FPRINT(&TMP1, 'HYYMDs', 'A23');
-DEFAULT &&CM__SCDATE = &CURDT.EVAL
-DEFAULT &&CM__SCDEADT = MISSING
-DEFAULT &&CM__SCDEIDT = &CURDT.EVAL

MODIFY FILE f_wtc_customer
FIXFORM FROM SQLIN ALIAS PROPAGATE
GOTO MATCHIT1

CASE MATCHIT1
COMPUTE
CM__SCDATE1/HYYMDs MISSING ON=HINPUT(&&CM__SCDATE.LENGTH, '&&CM__SCDATE', 8, 'HYYMDs');
CM__SCDEADT1/HYYMDs MISSING ON=&&CM__SCDEADT;
CM__SCDEIDT1/HYYMDs MISSING ON=HINPUT(&&CM__SCDEIDT.LENGTH, '&&CM__SCDEIDT', 8, 'HYYMDs');
CUSTOMER_ROW_ID/I11=E01;
PRODUCT_ROW_ID/I11=E08;
VERSIONS_ROW_ID/I11=E11;
ACCOUNTS_ROW_ID/I11=E02;
FY_MONTH_ID/I11=E10;
TIME_PERSPECTIVE_ROW_ID/I11=E09;
VALUE/D20.2 MISSING ON=E04;
SRC_UPDATE_DETAILS/A250V MISSING ON=E03;
SRC_NAME/A250V MISSING ON=E05;
DW_CREATED_BY/A100V MISSING ON=E06;
DW_CREATED_DATE/HYYMDs MISSING ON=E07;
DW_UPDATED_BY/A100V MISSING ON=E06;
DW_UPDATED_DATE/HYYMDs MISSING ON=E07;
MATCH CUSTOMER_ROW_ID PRODUCT_ROW_ID VERSIONS_ROW_ID ACCOUNTS_ROW_ID FY_MONTH_ID TIME_PERSPECTIVE_ROW_ID SRC_UPDATE_DETAILS
-* New record - logical key doesn't exist
ON NOMATCH PERFORM NUM_DATES:
ON NOMATCH INCLUDE
ON NOMATCH GOTO TOP
-* Update all records for a logical key, and call case to process type I change
ON MATCH IF D.VALUE EQ VALUE GOTO CALLMORE1:;
-* Compute type I fields before update
ON MATCH COMPUTE VALUE=VALUE;
ON MATCH UPDATE VALUE REC_ACTIVE_FLAG
ON MATCH GOTO CALLMORE1:
ENDCASE
-* Call case to update records for a logical key
CASE CALLMORE1:
-* Update all records for a logical key, and call case to process type I change
NEXT CUSTOMER_ROW_ID PRODUCT_ROW_ID VERSIONS_ROW_ID ACCOUNTS_ROW_ID FY_MONTH_ID TIME_PERSPECTIVE_ROW_ID SRC_UPDATE_DETAILS
ON NONEXT GOTO TOP
ON NEXT IF D.VALUE EQ VALUE GOTO CALLMORE1:;
-* Compute type I fields before update
ON NEXT COMPUTE VALUE=VALUE;
ON NEXT UPDATE VALUE REC_ACTIVE_FLAG
ON NEXT GOTO CALLMORE1:
ENDCASE

-* Set new key, Start and End dates for active rows
CASE NUM_DATES:
COMPUTE
DW_CREATED_DATE=CM__SCDATE1;
DW_UPDATED_DATE=IF CM__SCDEADT1 EQ MISSING THEN MISSING ELSE CM__SCDEADT1;
ENDCASE

CASE AT START
START &STARTAT
STOP &STOPAT
STOP DBMSERRORS &DBMSERROR

LOG DBMSERR MSG OFF
LOG DUPL MSG OFF
LOG INVALID MSG OFF
LOG NOMATCH MSG OFF
LOG FORMAT MSG OFF
LOG ACCEPT MSG OFF
LOG TRANS MSG OFF
CHECK 100000
ENDCASE
DATA VIA SQLGET
END
-RUN

-TYPE (ICM18744) Ending Load
-SET &&CM__RETURN = &FOCERRNUM;
-SET &&CM__RETURN = IF (&&CM__RETURN EQ 1416) AND (&DBMSERR LT &DBMSERROR)
- THEN 0 ELSE &&CM__RETURN;

-:ENDJOB
-TYPE (ICM18040) Return Code = &&CM__RETURN

SET CASESTAT=OFF
SQL SET UPCASE=ON; END
SET EMGSRV=OFF
SQL PURGE SQLIN;
END
FI SQLIN CLEAR

SET EMGSRV=ON
SQL SQLMSS
COMMIT WORK;
END
-RUN

-TYPE (ICM18076) Request: &FOCFEXNAME - finished processing

-SET &&CM__FOCCPU = &FOCCPU.EVAL - &&CM__FOCCPU;
-TYPE (ICM18007) CPU Time : &&CM__FOCCPU

-*[Main Condition]
-IF (&&CM__RETURN EQ 0) GOTO Big GrinEP_2;
-GOTO :ENDDEP
-*[Main End]

-*[Dependence]
-Big GrinEP_0
-TYPE (ICM18015) DEP_0: procedure p_cache started.
EX wacom/dwh/p_cache
-RUN
-IF (&&KILL_RPC EQ 'Y') GOTO :STOPRPCS;
-SET &RET_CODE = IF &&CM__RETURN EQ 0 THEN &FOCERRNUM ELSE &&CM__RETURN;
-TYPE (ICM18039) DEP_0 p_cache Return Code = &RET_CODE
-IF (&FOCERRNUM EQ 0) GOTO Big GrinEP_1;
-GOTO :ENDDEP

-Big GrinEP_1
-TYPE (ICM18015) DEP_1: procedure p_dwh_cntrl_tbl_status started.
EX wacom/dwh/p_dwh_cntrl_tbl_status
-RUN
-IF (&&KILL_RPC EQ 'Y') GOTO :STOPRPCS;
-SET &RET_CODE = IF &&CM__RETURN EQ 0 THEN &FOCERRNUM ELSE &&CM__RETURN;
-TYPE (ICM18039) DEP_1 p_dwh_cntrl_tbl_status Return Code = &RET_CODE
-IF &&STATUS EQ 'N' GOTO Big GrinEP_MAIN;
-GOTO :ENDDEP

-Big GrinEP_2
-TYPE (ICM18015) DEP_2: procedure p_dwh_cntrl_tbl_update started.
EX wacom/dwh/p_dwh_cntrl_tbl_update
-RUN
-IF (&&KILL_RPC EQ 'Y') GOTO :STOPRPCS;
-SET &RET_CODE = IF &&CM__RETURN EQ 0 THEN &FOCERRNUM ELSE &&CM__RETURN;
-TYPE (ICM18039) DEP_2 p_dwh_cntrl_tbl_update Return Code = &RET_CODE

-:ENDDEP
-EXIT

-:STOPRPCS
-TYPE (ICM18094) User termination via KILL_RPC flag

END
-RUN
SET PANEL=0
SET MORE=ON
SET 2PARTNAME=OFF
-RUN


WebFOCUS 7.6
Windows, All Outputs

 
Posts: 7 | Registered: August 22, 2013Reply With QuoteReport This Post
Guru
posted Hide Post
You say you are loading a 4 million row fact table. But it looks like you are using a load type of Slowly Changing Dimensions which as the name implies is designed for loading dimension tables. What are you trying to accomplish? Why not use load type Insert record from memory?

If that doesn't help for any further analysis we'd need to see the source and target synonyms. In that case I would suggest opening a hottrack case and providing them.
 
Posts: 380 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    laod is taking more than 17 hours

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.