8

I am new to data mart design and need to clear a few concepts.

I have read up a bit on dimension modelling where I see that fact tables store foreign key references to dimension tables.

Now suppose I have a phonenumber dimension table and a phone_extension dimension table. (These tables have different details because of which I cannot combine them)

As I understand both these dimension tables will have integer primary keys for better performance, and the fact table will have its own integer primary key and also store foreign key references to these dimension tables.

But suppose I have a situation that not all phone numbers have a phone_extension related to them. ( some phone numbers need not have an extension)

For phone numbers that have an extension, the fact table would have foreign key references to both the dimension tables, but how do I capture the situation where there are only phone numbers and no extension to them (and vice versa i.e extension with no phonenumbers)?

Should I capture such information with the phonenumber FK in the fact table having a value and phone_extension foreign key null?? Or are such non related objects not recorded in fact tables?

Also I need to generate report of this data mart. So do I start by querying the fact table and retrieving the dimension key values or report straight from the dimension table?

Thanks for your time in reading this!!
Appreciate any help!!

migrated from stackoverflow.com Jun 27 '11 at 16:54

This question came from our site for professional and enthusiast programmers.

  • possibly a serverfault question? – bluefeet Jun 27 '11 at 16:06
10

You can leave the FK to some dimension tables as NULL if those dimensions are not known or not applicable. You just have to remember to use outer joins when you do your reporting query.

Alternatively, some people create a "none" and/or an "n/a" dimension record for data mart dimensions and then populate fact table FKs to point at these rather than using NULLs. People who do this like this approach because they have an aversion to outer joins.

People who use NULL FKs in fact tables usually have an aversion to people who have an version to outer joins. ;) (in other words, this is a stylistic issue which can generate religious wars)

I say do whichever you prefer, but pick one approach and stick to it fervently.

10

Don't put nulls in the Warehouse or in the Marts.

The warehouse should be well normalized (at least BCNF) and therefore should exclude nulls. Nulls might be preserved in staging tables if they exist in data sources but they shouldn't be needed in the warehouse itself.

Marts should be designed to support presentation tools and user queries. Nulls just get in the way of those things because they are never displayed and they make user queries more complex and error-prone - especially in foreign key columns which are frequently subject to joins.

  • I agree, but for the reason cited by Brown: it's very valuable to have explicit synthetic records for the reason the field would otherwise be NULL. NULL tells users nothing; "Value could not be parsed", "Field left blank", or "No account executive assigned yet" is useful. – Jon of All Trades Aug 11 '14 at 19:26
0

dimension keys in facts should be not null and imho have fk's to the dimensions to elimnate the need for left outer joins by end users, reports etc. All loads of facts should do a left outer join to the dimension and default to a 0 key or no key at all and fail. Better to fail than to do a join to the dimension and have no idea you missed rows in your fact, till some users finally finds it (if that ever happens)

create a "n/a" record in the phone_extension dimension and link to it.

my rule of themb is the only nullable value in a dwh end datamart is the fact itself, so that aggregate functions like avg still work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.