Currently in the fact table I do not allow nulls and instead of null's there is 0 in amount fields etc. Is that a mistake, leaving the values as null instead could be more efficient?
I have done the same approach for foreign key'ed values, I used 0 instead of nulls there too? Should I have left nulls for efficiency?
The decision to replace null values with zeros in your fact table is generally driven by the reporting requirements on this fact table. For example if you want to know all the customer accounts with a zero balance it may be misleading to convert all the null balance values to zero.
For unknown foreign key values good practice is generally to use the -1 value as your surrogate key within your respective dimension. For example you would create a record in your customer dimension representing unknown customer with -1 as the surrogate key and "unknown" as the customer name. The -1 would then be a foreign key value in your accounts Fact table to indicate that there is no known customer attached to a particular fact record.
I hope this helps.
Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/
I tend to leave the nulls as nulls for a couple of reasons.
1) When you aggregate values (ex: average, min, distinctcount), these MDX functions use all of the nonempty values. Most of the time, if I want an average, I usually do not want to include the rows where there were nulls in the data source.
2) Usability in Excel pivot tables - When users connect to a cube using Excel OLAP Pivot tables, the default pivot table setting is to show nonempty values only. If you change nulls to zeros, would your users want to see those zero rows? Would they end up having to manually filter them out each time?