MSDN Documentation

Microsoft Developer Network
Home / SQL Server / Analysis Services / Multidimensional Models / Tasks / How to Create a Dimension

How to Create a Dimension in Analysis Services

This topic provides step-by-step instructions on how to create a dimension in SQL Server Analysis Services (SSAS) using SQL Server Data Tools (SSDT).

Prerequisites

Steps to Create a Dimension

  1. Open SQL Server Data Tools: Launch Visual Studio with SQL Server Data Tools installed.

  2. Open your Analysis Services Project: Navigate to File > Open > Project/Solution and select your SSAS project.

  3. Add a New Dimension: In the Solution Explorer, right-click on the "Dimensions" folder and select "Add Dimension...".

  4. Dimension Wizard: The "Create Dimension Wizard" will open.

    • Welcome Page: Click "Next".

    • Create from existing table: Choose "Create a dimension with a source table". Click "Next".

      Tip: You can also choose to create a dimension from multiple tables if your dimension data is spread across several related tables.

    • Source Table: Select the data source view that contains your dimension table. Choose the primary table that will serve as the source for your dimension from the dropdown list. Click "Next".

    • Select Attributes: The wizard will suggest attributes based on the chosen table. You can select or deselect columns to include as attributes in your dimension. Ensure the unique identifier column is selected. Click "Next".

      Note: Attributes form the basis of your dimension's hierarchy and are used for slicing and dicing data.

    • Dimension Name: Provide a descriptive name for your dimension (e.g., "DimCustomer", "DimProduct"). Click "Next".

    • Hierarchy: You can choose to create a parent-child hierarchy or a standard user-defined hierarchy. For most standard dimensions, you will create user-defined hierarchies later. Click "Next".

    • Completing the Wizard: Review the summary of the dimension you are about to create. Click "Finish".

  5. Dimension Designer: The newly created dimension will open in the Dimension Designer. Here, you can refine your dimension:

    • Attributes: Add, rename, or configure attributes.
    • Hierarchies: Create user-defined hierarchies (e.g., Year > Quarter > Month) by dragging and dropping attributes into the Hierarchy pane.
    • Dimension Properties: Configure properties like 'UnknownMember' and 'DefaultMember'.

  6. Deploy the Project: After making changes, right-click on your Analysis Services project in Solution Explorer and select "Deploy" to process and deploy the changes to your Analysis Services instance.

Example of Dimension Attributes

For a customer dimension, attributes might include:

Example of a User-Defined Hierarchy

A common hierarchy for a Product dimension could be:

Category > Subcategory > Product Name

By following these steps, you can effectively create and configure dimensions, which are fundamental components of your Analysis Services multidimensional model.