Dimension Hierarchy Support in Data Warehouse (1)

This is my first post about dimension hierarchy support in a data warehouse.

I will first starting with the requirement assumptions in this post and later posts will talk about the implementations.

Dimension is mainly about “View By”,  “Group by”, and “Filter By”.  You say that you want to view your last year sales by regions.  Last year is a filter and “By region” is the “view by” or “group by”, so there are two dimensions involved here:  the Calendar dimension and the Region dimension.

Each year, such as year 2010, is a dimension member in the Calendar dimension.  Each region, such as the East Region, is a dimension member.

Sometime there are hierarchical relationship among the dimension members.  for example, year 2010 is a dimension member, and the month “January 2010″ is also a member and we know that the member year 2010 can be related to another 12 dimension members.  the data for the dimension member year 2010 can actually further break down by those twelves members.  we call this relationship between the member year 2010 and the member month January 2010  is a hierarchical relationship.  The year 2010 is a parent member and the  month “January 2010″ is a child member.

The relationship is useful in BI since you can see where the data come from.  Basically if you know that the year 2010 is consistent of 12 child members, January 2010, February 2010, …, to December 2010, it would be great if BI allows you to drill from the group by view with the year 2010 to the view by the child members of year 2010.

If there is hierarchical relationships among regions, it would be great that when you view any region in your report, you can further see what are the other regions that the region is consist of and see the details, especially see how the figure is made from.

For example, if you see the sales for 2010 for the East region is 21M and the east region is consist of three child regions, region A, region B, and region C.  You may want to see how this 21M come from.  Whether the figure is 7M for each region, or the figure is actually unbalanced among regions may mean different for you and different action plan may come up.

The requirement assumptions are

• Dimension Hierarchy is for supporting drill down reporting.  You should be able to drill into a dimension member and see the further details about the member.
• Dimension hierarchy let you see the break down.  Browsing the dimension members is not the main purpose.  The purpose of having the dimension support in BI is for viewing the metrics along with the dimension.
• The number that is associated with the parent dimension members would typically be a number that can be added up from the child members.

Next post, I will talk about BI tool implementation.

Advertisement