Test Success

Test Error

Log In to start Learning

Login via

  • Home
  • Blog
  • How ETL acts as an intermed...
Post By Admin Last Updated At 2020-06-11
How ETL acts as an intermediate between OLTP and OLAP?

As days pass on the need of maintaining data is becoming more and more and usage is increasing day –to –day.   Traditional databases were incapable to handle all these. To handle the bulk amount of amount that we are experiencing data warehouse (DWH) is essential. It helps in maintaining the data effectively and efficiently. OLAP and OLTP transactions were responsible for storing and retrieving of data in/ from Dataware house through ETL processing.  Before going to know How ETL acts as an intermediate between OLTP and OLAP, let us know these concepts thoroughly.

Get more information at MSBI Online Training.


OLTP is an acronym for  Online Transaction process.  It is a software program that supports or facilitates the high transaction-oriented applications.  It supports the records of the day-to-day transactions in a high-end operation system like a bank, insurance, finance, hotels etc. OLTP must provide atomic in order to complete the transaction.This is an ongoing process which contains the sequence of processes. A typical example of OLTP is  Air  Line ticket booking which is a combination of actions like reserving the seat and getting pay for the seat sequentially.


It is an acronym for Online Analytical processing.  The transactions that happened in the DWH were analyzed and view by different customers according to their users.  It enables the users to extract the certain kind of medium of data This is an excellent media for multidimensional analyzing the previous business data so as to predict the future trend and take the required measures for the best result for the future.

Data Acquisition:

It is the process of getting the data from different data sources, integrating  the data and transforming them into  homogeneous data and then loading  into a target place  like Data warehouse is called ETL(Extraction, Transformation, and Loading )

Now Let us know how the data moves from different sources into the data Ware House in different forms elaborately.


As the name suggest it the process of extracting the data from different data sources. Finally, It may extract the data from the different external sources.

Files Sources: Web blog, Flat Files, XML

Relational databases: DB2, Sybase, Oracle  Server

Other sources: Ms-excel, TIBCO, MQ –Series

Data Transformation:

It is the process of transforming the data appeared from different sources into the homogeneous form. Therefore this transformation appears in different forms.  Let us now each of them n Detail.

              Data merging:

It is the process of getting the data from the different source (homogeneous /heterogeneous )  and merges ( combine) them into the unique form.

              This merging is of two types :

                          Horizontal merging: It is the process of merging  the data  from different                                                  sources  when the two sources having different data definitions

                          Vertical merging: It is the process of  merging  the data from different                                                        sources  when the two sources  having similar definitions

ETL/Online IT Guru

              Data cleansing:

This is one type of data checking.  Whenever the data enter in different sources,  it should check and modified based on the requirement. It checks whether the data enter is accurate and consistent. Hence this can occur within a single or between multiple records.

              Data Scrubbing:

it is a type of debugging. whenever the data the arrived is incorrect (or)  any error it should be modified based on the requirement.  The modification may be added (or) deleted based upon the requirement.

              Data Aggregation:

It is a process  of collecting and aggregating the data from different sources


All the data that is transformed from different sources is loaded into the Data ware House so as to be used for OLAP. This OLAP is very crucial as it predicts the business future. So, a  heart for future business and should be analyzed carefully.
Recommended Audience :
Software developersETL  developersProject ManagersTeam Lead’s

It's better to have a fundamental knowledge of keeping up and examining them for implementation. It's great to have knowledge of the utilization of Excel or the worksheets to understand the whole design of MSBI. Checkout in OnlineITGuru now  MSBI Training.