Hierarchies: Data relationship organization

Hierarchies in a logical data model are ordered groupings of attributes arranged to reflect their relationship with other attributes. Usually the best design for a hierarchy is to organize or group attributes into logical business areas. For example, you can group the attributes Year, Month, and Day to form the Time hierarchy.

In a logical data model, hierarchies contain attributes that are directly related to each other. Attributes in one hierarchy are not directly related to attributes in another hierarchy.

For example, Year and Quarter are attributes that are usually directly related to each other. One year has many quarters and both attributes are in the Time hierarchy.

Year and Customer are attributes that are usually not in the same hierarchy and are not directly related to each other. However, if you want to create a report that shows information about customer purchases in a particular year, there must be some way to determine how these two attributes are related. Year and Customer are related through a fact. It is the existence of a fact that ties the Time hierarchy to the Customer hierarchy. In this case, the fact is a customer purchase.

Therefore, facts exist at the intersection of hierarchies. They are identified by multiple attributes, which represent the level at which a fact is stored. A graphical example of how facts, attributes, and hierarchies are related and form a complete logical data model is shown in Sample data model.

For a discussion about hierarchies, see Creating Hierarchies to Organize and Browse Attributes.