Q:

We are using MicroStrategy7 as our reporting tool against our data warehouse which is DB2 on an IBM OS/390 mainframe. We are currently debating on which would be the best database structure for our sales hierarchy. On one hand, we are considering a recursive structure where an individual’s record includes a pointer to their immediate superior and, on the other hand, we are looking at a denormalized flat structure where all of an individual’s superiors from supervisor to director are held on their record. The problem is that we can see benefits of both structures but can't decide which is the best to go with. Your opinion would be appreciated.

A:

Scott Howard’s Answer: I can't answer this adequately without a better understand of your dimensions. I can, however, caution you not to get too cute or complex with your dimensional structures. It is admirable that you are trying to simplify your dimensional model and address potential RDBMS performance problems by collapsing the hierarchies, but this could, in turn, destroy MicroStrategy's ability to recognize the dimensional hierarchy and thus the related aggregation levels. Make sure that MicroStrategy can handle your proposed model.

Joe Oates’ Answer: While both approaches have their pros and cons, the usual approach when these structures are used with a BI presentation tool is to have a denormalized hierarchy. The reason for this is that it is much easier to do drill down and drill up with the denormalized hierarchy. If you have a reporting structure that can vary from two levels to 20 levels, then the recursive structure may be more appropriate. However, the recursive structure should include a column that indicates whether the top or bottom of the hierarchy has been reached.

Clay Rehm’s Answer: I agree that both methods will work. However, why couldn't you have both? The recursive structure is great for scalability and flexibility but is awful to write a query against. This could be your primary structure. Your denormalized structure would be built using the recursive structure as the source and is great for users who need to query that view. When the recursive structure changes (i.e., superiors are added or deleted), then the denormalized structure is rebuilt and repopulated.

The bottom line is that if you can successfully and accurately maintain both structures, you can satisfy a larger and varied audience.

Les Barbusinski’s Answer: I’m not a big fan of using recursive data structures in a data warehouse. It’s a very elegant approach but, in practice, almost always creates problems. The primary reason is that a recursive structure requires multiple SQL calls to extract a hierarchy. This is further complicated by the fact that the number of levels can be variable. For example, insurance commissions are almost always split along hierarchical lines, with the selling agent splitting his commissions with a mentor and/or one or more levels of management, depending on his/her seniority. Also, if you ask for an organizational hierarchy for a regional manager, you’re going to get fewer levels of the hierarchy than you would for a salesperson.

Since ANSI SQL does not support recursive SELECTs, it is impossible to extract hierarchies or chains from such a structure in a singleton SELECT. And, even if ANSI SQL did allow you to "chase chains," how would a business intelligence tool (such as MicroStrategy) – that dynamically generates SQL – know how many levels of the hierarchy to extract?

Of the two options you’re considering, the best bet is to utilize the "flat" structure. However, given that you’re using MicroStrategy, you may want to consider using a snowflake schema, since this is the tool’s preferred data structure.

Chuck Kelley’s Answer: While there are pros and cons to each option you present, I would tend to lean toward the recursive structure for the data warehouse and the denormalized version for the data mart that MicroStrategy7 reports against. The reasons are 1) that as the organization changes, you can always rebuild the denormalized version from the recursive structure and the recursive structure may be easier to keep up to date. 2) I am not convinced (contrary to what MicroStrategy may have told you) that MicroStrategy7 can deal very well with the recursive structure in an efficient manner. It can deal with a denormalized (star) structure although not as efficiently as a snowflake structure.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access