SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

A Tale of Broken BI

By Phil Factor,

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.

Total article views: 79 | Views in the last 30 days: 1
Related Articles

Aggregating Intervals

create intervals from effective dates and aggregate like and adjacent intervals


PowerPivot default measure aggregation

One of the PowerPivot advantages is flexibility of changing measure aggregation for instance changin...


How to avoid aggregations for the measure?

I want to keep all values for measure not aggregated, but pre-calculated.


Problem Using the Aggregate Function with Calculated Measures

Recently, I was working with a client we were working on a calculated measure using the aggregate fu...


Aggregating Intervals

Comments posted to this topic are about the item [B]Aggregating Intervals[/B] Thanks for the script....