Operational Data Store vs Enterprise Data Warehouse

You might be reading this because a consultant told you that what you thought was a data warehouse is actually an operational data store.  Confused?  Yeah, I was too the first time I heard this term.

Let’s break it down.  An operational data store (ODS) is a database that contains data captured from one or more data sources in real time or near real time, and usually with limited history (maybe the last month or the last quarter).  The ODS is a place you might go for a quick answer like ‘what was the last purchase we received from customer x’.  The operational data store is usually closer in design to the source system, meaning it is likely a lot of very small tables related to each other with primary/foreign key relationships (3rd normal form).  The loading process for the ODS probably did some basic data cleansing and might have applied some business rules to the data as it was loaded.  What that boils down to is that your ODS might or might not be an exact match for your source system.  The ODS might be used for some light reporting during the design of your data warehouse, and likely it will be a major data source for your data warehouse.

The enterprise data warehouse (EDW) might source some or all of its data from the ODS, but it stores it in a significantly different design.  The EDW is often designed following the Kimball methodology.  In the simplest possible terms in an EDW each table either holds the measurements that drive the business forward or the context that gives those measurements meaning.  I’ll be writing a lot more about what a data warehouse is in future blog posts.

That’s how I see the ODS vs the EDW.  I’m curious what you think.  Did I miss anything?  I look forward to your comments.

Leave a Reply

Your email address will not be published. Required fields are marked *