Hierarchies in Analysis Services 2005

Everyone knows them: hierarchies in Analysis Services 2005. With a hierarchy you can show certain data to the end user at different levels. A well-known example is:

Year -> Quarter -> Month -> Day

I find it rather unclear how exactly you configure this, so I thought it would be useful to write a short blog about it.

I learned this during a course at Infosupport and by applying it in practice in a project with OLAP cubes. In the example below I assume you use a date dimension that looks roughly like this:

1. Create a new Analysis Services project and configure a data source and a data source view. Add the date table from the data warehouse to the data source view, for example DimDatum or DimTime.

2. Create a new dimension: right-click Dimensions -> New Dimension. The wizard starts. For the dimension type choose ‘Time Dimension’ and select the table you just added to your data source view, so DimDatum. You then get the screen below. Fill in the right values, roughly as in the example. You can also change this later.

Next you get this screen:

Give your dimension a name, for example: Datum. If everything went well, the generated dimension appears in your list of dimensions.

Now you probably think: is that it? No. A few very important changes still have to be made, and you will surely forget them if you do not know about them: setting the relationships between the levels of the hierarchy. Also, some keys must be set so that a year + month is unique.

3. Open the dimension. The circle in the picture shows that the relationships are not set correctly. This is easy to overlook!

What we do now is set the relationships correctly.

4. On the left side of your screen, expand the DatumID (or the primary key of your date dimension). You now see all attributes that are linked to the key.

5. Drag the parent attribute under the child. In our case you drag Year under Quarter, then Quarter under Month and Month under Day. You do all of this on the left side of your screen, so do not drag anything to the middle!

6. Make sure the names in your hierarchy are the same as in the picture in the middle. In my example I use MaandNummerVanJaar and MaandNaam. We configure the hierarchy at the integer level of the dimension, so we use maandnummervanjaar and kwartaalnummer van jaar. This must also be visible in the middle of the screen, where the actual hierarchy is built!!

7. If all is well, you see the image below. The square boxes are now connected with arrows, which means the relationships are correct! Of course you can create as many hierarchies as you like.

8. Now we still have to set the keys. A month is not unique! The same goes for a quarter. After all, a month occurs in every year! On the left, click the attribute “MaandNummerVanJaar” and then properties. A long list of properties appears on the right side of your screen. The property you need is: “KeyColumns”. Click the … button next to the property. You now get this screen:

Click “Add” on the left and look for “Jaar” under Source. What you do now is make a combination of month + year, so that it becomes unique.

Click OK! Repeat this for the KwartaalNummerVanJaar attribute. To test whether it is configured correctly, deploy/process the dimension and press the “Browse” button. If all is well, you see the image below! You now see the numbers 1 to 12 for the months and 1 to 4 for the quarters. Of course this is not very neat, because we want to see the month names and the quarter numbers!

9. You do this in the property “NameColumn”. Say we take MaandNummerVanJaar as the attribute, then we fill in MaandNaam for NameColumn. If all is well, you can pick it from the list!

Example:

If it does not show as above, chances are that your keys or hierarchy are not set correctly!

Multiple hierarchies and configuring the keys
Hennie asked how to configure the keys when you want to use multiple hierarchies in one dimension. I had a look at this, and the answer is quite simple.

Say we have a dimension DimFunctie. In this dimension we have two hierarchies:

• Functie Type -> Functie Naam
• Functie Niveau -> Functie Naam

So we now have to make two combinations of keys, one for each hierarchy. The best thing to do is configure the first hierarchy as described above. To configure the second hierarchy, do the following:
1. Drag your highest level from the right side to the middle, and a new hierarchy appears (in my case Functie Niveau). Then drag the second level from the right under it (Functie Naam). If all is well, the program sees that you want to use Functie twice and creates a new attribute with the same name, followed by the number 1. So I now get an attribute on the left side: Functie Naam 1. Then I configure it the same way, so I drag the parent item under Functie Naam.

Hennie also asked for an example of a time dimension with more than 1 hierarchy, see the screenshot: