Building Great Cubes: Tip 1 - Less is More

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

• 5 or 6 dimensions with few or no natural hierarchies
• 70 or more (often many many more...) attributes 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 make it more difficult for the business user 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 base decisions on that understanding. One of the reasons for this is analytic and monitoring tools (as opposed to reporting) directly surface the cube metadata as part of the user interface (Excel, ProClarity and PerformancePoint Monitoring). Also, multiple measure groups are dangerous. Let me be clear. All of the features in MSAS 2005 are useful. They just aren't useful all of 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 that country's wealth. He also noted that this ratio held true for other scientific and economic distributions. A hundred years and four times that number of self help books later we have the Pareto Principle. 80% of the value come from 20% of the resources. This principle is true for cube design with the additional observation that the extra complexity from the 80% of the resources cost far greater than the 20% value that those resources provide.

Next time you create a cube, strongly consider what design set will result in 80% functionality and stop there. In my experience these cubes often look similar to this:

• 6 to 10 dimensions all with 1 strong natural hierarchy
• 2 or 3 exposed attribute hierarchies
• 1 measure group
• 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, 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.

Pete Sprague