One Version of the Truth

August 30th 2014

When you don't have Business Intelligence and all your reporting is directly out of your transactional system, it is very easy to have multiple versions of the truth. Complicated SQL combined with aggregated reporting can allow mistakes to creep in and be almost undetectable. Two reports might have subtly different definitions of sales, and there are no easy ways to know which is right. Indeed, it might go completely unnoticed because the two reports are built by different people and are for different departments.

A data warehouse, in theory, should solve this problem. There is one definition of sales that everyone agrees upon and one set of business rules applied.

However, that is not always the case. Conflicts still arise. For example:

  1. Operational reporting does not go away when you have a data warehouse system. These operational reports might be different to the data warehouse numbers (and often are because they skip replicating all the business logic built into the data warehouse).

  2. Two departments might genuinely measure the same metric differently. The Sales department might count the sale as soon as the order is placed (when their work is done). The Finance department might not count it until the order is shipped (when we can invoice the customer). Neither measurement is wrong: they are simply different.

  3. Even with cubes & reports from cubes, it is possible to choose different filters and selections. One person might be measuring model year sales - another fiscal year sales.


One nice thing overall about building a formal data warehouse is that it can be much easier to identify the discrepancies. If we have an agreed upon definition of sales, and another report does not match, then it's a case of identifying what filter(s) are different. Unfortunately, far too often, the work of identifying why a report is different still falls to the data warehouse team, who constantly have to defend the accuracy of their data.



CC image sourced from Flickr uploaded by Jason Borneman

Go back...

Visitor Comments


Post Your Own Comment

Note that comments may not appear immediately on this site.

Commenting is not available in this channel entry.