Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have 5 tables that have a common date. I have created a link table to create a star schema to join the dates, but while this is done, I need to associate a common ID between 2 tables. How can I include that in the star schema? The ID is only common between 2 tables out of 5.
The star schema looks like this:
Thanks.
You mean "Trade ID" in your example? If that is the case case I can see it is already associated.
Hi Ramkumar,
No not TradeID, I wish to associate something else called DerivedTradeID. As long as I don't use the common field name "DerivedTradeID" in these 2 tables, I get the correct star schema. But the moment I make the common field name "DerivedTradeID" in TradeReport and GUIBOSReport tables, a synthetic key is created, and the table view looks like this:
that is because you now have more than 1 common column between 2 tables key and DerivedTradeId.
Now you have to have one mapping table to store the combinations of Key + DerivedTradeID.
Basically the new mapping table will contain 3 columns
Key
DerivedTradeID
SK (Surrogate Key)
SK will go into these 2 tables and Key will go into the rest of the 3 tables. Hope that makes sense. To generate SK use the function AutoGenerate.
We thus eliminate the problem of having more than 1 common column between tables and hence no synthetic keys.
Thanks,
Ram
Hi Ram,
Thanks for your response. I deleted the link tables and created a new mapping table with the following:
TradeReport:
Date as Key,
[Trade ID] as DerivedTrade_ID,
Autonumber([Trade ID]) as SK
FROM..
GUIBOSReport:
Date as Key,
'R006' & right(EXTERNAL_ID,6) as DerivedTrade_ID,
Autonumber('R006' & right(EXTERNAL_ID,6)) as SK
Note that the DerivedTrade_ID in the GUIBOSReport is equal to the [Trade ID] in TradeReport once the manipulation above ('R006'..etc) is done. But I am stuck on how to create the Mapping table..I would initially write something like this:
MappingTable:
Key,
DerivedTrade_ID,
SK
FROM ??
Thanks for your help on this.
No worries mate. Can you mark this thread as Answered then?
Thanks,
Ram
Sorry Ram,
I think you missed it but I couldn't form the mapping table. I would start it like this:
MappingTable:
Key,
DerivedTrade_ID,
SK
FROM ??
But I don't know where to source those fields from. Since Key belongs to 3 tables and SK belongs to 2 tables and DerivedTrade_ID belongs to those same 2 tables. I'm actually unsure of the logic behind this at the moment.
OK. On top of your load scripts for tables TradeReport and GuiBosReport you will have to write something like this.
TradeReport:
load autonumberhash256(key, derivedtrade_id) as surrogate_key,*;
load blah blah ...
<your source statement>;
GuiBosReport:
load autonumberhash256(key,derivedtrade_id) as surrogate_key,*;
load blah blah
<your source statement>;
And you form the mapping table like this.
MappingTable:
load distinct surrogate_key, key, derivedtrade_id , blah blah blah
resident TradeReport;
concatenate(MappingTable)
load distinct surrogate_key, key, derivedtrade_id, blah blah blah
resident guibosreport;
drop fields key, derivedtrade_id from TradeReport;
drop fields key, derivedtrade_id from guibosreport;
Hope that makes sense.
Thanks,
Ram
Hi Ram,
I get the error "fields must be unique within the table" when I try this:
TradeReport:
Date as Key,
[Trade ID] as DerivedTrade_ID
AutoNumberHash256(Key, DerivedTrade_ID) as surrogate_key,*, <--also, what does * do?
You need to find common fields between all 5 tables
Identify Which are dimensions and facts tables
You need to create link table
I am attaching one sample demo for better understanding how to link this tables.
Hope this may help you
Vikas