Data Warehouse measures without a corresponding dimension

data-warehousedimensiondimensional-modelingfacttable

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.