Each knows them well, Hierarchies in Analysis Services 2005. With a hierarchy can be certain dates at various levels to the final show. A famous example is:

Year -> Quarter -> Month -> Day

Because I think it is rather unclear how exactly to configure it seemed convenient here a little blog to write about.

This knowledge I gained during a course at Info Support and in practice to apply a project with OLAP Cubes. In the example below I assume are using a date dimension that is similar to the following:

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

2. Create a new dimension by right clicking Dimensions -> New Dimension. The wizard will start. Choose the dimension type for 'Time Dimension and choose the table that you like in your datasource-view is added, the DimDatum so. You will then display below. Enter the appropriate values, much as in the example. This can also customize later.



Then you get this screen:




Give your scale a nickname, such as: Date. If all went well, the generated dimension appear in your list of dimensions.

Now you probably think, is that it? No, that there are still some very significant changes are made that you certainly remember if you do not know, namely the relationships between the hierarchies set. In addition, there are some keys are well put that one years + months as unique.

3. Open the dimension. With the circle on the picture you see that the relations are not good. You can see this quickly on the head!




What we do is put good relations.

4. Fold the left side of your screen DatumID open (or the primary key of your date dimension). You will now see all the attributes associated with the key.

5. Drag the parent attribute in the child. In our case drag years under Quarterly, then under Quarterly Month Month and Day below. You do this all on the left side of your screen is nothing to drag the middle!

6. Make sure that the names of your hierarchy similar to the picture in the middle. I use my example and VanJaar Month Number Month Name. The hierarchy we setup on the integrity level of the dimension so we use numbers vanjaar months and quarterly number of years. This should also be visible in the middle of the screen where the actual hierarchy is built!




7. If all goes well you get the following picture. You can see that the cubes are interconnected with arrows, this means that relations are good! Of course, you can create as many hierarchies as you want.




8. Now we must put the keys properly. One month is not unique! The same goes for a quarter. One month does happen in every year! On the left side of the attribute "VanJaar Month Number" and then click Properties. A big list of properties on the right side of your scheme. The property that you need is: "KeyColums. Click on the ... button on the property. You get this screen:




Rivet left the "Add" and look at SourceForge for "Year". What you do is a combination of months + years so it is unique.




Om te testen of het goed is geconfigureerd kun je de Dimensie even deployen/processen en op de "Browse" knop drukken. Click OK! Repeat for the Quarter Number VanJaar attribute. To test whether it is configured, you can also deploy the Dimension / processes and on the "Browse" button. If all goes well you get the picture below to see! You get the numbers for the months January to 12 to see and quarters 1 through 4. This is not so neat because we want to see the month names and Quarterly numbers!

9. Do this with the property "NameColum. Suppose we take VanJaar Month number as an attribute then we fill in Column Name: Name Month. This is good if you can choose from the list!

Example:




If it does not appear as above then chances are that your keys or hierarchy is not set correctly!

Multiple hierarchies and the configuration of the keys
Hennie asked how to configure the keys if you want to use multiple hierarchies in a dimension. I have just looked and the answer is quite simple.

Suppose we have a dimension dimmable. In this dimension, we have two hierarchies:

• Job Type -> Function Name
  
• Job Level -> Function Name
  

We have to make two combinations of keys, one for both the hierarchy. What you can do best is the first hierarchy configuration as described above. To configure the second hierarchy, do the following:
1. Drag your highest level from the right toward the center, a new hierarchy appears (in my case Function Level). Then drag from right below the second level (function name). If all is well recognized that the program you want to use function twice now and he makes a new attribute of the same name, followed by the number 1. I now get an attribute on the left: Position Name 1. Then I configure it the same way, so I drag the parent item under Function name.

Hennie also asked for an example of a time dimension by more than one hierarchy, see screenshot: