You will find many conflicting opinions on this. The following is the opinion shared by me and many of my consulting colleagues within The Data Warehouse Institute, of what the difference SHOULD be. Enjoy.
The operational data store lives in the operational support system environment. It typically serves the purpose of providing "near" real-time integration and reporting of data across disparate operational systems. It is designed for update. It is fed by operational support sytems, AND it will feed those systems. It is NON-historic. Many times operational applications get built upon the ODS structures. That ends the significant differences from a data warehouse. The following charactersitics are shared between an ODS and a DW. It is subject oriented, it is highly normalized. The data integration is enables using the same suite of ETL tools and EAI tools that enable the data warehousing environments.
The data warehousing environment lives seperate from the operational support systems environment. It serves the purpose of decision support, historical data mining, trendings, etc. It can be updated near real-time, but usually is updated on a premeditated scheduled frequency. It has architectural layers designed in 2 OR 3 tiers to support 3 roles: intake, distribution and access. It is designed for read only. It contains history. It is subject-oriented. The intake layer is normalized, the distribution layer introduces dimensionality and denormalization. The access layer consists of a suite of data marts designed for specific purposes (for trending analysis, etc), some relfecting star schemas others reflecting normalized schemas (for list management and reporting). It is loaded via ETL tools and EAI tools. It is typically accessed using BI tools.
For More Information
- Dozens more answers to tough data warehousing questions from Mike Lampa are available here.
- The Best Data Warehousing and Business Intelligence Web Links: tips, tutorials, scripts, and more.
- Have an DW tip to offer your fellow administrators and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical data warehousing questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in July 2002