Defining a Standard Type Dimension Without a Data Source

  • Article

You can also a dimension in Business Intelligence Development Studio without an existing data source and then generating the tables and columns in the data source view and in the underlying data source. For more information, see Introducing the Schema Generation Wizard. When defining a dimension without a predefined data source view, you can define the dimension from scratch or based on a dimension template.

When you define a dimension without a data source, you can base the dimension on a dimension template or you can define it from scratch. The Dimension Wizard provides dimension templates from which you can build a common dimension type. You can choose from the following types of dimensions:

  • Account
  • Customer
  • Date
  • Department
  • Destination Currency
  • Employee
  • Geography
  • Internet Sales Order Details
  • Organization
  • Product
  • Promotion
  • Reseller Sales Order Details
  • Reseller
  • Sales Channel
  • Sales Reason
  • Sales Summary Order Details
  • Sales Territory
  • Scenario
  • Source Currency

Each of the standard templates supports attributes that you can choose to include in the dimension. You can also add your own template files for dimensions that are commonly used with your data. The dimension templates are located in the following folder:

C:\Program Files\Microsoft SQL Server\90\Tools\Templates\olap\1033\Dimension Templates

Creating a Standard Dimension Without a Data Source

The Dimension Wizard guides you through the following steps for creating a standard dimension without using a data source.

Specify Dimension Type

On the Specify Dimension Type page of the Dimension Wizard, you can specify the dimension type, unless you are building the dimension based on a template, in which case the type is defined for you. On this page, you can also identify standard attributes for the specified dimension type. This page sets the Type property for the dimension and for any attributes that you identify as standard types for the dimension.

If you selected a template that corresponds to a dimension type, this page is populated with the options for that dimension type. If you did not select a template, or if there is no corresponding dimension type, the default dimension type is Regular. If a dimension type is not already selected, choose the most appropriate type for the dimension that you are creating. If no appropriate type is listed for Dimension type, use Regular.

When you choose a dimension type, the wizard lists the attribute types that are applicable to this dimension under Dimension attributes. In the table, select attribute types that are supported by your data. To select an attribute type, select the Include check box next to the attribute type, and type the name for the attribute under Dimension Attribute. The default name is the same as Attribute Type. If you do not select an attribute type, any references to that attribute are removed from the dimension and its hierarchies.

Note

Note that you select Time as the dimension type to create a time dimension. For more information about attribute types for a time dimension, see Time (SSAS).

Identify Key Attribute and Changing Dimensions

On the Specify Dimension Key and Type page, select the attribute that you want to be the key attribute for the dimension. The key attribute typically corresponds to the primary key column in the main dimension table, and it typically indexes the leaf members of the dimension.

If you selected a template, and a key attribute is defined in the template, that attribute is the default key attribute. If you selected a template but no key attribute is defined in the template, all the attributes that you selected on the Specify Dimension Type page are available in the list, and the default is the first attribute in the list. You can choose any of the attributes that you selected on the Specify Dimension Type page to be the key attribute. If you did not select any attributes, the wizard automatically creates a key attribute and names it by concatenating the dimension name and "ID".

Finally, specify whether this dimension is a changing dimension. Members in a changing dimension move over time to different locations within the hierarchy. To enable Analysis Services to track these changes, the wizard generates additional columns and creates attributes that correspond to those columns. Any packages that you subsequently create with the Schema Generation Wizard manage surrogate keys based on slowly changing dimension characteristics of the dimension.

When you select the This is a changing dimension check box, the Dimension Wizard defines the following attributes with the indicated setting for the attribute Type property:

Attribute Type

SCD OriginalID

SCDOriginalID

SCD End Date

SCDEndDate

SCD Start Date

SCDStartDate

SCD Status

SCDStatus

The This is a changing dimension check box is selected by default if you use a template that has these slowly changing dimension attributes defined. If you clear the check box, the slowly changing dimension attributes are removed from the dimension.

You can use Dimension Designer to configure properties for a slowly changing dimension.

Completing the Dimension Wizard

On the Completing the Wizard page, review the structure of the new dimension, and then name the dimension. Select the Generate schema now check box to start the Schema Generation Wizard after you click Finish. In most cases, you should not select this check box if you plan to create additional objects. If you do not select this check box, you can use Dimension Designer to generate the schema later.