difference between factless fact and bridge table

View previous topic View next topic Go down

difference between factless fact and bridge table

Post  dbadwh on Sun Oct 23, 2011 1:55 pm

Hi,
In design tip 136, kimball's example on bridge table, the bridge table looks like a factless fact table. I got used to have fact less tables for linking dimensions. My understanding is to have bridge table to handle many to many relationships and it will have a group key but factless fact will contain no measure but surrogate keys of two or more dimensions. Can you please enlighten me?

dbadwh

Posts : 31
Join date : 2011-09-30

View user profile

Back to top Go down

Re: difference between factless fact and bridge table

Post  ngalemmo on Sun Oct 23, 2011 5:45 pm

One way dimensional modeling diverges from ER modeling is that tables have specific designations based on what they are used for. In ER modeling a table is a table is a table, while in dimensional modeling you have fact tables which represent business events or states, dimension tables which provide context for facts, and bridge tables that resolve many-to-many relationships between a fact and a dimension. A bridge table always sits between a fact and dimension, a fact table does not.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Bridge Table

Post  dbadwh on Mon Oct 24, 2011 2:05 am

I understand the fact table and fact less table mean same except there is no measure in it. But Bridge table is used to resolve many to many relationship using a group key. Normal scenario, I use fact-less fact to link to dimensions when there is a need. My confusion started after reading the design tip #136. In this tip, Kimball used only the surrogate keys of the two dimensions and not used any group keys. And the bridge table looked more like a fact-less fact. So in what way it differs?

dbadwh

Posts : 31
Join date : 2011-09-30

View user profile

Back to top Go down

Re: difference between factless fact and bridge table

Post  ngalemmo on Mon Oct 24, 2011 8:01 am

As I said, it differs in what it is used for and where it appears in the schema. It has little to do with what columns it may or may not have.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: difference between factless fact and bridge table

Post  dbadwh on Mon Oct 24, 2011 1:14 pm

Thank you very much for the reply. Can you please provide an example if possible?

dbadwh

Posts : 31
Join date : 2011-09-30

View user profile

Back to top Go down

Re: difference between factless fact and bridge table

Post  ngalemmo on Mon Oct 24, 2011 2:51 pm

If you have a table that joins to a fact table on one side and a dimension table on the other (and you are not snowflaking a dimension), it is a bridge table. Another characteristic of a bridge table is it will only have two foreign keys and, in the case of a hierarchy bridge, both reference the same dimension.

In the case of a multivalued dimension bridge, one FK references a dimension and the other either references a group key, which is carried in the fact, or a fact row key, which is also carried in the fact. Either one works, but the latter usually creates a much larger bridge table. Wither you decide to group dimensions or not depends on the application of the bridge. Sometimes grouping does not significantly reduce the size of the bridge or puts too large a burden on the load process.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Bridge Table and Factless Fact

Post  dbadwh on Wed Oct 26, 2011 9:28 am

If I am not having a group key and have only the surrogate keys of dimension, will it not become factless fact instead of bridge table?

dbadwh

Posts : 31
Join date : 2011-09-30

View user profile

Back to top Go down

Re: difference between factless fact and bridge table

Post  ngalemmo on Wed Oct 26, 2011 10:47 am

The type of table is determined by how it is used. In a dimensional model, tables are built for a specific purpose.

A fact table is used to record a business event or state.

A bridge table is used to handle many-to-many relationships between a fact and a dimension.

That is all there is to it. That is what distiguishes a fact from a bridge table. It doesn't matter what is actually in the table other than having what it needs to perform its function.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: difference between factless fact and bridge table

Post  arowshan on Fri Oct 28, 2011 2:57 pm

I just want to add that a bridge table could also be between two dimension tables which have many-to-many relationship. An exmaple would be an account dimension which could have muntilple customers attached to it. In this case we need a bridge table between the account and customer dimensions. See Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling section 9.2.2 for the example and discussions on Multivaluded Dimensions. So bridge tables could be between fact and dimension tables and also between two dimensions.

arowshan

Posts : 23
Join date : 2011-10-18
Location : Vancouver, Canada

View user profile

Back to top Go down

Re: difference between factless fact and bridge table

Post  ngalemmo on Fri Oct 28, 2011 3:24 pm

Well, I hate to split hairs, but no. A table that relates two or more dimensions is a fact table.

A bridge table to resolve many-to-many relations or multivalued dimensions do not join two dimensions.

I havn't read the book, but, there are two ways to resolve multivalued dimensions:

A. Assign a key to the new fact and add rows to the bridge referencing the fact row key and the dimension key.

B. Create a 'group' table to collect and assign keys to all unique combinations of a multi-valued dimension and store the group key in the fact. The bridge would consist of the group key and the dimension key.

In scenario B, the group table isn't a dimension and isn't considered part of the end-user model. It is a background table used to support assigning keys during ETL, So, while the model may be drawn with the bridge related to the group table and the dimension, the group table does not participate in end-user queries, so the joins are to the fact and the dimension.

Also bridge tables for hierarchies also appear to join two dimensions (or the same dimension twice), but in use, one of the FKs are used to join to the fact.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: difference between factless fact and bridge table

Post  arowshan on Fri Oct 28, 2011 3:54 pm

http://i48.servimg.com/u/f48/16/98/44/01/bridge11.png

see the above image from the Kimball toolkit. As I siad there two types: Many-to-many between fact and dimension and many-to-many between two dimensions.

Also, for option B, how would you join the Fact to the dimension table for queries without the bridge table? Isn't the group table the bridge table??

arowshan

Posts : 23
Join date : 2011-10-18
Location : Vancouver, Canada

View user profile

Back to top Go down

Re: difference between factless fact and bridge table

Post  ngalemmo on Fri Oct 28, 2011 4:14 pm

The Account dimension sort of performs the role of a group table, that is an account consists of one or more customers. The diagram is simply a necessity to show where the keys come from. (Erwin does have a facility to specify a partial key, so the model could be drawn without including the account dimension table). It does not mean that to get the customers you MUST join through the Account. When presented as a star schema, the account dimension would hang off the fact, and the bridge would hang of the fact as well, via the account key. It would not be drawn hanging off the account dimension, since, by definition, a bridge sits between a fact and a dimension. Customer would hang off the 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: difference between factless fact and bridge table

Post  arowshan on Fri Oct 28, 2011 6:05 pm

I get your point but in the case of this example, you have an option to do either or. In other words, you could hang the bridge table off the Orders fact table or you could have the bridge between account and customer. This way even though like you are saying the account table acts like a group table, it is still part of the model.

Here is a quote from the Kimball toolkit:
An AccountToCustomer bridge table between the account and customer dimensions can capture the many-to-many relationship with a couple of significant benefits. First, the relationship is already known in the source system, so creating the bridge table will be easier than the manual build process required for the SalesRepGroup table. Second, the account-customer relationship is interesting in its own right. The AccountToCustomer bridge table allows users to answer questions such as "What is the average number of accounts per customer?" without joining to any fact table.

arowshan

Posts : 23
Join date : 2011-10-18
Location : Vancouver, Canada

View user profile

Back to top Go down

Re: difference between factless fact and bridge table

Post  ngalemmo on Sat Oct 29, 2011 3:18 am

Right, but the opperative phrase here is:

without joining to any fact table.

Its still a bridge, even if you can use it as described.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

difference between factless fact and bridge table

Post  dbadwh on Thu Nov 03, 2011 7:36 am

You Mean to say it is situation Based?

dbadwh

Posts : 31
Join date : 2011-09-30

View user profile

Back to top Go down

Re: difference between factless fact and bridge table

Post  ngalemmo on Thu Nov 03, 2011 12:06 pm

dbadwh wrote:You Mean to say it is situation Based?

It has to do with the intended use of the table. Bridge tables resolve many-to-many relationships between a fact and a dimension, so in its design there is, conceptually, a 'parent' key and a 'child' key. So while you may be able to do other things with it, such as the cross dimension join mentioned by Kimball, it still doesn't change the nature of the table. A fact table does not have the concept of dependence between the keys. A fact is measures (or not) and a bunch of dimension references for context. In a fact, each dimensional reference may be used independently of any other.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: difference between factless fact and bridge table

Post  dbadwh on Wed Nov 16, 2011 12:39 pm

Thanks for the reply and your patience.

dbadwh

Posts : 31
Join date : 2011-09-30

View user profile

Back to top Go down

What I understand is this.Please correct me

Post  kt_raj1 on Tue Apr 24, 2012 5:29 am

Hi,

My understanding is this:

Factless Fact : A factless fact contains information like bridge excepting that it can contain measure, for example if if in a factless fact I have class, time, student dimension information then I can measure how many students belong to a class.

Bridge table : Bridge table contains keys only from both fact and dimensions, where it resolves m:m relationship.

Please correct me if I am wrong.

Thanks and regards,
Raja

kt_raj1

Posts : 2
Join date : 2012-04-24

View user profile

Back to top Go down

RE: Difference Between Fact and Bridge

Post  jjplet on Tue May 01, 2012 4:44 pm

As I understand ngalemo's explanation, the difference between a Fact table and a Bridge table is how it is used. For example,

Consider a model with business related facts in my fact table (Sales Amount, Sales Quantity, etc), and an Employee dimension. Now, I also need to track Employee Degrees (BA, BS, MBA, PhD, JD, etc.) so I have an Degree dimension related to the Employee dimension via an Employee-Degree bridge table (essentially a Dimension-to-Dimension bridge). Now, this bridge table is a bridge from a Sales standpoint because there are no sales facts in it, but if I need to perform some sort of HR analysis of Employees and degrees then this very same bridge table can be considered a fact table FOR THAT HR BUSINESS PROCESS. So whether a table is a 'bridge' or a 'fact' is really more of a distinction of the ROLE of the table and the business process in question. Is this correct?

- Jim

jjplet

Posts : 7
Join date : 2011-04-15

View user profile

Back to top Go down

Re: difference between factless fact and bridge table

Post  hang on Wed May 02, 2012 8:35 am

Not quite right, IMO. By the same token, the employee dimension table would also be referred to as a fact table if some sort of analysis (eg. head count) is perform on it.

There is no obvious distinction between factless fact and bridge table in Kimball's literature. However Kimball did highlight one crucial attribute other than measures that qualify a table to be a fact table, a time series or date key to be more specific. So my take is, a bridge table can only become factless fact table if you add a snapshot date key, or some form of time series, to TRACK the changing relationship between dimensions. What can you do on (factless) fact table but can't on dimension table or bridge table? Trend analysis.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: difference between factless fact and bridge table

Post  jjplet on Wed May 02, 2012 9:47 am

Thanks, Hang. Your point of a fact table being essentially a Bridge Table that includes time variance makes sense.

Back to the Employee Degree scenario (which is one I need to build), if I only need to store the different degrees that someone has, and when they attained them, I'm thinking I could simply combine these values and relationships into a single table that is directly off of the Employee Table (rather than a separate Degree table and separate bridge table). What would be the pros / cons of this approach? Is this approach above sound? It just seems like overkill to have a Degree dimension that only has a couple fields and only 20-30 records.

DimEmployee
- DimEmployeeKey (SK)
- Employee (NK)
- (Lots of Employee Attributes)

DimDegrees
- DimDegreeKey (SK)
- DimEmployeeKey (FK)
- DegreeAbbreviation
- DegreeName
- DateAttained

Thoughts?


jjplet

Posts : 7
Join date : 2011-04-15

View user profile

Back to top Go down

Re: difference between factless fact and bridge table

Post  ngalemmo on Wed May 02, 2012 12:06 pm

The approach you suggest takes a simple dimension with a handful of rows and turns it into a complex dimension with a large number of rows. Where is the win?
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: difference between factless fact and bridge table

Post  jjplet on Wed May 02, 2012 12:19 pm

ngalemmo wrote:The approach you suggest takes a simple dimension with a handful of rows and turns it into a complex dimension with a large number of rows. Where is the win?

Well, my thinking is that it takes a simple dimension with a handful of rows AND a bridge table with a large number of rows and combines them into into a simple dimension with a large number of rows. The 'win' is what seems like a simpler model (I say seems like because that's my perspective anyway). What would I lose? What would make this a 'complex' dimension? What would be the win of having the separate Dimension and Bridge if combined version works? Or would the combined version not work?

-Jim

jjplet

Posts : 7
Join date : 2011-04-15

View user profile

Back to top Go down

Re: difference between factless fact and bridge table

Post  ngalemmo on Wed May 02, 2012 2:25 pm

What makes it complex is the dimension now has a compound primary key. You cannot use this as a conforming dimension or in aggregate facts. It is questionable wither it performs well, and it doesn't eliminate the bridge, it just denormalizes it into a single table. If you want to simplify the model for queries, create a view.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: difference between factless fact and bridge table

Post  jjplet on Wed May 02, 2012 3:28 pm

Ah. Thanks for the clarification and the assistence.

jjplet

Posts : 7
Join date : 2011-04-15

View user profile

Back to top Go down

Re: difference between factless fact and bridge table

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