Understanding Hierarchical Relationships

Archibus supports hierarchical data relationships that are fixed and/or of limited depth. Such fixed hierarchies are common in cost accounting categories (cost centers, financial chargeback hierarchies) that companies use to record costs from year-to-year in apples-to-apples fashion. Examples of fixed hierarchies are Department-Divisions, Rooms-Floors-Buildings, and Work Requests – Work Orders.

These fixed hierarchies can be rolled up arbitrarily high by using “bridge” tables -- as departments and divisions can be rolled up by the Business Unit table; and as properties can be rolled up by using site, city, state, region, and country as bridge tables.

However, some business situations require more flexible hierarchies and also need an interactive way of assigning elements, like rooms, costs, and tasks, to a hierarchy that may be many levels deep. These hierarchies can be used during design and construction -- where the levels of abstraction of the problem keep changing and elements keep migrating between owners and between levels of the hierarchy -- or in strategic planning -- where organizational units are dynamic and are reconfigured year-to-year in order to flexibly pursue refinements in the company’s strategic mission. An example of such a dynamic hierarchy is account codes that roll up to other account codes.

To provide this flexibility, Archibus also supports dynamic (n-level) hierarchy fields. This is known as an unlimited-tier hierarchy or a dynamic hierarchy . A corresponding OCX Archibus Aware Control enables users to access, restrict, and enter values into this hierarchy by means of an interactive tree control.

Use Cases

Some situations that may use dynamic hierarchies are:

  • Organizational Hierarchies . Rolling up room, and other asset, assignments by an arbitrary number of organizational levels.
  • Costs . Rolling up costs by a hierarchical set of account codes.
  • Action Items. Rolling up tasks for projects by nested levels of milestones and objectives.
  • Equipment and Building Components. Categorizing and summing equipment information by hierarchies, such as CSI, that describe how equipment or building components assemble into facility classifications and sub-classifications.
  • Equipment. Grouping equipment by System assemblies and sub-assemblies. Bills of Material (BOM) hierarchies are similar to such equipment component hierarchies.
  • Employee/Manager. Providing for tracking hierarchical management relationships for employees.

Types of Dynamic Hierarchies

In order to discuss how these hierarchies can be modeled it is useful to look at how such hierarchical information is typically recorded. There are typically three types of primary keys used in hierarchical tables. The three types of hierarchical primary keys are

  • substring -- where fixed substrings within the primary key field denote the levels.
  • concatenated -- where each level adds a delimited string to the primary key field.
  • parent-child – where each record has a field for a parent key that validates on the same table.

A substring example is CSI Uniformat:

  • A -- tier 1 is denoted by the first character
  • A10 -- tier 2 is denoted by the first through third characters
  • A101 -- tier 3 is denoted by the first through fourth characters
  • A1011 -- tier 4 is denoted by all the characters

Concatenated keys are similar to substrings except that they use a delimiting character between levels rather then having distinct counts of characters between levels.

  • 10 -- tier 1
  • 10-10A -- tier 2
  • 10-10A-1011 -- tier 3

A parent-child hierarchy is one in which there is no hierarchical pattern to the primary key of each record. The hierarchy must be determined by a relationship value that is included in the record in another field. This is true of Equipment assemblies, Bills of Material, CSI Masterformat, Project Tasks, and PeopleSoft Organizational levels.

An example of parent child data is:

Primary Key Parent Key
10
10A 10
1011 10A

Modeling

Each of the above three types of hierarchies can be modeled in the same way: a hierarchy key value is stored with each record in the table.

For the substring example records might be:

Primary Key Hierarchy Keys
A A|
A10 A|A10|
A1010

A|A10|A1010|

A1011 A|A10|A1010|A1011|

For the concatenation example records might be:

Primary Key Hierarchy Keys
10 10|
10-10A 10|10A|
10-10A-1011

10|10A|1011|


An example of parent-child records might be:

Primary Key Hierarchy Keys
HQ-Elev1 HQ-Elev1|
LiftAssembly123 HQ-Elev1| LiftAssembly123|
Motor3452

HQ-Elev1| LiftAssembly123|Motor3452|

Pully-BeltAssembly21 HQ-Elev1| LiftAssembly123| Pully-BeltAssembly21|