In my incomplete view of BI tables, a Fact table represents action and a dimension an entity.
I have a FactOrder
table that contains order information (including OrderId and CustomerId). There is a separate dimension for people who are actually connected to the order but are not customers. So they are saved in a separate table called DimServiceUser
. A linking table connects an Order
to a ServiceUser
. Should this intermediate OrderServeruser table be defined as a Dimension, a Fact or another type?
It really is more of a bridge table. Here's what is going on.
Your FactOrder table is a fact table, but it also contains a degenerate dimension. A degenerate dimension acts as a dimension key in the fact table, however does not join to a corresponding dimension table because all its interesting attributes have already been placed in other analytic dimensions. So you have an implied DimOrder in there that didn't require a separate table.
A bridge table can connect a set of values to a single fact table row, or it can connect two dimensions (such as customers and bank accounts). It is a way of handling legitimate many-to-many relationships. A bridge table is like a factless fact table. But in dimensional modeling we do not join fact tables together, whereas it is acceptable to join bridge tables and fact tables together. If you must force your bridge table into being a fact or dimension, it is closer to a fact table. But doing so may make it easier to implement bad modeling habits in the future. If you can call it a bridge instead, I would just go with that. (Make sure you read that third link on "like a factless fact table". It was written by the author of Star Schema: The Complete Reference. That is a pretty well accepted source.)