Oracle Business Intelligence and Data Warehousing Practice Notes and Knowledge Repository

  • About Me

    Hello Friends,

    This is Santosh Kumar Gidadmani, a Business Intelligence and Data Warehouse Enthusiast passionate about blogging articles in the BI, Data warehousing, space. This is my attempt to share my experience and knowledge on Oracle BI & Data Warehousing Subjects.

  • OBIEE 11g Certified Implementation Specialist

  • Oracle Partner Network Certified Specialist

  • Visit My Profiles

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 73 other followers

  • Blog Hits Since Nov 2010

    • 364,321 Hits
  • Live Traffic

  • Visitors Country

    Since-Mar'12Free counters!

Archive for the ‘DW OLTP Vs OLAP’ Category

Data Warehouse – OLTP Vs OLAP

Posted by Santosh Kumar Gidadmani on January 3, 2011

Find out the complete differences between OLTP and OLAP.

Area OLTP SystemOnline Transaction Processing (Operational System) OLAP SystemOnline Analytical Processing (Data Warehouse)
Time Scale Stores current data Stores historical data for analysis
Indexing Few Indexes More Indexes
Normalization Fully Normalized Partially normalized
Stored Values Stores typically coded data Stores descriptive data
Source of data Scattered among different databases or DBMS and using different value coding schemes Centralized in data warehouse. Or in a collection of subject oriented data marts
Purpose of data To control and run fundamental business tasks To help with planning, problem solving, and decision support
What the data Reveals A snapshot of ongoing business processes Multi-dimensional views of various kinds of business activities
Inserts and Updates Short and fast inserts and updates initiated by end users Periodic long-running batch jobs refresh the data
Processing Speed Typically very fast Depends on the amount of data involved; batch data refreshes and complex queries may take many hours; query speed can be improved by creating indexes
Space Requirements Can be relatively small if historical data isarchived Larger due to the existence of aggregation structures and history data; requires more indexes than OLTP
Modeling Uses Entity relational model Use Dimensional Model (Start and snowflake schema)


Difference between OLTP and OLAP in Kimball’s words.

The users of an operational system turn the wheels of the organization. They take orders, sign up new customers, and log complaints. Users of an operational system almost always deal with one record at a time. They repeatedly perform the same operational tasks over and over.

The users of a data warehouse, on the other hand, watch the wheels of the organization turn. They count the new orders and compare them with last week’s orders and ask why the new customers signed up and what the customers complained about. Users of a data warehouse almost never deal with one row at a time. Rather, their questions often require that hundreds or thousands of rows be searched and compressed into an answer set. To further complicate matters, users of a data warehouse continuously change the kinds of questions they ask.

Add your thoughts if any other points are missing.

– Santosh

Posted in Data Warehousing, DW OLTP Vs OLAP | Leave a Comment »

%d bloggers like this: