Difference between hierarchy and levels

Question

I have just started working on Mondrian. I am having a hard time understanding hierarchies and levels. What is Difference between a level and a hierarchy of a dimension in a cube ? Why do we need to have level inside a hierarchy while building a Mondrian schema ?

Thanks for reading

Answer 1

Taken from Pentaho Mondrian Documentation

• A member is a point within a dimension determined by a particular set of attribute values. The gender hierarchy has the two members 'M' and 'F'. 'San Francisco', 'California' and 'USA' are all members of the store hierarchy.
• A hierarchy is a set of members organized into a structure for convenient analysis. For example, the store hierarchy consists of the store name, city, state, and nation. The hierarchy allows you to form intermediate sub-totals: the sub-total for a state is the sum of the sub-totals of all of the cities in that state, each of which is the sum of the sub-totals of the stores in that city. -A level is a collection of members which have the same distance from the root of the hierarchy.
• A dimension is a collection of hierarchies which discriminate on the same fact table attribute (say, the day that a sale occurred).
• For reasons of uniformity, measures are treated as members of a special dimension, called 'Measures'.

An example Let's look at a simple dimension.

<Dimension name="Gender" foreignKey="customer_id">
  <Hierarchy hasAll="true" primaryKey="customer_id">
    <Table name="customer"/>
    <Level name="Gender" column="gender" uniqueMembers="true"/>
  </Hierarchy>
</Dimension>

This dimension consists of a single hierarchy, which consists of a single level called Gender.

The values for the dimension come from the gender column in the customer table. The "gender" column contains two values, 'F' and 'M', so the Gender dimension contains the members [Gender].[F] and [Gender].[M].

Another example shows example of time dimension:

Here is an example of a time dimension:

<Dimension name="Time" type="TimeDimension">
  <Hierarchy hasAll="true" allMemberName="All Periods" primaryKey="dateid">
   <Table name="datehierarchy"/>
    <Level name="Year" column="year" uniqueMembers="true" levelType="TimeYears" type="Numeric"/>
    <Level name="Quarter" column="quarter" uniqueMembers="false" levelType="TimeQuarters"/>
    <Level name="Month" column="month" uniqueMembers="false" ordinalColumn="month" nameColumn="month_name" levelType="TimeMonths" type="Numeric"/>
    <Level name="Week" column="week_in_month" uniqueMembers="false" levelType="TimeWeeks"/>
    <Level name="Day" column="day_in_month" uniqueMembers="false" ordinalColumn="day_in_month" nameColumn="day_name" levelType="TimeDays" type="Numeric"/>
  </Hierarchy>
</Dimension>

Also note that dimension can have more than one hierarchy:

A dimension can contain more than one hierarchy:

<Dimension name="Time" foreignKey="time_id">
  <Hierarchy hasAll="false" primaryKey="time_id">
   <Table name="time_by_day"/>
    <Level name="Year" column="the_year" type="Numeric" uniqueMembers="true"/>
    <Level name="Quarter" column="quarter" uniqueMembers="false"/>
    <Level name="Month" column="month_of_year" type="Numeric" uniqueMembers="false"/>
  </Hierarchy>
  <Hierarchy name="Time Weekly" hasAll="false" primaryKey="time_id">
    <Table name="time_by_week"/>
    <Level name="Year" column="the_year" type="Numeric" uniqueMembers="true"/>
    <Level name="Week" column="week" uniqueMembers="false"/>
    <Level name="Day" column="day_of_week" type="String" uniqueMembers="false"/>
  </Hierarchy>
</Dimension>

We can also simplify simple schema in the documentation:

<Schema>
 <Cube>
  <Dimension Gender>
    <Hierarchy>
      <Level Gender>
    </Hierarchy>
  </Dimension>
  <Dimension Time>
    <Hierarchy>
      <Level Year/>
      <Level Quarter/>
      <Level Month/>
    </Hierarchy>
    <Hierarchy>
      <Level Year/>
      <Level Week/>
      <Level Day/>
    </Hierarchy>
  </Dimension>
  <Measure Unit Sales/>
  <Measure Store Sales/>
 </Cube>
</Schema>