Lesson 2 (1) define a multi-dimensional dataset

using the multi-dimensional dataset wizard, you can easily define a simple multi-dimensional dataset in SQL Server 2005. This wizard helps you define measurement values and dimensions for cubes. In this wizard, you can define a multi-dimensional dataset based on the data source, or you can define a multi-dimensional dataset without using an existing data source. If you define a cube without using an existing data source, use this wizard to generate an infrastructure data source architecture. In this tutorial, you will define a cube based on an existing data source. For more information, see .

when you define a multi-dimensional dataset based on an existing data source, the Wizard connects to the database defined in the data source object and reads data from the specified table to help you define measurement values and dimensions. The defined measurement values and dimensions are based on tables identified as fact tables, dimension tables, or both. When you use this method, you can enable auto generate . In this way, the cube wizard automatically defines the attributes of each column in the dimension table. You can also have this wizard automatically try to generate a multi-level hierarchy. If auto generate is not enabled, You can manually create attributes and generate hierarchies in the cube wizard, you can also create these attributes in the cube designer later. For more information, see .

When you use this wizard to define a multi-dimensional data set, you can also define the dimension as a time dimension, and then"Time attribute name"Columns mapped to the basic dimension table of the "time" dimension. These mappings are used for time-related multi-dimensional expression (MDX) computation, such as comparison between the current period and the parallel period. The time smart wizard also uses these mappings. You can also use the business intelligence Wizard to define these dimension attributes in the cube designer later. For more information, see , And .

In the following task, you will use the cube Wizard to generate the first cube based on the data source you defined in course 1. You will use"Auto generate"Create an attribute and define a hierarchy, specify the dimension as a time dimension, and map its columns to time-related attributes.

This course requires that you complete all the steps in Lesson 1st, or load the Analysis Services project file that contains the complete steps of the previous course installed along with the Service Pack 1 example. The default location of this project file is c: \ Program Files \ Microsoft SQL Server \ 90 \ samples \ Analysis Services \ tutorials \ Lesson 1 complete.

 

Define a multi-dimensional dataset and Its Attributes

2. In Solution Explorer, right-click"Multi-dimensional dataset"And then click"Create a cube".

4. In"Welcome to the Cube wizard"Page, click"Next".

6. In"Select generation method"Page, confirm that the selected"Use a data source to generate a multi-dimensional dataset"Options and"Auto generate"Option, and then click"Next".

8. In"Select data source view"Page, confirm that the adventure works DW Data Source view is selected.

   When you use the cube Wizard to generate a cube, you can"Select data source view"Click"Done"To allow the Wizard to define other attributes of a multi-dimensional dataset. In this case, the wizard will directly enter"Complete wizard"Page, on which you can specify a name for a cube and view its structure. The wizard defines a multi-dimensional dataset by using the default settings and the data it queries from the basic data source object.

    

10. Click"Next"Go to other pages of the Wizard to view and change the cube definition specified by the wizard.

    This wizard scans the tables in the database defined in the data source object to identify the fact data table and dimension table. Fact data tables contain related measurements, such as the number of parts sold. A dimension table contains information about these metric values, such as the month when a product is sold or the month when the product is sold.

12. After the wizard identifies the fact data table and dimension table, go"Checking fact data tables and dimension tables"Click"Next".

13. In"Identify fact data tables and dimension tablesThe fact data table and dimension table identified by the wizard are displayed.

    For the analysis services Tutorial Project, the wizard identifies four dimension tables and one fact table. Defines a measurement value group for the fact data table. If multiple fact data tables are detected, multiple measurement value groups are defined. Each dimension table must be linked to a fact data table in a multi-dimensional dataset. A dimension table has one of the following link types:

    • The direct primary key-foreign key relationship with the fact data table. This is called the "Star architecture ".".

    • Indirect primary key-foreign key relationship between a table and a fact table. This is called "snowflake schema ".".

    Note: tables can be used as fact tables and dimension tables. In Lesson 5th, you will define dimensions based on fact data tables. For more information, see .

    In"Identify fact data tables and dimension tables"You can also specify a time dimension table and associate the time attribute with the columns in the specified dimension table. The time attribute is associated with the columns in the specified time dimension table by using a time-based multi-dimensional expression (MDX) calculation (for exampleYTDAndParallelperiodAnd the time intelligence Wizard will use this association to define time-related computing members. For more information, see .

    Displays"Identify fact data tables and dimension tables"Page, where the fact data table and dimension table are selected for the Analysis Services Tutorial Project.

15. In"Identify fact data tables and dimension tables"Page"Time Dimension Table"List, selectTimeAnd then click"Next".

17. In"Select time period"Page, set the time property nameMaps to corresponding columns in the dimension table based on the specified dimension as "time. Map these attributes according to the following list:

    • SetYearProperty ingCalendaryearColumn.

    • SetHalf YearProperty ingCalendarsemesterColumn.
    • SetQuarterProperty ingCalendarquarterColumn.
    • SetMonthProperty ingEnglishmonthnameColumn.
    • SetDateProperty ingFulldatealternatekeyColumn.

    Demonstrate these column mappings in the Wizard.

19. click next to go to the next page of the wizard.

    the select measurement value page appears, showing the measurement value selected by the wizard. This wizard selects the data type columns in the tables marked as fact data tables as measurement values. In this lesson, only one measurement value group is defined. However, in Lesson 4th, you will use multiple measurement groups.

21. On the select measurement value page, view the selected measurement value in the Internet sales measurement value group, and clear the check boxes for the following measurement values:

    • promotion keywords

    • currency keyword
    • sales region keyword
    • Revision No.

    this wizard Selects all numeric columns not linked to the dimension in the fact data table as the measurement value. However, these four columns are not actual measurements. The first three columns are the key values that link a fact data table to a dimension table not used in the initial version of this cube. You can also change the metric value name on this page, or wait and change it in the cube designer. related topics:

    check boxes and other selected dimensions are displayed on the select measurement value page.

23. Click"Next".

    Because you have selected"Auto generate"So the wizard scans the hierarchy. This wizard samples the Column records in a table defined as a dimension table to determine whether there is a hierarchical relationship between columns. Hierarchical relationships are many-to-one relationships, such as the relationship between "city/county" and "province/city/Autonomous Region.

25. After the wizard scans the dimension and detects the hierarchy, go"Detection hierarchy"Click"Next".

27. In"View new dimension"On the page, use the Expand Tree control to display the hierarchies and attributes of the Three Dimensions detected by the wizard, and view the hierarchies of each dimension.

    Shown"View new dimension"Page.

29. ExpandProductDimension and"Attribute"And then clearLarge photoCheck box. Click"Next".

    Large photoColumn is not very useful in the multi-dimensional dataset of this Tutorial Project, and it may occupy a large amount of space, so it is best to delete it from the multi-dimensional dataset.

31. In"Complete wizard"Page, change the cube nameAnalysis Services tutorial. On this page, you can also view the measurement value groups, measurement values, dimensions, hierarchies, and attributes of a multi-dimensional dataset.

33. Click"Done"Button to complete the wizard.

    In the analysis services Tutorial Project of solution resource manager, the Analysis Services tutorial cube is displayed in"Multi-dimensional dataset"Folder, and the three database dimensions are displayed in"Dimension"Folder. In addition, the Multidimensional Dataset Designer displays the Analysis Services tutorial Multidimensional Dataset in the center of the development environment. Note that the data source view designer has been opened on other tabs of business intelligence development studio.

35. On the toolbar of the cube designer"Zoom"The level is changed to 50% to make it easier to view dimension tables and fact data tables in a cube.

    The dimension table and fact data table in the designer are displayed. Note: fact tables are yellow and dimension tables are blue.

37. In"File"Click"Save all".

    This will save the changes you have made in the Analysis Services Tutorial Project so far. You can stop the tutorial here as needed and continue later.

You have successfully defined the first cube. You can use the cube Wizard to quickly and conveniently define a simple cube.