Wednesday 27 August 2014

Difference Between an ODS and a Datawarehouse

Answer1:
An ODS is a database that is subject-oriented, integrated, volatile and current. It is usually used by business managers, analysts or customer service representatives to monitor, manage and improve daily business processes and customer service. An ODS is often loaded daily or multiple times a day with data that represents the current state of operational systems. 

An EDW is a database that is subject-oriented, integrated, non-volatile (read-only) and time-variant (historical). It is usually used by financial analysts for historical trend analysis reporting, data mining and other activities that need historical data. An EDW keeps growing as you add more historical snapshots, either daily, weekly or monthly. Because an EDW has historical data (and the ODS usually does not), some companies use the EDW as a hub for loading their data marts.


Answer2:
To a business user/information consumer, an ODS is best described as a means of moving  the reporting function from the transactional systems so that users can report on current activity without impacting the performance of the transactional system. That is why the concept of an ODS was created. It is not a duplicate/copy of the OLTP system, but is designed to readily load transactions (DB logs) as well as facilitate creation of reports. The ODS may carry (store) data a little longer than the OLTP system, but generally it is defined as current/short term data. An ODS is normally tied to one OLTP system, so in a larger organization you may see multiple ODS instances.

ODS system generally works as a complementary system to EDW to achieve near real time analysis of important business facts. e.g. by end of every six hours, for a online marketing portal, it might be important to calculate total sales $ irrespective to whatever views (geography, marketing channels etc) sales has been done.

The DW on the other hand is the longer term repository of transactional data, and is designed around subject areas, rather than transactional applications. It normally has multiple systems sending data to it, and some of those systems can be ODS. It is designed to store data at the lowest level of detail (atomic) available from the data sources. The DW (or EDW) is the principle resource for providing normalized data to the business intelligence (BI) layer, either indirectly through a data mart/application or (infrequently) through direct query. Whether the DW design is 3NF or dimensional (federated data marts) probably wouldn't be of much concern to the end user. They just want a conformed, confident source of information without having to go to multiple sources to get it.

15 comments:

  1. Excellent ! I am truly impressed that there is so much about this subject that has been revealed and you did it so nicely.
    Data Warehousing Training in Chennai

    ReplyDelete
    Replies
    1. Thank you Madam . Problems occuring in the daily work are the inspiration to this blog. Please try solving SQL queries in this blog.

      Delete
  2. This comment has been removed by the author.

    ReplyDelete
  3. You blog post is just completely quality and informative. Many new facts and information which I have not heard about before. Keep sharing more blog posts.
    Python training in marathahalli
    Python training institute in pune

    ReplyDelete
  4. Nice work, your blog is concept oriented ,kindly share more blogs like this
    Tableau Online Training

    ReplyDelete
  5. Your good knowledge and kindness in playing with all the pieces were very useful. I don’t know what I would have done if I had not encountered such a step like this.
    angularjs Training in marathahalli

    angularjs interview questions and answers

    angularjs Training in bangalore

    angularjs Training in bangalore

    angularjs online Training

    angularjs Training in marathahalli

    ReplyDelete

  6. Thank you for sharing such great information very useful to us.
    Data Warehousing Training in Noida

    ReplyDelete
  7. The business process involved in the data warehouse solution providers helps in keeping the robust data security along with efficient data modelling and problem-solving techniques.

    ReplyDelete
  8. Please go ahead and share.Thanks.

    ReplyDelete
  9. Great Post!!! Thanks for sharing this post with us.
    Learn AWS Basics
    Advantages of AWS

    ReplyDelete