difference between factless fact and bridge table

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?

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.

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?

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.

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

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.

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?

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.

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.

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.

https://i.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??

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.

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.

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.

You Mean to say it is situation Based?

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.

Thanks for the reply and your patience.

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

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

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.

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?

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?

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

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.

Ah. Thanks for the clarification and the assistence.