Once, so long ago that I can safely recount it without breaking confidences, I had the task of sorting out a problem with a 'Business Intelligence' report for a motor manufacturer. A senior engineer for one of the many associated companies, bemused by some car acceleration and braking figures, had questioned whether someone had "messed up the SQL".
With a task like this, you check the obvious first. No, the same figures produced the same results by four different methods, including hand-calculation; it looked as if the problem was the data. The system had been in place for years. Where had the data come from? I uncovered a labyrinthine maze of abstracts and aggregations, but the raw data was elusive. When I eventually found it, I found that the aggregations were correct, and the data was apparently correct.
Then it dawned. Acceleration and braking figures are quoted in quite a complicated way. Acceleration, for example, is the time it takes to reach a particular speed, which in turn is the distance that would be covered in that time interval if the car travelled at the same rate for that time interval.
This would be fine if humanity agreed on that distance, or how one measured it. Yes, they had extracted the raw data from different systems, some of which used imperial measurements, and others metric. There were also variations in the time intervals for measuring speed. Clarity was lost as soon as they became aggregated 'acceleration figures'.
It was nobody's fault, though the IT systems got the blame. If we don't collect the raw data in the same consistent way, in the same units of measurement, then aggregation and consolidation is more difficult. If it is not subject to rigid controls then it becomes impossible.
This is why the Business Intelligence expert must be a cynical generalist with a keen eye for the detail of the bedrock of data types and check constraints. Graphs and charts are dangerous in the wrong hands, and if built on data that is carelessly gathered will mislead as often as they lead. The canny BI specialist will spend a lot of time verifying and validating the raw data before emerging with the seductive visualizations.
Phil Factor.