Monday, March 16, 2009

What's the Purpose of a Datawarehouse?

I've been meaning to write this since I first began my foray into the world of datawarehousing back in December of 2006.

What is the purpose of a Datawarehouse?

  1. Reporting

    • Key Performance Indicators (KPI)

    • Performance - Data is stored in such a way (facts and dimensions) which enables higher performance against the same relational tables in an OLTP system.

    • Slicing and Dicing - Viewing the data from multiple angles (dimensions) is the one of the best aspects of the Datawarehouse. Also included is the ability to drill down into the finer grained details.

  2. Single Source of Truth - Especially important in an enterprise environment which may have many systems of record. The datawarehouse allows you to aggregate all those systems into one environment. Not always completed, it's more of an ideal than anything.

  3. Clean Source Data (Transformation) - The datawarehouse allows you to "fix" the sub-par OLTP systems. Think garbage in, garbage out.

From my point of view as a database developer, #3 is the most important. I believe that the "Transformation" stage of ETL should be easy if only the source system had been designed properly. I've seen instances where a column (VARCHAR2(30) for example) meant one thing at one point in time and another at a different point in time. Or a data was stored in the column as MM/DD/YYYY, DD-MON-YY or Month DD, YYYY. How do you clean that up?

I also recognize that in an enterprise environment you can have the best designed OLTP systems but you still have to map the data to one field.

Ultimately, I think #1 should drive the design of all OLTP systems, making concessions where needed for performance. From there, your datawarehouse should flow fairly naturally. The ability to report on the data that you gather allows the business to make decisions for the future. In other words, it's the data stupid.
at

4 comments:

  1. UnknownMarch 17, 2009 at 12:12 PM

    This comment has been removed by the author.

    ReplyDelete
  2. Oracle MikeMarch 17, 2009 at 12:21 PM

    OK, I just wanted to change one little thing in my comment...and I got put through the wringer!! After about 10 min of trying to log on to something, recover a non-existent password...LOL...alright I'll stop whining.

    Here was my comment (with the one-word change!!):

    Hey Chet,
    Do you also use the term "Datamart" in your environment? If so, how do you define it, specifically with respect to how it compares to your use of the term "Datawarehouse"? If not, do you think the term/concept has value? Thanks!!
    -Mike

    ReplyDelete
  3. oraclenerdMarch 17, 2009 at 10:12 PM

    We don't currently use the term Datamart but I have read where it is interchangeable with datawarehouse. My understanding is that the Datamart is a subset of the Datawarehouse, more akin to a subject area. Hope that helps.

    ReplyDelete
  4. UnknownMarch 18, 2009 at 9:41 AM

    I read a white paper that described a Datamart like this: The datawarehouse is ... a "data warehouse". OK, that's not very informative, is it? The idea is that the "warehouse" is where rough copies of all data are "stored" (in line with the warehouse metaphor). The datamart is that ETL'd, refined architecture'd, tuned engine'd, etc. version of the warehouse. Of course, all the ETL'ing, refining and tuning takes place in the warehouse, out of the customers' sight. So to be painfully explicit, I guess it's like a furniture store that has its showroom out front where all the customers browse and the giant warehouse out back where other furniture is being retired from inventory, worked on or being put into the sales inventory cycle. I like this metaphor - it makes sense to me; but since I read about it only in one place I thought I'd ask a real-life data guy like yourself if the metaphor is used outside the world of white-paper academia and maybe a handful of hyper-mega-data shops (Federal repositories, Fortune 100 co's, etc.). Thanks for the insight on this topic from your point of view.

    ReplyDelete