Creating Dimensions

Dimensions are fundamental objects in a Microsoft SQL Server Analysis Services (SSAS) cube. They provide the context for analyzing data by representing business entities such as customers, products, or dates. This tutorial guides you through the process of creating and configuring dimensions within your Analysis Services project.

Prerequisites

Before you begin, ensure you have the following:

• SQL Server Data Tools (SSDT) for Visual Studio installed.
• An existing Analysis Services project with a data source defined.

Creating a Dimension

There are several ways to create a dimension in Analysis Services:

• Using the Dimension Wizard: The recommended and most common approach for creating new dimensions.
• Creating a Shared Dimension: A dimension that can be reused across multiple cubes.
• Creating a Cube Dimension: A dimension that is specific to a particular cube.

For this tutorial, we will focus on using the Dimension Wizard to create a new dimension.

Using the Dimension Wizard

To launch the Dimension Wizard:

1. In Solution Explorer, right-click the Dimensions folder.
2. Select New Dimension.

The Dimension Wizard will appear.

Step 1: Welcome Screen

Click Next to proceed.

Step 2: Creation Method

Select Use an existing table and click Next.

Step 3: Source Table

Choose the table from your data source view that will be the basis for your dimension. For example, select a DimCustomer table.

Ensure the Key column(s) are correctly identified (usually a unique identifier for the dimension). Click Next.

Step 4: Key Columns

Verify the key columns selected. If necessary, adjust them. Click Next.

Step 5: Remaining Columns

Select the columns from the source table that you want to include as attributes in your dimension. These will be used for filtering, grouping, and labeling data in your reports. For a customer dimension, you might select FirstName, LastName, City, and Country.

Click Next.

Step 6: Finish

Review your selections. You can change the name of the dimension if desired. Click Finish.

Dimension Attributes

Once the dimension is created, it will appear in the Dimensions folder in Solution Explorer. Double-click the dimension to open it in the Dimension Designer.

The Dimension Designer displays the attributes that were created from the columns you selected.

• Attribute Properties: Each attribute has properties like Name, AttributeHierarchyEnabled, and IsKey.
• Attribute Hierarchy: By default, most attributes will have their own hierarchy enabled, allowing them to be used independently in queries.

Creating Hierarchies

Hierarchies represent parent-child relationships within a dimension, enabling drill-down analysis. For example, a Geography dimension might have a hierarchy of Country > State > City.

To create a hierarchy:

1. In the Dimension Designer, drag and drop attributes from the Attributes pane to the Hierarchies pane in the desired order.
2. For example, drag Country, then State, then City into the Hierarchies pane.
3. The wizard will prompt you to name the hierarchy (e.g., "Location Hierarchy").

You can also create User-defined hierarchies and Parent-child hierarchies for more complex data structures.

Advanced Features

Analysis Services dimensions offer advanced features:

• Attribute Relationships: Define relationships between attributes within a dimension. This is crucial for performance and correct aggregation. For instance, the relationship between City and State.
• Aggregations: Pre-computed summary data to improve query performance.
• Dimension Security: Control which users can see specific members of a dimension.
• Translation: Support for multiple languages for dimension member names.

Conclusion

Creating well-defined dimensions is a critical step in building an effective SSAS cube. By understanding how to use the Dimension Wizard and configure attributes and hierarchies, you lay the foundation for powerful data analysis and reporting.

Next: Creating Cubes