Can Dimension tables contain NULL values? 1

[jw970170]

Can dimension tables contain NULL values? This comes up if we have a FACT table that contains a time dimension. Suppose the time dimension table has attributes Year, Month, Day. The fact table contains all kindas of transactions. Suppose there is a transaction that occurs at a yearly level. It is valid for an entire year but not a specific day or month. This transaction in the fact table would reference a row in DIM_TIME that has a value for the year but NULL for Day and Month (since it covers an entire year, but not any specific day or month). Is that ok (to have NULL for a couple fields in the DIM tables)


FACT_TABLE
KeyField
TimeID(FK)
Value

DIM_TIME
TimeID
Day
Month
Year

 

[MDXer]

Yes a dimension can contain Empty members this are reffered to as ragged hierarchies. THese are more typically seen in Geographic Dimensions where in the US you have

Country
State
County
City
Zip


Where in other countries you have
Country
City
Zip

THere are a number of way to handle these types of dimensions with no one way being right but more finding what is right for your situation. In the Dimension editor there is a property on what should be done in the handling of empty members.


Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[jw970170]

thank you. This was exactly what I was wondering. I was sure if it was bad practice or not too set up a database with this type of situation possible.