MSBI # 20 – SSAS # 6 – How Defining a Cube in SSAS

Hi Friends ,

Continuing from my last post MSBI # 19 – SSAS # 5 – How to Defining a Data Source and Data Source View within an SQL Server Analysis Services « (B)usiness (I)ntelligence Mentalist

Today we are going to understand how we are going to define Data Source View in Analysis service

we can start by defining dimensions independently of any cube, and then define one or more cubes that use those dimensions. You can also define a cube and its dimensions in a single pass, by using the Cube Wizard in Business Intelligence Development Studio.

Defining 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 Build Method page, verify that the Build the cube using a data source and the Auto build options are selected, and then click Next.

4. On the Select Data Source View page, verify that the Adventure Works DW data source view is selected.

5. Click Next to continue through the additional pages of the wizard to review and change the cube definitions that the wizard specifies.

The wizard scans the tables in the database that is defined in the data source object, to identify fact and dimension tables. Fact tables contain the measures you are interested in, such as the number of units sold. Dimension tables contain information about those measures, such as the product that was sold, the month in which it was sold, and so on.

6. On the Detecting Fact and Dimension Tables page, click Next when the wizard has finished identifying fact and dimension tables.

7. On the Identify Fact and Dimension Tables page, the fact and dimension tables identified by the wizard are displayed.

For the Analysis Services Tutorial project, the wizard identifies four dimension tables and one fact table. A measure group is defined for the fact table. If multiple fact tables were detected, multiple measure groups would be defined. Each dimension table must be linked to a fact table within the cube. Dimension tables have one of the following types of relationship:

o A direct primary key to foreign key relationship with a fact table. This is referred to as a star schema.

o An indirect primary key to foreign key relationship with a fact table through some other table. This is referred to as a snowflake schema.

Notice that a table can serve as both a fact table and a dimension table. In Lesson 5, you will define a dimension based on a fact table.

On the Identify Fact and Dimension Tables page, you can also specify a time dimension table and then associate time properties with columns in the designated dimension table. This association of time properties with columns in a designated time dimension table is required for time-based Multidimensional Expressions (MDX) calculations, such as YTD and ParallelPeriod, and is also used by the Time Intelligence Wizard to define time-related calculated members. For more information, see Defining Time Intelligence Calculations using the Business Intelligence Wizard.

The following image shows the Identify Fact and Dimension Tables page of the wizard, with fact and dimension tables selected for the Analysis Services Tutorial project.

1.On the Identify Fact and Dimension Tables page, select Time in the Time dimension table list, and then click Next.

2.On the Select Time Periods page, you map time property names to columns in the dimension table that underlies the dimension that is designated as the Time dimension. Map the properties according to the following list:

• Map the Year property to the CalendarYear column.
• Map the Half Year property to the CalendarSemester column.
• Map the Quarter property to the CalendarQuarter column.
• Map the Month property to the EnglishMonthName column.
• Map the Date property to the FullDateAlternateKey column.

The following image demonstrates these column mappings in the wizard.

1.Click Next to go to the next page of the wizard.

The Select Measures page appears, displaying the measures that the wizard selected. The wizard selects as a measure each numeric data type column in the tables that it identified as fact tables. In this lesson, only one measure group is defined. However, in Lesson 4 you will work with multiple measure groups.

2.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:

o Promotion Key

o Currency Key

o Sales Territory Key

o Revision Number

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. You can also change measure names on this page, or you can wait and change them in Cube Designer

The following image shows the cleared check boxes and the remaining selected measures on the Select Measures page.

3.Click Next.

The wizard scans for hierarchies because you selected the Auto build option earlier in the wizard. The wizard samples records in each column in the tables that are defined as dimension tables, to determine the presence of hierarchical relationships between the columns. A hierarchical relationship is a many-to-one relationship, for example the relationship between City and State.

4.On the Detecting Hierarchies page, click Next after the wizard has finished scanning the dimensions and detecting hierarchies.

5.On the Review New Dimensions page, review the structure of the dimension hierarchy of the three dimensions by expanding the tree control to view the hierarchies and attributes that the wizard detected for each dimension.

The following image shows the three dimensions on the Review New Dimensions page.

6.Expand the Product dimension, expand Attributes, and clear the check box for Large Photo. Click Next.

The Large Photo column is not useful in the cube for this tutorial project, and because it may use a significant amount of space, it is better to remove it from the cube.

7.On the Completing the Wizard page, change the name of the cube to Analysis Services Tutorial. On this page you can also preview the measure groups, measures, dimensions, hierarchies, and attributes of the cube.

8.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 three database dimensions appear in the Dimensions folder. Additionally, in the center of the development environment, Cube Designer displays the Analysis Services Tutorial cube. Notice that Data Source View Designer is also open on another tab of Business Intelligence Development Studio.

9.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.

The following image shows the dimensions and fact tables in the designer. Notice that the fact table is yellow and the dimension tables are blue.

10. On the File menu, or on the toolbar of BI Development Studio, click Save All.

This saves the changes you have made to this point in the Analysis Services Tutorial project, so that you can stop the tutorial here if you want and resume it later.

Hope this helps !!

If you really Like reading my post,then please don’t forget to subscribe my blog !!

For more interesting links and daily update please subscribe our link resource website

Todays link are follows:

Link Resource # 14 : August 8 – August 10 « Dactylonomy of Web Resource