Dummy Row in Dimension Tables

View previous topic View next topic Go down

Dummy Row in Dimension Tables

Post  thomaszhwang on Tue Aug 23, 2011 1:26 pm

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.

thomaszhwang

Posts : 32
Join date : 2011-08-11

View user profile

Back to top Go down

Re: Dummy Row in Dimension Tables

Post  LAndrews on Tue Aug 23, 2011 1:57 pm


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.

LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Dummy Row in Dimension Tables

Post  thomaszhwang on Tue Aug 23, 2011 3:05 pm

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.

thomaszhwang

Posts : 32
Join date : 2011-08-11

View user profile

Back to top Go down

Re: Dummy Row in Dimension Tables

Post  ngalemmo on Tue Aug 23, 2011 3:50 pm

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....
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Dummy Row in Dimension Tables

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum