Handling Transactional-Level Data - Expert Cube Development with SSAS Multidimensional Models - page 129

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 the customer being asked a multiple choice
question. Therefore, each transaction can have zero, one, or more sales reasons
linked to it. To represent this in the relational model, we have a regular dimension,
SalesReasons , that has all the possible reasons for a sale, and a bridge table that
makes the connection between a regular dimension (in this case the Sales Order
degenerate dimension that we created in the drillthrough section) and the dimension
with the many-to-many relationship. The bridge table has a row for each existing
combination of order and sales reason, in this way defining the many-to-many
relationship that exists between Sales Reasons and Sales Orders .

Usually, a bridge table links two regular dimensions and is represented in the
relational model as shown in the following diagram:

Dim_Orders Dim_SalesReasons

PK ID_Order PK ID_SalesReason

l1
-

OrderNumber
LineNumber

SalesReason
SalesReasonType

Fact_Sales Bridge_OrdersSalesReasons

PK ID_FactSales
ID_Order
Value

PK,FK1
PK,FK2

ID_Order
ID_SalesReason

Fk1