Time and time again I am faced with these dimensional modeling debates : “Do I create a new dimension or just add an attribute to an existing one?”

I believe there is no right and wrong here, only considerations of performance and convenience of use. Performance, however, here is both in terms of the user experience and the time to market, that is development time. I can see an overhead in creating a separate dimension: extra ETL processes, extra cube objects, extra keys in the fact table, extra maintenance required. The bottom line of this decision, in my opinion, is in whether it provides benefits in terms of:

1. creating a report (makes it easier and more intuitive) or
2. creating the datamart (makes ETL or MDX calculations more straightforward without compromising the former) or
3. user-experience benefits (query execution time)

If there is no tangible benefit to any of the above or, worse still, if any of them are compromised as a result of adding a dimension, it cannot be a good idea.

A good reason to create a dimension could be when two facts of different granularity need to be linked together, such as actual revenue and budget/plan/forecast (convenience and functionality). Another good reason is to normalize-out rapidly-changing attributes out of a fat, long and relatively static otherwise monster dimension (user experience and ETL benefit). Another one is to create a junk dimension when there are lots of unrelated low-granularity columns in a fact table (half a dozen of nominal categories with a few unique values that do not belong in any current dimensions)

Ultimately, if it is really a twilight zone and you are still sitting on the fence – just make it an attribute and promote it to a dimension once you will get a clear case for that: reduce your development time and business will love you for that alone 🙂

I hope this may helped someone decide. Please do comment and suggest your own!