Dimensions, Hierarchies, and Levels

Dimensions, attributes, hierarchies, and levels, are the way we define our business model in MDX. They represent a meta-definition of our data, similar to tables and columns in SQL.

Dimensions represent our main business concepts and are a generalization of concrete entities (Geography, Time, or Products). Attributes are used to capture relevant details about the dimension. For example, color, category, sub-category, price, and size, are all attributes which are used to capture the details of the Dimension Product. Similarly, date, month, year, hour, minute, and seconds, are the attributes used to capture the details of the Time dimension.

Attributes have relationships with one another. A day has 24 hours, an hour has 60 minutes, and a minute has 60 seconds. When the attributes are organized in order to represent their relationship with one another, a hierarchy is formed.

Hierarchies are not specific to time dimension. A continent has countries, a country has states, and states have cities.

One of the key tasks when doing dimension modeling is to identify the attributes which are needed to fully capture the data related to a dimension. And to identify how those attributes relate to one another.

It is not necessary for a dimension to have a single hierarchy. In common business cases you will see that dimensions can contain many hierarchies.

As we would like to generate some reports taking into account the geographical as well as the economic properties of the countries, we will define two separate hierarchies (i.e. geographical and economical) within that dimension.

For the geographical hierarchy, [Geography], we want to see the continents, countries, and cities. This directly defines the three levels of our hierarchy, [Continent], [Country], and [City].

For the economic hierarchy, [Economy], we want to see the economic partnerships (EU, NAFTA) and the countries. This defines two levels of our hierarchy, [Partnership] and [Country].

Both hierarchies do not need to define the same number of levels nor share the same levels. In our example, we could have the economic hierarchy without the country level and/or have two of the same country in two economic partnerships.

Using MDX syntax, we will represent this structure:

Dimension
        [Geography]
Hierarchy
        [Geography].[Geo] and [Geography].[Economy]
Levels
        [Geography].[Geo].[Continent],
        [Geography].[Geo].[Country],
        [Geography].[Geo].[City],
        [Geography].[Economy].[Partnership],
        [Geography].[Economy].[Country]

We have now finished with our meta-definition. Let's see how to create some actual data as described in the next chapter.

( Do you want to try this out live? Our Community Edition is available here. )