Let Chris Webb explain how to build dimensions: one of the foundations of multidimensional cube design, in this 1-hour video. A SQL Server Analysis Services (SSAS) dimension is made up of one or more attributes. An attribute is a real-world entity that can be used to aggregate data. For example, on a Date dimension you might have attributes like: year, quarter, month and date.
When you build a dimension with the New Dimension wizard, the first important thing you have to do is to choose the main table in the data warehouse to build it from. You will also have to define the Key Attribute: the attribute that represents the lowest level of detail on the dimension. Once you have done that, on the next step of the wizard, you can define other attributes on the dimension. Finally, you can enter the name for the dimension. When the wizard finishes the Dimension Editor will open. It is important to use human readable, business friendly names for your attributes and dimensions!
In the Dimension Editor you can set various properties of the dimension and its attributes, such as the KeyColumns and NameColumn properties, AttributeHierarchyEnabled, AttributeHierarchyOptimizedState, DefaultMember and IsAggregatable. The order of members on an attribute can also be controlled. You can process a dimension by clicking on the Process button in the Dimension Editor, and once you have done that you’ll be able to browse the dimension on the Browse tab.
All attributes, by default, become visible to end users as hierarchies. You can also create user hierarchies by combining attribute hierarchies into a kind of a multi-level drill path. Attribute hierarchies and user hierarchies have different strengths and weaknesses, and you need to understand what they are to make sure your dimension is as easy-to-use as possible.
Another important consideration when designing dimensions are attribute relationships. Attribute relationships describe the one-to-many relationships that exist between attributes in a dimension, and optimising your attribute relationships can be extremely important for query performance.
Parent/child hierarchies are a way of handling variable-depth hierarchies. They are very flexible but they should be used with care because they have a number of disadvantages, especially for performance. A regular user hierarchy can be made to look like a parent/child hierarchy by using the HideMemberIf property. BIDS Helper can be used to convert a parent/child hierarchy to a ragged user hierarchy, and this can avoid some of the problems associated with parent/child hierarchies.
Make sure to watch the remaining videos in this series, and check-out our MDX online course, too.
