Database Reference
In-Depth Information
dimensional tables and combining them into a single table. The table DimWasOnSale is combined with the table
DimEmployeeWasTemp to form one junk dimension table, DimMiscInformation.
Another option could have been to move all columns in the fact table and create a fact dimension; however,
that would add quite a lot of extra data to the fact table. With the current design, you end up with only a single
integer linking to a particular combination of values.
Be careful not to go overboard on combining too many dimensions into a large junk dimension. We have
seen this happen, and it does not work out well. It is very much like having a miscellaneous folder on your hard
drive where you always have trouble finding what you are looking for because it is filled up with such a huge
collection of junk.
Many-to-Many Dimensions
A many-to-many dimension is another common component in data warehouses. In these dimensions, you have
a set of two tables connected by a bridge table. The bridge table defines the many-to-many relationship.
In Figure
4-18
, our FactTitleAuthors bridge table links the DimAuthors and DimTitles together. In order to
process the author's information, however, you have to go through the DimTitles table and its associative bridge
table.
Figure 4-18.
A many-to-many dimension
DimAuthors is not a standard dimension design because it does not link directly to the primary fact table,
FactSales, and is instead linked to the bridge table, FactTitleAuthors.
Fact vs. Bridge Tables
Bridge tables are fact tables, just not the primary fact table of a data mart. A bridge table's purpose is to provide a
connection between dimension tables, not store measures, and it is often referred to as a
factless fact table
.
On closer inspection, notice that all fact tables represent a many-to-many relationship between dimension
tables; it is inherent in their design. Therefore, in our design (Figure
4-18
), one title can sell in many stores, and
one store can sell many titles. In this example, the FactSales table bridges DimTitles and DimStores and is the
primary table of the data mart.
Still, this does not make both DimTitles and DimStores many-to-many dimensions; instead, they are just
two regular dimensions connected to the primary fact table, FactSales. DimAuthors, on the other hand, does
not connect directly to the primary fact table. Instead, it connects indirectly through DimTitles. This indirect
connection is the hallmark of a many-to-many dimension.
