Data Warehouse measures without a corresponding dimension

Question

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?

Answer 1

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.

Answer 2

Essentially you have NULL values in salesCategory column. Having NULL values in fact tables is generally bad because of the problem your are facing.

The best thing to do is correct your data so that these values are present as this will provide the most accurate results.

If you can't remove/correct the NULL values then you are limited to filtering the rows with NULLs but this will skew your aggregates or using the 'UnknownMember' to deal with NULLs. This will mark rows with a NULL Salescategory values as 'Unknown'. This way your aggregates are correct and all the data is visible.

If you are using SSAS then you can set 'UnknownMember' in the your dimension -> properties -> KeyColumns -> NullProcessing.

Answer 3

A data warehouse exists to provide the business with usable data. A NULL value does not tell your users anything; you can do better. Also, a NULL value will drop out when joining your fact table to a dimension table, causing undercount.

In your dimension table, include a record for each scenario where a transactional database might store a NULL. All of my dimension tables have at least two, for "Not applicable" and "Unknown", but those are only fallback options for when you can't give a better answer.

For an example, consider Salesperson. If a sale has NULL in this field in the transactional database it could be because...

• the order was just placed and the salesperson hasn't been entered yet
• the sale came directly through the corporate web site; there won't be a salesperson
• multiple salespeople were involved and no single person should get credit
• it's not a normal sale, but a accounting correction
• the salesperson wasn't recorded on the invoice
• salesperson's name was illegible on the invoice
• the salesperson is known, but hasn't been loaded into your Salespeople dimension yet

Each scenario (that's plausible for your business) should get a value in your Salespeople dimension, and every record in your Sales fact table should point to one of these. If necessary, fall back on something generic like "No salesperson available," but only if there's no information with which to do better.

About the only place I use NULL values in a data warehouse is where a metric is missing or inapplicable. Your SalesQuantity and SalesPrice fields are probably of INT and DECIMAL types, so you obviously can't store a text value; you could use codes like "-1 = not entered, -2 = pending, -3 = illegible" but then you'd have to diligently avoid these when performing aggregates.