How would you ask for the top 10 yellow products based on Store Sales? That's the question we posed in the previous MDX Puzzle (Mastering OLAP, July 1999). You can look at this question in a couple of ways. The Product dimension of the Sales cube doesn't include a Member Property called Color, but the Store dimension has a property called Store Type. You ask for the top 10 supermarket stores based on Store Sales as follows:

SELECT \{ \[Store Sales\] \} ON COLUMNS,
TOPCOUNT( Filter( \[Store\].\[Store
  Name\].Members,
  \[Store\].CurrentMember.Properties("Store
  Type") = "Supermarket" ), 10, \[Store Sales\]
  ) ON ROWS
FROM \[Sales\]

Is using a Member Property the right way to solve this problem? You have more than one way to build this capability into the cube and analyze products by color. You can make a Product Color dimension or set up a virtual dimension called Product Color based on the Member Property. Or, you can use Color as an intermediate hierarchical level for the Product dimension by selecting the Color column as one of your hierarchy levels in the OLAP Manager's dimension wizard, which forces all products to be organized by Color. Let's discuss some of the pros and cons of these solution choices that DBAs face when they build a cube.

The first solution is to create a Product Color dimension. If Color is a key attribute of Product and you need to analyze Color, the best solution is to create such a dimension. One advantage of this solution is that Product Color is a different dimension from Product. This approach lets you put the two dimensions separately on rows and columns and compare all the various combinations, such as Product Family against Product Color. On the negative side, making Product Color a dimension increases the cardinality of the cube and raises disk usage.

The second solution is to make Product Color a Member Property and then create a virtual dimension based on this new property. This approach has the same advantage as the previous solution, in that Product Color and Product are separate dimensions. In the initial release of OLAP Services, virtual dimensions are significantly slower than real dimensions. A virtual dimension has a limit of about 730 unique Member Property values. About 1000 members is a practical limit in the dimension containing the members that have properties. If you exceed this limit, the virtual dimension is too slow to use. This solution might still be good if the client software does not otherwise support querying by Member Properties and the dimension containing the Member Properties is relatively small.

The third solution is to organize products by color within the Product dimension. You can do so and still keep your products organized by Product Family if you create two hierarchies for the Product Dimension. Multiple hierarchies is another OLAP Services feature that is not well known. You won't find a multiple hierarchies option in the OLAP Manager. You must name your dimensions with a period in the middle; for example: Product.Color and Product.Family. Using the period to join the first part of the name to another dimension tells OLAP Services to treat the two hierarchies as a single dimension. Then the OLAP client software will group the two hierarchies together to show that they are alternative organizations of the same information.

Having two hierarchies increases the flexibility of the cube without greatly affecting the complexity. If you have more than six or seven dimensions, most users will have a difficult time understanding the information. Multiple hierarchies organize this information in a way that users can better understand it. Also, using multiple hierarchies helps a user understand that different dimension hierarchies are usually mutually exclusive. For example, analyzing product sales by calendar year and fiscal year at the same time probably doesn't make sense. Multiple hierarchies have the same impact on cardinality as multiple dimensions. Therefore, adding a new hierarchy has the same impact on cube size and storage needs as adding an entirely new dimension. Also, the Member Properties feature provides the ability to easily filter OLAP data sets. MDX supports Member Properties, so you can create a query that filters dimension members based on Member Property values.

We don't recommend making a single-dimension hierarchy that has both Product Family and Product Color as intermediate levels. If Color and Family are independent variables, you end up with a hierarchy such as:

All Products
Drink
Yellow
Green
Blue

Food
Yellow
Green
Blue

This solution isn't good because of the multiple dimension members with each color name. Imagine how difficult selecting all the yellow products will be if this dimension is large. You will have to search in each Product Family for the members called Yellow.

The discussion of last month's MDX Puzzle raises an interesting point. OLAP Services always provides more than one way to skin a cat when solving a business problem. Some of the solutions we just walked through use the technology features that OLAP Services offers, and some of the solutions imply the selection of one logical modeling approach over another. Understanding your options is the first step in making the right choice.