Loading 2 Fact tables not sharing all primary key columns

[#1]

Hi,

I am having issues with loading 2 fact tables (combining them into 1). As the tables do not share all key columns (Dist_id is missing in Fact2, please see below script), I am getting unexpected results.

Can anyone suggest the best approach?  I have tried link tables, while they work fine, the performance is really slow and also the qvw file size goes up 10x the original size which is not needed as I need to load many more records.

Thanks,

Deepak

Fact:

NoConcatenate

LOAD

     TEXT(Dist_id) as %_Dist_ID,

     TEXT(Brand) as %_Brand_ID,

     DATE(T_date) as %_Day_Date_ID,

     Fact1

FROM

Fact1.csv

(txt, unicode, embedded labels, delimiter is '\t', msq);

concatenate(Fact)

LOAD

     TEXT(Region_id) as %_Region_ID,

     TEXT(Brand) as %_Brand_ID,

     DATE(T_date) as %_Day_Date_ID,

     Fact2

FROM

Fact2.csv

(txt, unicode, embedded labels, delimiter is '\t', msq);

[swuehl]

Could you describe a bit more what the unexpected results are if you use the concatenated table?

You won't get links to Dist_id, of course. How do you get around this using link tables, then?

Regards,

Stefan

[#3]

Hi Stefan,

Link tables work. Here is the way I linked them (please note I would not concatenate the tables as above in this case). My question is about how would one approach this issue when key fields are missing in one of the fact tables.

If anyone has a qvw file to explain this when primary keys are missing in one of the fact tables and the method to load them correctly, that would be greatly helpful.

LNK_TABLE:

LOAD  distinct

Fact1      AS %_KEY_LNK_ID,

%_Brand_ID    AS %_Brand_ID,

%_Day_Date_ID AS %_Day_Date_ID,

%_Dist_ID     AS %_Dist_ID

RESIDENT Fact-1;

CONCATENATE

LOAD distinct

Fact2      AS %_KEY_LNK_ID,

%_Brand_ID    AS %_Brand_ID,

%_Day_Date_ID AS %_Day_Date_ID,

%_Region_ID   AS %_Region_ID

RESIDENT Fact-2;

Regarding your question about the unexpected result = many charts in my dashboard not showing up when choosing district_id levels

Hope this helps.

Deepak

[#4]

if you want to concatenate two fact tables then:

first of all remove keywords 'no concatenate' and 'concatenate' from ur script.

rename %_Region_ID as %_Dist_ID,

it will automatically concatenate two tables.

hope this will solve ur prob.

[#5]

Vijit

I am unable to use your suggestion as I have dimension tables based specifically on %_Region_ID and need to preserve the naming of this key field.

Thanks in advance for any other tips.

Deepak

[swuehl]

Deepak,

would it be possible that you upload a small sample file here to the forum?

I think that would probably help me to understand your problem.

I assume that dist_ID and region_ID are something totally different, or could you derive one from the other somehow?

Regards,

Stefan

[#7 · Accepted solution]

Thanks Stefan, Yes, the region_id can be derived from the district_id. Hence, I was able to create a aggregate link key of Region,Brand and Date in both fact tables. Worked perfectly.