Natural Hierarchies in Period Dimension

This Period Dimension hierarchy containing Year, Quarter, Month and Date also has a yellow warning sign.

This is the present Attribute Relationship

Every attribute has a relationship to the Key Attribute which in this case is The Date.

First we delete the existing relations from The Date to Quarter Name and from The Date to Year Number.

Now we drag Month Name to Quarter Name to define that Months roll up into Quarters.

Finally we drag Quarter Name to Year Number.

Now the Attribute Relationship reflects reality – there is a one-to-many relation between each Attribute – a so called Natural Hierarchy.

Building Natural Hierarchies can improve both query and processing time – it is essential to working with performance.

The yellow warning sign has disappeared from the Hierarchy

If we try to process the Period dimension we will however receive an error.

Because Quarter Name no longer has relations to the Key attribute – but only to Year Analysis Services is not able to identify Q4 as a unique value.

We have to define a composite key to make sure that Quarter Name is unique – the Month Name has the same problem – January could be January in any year, it is not unique.

First we take care of the Quarter Name. In the Properties of Quarter Name, we change the KeyColumn:

By choosing YearNumber and QuarterNumber as Key Columns we make a composite key which is unique.

The KeyColumn is now a Collection…

…so we have to specify NameColumn as Quarter Name…

…and finally we make sure that Order By has been set to Key.

On the Month Name we also create a Composite key from Year Number and Month Number.

Finally the Description property of the Hierarchy needs to be changed to reflect the changes of the attributes.

The syntax can be written in more than one way. This reflects the new composite keys at different levels.

Now we are ready to Process the Period dimension again.

After processing the Cube we should test that Dynamic Time still works in the TARGIT Client.