Designing dimension hierarchies: Natural or Unnatural

Question

I'm using Analysis Services and when designing dimensions I'm never sure how far to go to build natural hierarchies.

What I mean is I've added in all the genuine attribute relationships. So most hierarchies are natural anyway but the most commonly requested hierarchy is 3 or more levels with a middle level as a slowly changing attribute.

The scenario is tracking jobs. The job has many attributes which are all static but the Debtor attribute (i.e. who's paying the invoice) can change over the course of the job. So the hierarchies look like this

 - Manager -> Debtor -> Job Name
 - Director -> Debtor -> Job Name 
 - Office -> Debtor -> Job Name 
 - Office -> Manager -> Debtor -> Job Name

So within the dimension there are many hierarchies that start with static attribute(s) of the job followed by the debtor (wich slowly changes) with the job name (dimension key) at the bottom.

So what we do at the moment to "naturalize" these hierarchies is create "fake" attributes for each debtor that appears in a hierarchy that is a combination of the attributes above it. e.g. for the first example above the Debtor level attribute would have a key of Manager and Debtor id's. And for the last example the Manager level would have a key of Manager and Office and the Debtor level attribute would have a key of Office, Manager & Debtor. We then hide all these attributes so they are only used in the hierarchies.

So this makes our dimensions a lot more complicated but we do get the benefit of extra performance on our queries. Often this is a noticeable improvement. Apart from complexity we constantly hit problems because we now have multiple versions of a "Debtor" and the key of the attribute is not the id of the debtor. So this affects Drillthrough and Reporting actions as well as making certain types of calculation more difficult if we want to change behaviour for certain levels.

The clients we use are Reporting Services, Excel and Office Web Components.

I've been told that on early versions of SQL 2005 complex queries involving unnatural hierarchies could result in the server getting completely tied in knots which is another reason we've gone to great lengths to avoid unnatural hierarchies.

Also, the exclamation mark design warning is so dramatic in Visual Studio that it seems like a really bad thing to have unnatural hierachies.

What do other designers do in these situations? How far do you go to avoid unnatural hierarchies?

Answer 1

The way to do hierarchies in a slowly changing dimension on a SSAS cube is to synthesize a pseudo-hierarchy with actual keys hidden behind the scenes but just showing the attributes as if they were keys.

Office     Manager    DebtorKey  Debtor      JobKey   Job Name    From        To
Scunthorpe Bloggs     101        Scarper&Co  2001     Fixit       2010-01-01  2010-01-31
Scunthorpe Bloggs     102        Bodgett     2002     Fixit       2010-02-01  9000-01-01

This hierarchy gets constructed over the original slowly changing dimension and is used to do the attribute relationships. You do want the levels in a hierarchy to have the proper attribute relationships. IIRC these are necessary for the cube to do the 'Autoexists' optimisation (resolve non-emptiness purely from the dimension before hitting the fact table) - which is why the cube is slow when these relationships are not set up.

You may have to apply the hierarchy to the dimension in SQL before constructing the cube. Certainly, if you want to load updates, the keys will need to remain static, although if you have time to do a complete refresh this may not be necessary.