Difference between ODS and Datawarehouse
-
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.
Regards,
Sudheer
-
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.
Steve.
-
ODS is the part of data warehouse architecture.where you are collecting and integrating the data and ensures the completeness and accuracy of the data,and it provides the near data of warehouse data, it is like "Instant mix food" for hungry people:-D. ODS provides the data for impatient business analyst for analysis.
-
mf.gadagin (1/7/2013)
ODS is the part of data warehouse architecture.where you are collecting and integrating the data and ensures the completeness and accuracy of the data,and it provides the near data of warehouse data, ???? ????? ????? ???????[/url]??? ???it is like "Instant mix food" for hungry people:-D. ODS provides the data for impatient business analyst for analysis.?????? ????? ??????????? ??? ?????? ???????????? ????? ???????
very nice, great analysis, thanks alot
-
I would add that an ODS is:
1. Created as a "near copy" of the transaction system(s).
2. In existence to avoid stressing the transaction system with analytic queries for which the transaction server is not configured to handle.
3. More likely (at least in my experience) to answer business questions closer to the "here and now"--operational vs strategic requirements.
-
Some good answers in there. I agree that an ODS is often a part of a data warehouse, which also contains other components like master data repositories, data marts, cubes/tabular models (SSAS), ETL/process control, etc.
Something to be aware of is that an ODS is often "operational" (which usually means it has an up-time requirement of 24x7), and for that reason it can make an excellent part of the DW to serve as an interface to other systems. Data Marts, on the other hand, are more optimised towards being used by humans directly, and need not be "operational" (often they are available over extended business hours), giving plenty of time to run the ETL to update them.
An ODS often receives real time, near real time or multiple refreshes during the day, whereas a data mart may only be refreshed once a day.
An ODS can be a "dumping ground"; sometimes they form an "NDS" (normalised data store) and can even be based on the Inmon model for a data warehouse. Sometimes they're just a raw bunch of tables that have little or no relationship to each other.
There's even a form of the ODS that's actually extracted from data marts (rather than the other way around)!
Kimball is a great place to start with data warehousing:
http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/books/
-
Bruce W Cassidy (2/17/2014)
Something to be aware of is that an ODS is often "operational" (which usually means it has an up-time requirement of 24x7), and for that reason it can make an excellent part of the DW to serve as an interface to other systems.Correct! I see the part of the term "operational" as the "of the operational (source) system".
If you source system is a flat file, for example, the ODS for is could well be a wide table representing this file, or a few tables, which can represent the content of the same file. Decision of how to populate the ODS should be taken by the DW developer. Some of such decisions could be a "dump" (replicated database, restored backup), or a selective tables dump, or a group of tables, which provides the same data as the operational system contains.
There's even a form of the ODS that's actually extracted from data marts (rather than the other way around)!
Strange... I was trying to imagine when and how this could be the case.
Data Marts are usually populated from the DW (Inmon or Data Vault), or form the DW (Kimbal model).
But is the DW populated from the source systems anyway? Why could not we populate the ODS from the source system? What about the recency of information in such ODS? Once a day? Why do we need such ODS?
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
-
Strange... I was trying to imagine when and how this could be the case.
Data Marts are usually populated from the DW (Inmon or Data Vault), or form the DW (Kimbal model).
But is the DW populated from the source systems anyway? Why could not we populate the ODS from the source system? What about the recency of information in such ODS? Once a day? Why do we need such ODS?
This maybe due to some really strange corner case. Such could be using BI tools to create the data marts without staging tables. Then someone decided they want an ODS after all and not wanting to alter the delicate BI procedure, just did some data manipulation from the data mart to normalise the data again. You could schedule both of these processes to occur one after the other. If done often enough throughout the day .. then you have an ODS from a data mart. Certainly this is not ideal, nor would I immediately trust the quality.
----------------------------------------------------
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply