SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Difference between ODS and Datawarehouse


Difference between ODS and Datawarehouse

Author
Message
cutespn
cutespn
Mr or Mrs. 500
Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)

Group: General Forum Members
Points: 543 Visits: 20

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

My Blog


Loner
Loner
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6412 Visits: 3364

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.


Rick Wilson-376426
Rick Wilson-376426
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 1

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.


stevefromOZ
stevefromOZ
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: Moderators
Points: 9550 Visits: 3757

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.
mf.gadagin
mf.gadagin
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 42
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.
miraeg77
miraeg77
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 7
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, شركه تنظيف منازل بالرياضنقل عفش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

شركات نظافة بالرياض تخزين اثاث تنظيف فلل شركة تنظيف منازل شركات تنظيف بالرياض
sneumersky
sneumersky
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3192 Visits: 487
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.
Bruce W Cassidy
Bruce W Cassidy
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3103 Visits: 1033
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/
VALEK
VALEK
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1234 Visits: 462
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
MMartin1
MMartin1
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7229 Visits: 2033

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.

----------------------------------------------------
How to post forum questions to get the best help
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search