Business hierarchies are the “crown jewels” of an analytic-driven organization.  They encode the analytic framework for marketing, product and sales management, and competitive benchmarking.  This project will walk you through the steps to create a high-performing EDW model for dynamic, flexible business hierarchies.  The project will provide technical artifacts for a Netezza DW appliance solution, but the modeling concepts are applicable to other EDW platforms.  I will post on this site progress of this project journey, and invite your suggestions and stories from your own experiences.  You can reply using the commenting dialog at the end of each post.

In this project I will layout a modeling approach and a prototype for hierarchies that work the way you want, and they way Chelsea in finance wants, and Joe in marketing wants, and your merchandising director and suppliers want.  The result is a compendium of approaches I have used at various clients in several industries over the last six years, born out of lot of listening and technical experimentation, and tempered with published research from DW gurus like Ralph Kimball and Joe Celko.  I’m certain to have violated some rules along the way, but my view is that things that actually get used leave a trace of broken rules in pursuit of the greater good.

First, the requirements.  Below is a list of “We needs” that come up time again when discussing hierarchy requirements with business folks:

• We need to report the same sales data by different roll-up hierarchies
• We need hierarchies that can report summary results consistently across business units.
• We need hierarchies that support a variable number of levels (aka, “ragged hierarchies”)
• We need to link related hierarchies–for example, sales organization hierarchy linked to product hierarchy
• We need to track hierarchy changes so we can report based on the hierarchy structure today, or at any historical date.
• We need to evaluate the impact of planned hierarchy changes simultaneously with current reporting.
• We need to integrate external product or customer data with our internal hierarchies.
• We need to update hierarchies with known changes in advance of the delayed feeds from external data suppliers.
• We need to navigate the hierarchy in various ways depending on the business question.
• We need to dynamically aggregate facts by hierarchy level or node.
• We need to track multiple occurances of attributes such as addresses, industry codes, etc.
• We need to handle hierarchy nodes with more than one parent (e.g, a store served by two distribution centers).
• We don’t want to update the fact table(s) when a hierarchy changes.
• We don’t want to rely on “meaningful identifiers” that become outdated and lead to confusion.
• We need to build in health checks alert us to inconsistencies in the hierarchies that could distort reporting.
• We need a standardized approach to building hierarchies that is repeatable and easy to maintain.
• Importantly, we need to make the user interface simple and intuitive.

Whew!  That is a tall order.  When hierarchies are inflexible or difficult to change, it is nearly impossible to address more than a few these needs.  Many organizations learn to live with inflexible hierarchies.  We must change that paradigm.  The value of business nimbleness that flexible hierarchies enable will far exceed the the investment to create them.

***********

Look for upcoming posts describing a conceptual architecture for flexible hierarchies, and demonstrating an industry use case.  In the meantime, please send me hierarchy challenges you have seen in your own organizations, and I’ll reply and/or incorporate them into the demo.  Use the commenting box below.