Project Botticelli

Designing Dimensions Purchase this course

30 March 2015 · 3 comments · 2072 views

SSAS Cube Design Series

Chris shows an attribute hierarchy of a time dimension

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.

Log in or purchase access below to the premium version of this content.

Purchase This Course or Full Access Subscription

Single Course


Access this course for its lifetime*.
Subscription Best Value


Access all content on this site for 1 year.
Group Purchase

from $480/year

For small business & enterprise.
Group Purchase

  • Redeem a prepaid code
  • Payment is instant and you will receive a tax invoice straight away.
  • Your satisfaction is paramount: we offer a no-quibble refund guarantee.

* We guarantee each course to be available for at least 2 years from today, unless marked above with a date as a Course Retiring.


goranbanjac · 2 November 2015

Hi Chris,

In your video (min 40:00) you are talking about tweaking attribute relationship for better performance and your experience with a client and need to drill by color and brand. Would you be kind to share your attributes design?

Chris Webb · 2 November 2015

Drop me an email (contacts at and I'll send you a diagram showing you what I'm talking about.

goranbanjac · 3 November 2015

I will.
Thank you very much!

Online Courses