Choosing between an Elements table vs. Elements and Relations tables for hierarchies

When you create hierarchies in your OLAP model, you are prompted to select either an Elements table, which contains parent information, or Elements and Relations tables, which are separate tables to define relationships between elements.

If you maintain your hierarchy only through the user interface, the choice between selecting only an Elements table or adding a Relations table is functionally equivalent. The storage is handled by the OLAP Modeling user interface and the user interface behavior remains consistent across both options.

When you load hierarchies from a source system, base your table selection on how relationships are structured in the source rather than on hierarchy complexity.

To select an optimal method, focus on how data is maintained and source system is structured. Regardless of which method you select, OLAP Modeling ensures consistent user experience.

Selecting an Elements table only

When you select an Elements table only, the table stores all hierarchy elements, their attributes and parent-child relationships.

Selecting an Elements table without a Relations table streamlines the process of designing, maintaining, and understanding dependent source data.

Select an Elements table without a Relations table in these scenarios:

Your hierarchy is flat. No parent-child relationships exist.
Your hierarchy is simple or moderately complex with only one parent per element. For example, organizational charts.
Your hierarchy is stable with minimal re-parenting or structural changes.

Selecting an Elements table only is not suitable for complex hierarchies with multiple parents because it can result in denormalized data.

Selecting Elements and Relations tables

When you select Elements and Relations tables, all hierarchy elements and their attributes are stored in the Elements table. Parent-child relationships are stored in the Relations table.

Selecting Elements and Relations tables streamlines relationship maintenance without modifying the Elements table.

Select Elements and Relations tables in these scenarios:

Your hierarchy is complex with multiple parents.
Your hierarchy structure changes frequently and requires re-parenting and restructuring without having to modify the Elements table.