Understanding Bridge Dimension
In this blog I would try to explain what bridge dimensions are,
where are they used and in what situation.
The star schema defines how the dimensions relate to the
fact table. The cardinality is 1:N from dimension to fact table. The structure
of the schema remains coherent till the point where 1:N cardinality is
followed. When there exists an N:N relation between the fact and dimension then
it causes the issue in relational integrity of the schema.
Imagine two situations
1.
Salesman selling an account: The fact table will
have the account number and salesman ID.
2.
Relation between account and customer dimensions
Situation 1 has cardinality is 1:N i.e. one sales man can
sell multiple account but one account number can have only one salesman associated
with it. Whereas, situation 2 one account can have multiple customers and one
customer can have multiple accounts and this type of cardinality is termed as
N:N.
Now, N:N relationship creates a problem when such dimensions
are directly connected to a star schema. Such as
What is the problem
with N:N
Just imagine the above schema to be
correct, the fact table will then have rows indicating for an account. If that
account has multiple customers then the same account number will be repeated
times the customer count that account has. For example
In this table above we see Account 1 has three customers and
customer 1 has two accounts and each is of different type. This creates an
issue when running the aggregate query and finding out which customer has what
account balance remaining.
One way to solve this discrepancy is to convert the scheme
to 1:N or N:1 cardinality.
To implement such a schema bridge dimension is used.
Bridge dimension: This
is the dimension which will be a bridge between customer and account dimension
and would relate to each of the dimension with 1:N relation.
Customer
Dimension Attributes
·
Customer_ID
·
Customer_Name
·
Customer_Current_add
·
Customer_DOB……and more….
Account Dimension
·
Account_ID
·
Account_Type
·
Account_Status
·
Account_Current_Bal
Cust_Acct_Bridge_Dimension
·
Customer_ID
·
Account_ID
We notice here that Customer dimension and account dimension
each is related to bridge dimension via cust_acct_bridge dimension with 1:N
cardinality. And account table is attached to fact table via star schema in 1:N
relation
References: