Definning Cube and its Dimension

After you define a data source view in your Microsoft Analysis Services project, you are ready to define an initial Analysis Services cube.

You can define a cube and its dimensions in a single pass using the Cube Wizard. Alternatively, you can define one or more dimensions and then use the Cube Wizard to define a cube that uses those dimensions. If you are designing a complex solution, you generally start by defining the dimensions

Defining a Dimension

In the following task, you will use the Dimension Wizard to build a Date dimension.

1.      In Solution Explorer, right-click Dimensions, and then click New Dimension.

2.      On the Welcome to the Dimension Wizard page, click Next.

3.      On the Select Creation Method page, verify that the Use an existing table option is selected, and then click Next.

4.      On the Specify Source Information page, verify that the Adventure Works DW data source view is selected.

5.      In the Main table list, select Date.

6.      Click Next.

7.      On the Select Dimension Attributes page, select the check boxes next to the following attributes:

·         Date Key

·         Full Date Alternate Key

·         English Month Name

·         Calendar Quarter

·         Calendar Year

·         Calendar Semester

8.      Change the setting of the Full Date Alternate Key attribute's Attribute Type column from Regular to Date.To do this, click Regular in the Attribute Type column. Then click the arrow to expand the options. Next, click Date > Calendar > Date. Click OK. Repeat these steps to change the attribute type of the following attributes as follows:

·         English Month Name to Month

·         Calendar Quarter to Quarter

·         Calendar Year to Year

·         Calendar Semester to Half Year

9.      Click Next.

10.  On the Completing the Wizard page, in the Preview pane, you can see the Date dimension and its attributes.

11.  Click Finish to complete the wizard.

In Solution Explorer, in the Analysis Services Tutorial project, the Date dimension appears in the Dimensions folder. In the center of the development environment, Dimension Designer displays the Date dimension.

12.  On the File menu, click Save All.

Defining a Cube

The Cube Wizard helps you define the measure groups and dimensions for a cube. In the following task, you will use the Cube Wizard to build a cube.

To define a cube and its properties

1.      In Solution Explorer, right-click Cubes, and then click New Cube.

2.      On the Welcome to the Cube Wizard page, click Next.

3.      On the Select Creation Method page, verify that the Use existing tables option is selected, and then click Next.

4.      On the Select Measure Group Tables page, verify that the Adventure Works DW data source view is selected.

5.      Click Suggest to have the cube wizard suggest tables to use to create measure groups.

The wizard examines the tables and suggests InternetSales as a measure group table. Measure group tables, also named fact tables, contain the measures you are interested in such as the number of units sold.

6.      Click Next.

7.      On the Select Measures page, review the selected measures in the Internet Sales measure group, and then clear the check boxes for the following measures:

·         Promotion Key

·         Currency Key

·         Sales Territory Key

·         Revision Number

By default, the wizard selects as measures all numeric columns in the fact table that are not linked to dimensions. However, these four columns are not actual measures. The first three are key values that link the fact table with dimension tables that are not used in the initial version of this cube.

8.      Click Next.

9.      On the Select Existing Dimensions page, select the Date dimension that you created earlier and then click Next.

10.  On the Select New Dimensions page, select the new dimensions to be created. To do this, verify that the Customer, Geography and Product check boxes are selected and clear the InternetSales check box.

11.  Click Next.

12.  On the Completing the Wizard page, change the name of the cube to Analysis Services Tutorial. In the Preview pane, you can see the InternetSales measure group and its measures. You can also see the Date, Customer, and Product dimensions.

13.  Click Finish to complete the wizard.

In Solution Explorer, in the Analysis Services Tutorial project, the Analysis Services Tutorial cube appears in the Cubes folder, and database dimensions appear in the Dimensions folder. Additionally, in the center of the development environment, Cube Designer displays the Analysis Services Tutorial cube.

14.  On the toolbar of Cube Designer, change the Zoom level to 50 percent, so that you can more easily see the dimensions and fact tables in the cube. Notice that the fact table is yellow and the dimension tables are blue.

15.  On the File menu, click Save All.

	Mr. Ravi Krishna - Senior Software Engineer I am a Mocrosoft ASP.net Developer and MCP Certified professional. I have overall 5 years of experience in IT Industry,in that 3 years experience in Microsoft BI(SSAS,SSIS,SSRS). I have experience on various business domains like Automation and Chemical. https://sites.google.com/site/rkkumardotnet/ Read more