The Reality of Managing Hierarchies
Posted by Alan on January 19, 2010
When building a solution, whether it be on the analytical side or on the planning side you are going to have to bring in data from one or more host systems. In real life, odds are that some of these systems will not have pre-built cubes or the pre-developed cubes will not meet your needs and and you will have to either create from new or modify existing.
Now creating cubes is not big of a deal and if you are not comfortable using BIDS there are various 3rd party products that are designed to help facilitate the creation of cubes. However, what can cause you headaches is the long-term management of dimension hierarchies. Specifically the management of hierarchies that tend to change on a regular basis such as natural accounts, products, sales staff, customers, etc.
The problem lies in the fact that many host systems (i.e., ERP, CRM, etc.) do not provide a built in mechanism for managing hierarchies. For instance, in Dynamics GP, which is the ERP system we implement there is no standard mechanism for managing how natural accounts roll into one another. The concept of a hierarchy is foreign to the system and all account roll-ups happen via “hard coding” accounts or account ranges in the reporting environment being used. This hard coding of ranges works fine most of the time, but if you add a new account to the system that does not follow the pre-determined rules for ranges then you are forced to manually adjust the report.
The same holds true of any cube you design. If your host system can’t tell you where a new dimension member should fall in the hierarchy you are forced to fall back to the hard coding method. Unfortunately this method is imperfect at best because I guarantee that over time new dimension members will be added that don’t meet the pre-defined rules and you will have to go back and tweak your cube design to accomodate. Now if you are a service provider who gets paid for each change that has to be made, then this is a great thing. However, if you are an end user, the extra cost and turn around time may become a big hassle.
So how do you avoid this problem? The most important thing is to make sure you realize it is a problem. Much like the old G.I. Joe cartoon — knowing is half the battle. After that, you can plan and budget for solutions that will help make the management more of an end-user or business analyst task rather than an IT task. It could be as simple as making minor modifications or adding additional fields to your host system that can be picked up by the cube. Or you might need to look at a more encompassing solution like Master Data Services from Microsoft. Either way, it is important to recognize that when someone says “sure we will just build you a cube, no big deal” that the initial build is the simple part and it is the ongoing maintenance that can make or break the success of the solution.