Varigence, Inc.

Dimension tables let you model not only the relational parts of a table, but also the analytical aspects. If your dimension is going to be exposed through an OLAP cube, this allows you to model all aspects of the dimension in the same editor. The relational features are modeled the same way for all table types, while the analytical features vary depending on the purpose of the table. For dimensions, the dimension editor lets you model the properties that control how a Microsoft Analysis Services dimension will be emitted.

To create a dimension, follow these steps:

1. From the Home tab of the ribbon, click the lower portion of the Table split button. From the list of table types, choose Dimension.
   
2. Follow the directions from the Creating a Basic Table topic to define the relational portion of the dimension, using this information:
   Table Name: DimProduct
   Schema: AdventureWorksLTDataMartSchema
   Keys: IK_DimProduct (using column ProductID)
   Indexes: IX_DimProduct_ProductName (using column ProductName)
   Columns:

   Name	Data Type	Length	Nullable
   ProductID	Int32		No
   ProductName	String	50	No
   Color	String	15	Yes
   Size	String	5	Yes
   StartDate	Date		No
   EndDate	Date		Yes
   CategoryName	String	50	No
   SubcategoryName	String	50	No

   The dimension editor should look like this:

   
3. The Attributes area lets you define the attributes that will appear in the OLAP dimension. You can populate this by dragging and dropping columns from the columns grid into the Attributes area. Drag the ProductID column to the Attributes area. Notice that when you select an attribute, you can view and edit all its properties in the Property Grid.
4. Right click on the Product attribute to set the attribute usage to Key.
   
5. Rename the attribute to Product Name using the context menu or by pressing F2 with the attribute selected.
6. Drag and drop the ProductName column to the Name Column folder item under the Product Name attribute.
   
7. You can create additional attributes by using an Accelerator. On the Table Tools ribbon, Tables tab, click the Generate Attributes and Relationships split button. Choosing the Generate Attributes and Relationships menu item will automatically create attributes for appropriate columns from the table, and relationships for those attributes.
   
8. The attributes and relationships will be populated
   

   Note that the accelerator populates a default set of relationships, ensuring that every attribute is related to the key.

   It's always a good idea to review the relationships to ensure they match your data. Attribute relationships are important to getting the best performance from Analysis Services.
9. To adjust the relationships to reflect the data, locate the Category Name relationship in the Relationships area. Right click on the Parent folder and select Set Parent Attribute. Select the Subcategory Name attribute.
   

   This creates a relationship chain that includes Product Name -> Subcategory Name -> Category Name.

10. Next, you will add a hierarchy. Right click in the empty Hierarchies area, and select Add Hierarchy.
    
11. Rename the hierarchy to Categories. You can add levels to the hierarchy by right-clicking it and choosing Add Attributes from the context menu, or by dragging and dropping the attributes from the Attributes tree view. Add the following attributes (in order): Category Name, Subcategory Name, and Product Name. The Hierarchies area should look like this when you are finished:
    
    Drag and drop the attribute on the Hierarchy node (Categories) to add the attribute as a new level at the end. Dropping an attribute on an existing level will replace the level's associated attribute. You can also drag and drop levels within a hierarchy.
12. Save the project to commit your changes.

Once a dimension is created in the model, it will emit as a relational table. If it is referenced from a cube, it will also emit as an Analysis Services dimension.