Defining a Cube

It is easy to define a simple cube in SQL Server 2005 by using the Cube Wizard. The wizard helps you to define the measures and dimensions for a cube. In the wizard, you can define a cube based on a data source, or you can define a cube without an existing data source. If you define a cube without an existing data source, you then use the wizard to generate the schema for the underlying data source. In this tutorial, you will define a cube based on an existing data source. For more information, see Working with Relational Schemas and Introducing the Schema Generation Wizard.

When you define a cube based on an existing data source, the wizard connects to the database that is defined in the data source object, and reads the data in the specified tables to help you define measures and dimensions. The measures and dimensions that you define are based on tables that are identified either as fact tables, as dimension tables, or both. When you use this method, you can enable Auto build and the Cube Wizard will then automatically define attributes from columns in the dimension tables. Optionally, you can have the wizard automatically try to build multiple-level hierarchies. If you do not enable Auto build, you can create the attributes and build the hierarchies manually in the Cube Wizard, or you can create them later in Cube Designer. For more information, see Using the Cube Wizard to Define a Cube, Dimensions, Hierarchies and Attributes.

When you use the wizard to define the cube, you can also define a dimension as a time dimension and then map Time Property Names to columns in the underlying dimension table for the Time dimension. These mappings are used for time-related Multidimensional Expressions (MDX) calculations, such as period-to-date and parallel period comparisons. These mappings are also used by the Time Intelligence Wizard. You can also define these dimension properties later in Cube Designer by using the Business Intelligence Wizard. For more information, see Configuring Attribute Types, Time (SSAS), and Defining Time Intelligence Calculations using the Business Intelligence Wizard.

In the following task, you will use the Cube Wizard to build your first cube based on the data source you defined in Lesson 1. You will use Auto build to create attributes and to define hierarchies, and you will also designate a dimension as a time dimension and map its columns to time-related properties.

Note

This lesson requires that you complete all of the procedures in Lesson 1 or load the Analysis Services project file containing the completed procedures for the previous lesson that is installed with the samples for Service Pack 1. The default location for this project file is C:\Program Files\Microsoft SQL Server\90\Samples\Analysis Services\Tutorials\Lesson 1 Complete.

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.

   Note

   When you build cubes with the Cube Wizard, on the Select Data Source View page you can choose to click Finish to let the wizard define the remaining properties of the cube for you. In that case the wizard takes you directly to the Completing the Wizard page, on which you can name the cube and review its structure. The wizard defines the cube by using default settings and data that it queries from the underlying data source object.

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:

   • A direct primary key to foreign key relationship with a fact table. This is referred to as a star schema.
   • 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. For more information, see Defining a Fact Relationship and Fact Relationship Properties.

   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.

   

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

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

   

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

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

    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. Related topic:Defining and Configuring a Measure

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

    

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

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

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

    

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

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

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

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

    

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

You have successfully defined your first cube. Defining a simple cube is quick and easy with the Cube Wizard.

Next Task in Lesson

Reviewing Cube and Dimension Properties

See Also

Other Resources

Defining and Configuring Cubes and Cube Properties
Defining and Configuring Dimensions, Attributes, and Hierarchies
Using the Cube Wizard to Define a Cube, Dimensions, Hierarchies and Attributes

Help and Information

Getting SQL Server 2005 Assistance