MSAS : Building the Cube Part #2

Tutorial 13: MSAS : Building the Cube Part #2

Click ‘New Dimension…’ button to launch the Dimension Wizard. The welcome dialog box of the Dimension wizard appears. The Check box option is to be checked if we want to skip this screen in future. For the present we leave it as it is and click the Next button.

The next requirement is to select the kind of schema we want for our cube. On clicking Next we are taken to the ‘chose how you want to create the dimension’ screen

The initial prompt to select the Dimension Table appears once more, enabling us to select other dimension tables. We proceed to select region and Click next.

The next screen prompts the user select the levels for the dimension chosen if such levels exist. We will select the following levels: sales_country, sales_region, sales_state_province, sales_district and sales_city. We will rank them in a logical hierarchy for optimal reporting. The best method of arranging the logical order of levels is having a knowledge of the data we are handling. However, if the user attempts to arrange the levels in an order of his own which is not logical, a prompt appears informing the user that the order is not logical. Nevertheless if the user still wants to arrange the order in that fashion the Wizard will cease to argue with the user.

The specification of member keys is the next activity. Since we do not propose to cover the issues related to this topic in this tutorial we will leave the default values and proceed to click Next.

We will also not meddle with the Advance options dialog box and simply Click Next to proceed.

The dimension Wizard dialog box appears. The user is prompted to enter a name for the Dimension. We will name this dimension “Region”. We can preview the hierarchies that will be created by clicking the + sign next to the topmost level of the dimension tree to expand the levels listed. Since we do not propose to change any other settings we will click Finish to complete the task. We are returned to the cube wizard where we can see that Region is a dimension of the cube.

We will be creating the next dimension using a snowflake schema as the product_id key is joined to a series of Product Dimension tables and each of these tables are required for defining the levels of a multi-tier hierarchy. This complex is easily defined by the Dimension Wizard. We need to begin the process by clicking on the New Dimension button of the screen above. In the “Chose how you want to create the dimension” we will select the snowflake schema radio button and click Next.

We need to select the product and product_class tables to create the joins. Double click on the items and then Click Next to continue

The Create and Edit Joins dialog box opens showing us the joins between the two tables selected. The tables are linked by the key field product_class_id. Normally data types and other factors will have to be reviewed. For our purpose of study we will leave these issues for a later tutorial and focus on the simple join, which appears to require no correction.

The process of selecting levels for the dimension now must be done. We shall order them from the most summarized to the most detailed—from Product_category-> Product_subcategory-> Brand Name. Then we go on to specify the Member Key Columns.

We will Click Next on the following two screens and proceed to the Finish dialog box.

We will name our dimension ‘Product’, have a look at the hierarchies that will be created and proceed to Dimensions list in the Cube Wizard by Clicking Finish.

With this the second dimension of our cube has been created and appears in the list of dimensions for the cube