Difference between ODS and DW (or Mart) ?

Hi All,

I am new to the forum and posting my first question. Please excuse me if question sounds basic.

While going through Kimball book 'DW toolkit', he has mentioned that Data Staging area should not accessible to end users and should not be queried.

When I was trying to find definition of ODS, most of the sites mentioned that ODS is residing in Staging and used for queries related to recent transaction records.

This is confusing to me and contradictory to what Kimball has stated above.

Can someone please explain what is exact purpose of ODS and where does it locate in DW architecture?

Regards,
Chinmay Athavale

Unfortunately no. You are likely to get 10 different answers on what an ODS should be. The ODS is one of those concepts that has morphed into multiple implementations based on user needs. I use it as a common staging area for loading into a data warehouse. Some of my previous clients term an ODS as simply an integration point distributing data to any other consumer. My advice is to build what you need and call it what whatever you want.

As B&L suggests, its anyone's guess what ODS means these days. It seems our profession is getting to the point where we are starting to sound like economists.

The classic definition is a somewhat normalized data store to handle one-of (i.e. small) queries to handle operational needs, such as 'what are the outstanding orders for customer x'. They come about because of the difficulty getting a consolidated view through typical ERP systems as well as to offload those types of queries from the ERP system. Data is usually held for a shorter term than in a data warehouse, usually months at the most. It is not what is often referred to as a staging area, as staging areas by nature are transient. However, if you are constructing an ODS in concert with a data warehouse, an ODS can be leveraged as a pass-thru to perform change detection for staging to a data warehouse. Depending on your situation, an ODS could also reference dimensions in the data warehouse to reduce data redundancy.