Edit

Share via

Create a Dimension by Generating a Non-Time Table in the Data Source

  • Article

Applies to: SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium

In Microsoft SQL Server SQL Server Analysis Services, you can use the Dimension Wizard in SQL Server Data Tools to create a dimension without using an existing data source. You do this by selecting the Generate a non-time table in the data source option of the Select Creation Method page of the wizard. To create a new dimension table in the underlying data source, you must have permission to create objects in the underlying data source. When defining a dimension without a predefined data source view, you can either define the dimension from scratch or use a dimension template.

The Dimension Wizard provides sample dimension templates from which you can build a common dimension type. You can select 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\100\Tools\Templates\olap\1033\Dimension Templates

You can use Dimension Designer after you complete the Dimension Wizard to add, remove, and configure attributes and hierarchies in the dimension.

When you are creating a non-time dimension without using a data source, the Dimension Wizard guides you through the steps of specifying the dimension type, and identifying the key attribute and slowly changing dimensions.

Specify Dimension Type

On the Specify Dimension Type page of the Dimension Wizard, you can specify the dimension type. If you are building the dimension based on a template, the dimension type is defined for you. On this page, you can also select standard attributes for the specified dimension type if any are available.

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, select the most appropriate type for the dimension that you are creating. If no appropriate type is listed for Dimension type, use Regular.

When you select a dimension type, the wizard lists the attribute types that apply to this dimension under Dimension attributes. 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.

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, the default is the first attribute in the list. The list contains all the attributes that you selected on the Specify Dimension Type page. You can select any one 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 in the hierarchy. The wizard generates additional columns and creates attributes that correspond to those columns. These columns will let users query the dimension in such a way as to factor in the change. 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 attributes indicated in the following table:

Attribute Type
SCD OriginalID SCDOriginalID
SCD End Date SCDEndDate
SCD Start Date SCDStartDate
SCD Status SCDStatus

By default, the This is a changing dimension check box is selected 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, type a name for the new dimension and view the dimension structure. 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.

See Also

Create a Time Dimension by Generating a Time Table
Create a Time Dimension by Generating a Time Table