Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Difference between ODS and Datawarehouse Expand / Collapse
Author
Message
Posted Tuesday, December 5, 2006 7:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, July 25, 2010 12:00 PM
Points: 125, 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

Post #328179
Posted Wednesday, December 6, 2006 7:09 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 17, 2014 7:55 AM
Points: 2,805, Visits: 3,067

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.

Post #328280
Posted Thursday, December 7, 2006 6:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 12, 2007 9:57 AM
Points: 1, 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.

Post #328730
Posted Thursday, December 7, 2006 8:12 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Wednesday, August 6, 2014 8:12 AM
Points: 1,815, Visits: 3,456

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.
Post #328778
Posted Monday, January 7, 2013 10:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 7, 2013 10:49 PM
Points: 8, 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. ODS provides the data for impatient business analyst for analysis.
Post #1403970
Posted Wednesday, November 27, 2013 1:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 22, 2013 10:24 AM
Points: 3, 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. ODS provides the data for impatient business analyst for analysis.
مؤسسات نظافه بالرياضشركة عزل خزانات بالرياضنظافه منازل بالرياض
very nice, great analysis, thanks alot


شركات نظافة بالرياض تخزين اثاث تنظيف فلل شركة تنظيف منازل شركات تنظيف بالرياض
Post #1518200
Posted Tuesday, December 10, 2013 8:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:12 PM
Points: 2,148, 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.
Post #1521542
Posted Monday, February 17, 2014 5:29 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 958, Visits: 1,031
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/
Post #1542316
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse