Historical Measures Missing

[jimbo1580]

Hi,

I am working on a data warehousing project. Our main fact table is going to be an accumulating snapshot that records dates and metrics as, in our world, cases flow through the business process. We are capturing dates and metrics at 9 major points in the process. The business wants all historical cases in our existing transactional system loaded into the warehouse. But, we have an issue. All the historical cases have the dates that the business wants to track, but not all of the measures. Up until now, they were only tracking certain metrics in real time and were not capturing their values throughout the process. There is no way determine what the values were at any time in the past.

My question is, what values do I put in the fact table for these metrics that are not available for historical cases? I can't replace NULL for 0 because 0 is a valid value that means something. Also, how do I prevent the users from running ad hoc analysis that are generating incorrect results because some metrics are missing?

Thanks!

[ngalemmo]

Generally speaking, its best to leave measures NULL if you have nothing else for them. Nulls will be ignored in aggregate functions (average, count, min, max, etc...) which will minimize potential problems.

But is also begs the question: what is the appropriate history to load? This is something you will need to work out with the business and clearly lay out how it will be generated and what the implications are.

[jimbo1580]

"...This is something you will need to work out with the business and clearly lay out how it will be generated and what the implications are...."

The users want all historical cases loaded because they want to perform analysis that does not include the missing metrics, such as how many cases we have processed per client. I can explain to them that tcertain measures are not there and include a description of this in the data document, but I'm really not sure how to explain the implications. I am concerned that they will run reports that generate undesirable results and they don't know that they are wrong.

For example, let's say they want to perform an analysis in a pivot table where they want to look at "total cases" and "total value" per client, where "total value" is a metric that is sparcely populated for historical cases. The "total cases" would include all cases for that client regardless of whether there is a "total value" measure populated or not. "Total value" would be the sum of "total value" for all cases for that client. If the user then performed an average function using those two metrics, the number would not be correct because it would be including cases in the "total count" that did not contribute to the "total value" measure.

Any suggestions? Am I misunderstanding or overcomplicating my issue?

[ngalemmo]

If you use null for total value that have no value, the average will calculate correctly. For example, the average of (5, null, 6, 7, null) would be 6, not 3.6. Also, if you did a count(total value) it should return 3, whereas a count(*) would return 5.

The bigger issue is communicating with the business. Sometimes just a blanket statement such as "historical data prior to is incomplete and known to be inaccurate" is enough. Beyond that, it can get complicated. But, as you said, they want to do analysis that excludes the missing metrics, so a statement as to which metrics are good from what point in time may be all you need to do.

[jimbo1580]

Thank you for the feedback. I will definitely be including the descriptive info of the measures and I will run some tests with our ad hoc analysis tool (Excel at this point) to make sure the numbers are coming out correctly

[Sponsored content]