Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
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.
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
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
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.
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
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
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.