I have an operations database that contains this table:
salesQuality salesPrice salesCategory
According to my business requirements, the salesQality
and salesPrice
are measures.
However, salesCategory
is a dimension.
Unfortunately, in the operations database, there are some rows without the salesCategory
value.
What should I do in the fact table to represent these sales that don't have a category value in the category dimension?
Best Answer
The standard approach for handling missing dimensional data would be to enter a null value for the relevant data in the fact table.
If this approach is problematic for some reason (table constraints, business rules) , adding a dimensional value to represent 'null' would be acceptable, if less technically correct. If you choose to implement this solution, keep in mind that you will need to consider that null <> null for incremental processing.
Another possible solution would be to encourage the business (modify the application, change the data feed, consult with the outside data vendor etc.) to define a more robust salesCategory dimension- that is, define more cases for the dimension so that the number of missing values is reduced or eliminated and less holes in the data are present.