MSBI # 18– SSAS # 4 – Building a Cube and Dimension Using SSMS–SSAS Wizards

Understanding Note

Steps to Perform

TUsing Business Intelligence Development Studio, open the SSAS solution in the C:\BI\Part 2\Module 05\Demos folder.

a. Click Start, All Programs, Microsoft SQL Server 2008, and then click SQL Server Business Intelligence Development Studio.

b. On the File menu, point to Open, and click Project/Solution.

c. Navigate to C:\BI\Part 2\Module 05\Demos, and double-click SSAS.sln.

The data source connects to the AdventureWorksDW database on the SQL Server host computer.

d. In Solution Explorer, double-click Adventure Works DW.ds.

e. Click Edit to review the connection manager details.

f. Click Cancel twice to close all dialog boxes.

Notice the tables defined in the data source view. In this scenario, only one fact table is defined and selected dimension tables are included. Friendly names have been assigned to each table. Also, notice the named calculations in the Time table.

g. In Solution Explorer, double-click Adventure Works DW.dsv.

h. In the Time table, scroll if necessary to view the named calculations, Quarter and Month.

Launch the cube wizard.

i. In Solution Explorer, right-click the Cubes folder, and click New Cube.

j. On the Welcome page, click Next.

Notice the options for creating a cube. In this scenario, you use the option to create a cube from existing tables.

k. On the Select Creation Method page, click the non-default options to read the corresponding descriptions, and then click Use existing tables.

l. Next.

The cube wizard has been changed to first prompt you to select only measure group tables.

m. On the Select Measure Group Tables page, select the ResellerSales and InternetSales check boxes, and then click Next.

Next you select the measures to include in the cube.

n. On the Select Measures page, select the Measure check box to clear all selections.

o. Select the check boxes for the following measures: Order Quantity, Total Product Cost, Sales Amount, Order Quantity – Internet Sales, Total Product Cost – Internet Sales, and Sales Amount – Internet Sales.

You can rename measures here also. Remember that measure names must be unique within the cube.

p. Right-click Order Quantity, and replace the text with Reseller Order Quantity.

q. Repeat the previous step to rename measures as shown in the following table:

Measure Revised Name

Total Product Cost Reseller Cost

Sales Amount Reseller Sales

Order Quantity – Internet Sales Internet Order Quantity

Total Product Cost – Internet Sales Internet Cost

Sales Amount – Internet Sales Internet Sales

r. Click Next.

Next select the dimensions to include. In this scenario, because you will create the Product dimension using the dimension wizard later, clear the selection. In addition, Reseller Sales and Internet Sales are used exclusively as fact tables in this scenario, so you must clear these selections.

s. On the Select New Dimensions page, clear the Product, Reseller Sales, and Internet Sales check boxes.

t. Click Next.

Name the cube Sales.

u. On the Completing the Wizard page, in the Cube name box, type Sales.

Review the results on the Cube Structure and Dimension Structure pages. Notice the new dimensions are created with only the key attribute.

v. After reviewing the Cube Structure page, in Solution Explorer, double-click Reseller.dim.

w. Double-click Time.dim.

x. Double-click Sales Territory.dim.

Understanding Note

Steps to Perform

Launch the dimension wizard.

a. In Solution Explorer, right-click the Dimensions folder, and click New Dimension.

b. On the Welcome page, click Next.

Notice the options for creating a dimension. In this scenario, you use the option to create a dimension from existing tables.

c. On the Select Creation Method page, click the non-default options to read the corresponding descriptions, and then click Use an existing table.

d. Next.

In this scenario, create the dimension based on the Product table. Notice the key column is selected, but you have the option to add more columns to address situations where a composite key is required to make each dimension member unique.

e. On the Specify Source Information page, in the Main table drop-down list, select Product.

f. In the Name column drop-down list, select EnglishProductName, and click Next.

Keep the default selections for related tables.

g. On the Select Related Tables page, click Next.

Select attributes for the dimension.

h. On the Select Dimension Attributes page, select the following attributes (in addition to the key attributes already selected from each table associated with the dimension):

Color

List Price

Size

Disable browsing for List Price. This attribute will be used for reporting, but not for analysis. That is, the user will not be able to put this attribute on rows or columns.

i. In the List Price row, clear the Enable Browsing check box.

j. Click Next.

Keep the default dimension name, Product. On the Dimension Structure page, notice the attribute for List Price is gray to indicate it is unavailable for browsing.

k. On the Completing the Wizard page, click Finish.

Add the dimension to the Sales cube.

l. In Solution Explorer, double-click Sales.cube.

m. Right-click in the Dimensions pane, and click Add Cube Dimension.

n. In the Add Cube Dimension dialog box, select Product, and click OK.