Mastering OLAP: Multiple Hierarchies per Dimension

Build flexible OLAP models your users will love

This month we discuss the implementation and use of multiple hierarchies in SQL Server 7.0 OLAP Services. Multiple hierarchies let you organize the same dimension of an OLAP cube in different ways. Rolling up the Time dimension with both fiscal and calendar years is one classic example. Multiple hierarchies are a natural way to organize business information to build flexible OLAP models your end users will love.

In this column, we explain how to create multiple hierarchies with the OLAP Manager and how to use them from the MDX expression language. And we point out a few limitations and gotchas. (But first, if you're on the edge of your seat awaiting news about last month's MDX Puzzle, look at the sidebar, "August MDX Puzzle Solution Revealed," page 66.)

With multiple hierarchies, each dimension in a cube is a collection of elements (known as members) that you can use to analyze a business. These dimension elements are organized into a hierarchy of categories (also known as members). For example, you might organize a geography dimension by country, state, city, and town. Frequently, you can categorize dimension members in more than one way. The need to represent time as both a fiscal and a calendar year is probably the most universal example in the business world. Both year types contain days, but the days are organized differently. Retail products are another great example. Organizing retail products by brand makes as much sense as organizing by store department. But you don't want to mix these organizations in a single-dimension hierarchy because product brands span departments and departments span brands. That isn't a hierarchy; it's a network!

You might be familiar with multiple hierarchies in a single dimension, but you might not know how to build them using OLAP Services. In SQL Server 7.0, the ability to create multiple hierarchies is a hidden feature. Let's use OLAP Manager to build a new cube in the FoodMart database to demonstrate how to create more than one hierarchy for a dimension.

First, you create two time hierarchies. Specifying a second hierarchy is like learning a secret handshake because the OLAP Manager treats multiple hierarchies as if each hierarchy were a separate dimension.

Start the OLAP Manager, and create a new shared dimension in the FoodMart database, as Screen 1 shows. On the first page of the Dimension Wizard, choose a single-dimension table. Then, on the next page, select time_by_day as the dimension table. On the third page, choose the Time dimension. To keep the example simple, create the first hierarchy as a fiscal calendar that starts on July 1, and the second hierarchy as a standard calendar that starts on January 1. So, on the fourth page, select Year, Quarter, Month, and Day, and select July 1 for Year starts on. When you click Next, the OLAP Manager grinds away and then gives you an opportunity to name your new dimension. Pay attention; this part is important! You need to use a two-part name, and separate the parts with a period. Type in NewTime.Fiscal, as Screen 2, page 66, shows. In the wizard, when you click Finish, you end up in the Dimension Editor. For now, accept the defaults and close the Dimension Editor window.

Now start again, and go back through the Dimension Wizard to create the second hierarchy for the NewTime dimension. Make the same choices in the wizard as you did for the first three pages. When you reach the fourth page, titled Create the time dimension levels, select Year, Quarter, Month, and Day, and select January 1 for Year starts on. On the last page of the wizard, type in the name NewTime.Calendar. When you click Finish, you're back at the Dimension Editor, which you can close to return to the main tree view of the OLAP Manager.

Then, create a cube by right-clicking on the Cubes folder in the FoodMart database. Select the popup menu option titled New Cube, Wizard. Then, in the wizard, on the page titled Select a fact table for your cube, choose sales_fact_1998. On the next page, move store_sales and store_cost to the list box on the right. This step creates measures with these names.

The next page of the Wizard is titled Select the dimensions for your cube. Move your new hierarchies (NewTime.Fiscal and NewTime.Calendar) to the list box on the right. Also, move Product, Customers, and Store to the list box on the right. Then, on the last page of the Cube Wizard, enter the name of the new cube. In Screen 3, the new cube is called Time Piece.

Clicking Finish on the final page of the Cube Wizard puts you in the Cube Editor. If you haven't used the Cube Editor before, notice that the dimensions and measures are listed on the left and you can view properties for each of these elements in the pane on the lower left of the window. On the right, you see the Fact table and Detail tables with their key field relationships. When you close the Cube Editor, the OLAP manager asks whether you want to set the data storage options now. Select Yes to enter the Storage Design Wizard. Select Multidimensional OLAP (MOLAP) as the storage type, because it's fast and space efficient. On the page titled Set aggregation options, select Performance gain reaches, type in 10, then click Start. When the OLAP Manager finishes creating the aggregations, you can click Next. On the final page, select Process Now and click Finish. This process takes a couple of minutes as it works its way through about 165,000 rows.

Now that the cube is built, you can run the MDX Sample program to see what's inside the cube. In Screen 4, look at the tree view on the middle left side of the main window. Both the NewTime dimension and the Store dimension are expanded to show their contents. You can see that the symbols are different for the items in these dimensions because the Store dimension has a single hierarchy with no name. Therefore, the display shows just the level names of the dimension. The NewTime dimension you created earlier shows the two hierarchies with the stair-step symbols.

Screen 4 also shows a query that demonstrates the structure of the NewTime dimension. This query uses the generate function to list 1997 and 1998 at the year level of the Calendar hierarchy, and for each of these year members, the query also lists the months in the first quarter. Note that each year starts with January. If you change all occurrences of the name Calendar to Fiscal in this query and rerun it, you will see that the Fiscal hierarchies start the year with July.

By using two-part names in the Dimension Wizard, you can create a single dimension with multiple hierarchies. The OLAP Manager treats these hierarchies as if they were multiple dimensions, but in the MDX Sample program, the structure is reflected as two hierarchies within a single dimension. By running two queries, you can see that the same months are organized into two different sets of quarters and years, but the aggregated values for the months remain the same. The way these multiple hierarchies are organized is exactly what you want.

Now, let's dig a little deeper into how multiple hierarchies affect the way you write MDX queries. As in Screen 4, you use the hierarchy name to further qualify the dimension name (e.g., [NewTime].[Calendar]). You might logically conclude that if you want to reference the current member, the hierarchy name is not required. According to the OLAP Services documentation, CurrentMember "returns the current member along a dimension during an iteration, rather than along a hierarchy." Why is it important to know that CurrentMember works on a hierarchy and not a dimension? This situation is just one aspect of a larger issue. What really happens when OLAP Services executes an MDX query is that it treats multiple hierarchies as if they were different dimensions. The following code demonstrates this point:

SELECT [NewTime].[Calendar].[Year].Members ON columns,   [NewTime].[Fiscal].[Year].Members ON rowsFROM [Time Piece]

The MDX query in Listing 1 demonstrates how you can put two hierarchies of the same dimension on different axes (rows and columns). If you think about listing different time hierarchies on rows and columns, the result doesn't make sense. The only combination of members that returns values in this query is one in which the months are the same. For example, the cell value defined by [Calendar] .[1998] and [Fiscal] .[1997] returns a value that represents the intersection of the two years (January through June).

Now here's the gotcha: Typically, Time dimensions don't have an All Member because summing multiple years isn't meaningful in business analysis. Usually, the root level of a Time hierarchy is Years.

Look at the FoodMart Sales cube as an example. In this case, multiple members are at the root level of the hierarchy and one of the members is the default member. Remember that OLAP Services selects the default member when you don't explicitly call out a member from that dimension in an MDX query. This situation can be a problem if you create multiple hierarchies in the time dimension and one or both of the multiple hierarchies doesn't have an All Member.

Then, imagine you want to list all the fiscal years on the rows and some other dimension on the columns. Because OLAP Services treats the Calendar hierarchy as a dimension, you need to explicitly select some member from the Calendar hierarchy or OLAP Services selects the default member for you. If the fiscal years don't overlap with the Calendar member you selected, OLAP Services returns a null (empty) value.

If you want to explore this idea further, go back to the Dimension Editor for the two hierarchies we created in this column and use the properties panel to turn off the All Member. Then reprocess the Time Piece cube. You might discover that it's difficult to create MDX queries that list time periods on the rows or columns, because OLAP Services always uses the default member from the other Time hierarchy to filter your result.

We came up with two solutions to this problem. Solution one is to use an All Member in the time dimension when you have multiple hierarchies. Solution two is to create a Calculated Member on the Time hierarchy that aggregates the top-level members, and select this member in the WHERE clause. This method requires an expensive performance hit, but it is a viable alternative.

With the help of this article, you can begin experimenting with multiple hierarchies to build flexible OLAP models.