Designing Dimensions Purchase the entire course

30 March 2015 · · 2181 views

SSAS Cube Design Series

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 to play the video.

  • Using the Deployment Wizard 5-min Watch with Free Subscription

  • Using SQL Server Data Tools-BI 40-min

  • Designing Dimensions 1-hour 4-min

  • Cubes, Measures and Measure Groups 1-hour 10-min

  • Cube Security 30-min

  • Cube Deployment, Processing and Admin 36-min

  • Aggregations & Cube Performance 57-min

Purchase a Full Access Subscription

Individual Subscription


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

from $480/year

For small business & enterprise.
Group Purchase
  • You can also redeem a prepaid code.
  • Payments are instant and you will receive a tax invoice straight away.
  • We offer sales quotes/pro-forma invoices, and we accept purchase orders and bank transfers.
  • Your satisfaction is paramount: we offer a no-quibble refund guarantee.
  • See pricing FAQ for more detail.
In collaboration with
Project Botticelli logo Oxford Computer Training logo SQLBI logo Prodata logo