Design Standards - Numeric Type 2 Dimension Attributes

Our warehouse projects are looking for data architecture standards. When you have an attribute that represents a non-additive numeric amount in a Type 2 dimension attribute, can we define a data type standard? We want to embrace the design philosophy of minimizing nulls in our data models. Let's also assume these dimension attributes are non-additive in the context of our design. We would like to type them as char or varchar NOT NULL with a default of empty string so that consumers don't assume them to be additive. Can you see any problems or have you experienced any "gotchas" if you have tried or implemented such a data modeling standard?

Yes. They are numbers, not characters. Don't outsmart yourself. You need a base level amount of knowledge to create a report even in dimensional models. Most BI tools will prevent the foolish from trying to treat a dimension attribute as a fact measurement. You may also want to perform arithmetic operations on an attribute in a dimension.

It really depends on what kind of attribute you are talking about. If it is a business identifier of some sort (account number, order number, etc...), by all means, store them in a character column. Identifiers may be numbers now, but may change later.

If it is an amount, such as a rate or percentage or standard cost, they should be stored in numeric fields. Doing anything else really makes them a pain to work with.