Levels

A level is a collection of attributes related to one aspect of a hierarchy. For example, a Region hierarchy can contain States and City levels.

For more information about attributes, see Attributes.

You can define an All level at the highest level of a hierarchy. An All level contains a single member that aggregates data from all members in the child levels of the hierarchy. For example, you can include an All level in a Region hierarchy that aggregates data for all cities, in all states, in all regions.

Important: There are many ways to model a hierarchy using levels. Whether you follow best practice or different modeling techniques, it is important that you define each level so that the level key attributes uniquely identify the values in that level.

Best practice modeling

Both star and snowflake schemas can be used to implement best practice modeling. For example, in a star schema the relational data for each dimension is stored in a single dimension table that contains ID columns for each of the levels in the dimension, and each ID column uniquely identifies the values in the level. You might have a single dimension table for the Region dimension that contains the following columns:

Table 1. Example of a single dimension table using best practice modeling
Columns in a best practice Region dimension table
City ID (Primary key)
City name
City mayor
State ID
State name
State governor
Region ID
Region name

Alternative modeling

If you do not have unique ID data columns for each level in your hierarchy, you must be careful when you define the level key attributes for each level. For example, you might have a single dimension table for the Region dimension that contains the following columns:

Table 2. Example of a single dimension table using alternative modeling
Columns in an alternative Region dimension table
City ID (Primary key)
City name
City mayor
State name
State governor
Region name

You can create a hierarchy that contains Region, State, and City levels, like in the best practice modeling example. However, you must carefully define the level key attributes to ensure that each row in the level can be uniquely defined. For example, City name does not uniquely define the City level because there are cities with the same name in the United States and in England. The only way to uniquely define the City level is with the combination of the Region name, State name, and City name attributes, as shown in the following table.

Table 3. Example of unique level key attributes using multiple columns
Level	Level key attributes	Level related attributes
Region	Region name
State	Region name, State name	State governor
City	Region name, State name, City name	City mayor