Teradata BI Repository does not handle ragged or skip-level hierarchies. A ragged hierarchy is where leaves are not all at the lowest level. A skip-level hierarchy is where some low level leaves do not descend from an immediately-above parent level. For information on how to model hierarchies, see Creating a Hierarchy for a Dimension.
We recommend altering your model in the fact table and dimension tables to avoid anomalies.
Use the following examples to organize hierarchies, levels, and members so skip-level hierarchies do not occur:
- Some U.S. cities, such as Washington, D.C., do not belong to a state because they are considered a district. If you create a separate level in the hierarchy for Washington, D.C., a hole or gap in the hierarchy is created. Consider categorizing Washington, D.C. as a state. Even though it is officially a district, there are no other districts in the United States structure so this would be acceptable and does not create a hole or gap in the hierarchy.
- Repeat the leaf name up at the level with the hole. Although this strategy does not work if your hierarchy contains names of U.S. cities. For example, associating Washington, the city, with the state of Washington would not be accurate.
- Copy the name of the parent of the hole down into the hole level. This might be a strategy applicable to skip-level and ragged hierarchies.