Help with load script optimizing

[jalanhart]

Here is my Load script that I'm trying to get to work, but it gets to about a minute and a half and stalls for a minute and then says "operation aborted". I'm assuming it's crashing as it's trying to make more synthetic keys. 

LIB CONNECT TO [SQL CONNECTION];

[invoice_statistics]:

LOAD

[STATISTIC_NO],

[INVOICE_DATE],

    year([INVOICE_DATE]) as [STAT_YEAR_NO],

    month([INVOICE_DATE]) as [STAT_PERIOD_NO],

[INVOICED_QTY],

[COST],

[BASE_SALE_UNIT_PRICE],

[NET_AMOUNT],

[GROSS_AMOUNT],

[CATALOG_DESC],

[CUSTOMER_NAME],

[COUNTRY_DESC],

[CUST_GRP_DESC],

[MARKET_DESC],

[REGION_DESC],

[DISTRICT_DESC],

[SALESMAN],

[ORDER_NO],

[CATALOG_GROUP_DESC],

[CUSTOMER_NO],

[CUST_GRP],

[CONTRACT],

[CATALOG_NO],

[PART_NO],

[SALESMAN_CODE],

[SALES_UNIT_MEAS],

[PRICE_UNIT_MEAS],

[CATALOG_GROUP],

[PART_DESC],

[CUSTOMER_PRICE_GRP_DESC],

[INVOICE_NO],

[SERIES_ID],

[parent_cust_id],

[Convt_UOM],

[Convt_QTY],

[convt_price],

[parent_cust_name],

[CITY],

    [CATALOG_NO] & ' - ' & [CATALOG_DESC] as [SALES_PART],

APPLYMAP( '__countryCodeIsoThree2Polygon', APPLYMAP( '__countryName2IsoThree', LOWER([COUNTRY_DESC])), '-') AS [invoice_statistics.COUNTRY_DESC_GeoInfo],

APPLYMAP( '__countryCodeIsoTwo2Polygon', UPPER([SERIES_ID]), '-') AS [invoice_statistics.SERIES_ID_GeoInfo];

SELECT "STATISTIC_NO",

     "INVOICE_DATE",

    "INVOICE_ID",

"INVOICED_QTY",

COST,

"BASE_SALE_UNIT_PRICE",

"NET_AMOUNT",

"GROSS_AMOUNT",

"CATALOG_DESC",

"CUSTOMER_NAME",

"COUNTRY_DESC",

"CUST_GRP_DESC",

"MARKET_DESC",

"REGION_DESC",

"DISTRICT_DESC",

SALESMAN,

"ORDER_NO",

"CATALOG_GROUP_DESC",

"CUSTOMER_NO",

"CUST_GRP",

CONTRACT,

"CATALOG_NO",

"PART_NO",

"SALESMAN_CODE",

"SALES_UNIT_MEAS",

"PRICE_UNIT_MEAS",

"CATALOG_GROUP",

"PART_DESC",

"CUSTOMER_PRICE_GRP_DESC",

"INVOICE_NO",

"SERIES_ID",

"parent_cust_id",

"Convt_UOM",

"Convt_QTY",

"convt_price",

"parent_cust_name",

CITY

FROM "QLIK_V2_TestingCD".dbo."invoice_statistics";

TAG FIELD [COUNTRY_DESC] WITH '$geoname', '$relates_invoice_statistics.COUNTRY_DESC_GeoInfo';

TAG FIELD [invoice_statistics.COUNTRY_DESC_GeoInfo] WITH '$geopolygon', '$hidden', '$relates_COUNTRY_DESC';

TAG FIELD [COUNTRY_CODE] WITH '$geoname', '$relates_invoice_statistics.COUNTRY_CODE_GeoInfo';

TAG FIELD [invoice_statistics.COUNTRY_CODE_GeoInfo] WITH '$geopolygon', '$hidden', '$relates_COUNTRY_CODE';

TAG FIELD [SERIES_ID] WITH '$geoname', '$relates_invoice_statistics.SERIES_ID_GeoInfo';

TAG FIELD [invoice_statistics.SERIES_ID_GeoInfo] WITH '$geopolygon', '$hidden', '$relates_SERIES_ID';

DROP TABLES __countryAliasesBase, __countryGeoBase;

[autoCalendar]: 

  DECLARE FIELD DEFINITION Tagged ('$date')

FIELDS

  Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),

  Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),

  Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),

  Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),

  Month($1) AS [Month] Tagged ('$month', '$cyclic'),

  Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),

  Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),

  Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),

  Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),

  Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),

  If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,

  Year(Today())-Year($1) AS [YearsAgo] ,

  If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,

  4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,

  Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,

  If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,

  12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,

  Month(Today())-Month($1) AS [MonthRelNo] ,

  If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,

  (WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,

  Week(Today())-Week($1) AS [WeekRelNo] ;

DERIVE FIELDS FROM FIELDS [STATISTIC_DATE], [INVOICE_DATE], [CREATION_DATE], [LAST_USE_DATE], [PART_CREATED], [DATE_ENTERED] USING [autoCalendar] ;

LIB CONNECT TO 'SQL CONNECTION';

LOAD CONTRACT, 

PART_NO,

PART_PRODUCT_CODE, 

PART_PRODUCT_FAMILY, 

PART_STATUS, 

PLANNER_BUYER, 

UNIT_MEAS, 

ABC_CLASS,

CREATE_DATE,   

LAST_ACTIVITY_DATE,    

SUPPLY_CODE, 

TYPE_CODE, 

TYPE_DESIGNATION,  

NEGATIVE_ON_HAND, 

INVENTORY_PART_COST_LEVEL,   

LIFECYCLE_STAGE, 

FREQUENCY_CLASS, 

DECLINE_DATE, 

EXPIRED_DATE, 

Catalog_Desc;

[inventory_parts]:

SELECT CONTRACT,

"PART_NO",

"PART_PRODUCT_CODE",

"PART_PRODUCT_FAMILY",

"PART_STATUS",

"PLANNER_BUYER",

"UNIT_MEAS",

"ABC_CLASS",

"CREATE_DATE",

"LAST_ACTIVITY_DATE",

"SUPPLY_CODE",

"TYPE_CODE",

"TYPE_DESIGNATION",

"NEGATIVE_ON_HAND",

"INVENTORY_PART_COST_LEVEL",

"LIFECYCLE_STAGE",

"FREQUENCY_CLASS",

"DECLINE_DATE",

"EXPIRED_DATE",

"Catalog_Desc"

FROM "QLIK_V2_TestingCD".dbo."inventory_parts";

LOAD PART_PRODUCT_FAMILY, 

DESCRIPTION AS [PF_DESC], 

PF_CONCAT;

[inventory_prod_fams]:

SELECT "PART_PRODUCT_FAMILY",

DESCRIPTION,

"PF_CONCAT"

FROM "QLIK_V2_TestingCD".dbo."inventory_prod_fams";

[sales_reps_basic]:

LOAD

[SALESMAN_CODE],

[NAME],

[Status],

[DIVISION_FLAG] AS [COMPONENT];

SELECT "SALESMAN_CODE",

NAME,

Status,

"DIVISION_FLAG"

FROM "QLIK_V2_TestingCD".dbo."sales_reps_basic";

[SQLDBV2_DivisionsHierarchy]:

LOAD

[DIVISION],

[DIVISION LVL2],

[DIVISION LVL3],

[DIVISION LVL4],

[DIVISION LVL5],

if([COMPONENT]='FASTPATCH / POLYQUICK', 'FASTPATCH/POLYQUICK', [COMPONENT]) as [COMPONENT]

 FROM [lib://1. Raw Data:DataFiles/SQLDBV2_DivisionsHierarchy.qvd]

(qvd);

[SQLDBV2_Divisions]:

LOAD

[COMPONENT],

[DIVISION] AS [SQLDBV2_Divisions.DIVISION],

[LastReload]

 FROM [lib://1. Raw Data:DataFiles/SQLDBV2_Divisions.qvd]

(qvd);

LIB CONNECT TO 'SQL CONNECTION';

LOAD id, 

sec_site, 

contract AS [CONTRACT], 

part_type, 

part_no as [PART_NO], 

component_part, 

description as [component_desc], 

type_code, 

lpp, 

app, 

std_cost, 

lprd, 

qoh, 

uom, 

last_consumed, 

inv_value, 

avg_6mth_usage, 

units_on_order, 

value_on_order,

    component_part & ' - ' & description AS [COMPONENT_FULL];

[wvco_inventory]:

SELECT id,

"sec_site",

contract,

"part_type",

"part_no",

"component_part",

description,

"type_code",

lpp,

app,

"std_cost",

lprd,

qoh,

uom,

"last_consumed",

"inv_value",

"avg_6mth_usage",

"units_on_order",

"value_on_order"

FROM "QLIK_V2_TestingCD".dbo."wvco_inventory"

WHERE COMPONENT_PART NOT IN ('H2O','H2O-HOT', 'H2O-LB', '0', '00');

LIB CONNECT TO 'SQL CONNECTION';

LOAD CONTRACT, 

STAT_YEAR_NO, 

STAT_PERIOD_NO, 

PART_NO as [component_part], 

QUANTITY, 

QTY_IN_TRANSIT, 

QTY_AT_CUSTOMER, 

VENDOR_OWNED_QTY, 

TOTAL_VALUE, 

DESCRIPTION,

    STAT_YEAR_NO & ' - ' & STAT_PERIOD_NO AS [YEARMONTH];

[inv_value_per_period]:

SELECT CONTRACT,

"STAT_YEAR_NO",

"STAT_PERIOD_NO",

"PART_NO",

QUANTITY,

"QTY_IN_TRANSIT",

"QTY_AT_CUSTOMER",

"VENDOR_OWNED_QTY",

"TOTAL_VALUE",

DESCRIPTION

FROM "QLIK_V2_TestingCD".dbo."inv_value_per_period";

LIB CONNECT TO 'SQL CONNECTION';

LOAD ORDER_NO as [PO_NO], 

BUYER_CODE, 

CONTRACT, 

PART_NO as [component_part], 

ADDITIONAL_COST_AMOUNT, 

ADDITIONAL_COST_INCL_TAX, 

BUY_QTY_DUE, 

BUY_UNIT_PRICE, 

BUY_UNIT_PRICE_INCL_TAX, 

DATE_ENTERED,

    Year(DATE_ENTERED) as [STAT_YEAR_NO],

    Month(DATE_ENTERED) AS [STAT_PERIOD_NO],

REVISED_QTY, 

VENDOR_NO, 

TAX_AMOUNT_BASE, 

INVOICING_SUPPLIER, 

STATE;

[PO_Lines]:

SELECT "ORDER_NO",

"BUYER_CODE",

CONTRACT,

"PART_NO",

"ADDITIONAL_COST_AMOUNT",

"ADDITIONAL_COST_INCL_TAX",

"BUY_QTY_DUE",

"BUY_UNIT_PRICE",

"BUY_UNIT_PRICE_INCL_TAX",

"DATE_ENTERED",

"REVISED_QTY",

"VENDOR_NO",

"TAX_AMOUNT_BASE",

"INVOICING_SUPPLIER",

STATE

FROM "QLIK_V2".dbo."PO_Lines";

LIB CONNECT TO 'SQL CONNECTION';

LOAD TRANSACTION_ID, 

PART_NO as [component_part], 

CONTRACT,

    COST AS [TRANSACTION_COST],

SOURCE_REF1, 

TRANSACTION, 

DATE_APPLIED,

    YEAR(DATE_APPLIED) AS [STAT_YEAR_NO], 

    Month(DATE_APPLIED) AS [STAT_PERIOD_NO],

DIRECTION, 

QUANTITY AS [TRANSACTION_QTY], 

USERID, 

TRANSACTION_CODE;

[inv_transactions_history]:

SELECT "TRANSACTION_ID",

"PART_NO",

CONTRACT,

    "COST",

"SOURCE_REF1",

"TRANSACTION",

"DATE_APPLIED",

DIRECTION,

QUANTITY,

USERID,

"TRANSACTION_CODE"

FROM "QLIK_V2".dbo."inv_transactions_history"

where TRANSACTION_CODE in ('BACFLUSH','CO-DELV-OU','INTSHIP','INTUNISS','OESHIP','OEUNSHIP','PODIRSH','RETCORCRE','SOISS','SUNREC','UNISS');

[marcus_sommer]

I think your main-issue is that you tries to link multiple fact-tables. It's an appropriate method for tools with a relational data-model but Qlik used an associative data-model. And most suitable and officially recommended is to create a star-scheme with one fact-table and n dimension-tables.

This means the facts are merged together by concatenate (union) and/or join/mapping loads. Even if it sounds complicated and not expedient - no other method will be simpler and causing lesser efforts to get a working data-model.