Time taken by resident load is huge

[ananyaghosh]

Hi,

The following tables are used in resident loads:

1. F_SALES_TRN -> have more that 29 lacks of rows

2. D_FIRM  -> have more that 2 lacks of rows

3. F_SALES_TRN_TERR_LIST  - have more that 29 lacks of rows

4. D_TERRITORY -> have more that 2 lacks of rows

and I have used left join between each table on a common column name like the below code:

[Temp Division]:

load

"Firm ID",

"Sales Posting Day Key"

resident [F_SALES_POSTING_DAY];

left join

LOAD

[Sales Posting Day Key]

,[Terr ID]

resident [F_SALES_POSTING_DAY_TERR_LIST];

left join

load

   [Terr ID], 

    [Terr Regn Mgr Last Name],

    [Terr Regn Mgr First Name],

    [Manager Wholesaler]   

resident [D_TERRITORY];

left join

LOAD

"Firm ID",

"Firm Channel Desc"

resident [D_FIRM];

Now the problem is when I load my data model it takes huge amount time to load the data and number of rows are seems to be Cartesian join between the joining tables.

So do we have any solution that we can reduce the data load time as well as total number of rows?

Thanks,

Sandip Ghosh

[jaumecf23]

I think the problem could be in the first join that you do:

[Temp Division]:

load

"Firm ID",

"Sales Posting Day Key"

resident [F_SALES_POSTING_DAY];

left join

LOAD

[Sales Posting Day Key]

,[Terr ID]

resident [F_SALES_POSTING_DAY_TERR_LIST];

...

It could be possible that "Sales Posting Day Key" and [Sales Posting Day Key] for Qlikview is not the same name and this is why you can do a Cartesian join between these tables ?

[kaushiknsolanki]

Hi,

It is not the resident load which is taking time, but it is the join which is resource intensive.

Try below code.

[Temp Division]:

load

"Firm ID",

"Sales Posting Day Key"

resident [F_SALES_POSTING_DAY];

left join

LOAD Distinct

[Sales Posting Day Key]

,[Terr ID]

resident [F_SALES_POSTING_DAY_TERR_LIST];

left join

load Distinct

   [Terr ID],

    [Terr Regn Mgr Last Name],

    [Terr Regn Mgr First Name],

    [Manager Wholesaler]  

resident [D_TERRITORY];

left join

LOAD Distinct

"Firm ID",

"Firm Channel Desc"

resident [D_FIRM];

Regards,

Kaushik Solanki

[marcus_malinow]

A couple of comments:

1 - In order to determine where your bad join is happening, use NoOfRows-

LET vNoOfRows1 = NoOfRows('Temp Division');
TRACE $(vNoOfRows1);

2 - Use ApplyMap. If you are adding a single field in a join, and you are expecting only one value for each row in your original table, use ApplyMap instead of a JOIN

[ananyaghosh]

Hi,

Can u use ApplyMap in my above joins? If so, please give me the code with ApplyMap function.

[marcus_malinow]

Sandip,

if you don't already know how to use ApplyMap I would suggest reading up on it - Don't join - use Applymap instead

For your first join:

TerritoryMap:

MAPPING LOAD

[Sales Posting Day Key]

,[Terr ID]

resident [F_SALES_POSTING_DAY_TERR_LIST];

[Temp Division]:

load

"Firm ID",

"Sales Posting Day Key",

ApplyMap('TerritoryMap', "Sales Posting Day Key", Null()) as [Terr ID]

resident [F_SALES_POSTING_DAY];

[ananyaghosh]

hi,

I have used the below script with Apply Map:

[Mapping 1]:
mapping load
"Firm ID",
"Trn Key"
resident [F_SALES_TRN];

[Mapping 2]:
mapping LOAD
[Trn Key]
,"F_SALES_TRN_TERR_LIST.Terr Key" as [Terr Key]
resident [F_SALES_TRN_TERR_LIST];

[Mapping 3]:
mapping
load 
  "D_TERRITORY.Terr Key" as [Terr Key],
  [Terr Regn Mgr Last Name] & [Terr Regn Mgr First Name] as Manager
resident [D_TERRITORY];

[Mapping 4]:
mapping
load 
  "D_TERRITORY.Terr Key" as [Terr Key],
  [Manager Wholesaler]
resident [D_TERRITORY];

[Mapping 5]:
mapping
load 
  "Firm ID",
  "Firm Channel Desc"
resident [D_FIRM];

[temp]:
load
ApplyMap('Mapping 1', 'Firm ID') as 'Trn Key',
ApplyMap('Mapping 2', 'Trn Key') as 'Terr Keys',
ApplyMap('Mapping 3', 'Terr Key') as 'Manager',
ApplyMap('Mapping 4', 'Terr Key') as 'Manager Wholesaler',
ApplyMap('Mapping 5', 'Firm ID') as 'Firm Channel Descs';

but [temp] table does not loaded in my data model. Any suggestion?

[marcus_malinow]

Where are you loading [temp] from? You have no FROM or RESIDENT.

Would appreciate a 'helpful' here...