Murphy’s Law tells us, “Anything that can go wrong, will go wrong.” A corollary to this law states that it will go wrong at the worst possible moment. In the world of BI (business intelligence), “worst possible moment” can include:
-Inventory reports reveal strange errors just when the financial auditors arrive onsite
-Sales reports used to calculate technology royalties go haywire just before the royalty payments are due
-Fifteen minutes before the quarterly earnings call, someone notices the balance sheet doesn’t add up
“Worst possible moment” implies there is a “best possible moment.” For a business that lives and dies by the quality of its data, reporting, and its ability to use that data to make day-to-day decisions, there’s no such thing as a “best possible moment.”
Bottom line: As a BI professional, it’s always a crisis when someone calls you in a panic to ask, “Why don’t these reports match up?!”
Our advice? Keep calm and carry on.
How Mismatch Errors Happen
There are several reasons why two reports that supposedly show the same information from the same sources might not match:
-Figures in foreign currencies can cause headaches. If one report uses different exchange rates than another (or doesn’t do the conversion at all), the reports won’t match.
-If each report has a separate ETL (extract, transform, load) process, one (or both) might be buggy.
-One report might convert units of measure differently from the other.
-One report might use different rounding rules than another.
-One report might, by design, exclude certain information, such as sales-to-distributors vs. sales-to-customers. Sometimes the filtering is not made explicit to the report’s end user.
-The time period represented in one report may be different from that of the other. Even a few hours’ difference (caused by time zones, daylight savings time, or from one country to another) can cause large
discrepancies.
This list represents just the tip of the iceberg. The BI metadata nightmare is trying to locate a particular error from among the many possible sources. Some discrepancies are easy to spot, but many are subtle—hidden in field metadata, stored procedure code, and inside complex calculations.
And if each report has a different source or set of sources, all bets are off. Finding the needle in that haystack is nearly impossible. It would be a miracle if the reports ever matched in the first place.
Avoiding This BI Metadata Nightmare
Inconsistencies and errors are simply a fact of life when dealing with such large sets of data. And as thorough and exacting the methodologies a BI professional uses, there is always potential for simple human error or a system not properly handling data due to programming that didn’t account for a specific scenario.
The key to avoiding this BI nightmare is automated data lineage tools. The ability to visually compare the path the information took from the source(s) to each report is invaluable in locating the cause of a mismatch error. Such a tool can show a BI developer exactly where to look for the problem, or at least narrow the possibilities to a manageable number. This can save countless hours of hair-rending frustration for everyone.
