Dummy Row in Dimension Tables

[thomaszhwang]

Let's say I have an Employee dimension table. For Employee_Key = 0, I want to put a dummy employee there to facilitate any NULL case in fact tables. According to Kimball's methodology, I should leave that column to be NULL in the fact table, right?

So in this case, what kind of values should I use for the rest of the fields for Employee_Key 0 in the Employee table?

I think for text field, it's comparatively easy. I can put in values such as "N/A" or any other descriptive values to describe the situation, but what if the field type is boolean or numeric type? What kind of value should I put there, so as to avoid aggregation errors (e.g. It doesn't matter I make the Employee_Is_Manager filed true or false, it will aggregate to a wrong value for facts)?

Thanks.

[LAndrews]

No - employee_key should never be null in the fact table.

If you're "unknown employee" record has employee_key=0, then that is what should be in the fact.

For the dimension attributes, work with the business to establish what the default/unknown values should be. In my experience, there usually is a preference to have more meaningful values than 'n/a'. For example, the Job-title field might have "Unknown Job Title".

For boolean .... for this reason I never use the boolean type .... my dimension record would have Employee_is_Manager='U'

Numerics are a bit more tricky - in dimensions I tend to leave them as null or 0.

[thomaszhwang]

How about the Employee_ID (natural key) field? What if in the transaction system, the Employee_ID could be any string, how should I set the dummy Employee record in the dimension table? There is a little tiny chance that in the future some time, the transaction system could generate a Employee_ID exactly the same as the dummy employee. How should I take care of this kind of situation? Thanks.

[ngalemmo]

If you are not using Oracle, you can use an empty string as the employee id. Unfortunately, Oracle interprets an empty string as null, so you need to use something... "Unknown", "NULL", "N/A" are a few possibilities. If somone happens to actually use it as an employee ID, well, I don't know what to say....

[Sponsored content]