For the most part, creating a dimension for SSAS is an easy wizard driven process. However, there’s some important steps that should not be missed. Here’s a checklist I use to help me remember each step. This list should be repeated for each dimension.

• Run the Dimension Wizard – Right click the Dimensions folder and select New Dimension…
  • On the Select Creation Method step always choose Use an existing table
  • On the Specify Source Information step select the source view or table in the Main table drop down. Ensure that the correct key columns has been selected. Finally, change the name column if applicable.
  • On the Select Dimension Attributes step check all the Attributes to include on the dimension. Also, rename any attributes here. It’s possible to rename an attribute later, but it’s best to rename it during dimension creation. When a dimension is created each attribute gets an ID and Name properties. The value assigned to these properties is the attribute name provided in the wizard. Once the dimension is created the Name property can be changed, but the ID property cannot be changed. I can lead to confusion if these property values don’t match.
  • On the Completing the Wizard step name the dimension something meaningful. Remove Dim from the name!
• For each attribute consider the following properties :
  • AttributeHierarchyEnabled – By default an attribute hierarchy is built. Some attributes, however, will never be used on their own. For example, email address, phone number. Changing the AttributeHierarchyEnabled property to False prevents and attribute hierarchy from being built and saves on processing time.
  • AttributeHierarchyOptimizedState – Controls if an index is built on the attribute. Setting this to False will make processing faster, but query performance will suffer.
  • IsAggregatable – Controls if the All Member is created. Sometimes, it does not make sense to aggregate all the attributes in a dimension. For example, a scenario or budget dimension.
  • DefaultMember – By default the default member is the first member in the first level; usually All. In some cases it might make sense to change this. Also, hierarchies where there’s no All level should have this property set.
  • OrderBy – Controls the ordering of the members. This may need changed on some attributes. For example, a month attribute should be ordered by month number of year not month name.
  • AttributeHierarchyOrdered – Controls if the attribute is ordered. If the ordering does not matter set this to False. For example, it’s not helpful to order an attribute of invoice numbers. Processing will be faster.
• For Date dimensions set the following:
  • Dimension Type property – By default this is Regular. Change this to Time.
  • For each date attribute set the respective Type property. For example, Calendar Year should be set Years, Calendar Quarter to Quarters, etc…
• Create user Hierarchies
• Decide whether to set AttributeHierarchyVisible Property to False on attributes that participate in a user hierarchy. Attribute hierarchies are more flexible. A lot of times it’s better not to hide the attribute hierarchy.
• Design Attribute Relationships
  • Click the many side of the relationship and drag to the one side of the relationship.
  • Run Dimension Health Check from BIDS helper.
  • Create composite keys for attributes that cause duplicate key errors. For example, it’s typical to have User Hierarchy like: Year > Quarter > Month> Date. In this case, it’s best to have attribute relationships that follow the same chain. However, attribute relationships have to be many to one. Quarters and Months can exist in many years. Therefore, it’s necessary to create composite key on these attributes. In this example, change the KeyColumns property to include the Year. Also, the NameColumn property will need to be set.
  • Set attribute relationships as Rigid where possible. Dates, Type 2 SCD attributes, etc…

Repeat for next dimension.