Creating Dimensions

Using SQL Server Data Tools (SSDT)

This is the most common and recommended approach. SSDT provides a visual designer for creating and managing dimensions.

1. Open your SSAS project in SQL Server Data Tools.
2. In Solution Explorer, right-click the Dimensions folder and select New Dimension.
3. The Dimension Wizard will launch. Choose the dimension creation method:
   • Create from existing table: Select this to base your dimension on a table in your data source.
   • Create from a cube dimension: Use this to create a new dimension based on an existing dimension already used in a cube.
   • Create from a snowflake schema: Useful for normalizing dimension data.
4. Follow the wizard steps, specifying the source table, columns for the dimension attribute hierarchy, and other settings.
5. You will define the primary key, name columns, and potentially configure other properties like aggregation settings and member properties.

Example: Creating a 'Product' Dimension

-- Source Table Example:
-- CREATE TABLE DimProduct (
--     ProductID INT PRIMARY KEY,
--     ProductName VARCHAR(100),
--     Category VARCHAR(50),
--     Subcategory VARCHAR(50)
-- );
                        

Manually Creating Dimensions

Unbound dimensions are not directly linked to a specific table in the data source. They are useful for creating dimensions that represent logical concepts or data that is not stored in a relational table, such as:

• Time/Date Dimensions: While often generated from date tables, you can create custom date structures.
• Scenario Dimensions: Representing different business scenarios (e.g., Budget, Actuals, Forecast).
• User-Defined Hierarchies: Creating hierarchies that don't directly map to a normalized schema.

To create an unbound dimension:

1. In SSDT, right-click the Dimensions folder and select New Dimension.
2. In the Dimension Wizard, select Create from existing table, but then choose to specify the source table later or select a dummy table if required by the wizard.
3. Alternatively, after creating a basic dimension, you can detach it from its source table (if it was initially bound) and manually add members and attributes.
4. You can then manually define attributes and members, or populate them programmatically.