Many-to-many dimension relationships

In the dimensional model, the fact table has a many-to-one relationship with each dimension. However, sometimes this kind of modeling cannot represent the real world: for example, a product might belong to several categories. One way of solving this problem might be to choose a "primary" category for each product, to allow the use of a classical star schema. But, doing this, we lose possibly important information.

Analysis Services 2005 introduced the ability to handle many-to-many relationships between dimensions. This feature brings to the OLAP world the approach of modeling many-to-many relationships using bridge tables or factless fact tables that we saw in Chapter 2, Building Basic Dimensions and Cubes.

Implementing a many-to-many dimension relationship

Our example scenario for implementing a many-to-many relationship is based on Sales Reason. In Adventure Works, each Internet sale has a list of reasons for the transaction. This list is the result of...