SQL Server Magazine

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.

   Prev. page   [1] 2     next page