Database Reference
In-Depth Information
Figure 4-20.
Double counting with a many-to-many dimension
On the surface, the fact that there are two rows for this one sales event does not seem too bad. When you
make a report showing sales by authors, the data is even correct! There is a problem, however, if you make a
report showing sales by titles alone. If you place the AuthorId in the primary fact table, you have to list the sales
event multiple times, and title sales will aggregate once for each author. In software such as SSAS that performs
aggregations of the sales quantity for a given day, title, and store for you, it will double-count the sale. Oops!
With a report like this, humans can figure out pretty quickly what the issue is. But computers? Well,
computers are fast but not smart. You have to configure them around this issue because software, such as SSAS,
for example, expects the many-to-many dimensions to use a bridge table.
When you move the AuthorId back out of the primary fact table and access it only indirectly through the
bridge table, this problem is solved, and the sales event for that title will still be associated with all the authors
who wrote that book.
