Operational & Informational Data Stores

A data warehouses is kept separate from operational databases due to the following reasons:

• ·      An operational database is made for well-known tasks such as searching particular records, SQL queries, indexing, etc. whereas, data warehouse queries are unstructured and complex.
• ·  Operational databases support concurrent processing of multiple transactions. Concurrency control and recovery mechanisms are required for operational databases to ensure robustness and consistency of the database whereas for a warehouse data once stored is never deleted.
• ·    An operational database query allows for both read and write operations, whereas a warehouse query allows only read only operations of stored data.
• ·     An operational database maintains current data. On the other hand, a data warehouse maintains historical data. 

    

Differences between OPERATIONAL & INFORMATIONAL Data Stores

	OPERATIONAL	INFORMATIONAL
Data Content	Current values, Day to Day Values	Archived, derived, summarized, historical
Data Structure	Optimized for transactions	Optimized for complex queries
Access Frequency	High	Medium to low
Access Type	Read, update, delete	Read Only
Queries	Predictable, repetitive	Ad hoc, random
Response Time	Sub-seconds	Several seconds to minutes
Kind of Users	Clerks, DBAs, Database Pros.	Knowledge Workers eg. Analysts, Managers, Executives.
Number of Users	Large number, Thousands	Relatively small number, Hundreds.
Usage	Used to run the Business	Used to Analyse the state of Business
Focus	Focus on Storing Data	Focused on outputting Information
Models	E-R Model	Star Schema, Snowflake , Fact Constellation