Building Great Cubes: Tip 1

Less is More
By Peter Sprague


I have seen a lot of SSAS 2005 cubes that look very similar despite being created independently by different customers and partners across the US and Canada. They always look something like this:

- 5 or 6 dimensions with few or no natural hierarchies
- 70 or more (often many more...) attribute hierarchies, including the ever popular attribute [Customer Fax]
- 4 or more user defined time hierarchies
- Time attributes that contain all dates between 1901 and 2060
- 35+ measures
- Multiple measure groups
- Little relation to a specific user problem

Why is this a concern? These cubes makes it more difficult for the business user to navigate the data, and learn the tools. The complexity also makes it more difficult to understand the data that is returned. The biggest concern is that the users may misunderstand the data, and then base decisions on that misunderstanding. One of the reasons for this complexity is that analytic and monitoring tools (as opposed to reporting tools) directly surface the cube metadata as part of the user interface (Excel, PerformancePoint, ProClarity). Subsequently, multiple measure groups can be dangerous. Let me be clear... ALL of the features of SSAS 2005 are useful, they just aren't useful all the time.

These problem cubes rarely help users support a series of decisions. Seldom do they help users analyze their data to get to an actionable step.

Let me introduce Vilfredo Pareto, an Italian economist who in 1906 observed that 20% of the population in Italy owned 80% of the country's wealth. He also noted that this ratio held true for other scientific and economic distributions. A hundred years and hundreds of self-help books later, we have the Pareto Principle: 80% of the value comes from 20% of the resources. This principle holds true for cube design with the additional observation that the extra complexity from the 80% of the resources cost far greater than the 20% of the value that those resources provide. The next time you create a cube, strongly consider what design set will result in 80% functionality and stop there. In my experience, these cubes looks similar to this:

- 6 to 10 dimensions all with 1 strong natural hierarchy
- 6 or 7 exposed attribute hierarchies
- 1 time hierarchy that only contains dates relevant to the period the cube data
- All metadata expressed in business friendly terms
- Strong relation to a business problem

Remember our goal, to provide a cube that helps our users understand the data and supports further business action or decision. Consider this the first rule of cube design, Less is More.


.