Create link table between 5 tables

[sifatnabil]

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.

[#2]

You mean "Trade ID" in your example? If that is the case case I can see it is already associated.

[sifatnabil]

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:

[#4]

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

[sifatnabil]

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.

[#6]

No worries mate. Can you mark this thread as Answered then?

Thanks,

Ram

[sifatnabil]

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.

[#8]

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

[sifatnabil]

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?

[vikasmahajan]

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