If there is many-to-many relationship between sales office and product sold
as per the organization data model, a dimensional model cannot have one-to-many
relationship for same dimensions.
If we do something like that, we may find data from the source system which
cannot be fitted into dimensional model. For example- Let us say that you have one-to-many
relationship between the product and product related discount packages. However, in the dimensional model, we have only one-on-one relationship between the product and product related discount package. You will find that dimensional
model will stop accepting any further data after it has populated one product
to product discount packages (because internal/table level checks will stop
any further discount packages to be added for the same product). Similarly, if you try to have many-to-many
products sold by the sales office (Any sales office can sell any products),
to be represented as one-to-many (No sales office can sell the same product),
you will be stuck, as the sales office dimensional table will not allow the
same product to be added with another sales office.
As a side note-this kind of situation will be mostly encountered, when you are
trying to have the relationship across two dimensions to be included in the
same table. |