Dimensional Model: Connecting dimension to fact table using two approaches

Hi,

As I have seen there are numerous way of designing dimension model. Out of these below are two method of connecting dimension to fact tables.

1) Model-1 : Storing few dimension table’s surrogate keys into a bridge table and pointing its generated surrogate key to fact table as foreign key.
2) Model-2: Start schema  ( Storing surrogate key directly inside fact table as foreign keys )

Model-1 have disadvantage of complex ETL design and complex query to retrieve data while model-2 have advantage on over these points.
and model-2 is generic and easy approach widely being used which has simple ETL and query to retrieve data.

my thought on this is that model-1 can be used when, few dimensions have low cardinality and these are dependent each other in terms of their used with fact records. [pls make sure dependent here it doesn't mean they have hierarchical relationship].
If these dimension can be grouped inside a bridge table then I think this will help to get their grouping dimension in any front-end tool's filter fast rather than scanning whole fact table.

also I am not sure should it be called a bridge table or any other.

please share your thought on this

Hi,

a Dimensional model is a Star (not Start) schema with fact tables holding surrogate keys to dimensions. Bridge tables are used for modelling specific scenarios and are extensions to star schemas, not alternatives.
For example, if you had more than one sales person working on a Lead you wouldn't put multiple sales person SKs in your Lead fact table - you would put a single Sales Group SK on the Fact which references a bridge table which references the Sales Person Dim.
However your Lead fact table would still have SKs to Date(s) and Product and Company and whatever other attributes it had - so it's still a star schema, it's just one relationship uses a bridge table

Hope this helps

Regards,

@nick_white
Thanks for your response.
and sorry for typo ( yes it is star schema not start.. ).

The example you have shared, It shows that same dimension SKs(Sales Person Dim) are connecting to a bridge table with their roles.
Can't this be a example of junk dimension ?

also have you come across any example or scenario where bridge table is connected with different dimensions and forming a group?

Snowflaking can also be done for certain situations, but the snowflaking would be limited to 2 jumps in the join structure.

For example, say you had a geography table that had a hierarchy from Census Block, block group, census tract, county, state, region, with an alternative from county up to metropilitan area. And say you needed aggregations to the County and State or metro area or that you had data coming in at different places in the hierachy (different facts of course). You could create one big dimension table and then create surrogate keys for all of the different roll up levels and them create views, etc.

Or, you could put the levels of aggregation in different dimension tables and have a Census Black Dimension that had the dimension keys from the Block_Group Dimension, Census Tract Dimension, County Dimension, State Dimension, Metro Dimension, etc. Kind of a starflake.

mahendra_aseri wrote:@nick_white
Thanks for your response.
and sorry for typo ( yes it is star schema not start.. ).

The example you have shared, It shows that same dimension SKs(Sales Person Dim) are connecting to a bridge table with their roles.
Can't this be a example of junk dimension ?

also have you come across any example or scenario where bridge table is connected with different dimensions and forming a group?

A junk dimension is a dimension that collects attributes that don't conveniently belong to any other dimension. Normally it would consist just of flags or similar low cardinality values. Junk Dims would join directly to a Fact table - you wouldn't use a bridge table.

I can't think of a situation where you would want to join different dimensions in a group and link that group to a fact. A group, almost by definition, is a collection of similar objects and therefore would almost certainly be in a single Dim. Also, how would you ever use this type of structure as you could only query on common attributes of the Dims included in the group.
May be one scenario would be if you had split something like Companies into separate Dims for Partners, Suppliers, Resellers, etc. you might face this challenge - but in that case I would suggest that your Dim design is wrong and you should have a Company Dim that contains all Companies. You can split this into additional 'sub-type' Dims where a Company would exist in both the main Company Dim and also in any appropriate role Dims but this is getting into quite complex dimensional modelling.

I don't understand the point of 'model 1' as you describe it. What are you trying to gain by putting some dimension keys into another table?

The size of a surrogate key, assuming you are using integers or bigint's, is not that large that you would see a performance improvement by doing so. In fact, by converting what would be relatively small dimensions into a much larger table (due to the combinations of dimensional references) with the addition of adding another join, will only serve to create poor query plans and much longer query times.

It is important to realize all major database systems have special optimizations for dealing with clean star schemas. They cannot use those strategies if you deviate from the standard pattern. The approach you describe in model 1 has no up side.