Normalizing and Consolidating

Submitted by orangeboxtech on Fri, 04/16/2010 - 16:07

It's annoying, this idea that the only way that you can produce a worthwhile report is pull four different data sources (your sales volume, your customer data, your revenue, and your internet hits) into one file so that you can create the one report that shows you those series of metrics that are the most valuable to you...

...and it takes six hours.

There's usually an easier way, and it revolves around Data-warehousing. I'll avoid getting into the specifics of the reporting right now, that's the easy part. Data-warehousing is a planned extracting, transforming, and loading of data (called ETL) from your various data sources, and adding them into one relational database, where the you can then join the data sets on common elements. What an ETL process does is populate all your common elements among all your different sources into one list, and then attach those normalized lists to whatever transaction you might be looking at.

You want to know if your customer "Acme Widgets" has placed any orders on the web site and what their past sales have been? No problem...the warehouse has already taken care of the heavy lifting. All you need to do put your reporting tool on top of it and make your fancy looking reports or dashboards.

In short, a well put together Data-warehouse is the beginning of the backbone of any good business analytic system.