Advanced Dimension Design - Professional SQL Server Analysis Services 2005 with MDX - page 324

Figure 8-4
5. Create a new cube using the Cube Wizard. In the "Identify Fact and Di-
mension Tables" dialog mark all tables beginning with the word Fact as
fact tables only, all tables beginning with Dim as dimension tables, and
specify the time dimension table as shown in Figure 8-5 . Add all the di-
mensions created in step 4 as cube dimensions and complete the Cube
Wizard with the defaults. You will now have Adventure Works DW cube
created. The Cube Wizard detects the relationship between the fact and
dimension tables based on relationships in the DSV and creates appropri-
ate dimension usage. If you do need to change the relationships you can
do so on the Dimension Usage tab within the cube editor. If you look at
the relationships in the Dimension Usage tab you will notice that there are
multiple instances of certain dimensions. For example, you will see four
instances of the DimTime dimension Dim Time, Dime Time (Due Date),
Dim Time (Order Date), and Dim Time (Ship Date). Instead of creating
four database dimensions and using them within the cube, Analysis Ser-
vices 2005 creates cube dimensions from a single dimension. Typically
when you have multiple relationships between a fact table and dimension
table then the optimal way to model them is with a single database dimen-
sion. Such dimensions are called role playing dimensions.