One of the most over used phrases in Business Intelligence is "one version of the truth" so I thought it would be good to discuss why it's important and what IT professionals can do to achieve it. In an imaginary organisation like adventure works there would be several line of business systems such as a classic sales order processing system to track sales to resellers plus a shiny new web site for internet sales, while in the back office there would by supply chain management, finance and HR systems.
It's Monday morning and the heads of department have their weekly meeting to review sales and operations. Going round the table:
So what is going on here? There is no big consistent picture of how the company is performing. The sales manager is measuring his performance on the date a sale was made while the finance director only counts a sale when the invoice is paid. Another issue might be that they all get their information from a different system at a different time and because a lot of systems have overnight batch jobs to post data the view of the data could be very different on last thing Friday night to first thing Monday morning.
This is where a data warehouse can be useful. It will be designed to collect data form different systems at agreed times to give a consistent set of snapshots of the business from which all reports can be sourced. So that would be the one version of the truth sorted out then?
Yes and no. Yes it is consistent and accurate (a big assumption and a book in it's own right) , but it can still be distorted by the tools that access the date warehouse. Any front end tool that connects to the data warehouse will have additional business logic in. For example a report will be based on a query with logic in it and may have calculated fields that are derived from that query .
If the business is writing reports, than each department could create it's own version of a report and these could be inaccurate become so by not being kept up to date. If the IT department writes the reports then they will understand the data and have the tools to test the report is accurate and reflect what the user wants, which is why Reporting services at the moment doesn't have a good tool for end user reporting. However there are still problems here:
So what else can be done to crack this problem? In my opinion you need a semantic layer between the data warehouse and the reporting tool. This will contain several vital features for consistent reporting:
The Microsoft approach is to stuff all of this into a Unified Dimensional Model (UDM) and that is the subject of my next post.