Dimensional Attributes

This is more of a resource issue than a technical one.

Developers with a relational background have built our the current dimensional models. 'If all you have is a hammer, the only tool you use is a hammer...' applies here. What are called 'dimensions' are in fact copies of normalized tables (either base or association/junction tables) taken directly from the physical relational model. In another example, we have 3 separate dimensions for 'Customer', because on the relational model, 'Customer' is normalized into 3 different tables (because there are 3 types of Customers here, with about 1/2 of their attributes are in common).

At the other extreme, we have dimensions that to me are mini-marts - instead of populating dimensions with attributes that describe that entity (say Policy), they include all attributes that relate to a Policy (like customer address, service office, product, policyholder's name, current status, etc). As you would expect, these attributes are of course found elsewhere - in a product dimension, a customer dimension, an office dimension, a status dimension. It's almost like they took a factless fact table, along with handpicked attributes from its related dimensions, and collapsed them all into a single dimension. With this approach, if they wanted to get more info on one attribute within this 'dimension' (say, office_cd) they then link to the Office Dim to get the full range of Office attributes (office name, office region, office type, office size, office mgr, office phone nbr, etc). They do not feel this linking of a dimension to a dimension (the dreaded snowflake) as a bad thing. To them it's flexible.

I asked the creator whether they thought that office or product was truly an attribute of a policy, they said 'certainly'. I asked whether there was a distinction between an attribute and a relationship (i.e., a policy is related to an office, but is not an attribute of a policy) they replied that they were one and the same. I asked whether an office could then be an attribute of many other entities as well - sales rep, brokers, etc. Again the answer was yes, they could be.

Help! I thought this is pretty basic stuff. There's a strong push to build on top of what we already have and not rock the boat. Anyone have experience in how can I best re-direct the discussion back towards the fundamentals of dimensional design and away from 'this is how it's always been done'? As you can tell, we follow the 'decision by committee' approach to things, and I'm outnumbered.

- Desparately Seeking Reason

Its a political minefield.

The only reasonable way to deal with this is to involve a third party to evaluate and comment on the approach. Now, wither that will help your case or not would depend on which third party is chosen... which is the catch 22... the person who selects the arbitor will chose one that supports thier position. Which is the reason why a consultant is often described as someone who will take your watch to tell you what time it is.

Not sure a consultant would help here. The current designers don't see a problem so why pay money to bring someone else in. Unfortunately, you'll probably have to wait until the current design fails under its own weight of bad design. Once it does, you should be in a good position to take the reins.

Thank you both for responding.

If I'm relegated to re-using existing structures, poorly designed, yes, at some point the house of cards will fall down. The 'that's how we've always done it' approach ususually wins the day when veteran team members are polled. Perhaps I'm not advocating my concerns well enough.

I've done consulting work before so I know just what you're saying about bringing in an 'inbiased' opinion - these are the political assignments. But, fortunately I've also been contracted on many occassions to size up the situation and come up with a solution, and in most of these cases the solution is considered and accepted - these are the good assignments.

Whether or not the house of cards will fall on my watch, we'll see.