Database Reference
In-Depth Information
Changing the Connection
It may seem confusing that the Authors dimension does not connect directly to the fact table with the measures,
and you may be tempted to fix this by creating a direct connection. But if you try to do so, you will lose the correct
many-to-many relationship between DimTitles and DimAuthors.
When you attach the DimAuthors table to the FactSales table, your reports will have issues. Initially this may
seem like it works, but once you create an SSAS cube in the data warehouse, you will quickly spot the incorrect
values in your cubes.
Wait a second! Didn't we say that all fact tables map a many-to-many relationship? What gives?
The problem is that there are two types of many-to-many relationships here, at least from the perspective
of the primary fact table. We call them direct and indirect many-to-many relationships. Direct many-to-many
relationships can be connected directly to the primary fact table, whereas indirect many-to-many relationships
must be connected with a bridge table.
The distinction is based on measure granularity in the primary fact table, but to really understand what this
means, we need to examine the two designs in more detail.
Direct Many-to-Many Relationships
In the direct design, one row in a dimension table is associated with another row in a different table, but it could
be a different row for different events recorded in the primary fact table. A measured value in the fact table is
associated with only one row of data from each of the dimension tables.
Let's look at an example. In Figure
4-19
, we show the contents of two dimension tables, DimTitles and
DimStores, and one fact table called FactSales. The relationship between DimTitles and DimStores is a many-to-
many one, because sometimes a title is sold by one store, but on a different sales event, it sells in another store. This
means that from the perspective of the sales event, there will be only one store ID for each title ID sold. After all, it
makes sense that one part of the book will never be sold in one store and the other part of the book sold in another.
So, although one title can sell in many stores and one store can sell many titles, there will never come a time where a
single title has more than one store ID for a particular sales event. Therefore, from the position of a FactSales table, a
row in the DimTitles table will map to one and only one row in the DimStores table for a given measure (such as the
sales quantity), and each row in the fact table will have one title ID and one store ID (Figure
4-19
).
