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

. The Customer dimension —Customers who bought a product

. The Time dimension —Date of the purchase

The measures available for analysis describe the count of the units of the product bought
by customers and the amount paid for the products. This model makes it possible to
produce an analysis of the products bought by our customers and a count of those prod-
ucts, and we can analyze all of that based on the date of that purchase. Now, let's assume
that we have another fact, Warehouse , that contains information about products in the
warehouses from which the store gets its products. Therefore, the Warehouse fact will also
have three dimensions:

. The Product dimension —Products in the warehouses

. The Warehouse dimension —Warehouse that contains the products

. The Time dimension —Date the products arrived at the warehouse

The measure of this fact contains the count of a specific product that was delivered to the
warehouse at a specific time.

Because our task is to analyze sales of products by the Warehouse dimension, we need to
include this dimension in the Sales measure group. We can do so by using a many-to-
many dimension. All we have to do is define the cube dimension ( Warehouse ) that we
want to include in the measure group ( Sales ) as a many-to-many dimension, and then to
specify the Warehouse measure group through which the Warehouse dimension joins with
Sales data. We call such a measure group an intermediate fact. The intermediate fact,
Warehouse , is specified by the MeasureGroupID property of the dimension definition (see
Listing 8.3).

LISTING 8.3 DDL for the Warehouse Dimension Included in the Sales Fact as a Many-to-
Many Dimension

<Dimension xsi:type=”ManyToManyMeasureGroupDimension”>
<CubeDimensionID>Warehouse</CubeDimensionID>
<MeasureGroupID>Warehouse</MeasureGroupID>
</Dimension>

Defining the many-to-many dimension is easy; finding out what happens in the system
then is not so easy. Let's explore what happens. First, Analysis Services determines a
common dimension for both the Sales and Warehouse facts. Through this dimension,
Analysis Services will join two facts together. Analysis Services determines common
dimensions by looking for measure group dimensions that have the same value for the
CubeDimensionID property, and that aren't many-to-many dimensions. In the scenario
we're working with, we have two common dimensions: Product and Time . The Product
dimension defines this joint between the Sales fact and the Warehouse fact. However, we
don't want to use the Time dimension as a common dimension because the time of the
purchase and the time of the product's arrival at the warehouse are not related. Therefore,
we have to use a role-playing dimension to define two different cube dimensions: Time of