Archive for August 2007

I’m balanced, except when I’m not

Jamie posted a question on ragged hierarchies to his blog today:  Are you balanced or unbalanced? I started thinking about it this evening, and decided to post my thoughts here. I think that, for the most part, business requirements drive should drive the decision between a balanced and unbalanced hierarchy. But there is a gray area. You can often force an unbalanced hierarchy into a balanced hierarchy by filling in the missing levels between the top and bottom. This would typically be implemented by either leaving the intervening levels blank, or copying the parent value down through each intervening level.

For example, say I have the following unbalanced hierarchy, where the DC branch rolls up directly to the North region, and the California (CA) territory has no branches:

Corporate Region Territory Branch
HQ North NY NYC
  North   DC
  North MA Boston
  South NC Charlotte
  West CA  

I can implement a balanced hierarchy by filling in the blanks, like this:

Corporate Region Territory Branch
HQ North NY NYC
  North North DC
  North MA Boston
  South NC Charlotte
  West CA CA

Sometimes this is perfectly acceptable, but it may not accurately represent the business information. That is a call that has to be made on a case by case basis. However, there are some reasons that balancing the hierarchy may be desirable:

  • Parent-child hierarchies, when used in Analysis Services, only have aggregates created for the key attribute and the All level (or top attribute, if you disable the All level). That has a significant performance impact on large dimensions.
  • If you using Analysis Services, you can still simulate an unbalanced hierarchy by using the Hide Member If property, which allows you to hide a level if it is blank, or contains the same value as its parent.
  • Parent-child table relationships, while being simple to set up at the relational database level, are difficult to query. If you are developing a data mart that end users will be creating queries against, parent-child relationships are likely to cause problems.

In general, I prefer balanced hierarchies, but mostly for technical reasons. There are certainly business cases where they just don’t apply.

2007 PASS Community Summit

At the first of this month, I received word that I will be presenting at the PASS Summit this year. They had a late push for more BI front-end presentations, and evidently they were desperate enough to accept not one, but two of mine. One will be a co-presentation with my co-worker, David Darden, on developing non-financial applications in PerformancePoint Plan. The other will be on delivering BI information through the ProClarity Dashboard Server product. It’s late notice, but I’m excited to be doing it.

Prepping for the presentations has taken up a lot of time, recently, as well as a tremendous amount of real work to do. Fortunately, I’m working on projects right now that are leveraging both technologies, so I can get some work done on both at the same time.

Between the presentations and work deadlines, it’s been tough to find much time to update my blog. I am working on a few topics for it, though, so keep watching.

If you are attending the PASS Summit, drop me an email. I’d looking forward to the chance to meet some of you in person.

SQL Server 2008 CTP 4 is Out

And you can download it from here. It looks like there are a few new improvements in Reporting Services, and some new data types in SSIS (to complement the new data types in the relational engine), but nothing too major. Hopefully, I’ll have time to take it for a spin this weekend.