Hi All,
Can any one tell the difference between ODS(Operational Data Store) and Datawarehouse?
When do we go for ODS and when do we go for Datawarehouse.
I got the definition from PC world.
(Operational Data Store) A database designed for queries on transactional data. An ODS is often an interim or staging area for a data warehouse, but differs in that its contents are updated in the course of business, whereas a data warehouse contains static data. An ODS is designed for performance and numerous queries on small amounts of data such as an account balance. A data warehouse is generally designed for elaborate queries on large amounts of data.
We are working on our first data warehouse project and following this same learning curve, here is what we've learned about the differences:
Your ODS takes the form of a typical normalized schema, the type we are used to with transactional systems.
The DW on the other hand is used to translate the data from the highly normalized ODS schema into the star/snowflake schema(s) that are typically used with DW analytics. In the DW you design fact tables that store the data elements to be reported on and associate those fact table with various dimension tables that provide a hierarchy of how you will view the fact tables -for instance time: day/week/month/quarter/year is typically a dimension.
In short, the DW is just another way to organized your DB in order to make it friendly for analytics. Hope that helps.
I would go a little further on Ricks comment about 'another way to organised(sic) your DB', usually a DW goes further than just denormalizing the normalized OLTP schema(s) [yes, in a lot of cases it's the combination of more than 1 oltp that addsisignificant value to a DW]. The DW (or datamart in smaller implementations) is also a place where you can add value to the data that has come in from your various OLTP's and ODS. If you're starting at first principles, definitely go to your local Borders and try to find the 'Data Warehouse Toolkit' by Kimball et al. It's been quite a while since this was released (ie years) but a lot of the approaches still hold true. Note also that there are now some varients/flavours on this book (e.g. one for SQL/AS) plus there also another varient on the name where that book is more focused on the actual implementation whereas the initial book was more a guide to solving business problems using a DW.
