kevin.obrien 66193 (11/9/2016)
Hi,I am building a DW which will load data from 2 separate sources.
Each source will populate the Broker Dim table and the Account Dim table.
To identify which source the Broker/Account relates to, I added BrokerSource column and AccountSource Column.
In the Fact tbl, I have FKs to each of these Dims. CounterpartyBrokerKey and GenevaBrokerKey relate to the Broker Dim.
CounterpartyAccountKey and GenevaAccountKey relate to the Account Dim.
The problem I now have is that there are multiple FKs in the Fact table pointing to the one Dim table.
Is this ok? Or is there a better, more efficient way of designing this?
Thanks!
The question to ask is: does a row in your fact table really have both a CounterpartyBroker and a GenevaBroker, both a Counterparty Account and Geneva Account? If a row of your fact table can have both attributes, then yes it makes sense to have both columns and both foreign keys.
If this is related to the two sources and each fact row really only has one or the other then just have one BrokerKey and one AccountKey column in fact table and one foreign key to each dimension. Let the dimension describe the source instead of forcing the use of alternate foreign keys which will make queries more difficult.