Adding Functionality to Your Cube - Getting Started with SQL Server 2012 Cube Development - page 159

Adding many-to-many dimensions
Inthepreviousexamples,therehavebeenone-to-onerelationshipsbetweenthefact
table and the dimensions. However, this is not always the case; sometimes you need
to model a many-to-many relationship. Analysis Services solves this by working with
an intermediate measure group. In the example database, a sales order may have
many sales reasons, and a sales reason may have many sales orders connected to
it.
To model a many-to-many relationship, perform the following steps:
1. Right-click on the Dimension folder in the Solution Explorer and choose to
add a new dimension.
2. Select DimSalesReason as the main table, change the value of the Name
column to SalesReasonName and click on Next .
3. Change the attribute name to Sales Reason and click on Next .
4. Name the dimension as Sales Reason and click on Finish .
5. Double-click on the Adventure Works DW2012.dsv Data Source View in
Solution Explorer .
6. Right-clickonthe FactInternetSales tableandaddanewnamedcalculation.
7. Name the calculation as SalesDesc and add the following expression:
SalesOrderNumber + ' ' +
cast(SalesOrderLineNumber as nvarchar)
8. Add a new dimension by right-clicking on Dimensions and selecting New
Dimension .
9. Select FactInternetSales as the main table and select SalesDesc as the
value of the Name column.
10. Name the new dimension as Internet Sales Details .
11. Double-click on the Adventure Works DW2012.cube cube in the Solution
Explorer .
12. On the Cube Structure tab and click on the New Measure Group button.
13. Select the FactInternetSalesReason table and click on OK .
14. Click on the Dimension Usage tab and click on the Add Cube Dimension
button. Add the Sales Reason and Internet Sales Details dimen-
sions to the cube.