Logical Data Warehouse Design - Data Warehouse Systems: Design and Implementation - page 153

Number , Quarter , Semester ,and Year are, respectively, of type DayOfMonth ,
MonthOfYear , QuarterOfYear , HalfYearOfYear ,and Year .
Attributes in hierarchies must have a one-to-many relationship to their
parents in order to ensure correct roll-up operations. For example, a quarter
must roll up to its semester. In Analysis Services, this is stated by defining
a key for each attribute composing a hierarchy. By default, this key is set
to the attribute itself, which implies that, for example, years are unique.
Nevertheless, in the Northwind data warehouse, attribute MonthNumber
has values such as 1 and 2, and thus, a given value appears in several
quarters. Therefore, it is necessary to specify that the key of the attribute
is a combination of MonthNumber and Year . This is done by defining the
KeyColumns property of the attribute, as shown in Fig. 5.25 . Further, in this
case, the NameColumn property must also be set to the attribute that is
shown when browsing the hierarchy, that is, FullMonth . This should be done
similarly for attributes Quarter and Semester .
Fig. 5.25 Definition of the key for attribute MonthNumber in the Calendar hierarchy
Fig. 5.26 Definition of the relationships in the Calendar hierarchy
When creating a user-defined hierarchy, it is necessary to establish
the relationships between the attributes composing such hierarchy. These
relationships correspond to functional dependencies. The relationships for