Data warehouse

OLTP & OLAP in Data Warehouse

Online Transaction Processing (OLTP)
OLTP is application system which supports day to day activities of a business.  It is characterized by a large number of short on-line transactions (INSERT, UPDATE, DELETE). OLTP systems emphasize very fast query processing and maintaining data integrity in multi-access environments. For OLTP systems, effectiveness is measured by the number of transactions per second. OLTP databases contain detailed and current data. The schema used to store transactional databases is the entity model (usually 3NF). Normalization is the norm for data modeling techniques in this system.

The functionalities of any OLTP system is

  •  It is a source system which can be refreshed in a regular intervels. It will store less amount of data.
  • Supports for daily business like billing or application process, buying or selling etc..
  • These systems are directly or indirectly used by customers to avail the services offered by companies.
  • All the above activities may lead to millions of simultaneous select/insert/update/delete operations on the database.
                        a)Verify balance: Select query operation.
                        b)Withdraw Money: Insert and Update operation.
  • Stores only application specific data: Credit card systems stores only credit card data.
  • Only relevant and current data is maintained in OLTP systems.
  • Database design in OLTP systems follows normalization which is E-R modeling.
  • It supports day to day activities to run the business.
  • These systems are optimized for handling the customers current data.
  • It supports frequently select, update, insert, delete operations at the given point of time.
  • To support all above activities, there systems heavily normalized.
Online Analytical Processing (OLAP)
On the other side of business in additional to OLTP system there are so many analytics required to help people who are working in companies in decision making. This involves in monitoring daily sales, understanding the customer needs, risk identification, forecasting and mining the data on historical patterns.
The main challenges involved to perform these activities is availability of data. 
  • As per OLTP systems data is captured on various systems, they are application specific.
  • We cannot see all the data in one place. 
  • OLTP systems maintain only current data which will not allow historical data.  
  • OLTP system designed in normalization  hence querying on many tables in a single join may not appropriate.
Following are few activities business which management would like to do on daily basis.
  • Identifying high value customers.
  • Delivering customer reward programs 
  • Credit risk factors
  • Analysis of markets
  • Performance management
  • Data mining
  • Analysis is done at each department level
  • Who is good customer
  • Risk
  • To whom should they target for marketing.
For the daily activities and for analysis we use OLAP system
OLAP is a reporting system which is used to analyze the business by top level management.   
  • OLAP is characterized by a relatively low volume of transactions. Queries are often very complex and involve aggregations. 
  • For OLAP systems, response time is an effectiveness measure. 
  • OLAP  applications are widely used by Data Mining techniques. 
  • OLAP databases store aggregated, historical data in multi-dimensional schema (usually star schema). 
  • OLAP systems typically have data latency of a few hours, as opposed to data marts, where latency is expected to be closer to one day.
  • This approach is used to analyze multidimensional data from multiple sources and perspectives. 
  • The three basic operations in OLAP Roll-up (Consolidation), Drill-down and Slicing & Dicing.

Powered by Blogger.