SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multiple Joins on Fact Table


Multiple Joins on Fact Table

Author
Message
kevin.obrien 66193
kevin.obrien 66193
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1269 Visits: 372
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!


Attachments
dwsample.PNG (420 views, 14.00 KB)
Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)

Group: General Forum Members
Points: 154267 Visits: 22739
kevin.obrien 66193 (11/9/2016)
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?


This is fine and absolutely normal, think of i.e. date dimensions for order date, delivery date, payment date and expiry date, you wouldn't want to have multiple physical instances of the date dimension, rather instantiate the same date dimension multiple times.
Cool

Word of caution, a single instance of a dimension table used many times in a model will most likely result in an empty result set.
Chris Harshman
Chris Harshman
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35549 Visits: 6834
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.
kevin.obrien 66193
kevin.obrien 66193
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1269 Visits: 372
Word of caution, a single instance of a dimension table used many times in a model will most likely result in an empty result set.


Hi, any reason why this might be?
Thanks!
Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)

Group: General Forum Members
Points: 154267 Visits: 22739
kevin.obrien 66193 (11/9/2016)
Word of caution, a single instance of a dimension table used many times in a model will most likely result in an empty result set.


Hi, any reason why this might be?
Thanks!


Think of an order with an order data and a delivery date, most likely those will not be the same. If you use a single instance of a date dimension and link both dates to the that then obviously all rows will be filtered out.
Cool
kevin.obrien 66193
kevin.obrien 66193
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1269 Visits: 372
Think of an order with an order data and a delivery date, most likely those will not be the same. If you use a single instance of a date dimension and link both dates to the that then obviously all rows will be filtered out.
Cool


Ah yes. That makes sense. Thanks!
Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)

Group: General Forum Members
Points: 154267 Visits: 22739
kevin.obrien 66193 (11/9/2016)
Think of an order with an order data and a delivery date, most likely those will not be the same. If you use a single instance of a date dimension and link both dates to the that then obviously all rows will be filtered out.
Cool


Ah yes. That makes sense. Thanks!


You are very welcome, just ping back if you need any further assistance
Cool
RonKyle
RonKyle
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25943 Visits: 4340
Word of caution, a single instance of a dimension table used many times in a model will most likely result in an empty result set.


Not sure if I'm understanding this correctly, but this could be misleading. As you add a dimension to a cube, it won't be possible to hook the same dimension in by two dates. Assuming you are using the same date dimension, it would hook in by the second date and becomes a role playing dimension. Let's say you have a dimension Date. It's hooked into the cube by the one date and then ideally renamed to the Geneva date (or whatever it is, I can't go back while I'm typing this reply). The dimension Date is then added again, hooking into the second date. It will also be renamed. There are some downsides to this, but generally role playing dimensions are the way to go. The main advantage is that any changes made to the Date dimension will automatically be a part of both dimensions in this case.



Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)

Group: General Forum Members
Points: 154267 Visits: 22739
RonKyle (11/9/2016)
Word of caution, a single instance of a dimension table used many times in a model will most likely result in an empty result set.


Not sure if I'm understanding this correctly, but this could be misleading. As you add a dimension to a cube, it won't be possible to hook the same dimension in by two dates. Assuming you are using the same date dimension, it would hook in by the second date and becomes a role playing dimension. Let's say you have a dimension Date. It's hooked into the cube by the one date and then ideally renamed to the Geneva date (or whatever it is, I can't go back while I'm typing this reply). The dimension Date is then added again, hooking into the second date. It will also be renamed. There are some downsides to this, but generally role playing dimensions are the way to go. The main advantage is that any changes made to the Date dimension will automatically be a part of both dimensions in this case.


True for some but not for all, the cube design interface will instantiate the dimension automatically
Cool
MMartin1
MMartin1
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24187 Visits: 2170
...CounterpartyAccountKey and GenevaAccountKey ..

If these do represent the source, think of the maintenance nightmare when the source changes, or another is added. This is not the way to design IMHO.
Unless you know that an account can and will always be tied to one source, for all accounts... ( I would not think this is likely) then I would just use a separate source dimension instead of including the source in the account dimension. There is a gray area here as you could consider the source a slowly changing attribute in the account dimension, but I just dont think it strictly relates to the key despite the correlation. Hence why I would advocate a separate source dimension here.

----------------------------------------------------
How to post forum questions to get the best help
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search