Re: difference between factless fact and bridge table

[ngalemmo]

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.

[dbadwh]

Thanks for the reply and your patience.

[kt_raj1]

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

[jjplet]

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

[hang]

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.

[jjplet]

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?

[ngalemmo]

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?

[jjplet]

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

[ngalemmo]

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.

[jjplet]

Ah. Thanks for the clarification and the assistence.