Bridge Table and Dates

View previous topic View next topic Go down

Bridge Table and Dates

Post  JeremyL on Tue Oct 25, 2011 4:53 am

I have the following requirement:

1. Transaction Fact table to represent events.
2. Bridge Table to represent a M:M relationship between the fact and a dimension. There are many dimension records relating to the event (fact) but the number is uknnown.
3. Dimensions to represent the dimensional values.

The bridge is an elegant solution because it will handle a yet unknown number of relationships between the fact and dimension, there could be 2 or 200.

My question is where do I store the date attribute of when the fact occurred, on the fact or the bridge? If I store it on the Bridge or Dimension there will be an exponential growth in records, and I cannot store it on fact because I dont know how many fields will be needed to store an unknown number of dates relating to when an even occurred.

Its been a while since I needed to model a M:M scenario - any help will be gratefully received, and thank you for reading this post.

JeremyL

Posts : 6
Join date : 2011-10-25
Location : USA

View user profile

Back to top Go down

More context?

Post  elmorejr on Tue Oct 25, 2011 8:25 am

Can you provide a little more context? I honestly do not think I have heard of this type of scenario before.

You said...

Bridge Table to represent a M:M relationship between the fact and a dimension. There are many dimension records relating to the event (fact) but the number is uknnown.

If your date is treated as any other dimension (as it should be), why do you think dates will cause an exponential growth, whereas the other dimensions will not?

elmorejr

Posts : 25
Join date : 2011-10-20
Location : Columbus, Ohio, United States of America, Earth, Sol

View user profile

Back to top Go down

Re: Bridge Table and Dates

Post  JeremyL on Tue Oct 25, 2011 9:25 am

Yep, when I read that back it didnt make a whole lotta sense....! Sorry.

So a little more context. The grain of our fact is a sale-line, but a sale can be attributed to multiple sales guys and we need to track when a sales person was involved with a sale. So we not only have the sale-date, represented as a SK to the Calendar dim, but we have multiple event dates when each of the sales guys were involved with the sale. For example:

Event Date Person Role
10/01/11 John Pre Sales Expert
11/25/11 Mary Techncial Sales Consultant
12/01/12 Jose After Sales

So our fact table will store the SK for the sale date of 12/21/11

And the way I see it now (and this is where I'm hoping you guys can offer some advice, or correct me if this is the wrong approach), is within our bridge table between our factSales and dimPerson, call it SalePersonBridge, we will need to store 3 records:

10/01/11 - John - Pre Sales Expert
11/25/11 - Mary - Techncial Sales Consultant
12/01/12 - Jose - After Sales

Then when we make another sale, which has included involvement from the same 3 people, we will need to create 3 new records because the dates will be different, i.e. sales that occur in the future.

Whereas if there was a technique to store the dates in the fact we could re-use the original 3 bridge records....



JeremyL

Posts : 6
Join date : 2011-10-25
Location : USA

View user profile

Back to top Go down

Re: Bridge Table and Dates

Post  BoxesAndLines on Tue Oct 25, 2011 9:28 am

There's only one place to store it with your current design and that is the bridge table.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Bridge Table and Dates

Post  JeremyL on Tue Oct 25, 2011 10:18 am

The issue with this approach is.... there are on avg 3 sales guys involved in a sale and there are 169 sales guys making a theoretical maximum records in the bridge of 4,826,809!!! And that's without factoring forecasted sales of 13.4 million records!!!!!!!!!!!!!!!!!!!!!!

JeremyL

Posts : 6
Join date : 2011-10-25
Location : USA

View user profile

Back to top Go down

Re: Bridge Table and Dates

Post  ngalemmo on Tue Oct 25, 2011 10:52 am

You have more problems than that.

Assuming your sales fact row gets created, would the relationship between the sale and persons involved change over time? Or do you create the sales fact after everything is over? I would guess it is the former.

The only practical way to build the bridge, which would allow you at add new sales people to a sale without altering keys on the fact table, is to use a sale key on the fact table and build your bridge between the sale key and the sales person key, with dates if you so choose. The bridge will be large, depending on how many lines (rows) in a sale, it may be larger than the fact table.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Another solution...

Post  elmorejr on Tue Oct 25, 2011 11:16 am

Another solution may be to re-architect your flow into the fact.

1. On receipt of data for a sale, add the fact row(s)
- if there are multiple "entries" for the sale, allocate the measures data across the entries appropriately

2. On subsequent receipt of data for the same sale, remove the original fact data and reload using new allocations based on all rows received for that fact to date

The key here is the method of allocation to ensure the data remains additive to the original values.

Depending on how often and/or how late the data may arrive, you could have a lot of churn in your fact. Otherwise, as was suggested, the bridge table could become a problem.

elmorejr

Posts : 25
Join date : 2011-10-20
Location : Columbus, Ohio, United States of America, Earth, Sol

View user profile

Back to top Go down

Re: Bridge Table and Dates

Post  JeremyL on Tue Oct 25, 2011 11:22 am

ngalemmo wrote:You have more problems than that.

Assuming your sales fact row gets created, would the relationship between the sale and persons involved change over time? Or do you create the sales fact after everything is over? I would guess it is the former.

Its the latter.

But that doesn't alleviate the problem of having a very large Bridge table.

The minimum number of records in the Bridge will be the number of records in the Sales fact if only ever 1 sales guy is on the sale. Very unlikely so I completely agree that the bridge will be bigger than the fact.

It doesnt seem like a good solution.... isnt there a better way????

JeremyL

Posts : 6
Join date : 2011-10-25
Location : USA

View user profile

Back to top Go down

Re: Bridge Table and Dates

Post  ngalemmo on Tue Oct 25, 2011 12:08 pm

The only other alternative I can think of is another fact table at the sales person level. But it may raise more issues than it solves.

Since you know everything when the fact is created and will not have retroactive changes to the sales persons, you could add a second bridge without dates. This bridge can use a sales person group key between the fact and the bridge, any only generate a new key (and rows) for a new unique combination of sales people. It would be a much smaller bridge and would be used in queries that need sales people but not dates. Those who need dates would query using the much larger bridge.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Bridge Table and Dates

Post  JeremyL on Wed Oct 26, 2011 5:48 am

ngalemmo wrote: It would be a much smaller bridge and would be used in queries that need sales people but not dates. Those who need dates would query using the much larger bridge.

Agreed, but the intent is to analyse when sales guys were involved in a sale and the duration of their involvement so its an unavoidable situation.

Thanks for your assistance.

JL.

JeremyL

Posts : 6
Join date : 2011-10-25
Location : USA

View user profile

Back to top Go down

Re: Bridge Table and Dates

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum