Alan Whitehouse's Ramblings

Continuing to work until my heavy investment in lottery tickets finally pays off….

  • Categories

  • Archives

  • Deep Thought

    Historically speaking, all true change in the world has come thanks to leaders emerging, them taking charge and giving the masses someone to rally around. Can an intentionally "leaderless" movement survive or will it just slowly fade away?

  • Subscribe

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 18 other followers

  • Me!

  • Advertisements
  • Disclaimer

    The views expressed in this blog, while intelligent and always right, are strictly my own, and do not necessarily reflect the views of anyone else with which I am in any way affiliated. And don't forget, I own the rights to all information on this blog (except for the stuff I stole from other people).
  • Current Rant


    You want change, then get involved. Vote, run for office, go to shareholder meetings and contact advertisers or investors. Sitting around banging drums, singing kumbaya, smoking weed and having a camp out under the stars is not going to get you the change you want.

  • Admin Stuff

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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: