I was recently having a discussion about the role of default values in the fact table. The example being discussed had to do with a retail business who was giving plastic bags for free at the time of making the purchase. However at the time of billing the POS employee would actually enter a SKU number of plastic bags based on size mainly from an inventory stand point.
The questions being asked was should the free plastic bag show up in the Datawarehouse fact table under the order line as
SKU | Qty | Cost |
110-1332 | 1 | 0.00 |
Or
SKU | Qty | Cost |
110-1332 | 1 | NULL |
In this case the business went with the first approach since they wanted to reflect the “freeness” of the bag in the bill.
However have a look at how AVG() function behaves when it encounters a NULL as opposed to a Zero.
Notice how something that essentially is free still brings down the average order value because the ETL process uses a 0.00 instead of a NULL to represent the free items. While the correct answer depends on the business I would assume the second approach is more accurate financially since free items technically do not contribute to the order total and therefore should not impact the accounts but doesn’t look that way does it ??
The main reason I post this is because a lot of companies miss out on providing their MSBI developers business context or Business training to understand how managers/accounts would approach this issue and then be able to proactively handle it in the first place.
Please Consider Subscribing
