Data Warehousing Fundamentals # 2

One of the design fundamentals in data warehousing (DW) is ability to drill down.  The term is really a part of DW jargon so I would like to explain it more fully.  I would like to use an example of a retail store which keeps data on products sold, customer, purchase price, date, quantity purchased, total purchase price.  Each one of these categories of data is called a dimension.  If you would like to know how many customers bought this product on a particular day, you need to drill down into the details in that dimension.  Then total up the quantity sold.  That is a simple case of drilling down.

Now drilling across is different and the idea is that you move across dimensions.  If you would like to know total sales on a date for all products, you would add sales on that date.   That is simply drilling across.

Another way of thinking about the data being stored in matrix with many dimensions and data being stored in each dimension.  A simple two dimensional matrix of customer and product is simple to visualize.  However visualizing the huge numbers of dimensions common in a business is very difficult.  I think this complexity makes it hard for businesses to keep all the complexity of their business their mind.  They then create a simpler model of key success indicators.   The DW could then report on these key indicators.  However when one of these indicators is awry, we then need to drill across divisions or departments to find out which part of the business is causing the problem.   When we find the division that has a problem we need to drill down into the division to find the source of the problem.

I suggest you try to develop your own mental model of what all this means for you in your business and your role in the business.  I think developing a good mental model of these complex processes is very important and reviewing the fundamentals will help develop these models.  The big difficulty is not resorting to jargon as we try to communicate these ideas.


  1. Jim Reply

    I hope you did not think those ideas were fundementals, Graham. I will however go to wikipedia to try to find out what the heck you are talking about.
    I remember one of my physics profs was trying to teach us stuff that was advanced for us but fundemental to him. We just could not connect. Maybe you and I have the same problem.

  2. Graham Boundy Reply

    Next we need to start visualizing a star and snowflake schemas. Which is a fancy way of saying a multi-leveled dimensional hierarchy with many dimensions (i.e. many hierarchies).
    In the store example your dimensions are something like:
    – Date/Time (Year, Quarter, Month, Week, Day, Hour, Minute, Second,…)
    – Region to locations (Country, State, County, Municipality, Village, Neighbourhood, Address…)
    – channel ( store, mail order, internet…)
    – purhcaser gender (male, female)
    – purchaser age (0-18, 18-24, 25-35, etc)
    Knowledge of your business, reporting requirements and Ockham’s Razor (’s_Razor) kick in after about the 4th or 5th dimension.

Leave a Reply

captcha *