Defining a Standard Type of Dimension Based on a Data Source

A standard dimensions is a dimension whose attributes are bound to columns in one or more tables in a data source view. Typically, you begin by defining the data source view and then define dimensions based on the data source view. When you are defining a dimension using a data source, you specify the data source view upon which the dimension will be defined. With this option, the dimension structure will be based on dimension tables, their columns, and any relations between columns in the tables in an existing data source view.

When you base a new dimension on an existing data source, you can use auto build to help you define the dimension and its attributes. With autobuild enabled, Business Intelligence Development Studio samples the data in the source table and related tables. Business Intelligence Development Studio uses this data to define attribute columns that are based on the columns in the dimension tables, and to define hierarchies of attributes (called user-defined hierarchies). Whether you use auto build or not, you can use Dimension Designer after you complete the Dimension Wizard to add, remove, and configure attributes and hierarchies in the dimension.

Selecting the Dimension Type and Defining the Dimension Tables

Next, you specify whether you are defining a server time dimension or a standard type dimension. For more information about server time dimensions, see Defining a Server Time Dimension.

Defining a Standard Dimension as a Time Dimension

When you define a dimension in the Dimension Wizard as a Time Dimension, you are defining a standard dimension with a dimension type of Time, which is bound to a dimension table that contains columns for the time attributes you wish to use in your time dimension.

After you specify this dimension table in the data source view, you then map the columns in the underlying time dimension table to time property names in Analysis Services. For example, you might map the CalendarYear time table column to the Year time property, the CalendarSemester time table column to the Half Year time property, and so on. Each column that is mapped to a Time Property becomes an attribute in the newly defined dimension and the only attributes that you can define in the Dimension Wizard for the time dimension are those that map to a Time property. For more information about dimension and attribute types, see Time (SSAS), Defining Dimension Types, and Configuring Attribute Types.

Finally, based on the columns that you have mapped to Time properties, the wizard defines one or more time hierarchies - such as for calendar and fiscal hierarchies. You can add additional time dimension attributes later using the Dimension Designer. For example, while you can define time properties for calendar and fiscal time attributes, you may want to define additional attributes to enable you to define additional time hierarchies - such as for a marketing or a manufacturing calendar. For more information on defining attributes and user-defined hierarchies, see Defining and Configuring Dimension Attributes, and Defining and Configuring a User-Defined Hierarchy.

Defining a Standard Dimension

When you define a dimension in the Dimension Wizard as a Standard Dimension, are defining a standard dimension without defining its dimension type. You define its dimension type later in the wizard.

Selecting the Main Dimension Table

You begin by defining the main dimension table for the dimension that you are defining. This is the table that is directly linked to the fact table. For example, specify a Product table for a Products dimension or an Employee table for an Employees dimension. Within this table, you specify the key column that links this table to the fact table. For example, you would define ProductKey as the key column for a Product dimension. If you specified auto build, this key will be defined for you based on the primary key defined in the data source view. The key column determines the members of the dimension.

Optionally, you can define a column containing the member name. By default, the member name that will be displayed to users will be the value from the column. The values in a key column (such as ProductID or EmployeeID) are often unique, system-generated keys that are meaningless to the user. You can change the value displayed to users to the corresponding value in some other column in the dimension. The member name column can provide more meaningful values, such as product names or employee names. Users who browse the dimension will then see the more meaningful information for members of the key attribute, such as a product name instead of an ID number, but queries will still use key column values to correctly distinguish members that share the same name. If a composite key is specified for the key column, you must specify the column that provides the member values for the key attribute before you can continue to the next page of the wizard. For more information about configuring attribute properties in Dimension Designer, see Defining and Configuring Dimension Attributes.

Specifying Related Tables

Next, if you are building a snowflaked dimension, you specify the related tables from which additional attributes will be defined. For example, if you are building a customer dimension in which you wish to define a customer geography table, you might define a geography table as a related table.

Selecting Dimension Attributes

After defining all of the dimension tables upon which the dimension will be based, you define the attributes that you want to include in the dimension from these tables. If you specified auto build, all of the underlying columns from all of these tables are defined as dimension attributes. For each attribute in the list, you can specify the name, the key column, and the name column. You can change attribute names in the wizard or later using Dimension Designer. When an attribute is based on a descriptive column in the dimension table, you can set both the key column setting and the name column setting to the same column. However, if an attribute refers to the key column for a dimension table, you may want to specify a more meaningful column as the name column. For example, if a product category attribute in a Products dimension uses the ProductCategoryKey column as its key column, you can specify the ProductCategoryName column as its name column. Subsequent queries on this attribute will correctly distinguish members that have the same name while displaying a meaningful name to the user.

Note  If you have problems reading the values for any column on the Select Dimension Attributes page of the wizard, maximize the wizard window and change the width of each column heading until the values are readable.

Specifying the Dimension Type

Next, you specify the dimension type in order to map dimension attributes to standard attribute types. By default, all standard dimensions have a dimension type of Regular. The dimension and attribute types specified in the wizard set the Type property for the dimension and for the attributes.

First, under Dimension type, select a dimension type to define the dimension Type property setting. The Type property setting provides information about the contents of a dimension to server and client applications. In some cases, the Type setting only provides guidance for client applications and is optional. In other cases, as for Accounts , Time or Currency dimensions, the Type property settings for the dimension and its attributes determine specific server-based behavior and may be required to implement certain behavior in the cube. The default setting for the dimension type is Regular, which makes no assumptions about the contents of the dimension. Use this setting if no appropriate type is listed for Dimension type.

After you select the dimension types, the table under Dimension attributes lists attribute types that are appropriate for that type of dimension. You can then map any standard attributes that exist in the dimension to these standard attribute types. To make these mappings, select the check box under Include next to any standard attribute type for which there exists a corresponding attribute in the dimension. Then, under Dimension Attribute, select the corresponding attribute.

For example, the dbo.DimAccount table in the Adventure Works DW sample database contains the following: an AccountDescription column, which provides the account name; an AccountCodeAlternateKey column, which provides the account number; and an AccountType column, which provides the account type. If you are creating an Accounts dimension based on this table, you first select Accounts for the Dimension type. The table under Dimension attributes then lists four standard attribute types for an Accounts type dimension— Chart of Accounts, Account Name, Account Number, and Account Type. Under the Include column of the table, you would select the check box next to the following three account types and then select dimension attributes for each attribute type as shown in the following list.

For more information on dimension and attribute types, see Defining Dimension Types, Configuring Attribute Types.

Define Account Intelligence

Use the Define Account Intelligence page of the Dimension Wizard to map standard account types supported by Analysis Services to members of the account type attribute in the dimension. The server uses these mappings to provide separate aggregation functions and aliases for each type of account data.

The table on this page of the wizard lists account types from the data source table under Source Table Account Types. Under Built-In Account Types, select the corresponding standard account type supported by the server. This column will be populated already if the source data uses the standard names, and you can review the mapping provided by the wizard.

Define Parent-Child Relationship

Use the Define Parent-Child Relationship page of the wizard to specify whether a parent-child relationship exists in the dimension. A parent-child relationship exists when a parent attribute references members of the key attribute of the dimension. This relationship defines hierarchical relationships as well as aggregation paths between leaf members of the dimension. If you enables auto build, a parent-child relationship will be detected for you.

If a parent-child relationship does exist, select the This dimension contains a parent-child relationship between attributes check box, and then identify the parent attribute in the relationship. For example, if the key attribute in an Employees dimension is Employee, you might identify Supervisor as the parent attribute. The preview pane shows sample values for the child and parent attributes.

For more information about parent-child hierarchies, see Working with Attributes in Parent-Child Hierarchies.

Detecting and Reviewing Hierarchies

If you use auto build, the wizard scans dimension tables to detect relationships on which to build hierarchies. When the wizard finishes scanning the dimension tables, you can review the detected hierarchies to see whether it represents business logic that you want to include in the dimension. The hierarchies represent a best guess based on the structure of the data source view, so any particular hierarchy may or may not be useful for your business problem. You can review the new hierarchies and clear levels or entire hierarchies that are of no interest to your organization. You can also review and configure hierarchies by using the Dimension Structure tab of Dimension Designer after you complete the wizard. For more information, see Defining and Configuring a User-Defined Hierarchy.

See Also

Concepts

Defining a Standard Type Dimension Without a Data Source
Defining a Server Time Dimension
Introducing the Schema Generation Wizard
Defining and Configuring Dimension Attributes

Help and Information

Getting SQL Server 2005 Assistance