load script performance

[Luben]

how do i improve load performance or rather make below script more efficient 

MAP_SALES_REP:

MAPPING LOAD DISTINCT

     TERRITORY_CODE ,

     RESPONSIBLE_NAME

FROM [lib://QS_Projects_BA/10_Standard_Data/ALL/10_Shared/10_QVD/20_DIM/D_TERRITORY.qvd]

(qvd);

Map_TERRITORY:

Mapping LOAD Distinct

    TERRITORY_CODE,

   TERRITORY

FROM [lib://QS_Projects_BA/10_Standard_Data/ALL/10_Shared/10_QVD/20_DIM/D_TERRITORY.qvd]

(qvd);

MAP_SALES_GROUP:

MAPPING LOAD DISTINCT

     TERRITORY_CODE ,

     TERR_SALES_GROUP

FROM [lib://QS_Projects_BA/10_Standard_Data/ALL/10_Shared/10_QVD/20_DIM/D_TERRITORY.qvd]

(qvd);

MAP_COMMODITY:

MAPPING LOAD DISTINCT

    JOB_REFERENCE,

    COMMODITY_CODE

FROM [lib://QS_Projects_BA/10_Standard_Data/ALL/10_Shared/10_QVD/20_DIM/D_BL_TO_COMMODITY.qvd]

(qvd);

Map_CARRIER:

Mapping LOAD Distinct

    CARRIER_ID as CARRIER_CODE,

    CARRIER

FROM [lib://QS_Projects_BA/10_Standard_Data/ALL/10_Shared/10_QVD/20_DIM/D_CARRIER.qvd]

(qvd);

tmp1_CCA_F_BOOKING_COMMERCIAL_LINE:

LOAD

    key_CSA,

    key_award,

    key_bp,

    key_shipper,

    key_consignee,

    key_notify,

    key_nac,

    key_contract_holder,

    key_bkg_party,

    key_contract,

    key_agency,

    key_trade,

    key_POL,

    key_POD,

    key_LEG_POL,

    key_LEG_POD,

    key_POO,

    key_ROO,

    key_FPD,

    key_ROD,

    key_origin,

    key_destination,

    key_leg_voyage,

    key_voyage,

    LEG_DATE_TYPE,

//     LEG_POL_COUNTRY,

//     LEG_POD_COUNTRY,

//     LONG_LEG_FLAG,

//     LEG_VOYAGE_BOUND_ISSUE_FLAG,

//     CSA_ID,

     Source,

    JOB_REFERENCE,

//     SNAPSHOT_FLAG,

//     SNAP_DATE,

    DateNum,

//     DateNum_DIS,

//    WTD,

//     SALES_ORG_CODE,

//     BOOKING_OFFICE,

//     TERRITORY_SALES_OFFICE,

//     BOOKING_STATUS,

//     MYPRICE_FLAG,

    SALES_GROUP_CODE,

//     SALES_OFFICE_CODE,

//     AGENCY_CODE,

//     BULLET,

     COMMODITY_GROUP,

     COMMODITY,

//     ORION_COMMODITY,

//     PAYMENT_METHOD_CODE,

//     PAYMENT_METHOD,

    SHIPPING_TYPE,

    EQUIPMENT_SIZE_TYPE,

    CARGO_TYPE_CODE,

    CARGO_TYPE,

//     SHIPMENT_CATEGORY,

     FULL_EMPTY_CODE,

     FULL_EMPTY,

//     CONTRIBUTION_RECON_FLAG,

//     PRICING_GROUP,

    POL_COUNTRY,

   POD_COUNTRY,

    ORIGIN_COUNTRY,

    ORIGIN_COUNTRY_CODE,

    DESTINATION_COUNTRY,

    DESTINATION_COUNTRY_CODE,

//     SALES_OFFICE_COUNTRY_CODE,

//     BOOKING_OFFICE_COUNTRY_CODE,

    //BOOKING_TYPE,

     ACTUAL_CV_TEU_LSTS,

     //ACTUAL_CV_TEU,

     ACTUAL_CV_LSTS,

     ACTUAL_CV_BOX,

     ACTUAL_CV_WEIGHT,

//     TARGET_CV_TEU,

    ACTUAL_CV_NET_CONTRIBUTION,

//     TARGET_CV_NET_CONTRIBUTION,

//     POTENTIAL_MARKET_TEU,

//     TARGET_MARKET_TEU,

//     SALES_ORGANIZATION,

    FINANCIAL_VOYAGE_SERVICE

     , POL.MAIN_BUSINESS_ZONE,

     POD.MAIN_BUSINESS_ZONE

//     CIF_FOB,

//     GA_SEGMENT

FROM [lib://QS_Projects_BA/10_Standard_Data/ALL/10_Shared/10_QVD/40_DTM/30_FACTS/DTM_COMMERCIAL_LINE_Facts.qvd]

(qvd);

RAW_CLGCV06:

LEFT Join (tmp1_CCA_F_BOOKING_COMMERCIAL_LINE)LOAD

                CLGCBNO AS JOB_REFERENCE ,

     CLGCCARR AS CARRIER_CODE,

                "0CRM_TR" AS TERRITORY_CODE

FROM

                [lib://QS_Projects_BA/10_Standard_Data/ALL/10_Shared/10_QVD/10_RAW/10_SAP/CLGCV06.qvd]

(qvd);

 tmp1.2_CCA_F_BOOKING_COMMERCIAL_LINE:

NoConcatenate LOAD

key_CSA,

    key_award,

    key_bp,

    key_shipper,

    key_consignee,

    key_notify,

    key_nac,

    key_contract_holder,

    key_bkg_party,

    key_contract,

    key_agency,

    key_trade,

    key_POL,

    key_POD,

    key_LEG_POL,

    key_LEG_POD,

    key_POO,

    key_ROO,

    key_FPD,

    key_ROD,

    key_origin,

    key_destination,

    key_leg_voyage,

    key_voyage,

    LEG_DATE_TYPE,

    JOB_REFERENCE,

    DateNum,

  NUM(YEAR(DateNum), '0000') & NUM(MONTH(DateNum), '00')AS MONTH,

   NUM(Year(DateNum), '0000') & NUM(Ceil(Month(DateNum)/3), '00') AS QUARTER,

  NUM(YEAR(DateNum), '0000') & NUM(WEEK(DateNum), '00') AS WEEK,

  NUM(YEAR(DateNum), '0000')  AS YEAR,

    SALES_GROUP_CODE,

     COMMODITY_GROUP,

     COMMODITY,

    SHIPPING_TYPE,

    EQUIPMENT_SIZE_TYPE,

    CARGO_TYPE_CODE,

    CARGO_TYPE,

     FULL_EMPTY_CODE,

     FULL_EMPTY,

    POL_COUNTRY,

    POD_COUNTRY,

    ORIGIN_COUNTRY,

    ORIGIN_COUNTRY_CODE,

    DESTINATION_COUNTRY,

    DESTINATION_COUNTRY_CODE,

     ACTUAL_CV_TEU_LSTS,

     ACTUAL_CV_LSTS,

     ACTUAL_CV_BOX,

     ACTUAL_CV_WEIGHT,

    ACTUAL_CV_NET_CONTRIBUTION,

    FINANCIAL_VOYAGE_SERVICE

     , POL.MAIN_BUSINESS_ZONE,

     POD.MAIN_BUSINESS_ZONE,

   ApplyMap('MAP_SALES_REP',TERRITORY_CODE , ' NOT FOUND') AS SALES_REP,

     ApplyMap('Map_TERRITORY',TERRITORY_CODE , ' NOT FOUND') AS SALES_TERRITORY,

      ApplyMap('Map_CARRIER',CARRIER_CODE, ' NOT FOUND') AS CARRIER_CODE,

      ApplyMap('MAP_SALES_GROUP',TERRITORY_CODE, ' NOT FOUND') AS SALES_GROUP,

       ApplyMap('MAP_COMMODITY',JOB_REFERENCE, ' NOT FOUND') AS COMMODITY_CODE

       , IF (((LEN(TRIM(ORIGIN_COUNTRY_CODE)) = 0 OR ORIGIN_COUNTRY_CODE='NULL' OR ORIGIN_COUNTRY_CODE='-') AND (POL_COUNTRY = 'UNITED STATES' OR POL_COUNTRY = 'CANADA')) OR ((ORIGIN_COUNTRY_CODE = 'US' OR ORIGIN_COUNTRY_CODE = 'CA') AND (POL_COUNTRY = 'UNITED STATES' OR POL_COUNTRY = 'CANADA')), 'EXPORT'

                , IF ((((LEN(TRIM(DESTINATION_COUNTRY_CODE)) = 0 OR DESTINATION_COUNTRY_CODE='NULL' OR DESTINATION_COUNTRY_CODE='-') AND (POD_COUNTRY = 'UNITED STATES' OR POD_COUNTRY = 'CANADA') ) OR ((DESTINATION_COUNTRY_CODE = 'US' OR DESTINATION_COUNTRY_CODE = 'CA') AND (POD_COUNTRY = 'UNITED STATES' OR POD_COUNTRY = 'CANADA')) AND (POL_COUNTRY <> 'UNITED STATES' AND POL_COUNTRY <> 'CANADA')), 'IMPORT')) AS DIRECTION 

 Resident tmp1_CCA_F_BOOKING_COMMERCIAL_LINE;

  Drop Table tmp1_CCA_F_BOOKING_COMMERCIAL_LINE;

[COMMODITY_GROUPINGS]:

left Join (tmp1.2_CCA_F_BOOKING_COMMERCIAL_LINE)LOAD

    TRIM(COMMODITY_CD) AS COMMODITY_CODE,

    EXP_KEY_COMMODITY_TXT,

    EXP_SUB_COMMODITY_TXT,

    EXP_KEY_REEFER_TXT,

    IMP_KEY_COMMODITY_TXT,

    IMP_SUB_COMMODITY_TXT,

    IMP_KEY_REEFER_TXT

FROM [lib://QS_Projects_BA/10_Standard_Data/ALL/10_Shared/10_QVD/COMMODITY_GROUPS_TBL.qvd]

(qvd);

tmp2_CCA_F_BOOKING_COMMERCIAL_LINE:

NoConcatenate LOAD

*

, IF (DIRECTION = 'IMPORT', IF(CARGO_TYPE_CODE = 'RFR', 'REEFER', IF (CARGO_TYPE_CODE = 'OOG', 'OOG', IMP_KEY_COMMODITY_TXT))

               , IF (DIRECTION = 'EXPORT', IF(CARGO_TYPE_CODE = 'RFR', 'REEFER', IF (CARGO_TYPE_CODE = 'OOG', 'OOG', EXP_KEY_COMMODITY_TXT)), NULL())) AS KEY_COMMODITY

                , IF (DIRECTION = 'IMPORT', IF(CARGO_TYPE_CODE = 'RFR', IMP_KEY_REEFER_TXT, IMP_SUB_COMMODITY_TXT)

               , IF (DIRECTION = 'EXPORT', IF(CARGO_TYPE_CODE = 'RFR', EXP_KEY_REEFER_TXT, EXP_SUB_COMMODITY_TXT), NULL())) AS SUB_KEY_COMMODITY

Resident tmp1.2_CCA_F_BOOKING_COMMERCIAL_LINE

WHERE

                MATCH(DIRECTION, 'IMPORT', 'EXPORT')

                AND

                LEG_DATE_TYPE = 'Week at LAP';

Store tmp2_CCA_F_BOOKING_COMMERCIAL_LINE INTO [lib://QS_Projects_BA/10_Standard_Data/ALL/10_Shared/10_QVD/tmp2_CCA_F_BOOKING_COMMERCIAL_LINE.qvd]

(qvd);

Drop table tmp1.2_CCA_F_BOOKING_COMMERCIAL_LINE;

Drop table tmp2_CCA_F_BOOKING_COMMERCIAL_LINE; 

[ArnadoSandoval]

Hi @Luben 

Do you have the time this taking to complete? you may attach its log file, I am analyzing the script but it will be better to know its bottle neck sections, in order to target the sections deserving improvements.

hth

[Luben]

hello this is taking about 6 to 7 mins load time to complete 

thanks

[ArnadoSandoval]

Hi @Luben 

The diagram below is a top-level view of your Load Script, I will reference this diagram with my suggestions.

NOTE: It will be good that you find and upload a recent log file generated by this load script, it will be extremely helpful

hth

[ArnadoSandoval · Accepted solution]

@Luben 

These are some random improvement notes:

• You are performing several transformations applied to the original QVD (DTM_COMMERCIAL_LINE_Facts.qvd) that should be done as part of its generation, I am talking about these transformations:
• If your fact table is small the following enhancements might not show any improvements
• When building the table: tmp1.2_CCA_F_BOOKING_COMMERCIAL_LINE

NUM(YEAR(DateNum), '0000') & NUM(MONTH(DateNum), '00')         AS MONTH,
NUM(Year(DateNum), '0000') & NUM(Ceil(Month(DateNum)/3), '00') AS QUARTER,
NUM(YEAR(DateNum), '0000') & NUM(WEEK(DateNum), '00')          AS WEEK,
NUM(YEAR(DateNum), '0000')                                     AS YEAR,

The functions NUM, YEAR, MONTH, WEEK and Ceil applied over thousands of records may have a significant impact in the script performance (running time).

The fields: MONTH, QUARTER, WEEK and YEAR are likely to be consume across many dashboards and applications, so creating them once will simplify the overall applications' QVDs.

ApplyMap('MAP_SALES_REP',TERRITORY_CODE , ' NOT FOUND')  AS SALES_REP,
ApplyMap('Map_TERRITORY',TERRITORY_CODE , ' NOT FOUND')  AS SALES_TERRITORY,
ApplyMap('Map_CARRIER',CARRIER_CODE, ' NOT FOUND')       AS CARRIER_CODE,
ApplyMap('MAP_SALES_GROUP',TERRITORY_CODE, ' NOT FOUND') AS SALES_GROUP,
ApplyMap('MAP_COMMODITY',JOB_REFERENCE, ' NOT FOUND')    AS COMMODITY_CODE

The same concern with these ApplyMap functions, nothing prevent you to do them here, but, it will be better to perform them while creating the QVD: DTM_COMMERCIAL_LINE_Facts.qvd

The DIRECTION field, it is a heavy combination of IF/LEN/TRIM/string comparisons, specially if you are dealing with a large fact table; Also, DIRECTION should be an integer value, like 0 and 1 instead of 'IMPORT' and 'EXPORT', e.g. ( 0 = Import, 1 = Export ) Qlik as many other language are more efficient comparing/handling integers instead of strings!

The DIRECTION field (as numeric) should be part of the Facts qvd; the UI interface could easily map 0 and 1 to text when required.

These condition's expressions applied over thousands of records take time:

LEN(TRIM(ORIGIN_COUNTRY_CODE)) = 0 OR ORIGIN_COUNTRY_CODE='NULL' OR ORIGIN_COUNTRY_CODE='-'
LEN(TRIM(DESTINATION_COUNTRY_CODE)) = 0 OR DESTINATION_COUNTRY_CODE='NULL' OR DESTINATION_COUNTRY_CODE='-'
POL_COUNTRY = 'UNITED STATES' OR POL_COUNTRY = 'CANADA'
POD_COUNTRY = 'UNITED STATES' OR POD_COUNTRY = 'CANADA'
ORIGIN_COUNTRY_CODE = 'US' OR ORIGIN_COUNTRY_CODE = 'CA'
DESTINATION_COUNTRY_CODE = 'US' OR DESTINATION_COUNTRY_CODE = 'CA'

The expressions checking if the fields ORIGIN_COUNTRY_CODE and DESTINATION_COUNTRY_CODE are not nulls should be part of the Fact table, I will introduce two fields there: Valid_Origin_Country and Valid_Destination_Country with 1/0 values for Yes/No

Your fact table records four different interpretation of COUNTRIES, with the prefixes; POL_, POD_, ORIGIN_ and DESTINATION_ and knowing if they identify North America (UNITED STATES/CANADA, or US/CA) justify the introduction of four FLAGs in your fact table with these names: POL_NorthAmerica, POD_NorthAmerica, ORIGIN_NorthAmerica and DESTINATION_NorthAmerica, each one of them containing 1/0 values for Yes/No; these fields should be part of the FACT table.

The KEY_COMMODITY and SUB_KEY_COMMODITY fields rely in heavy IF statements, perhaps they could be replaced with a ApplyMap; but I withhold further comments on these two fields, pending your reply and implementation of these changes:

SUMMARY:

• The source FACT table: DTM_COMMERCIAL_LINE_Facts.qvd should contain several of the fields this load script is creating.
• The ApplyMap should take place at the time of creating the fact table.
• The DIRECTION fields should be part of the fact table.
• The resolution of the _NorthAmerica transactions should take place when building the Fact table, and appear as columns in it.
• Use flag fields, e.g. 0/1 instead of text-strings
• I did not comment on the LEFT joins, I am waiting for your reply to resume my suggestions by including the LEFT joins.

Please, confirm if you will implement these enhancements, and if you do, share with us the time the script is taking.

hth

[Luben]

can I send you an email.

thanks

[Luben]

for both direction The KEY_COMMODITY and SUB_KEY_COMMODITY fields are logic that was given by business.  

what would be the best way to write the If statements. 

Also the DTM_COMMERCIAL_LINE_Facts.qvd  was already created i am just adding those fields to them with the apply map function. 

[ArnadoSandoval · Accepted solution]

Hi @Luben 

I don't have any issue with the KEY_COMMODITY and SUB_KEY_COMMODITY fields, perhaps my comments were regarding improving the script performance.

Ideally, the changes that I suggested were to be applied to the Load Script creating the initial FACTs qvd, my understanding is that you are not allowed to touch that load script. Which is not a problem.

Also, i thought that the script you posted was part of a new solution or dashboard, and you were adding columns expected by that solution/dashboard.

Now, this diagram shows a proposed idea to implement the suggestion above and enhancing the FACTs.qvd.

Now, I am attaching a QVF file with these enhancements implemented in the load script; 

[ArnadoSandoval · Accepted solution]

@Luben 

Note, you may create a new application (QVF) with the script in the second tab; you can measure the time it takes to run, also, with this Fact Enhanced QVD you may update your original script, but I am not sure now of the scope of the original script.

hth