Advanced Modeling - Microsoft SQL Server 2008 Analysis Services - page 98

enced dimensions is worse than the performance of direct referenced or materialized
dimensions. On the other hand, if the dimension data changes, it doesn't require a re-
indexing of the fact.

Many-to-Many Dimensions

The ability to define many-to-many dimensions significantly enriches modeling capabili-
ties of Analysis Services. Dimension of this type can be bound to fact through the other
fact (thus enabling you to overcome a known limitation of multidimensional modeling
that prevents assignment of the same data to multiple members of one dimension). Many-
to-many dimensions in Analysis Services let you solve multiple business problems (for
example, managing a single account with multiple co-owners, making a demographic
analysis of sales data where a single customer can belong to multiple demographic groups,
revenue allocation between multiple producers, and many others. Let's take a look at an
example of a many-to-many dimension based on the FoodMart 2008 database. In this
example, we want to analyze the sales of products to customers based on the warehouse
availability of these products. In other words, we want to know how fast products
currently stored in a certain warehouse are sold (and therefore might need to be requested
from the supplier).

In Figure 8.6, we look at the sales of products to customers in our store over a period of
time. We define a simple measure group Sales with the following dimensions:

. The Product dimension —Products sold in the store

Warehouse Warehouse

Customer

Measure Group
Warehouse

Measure Group
Sales

Time
of Sale

Time
of Arrival

Product

FIGURE 8.6

Relationship between Warehouse and Sales facts by the Product dimension.