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:
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.
OLAP :
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.
Extraction:
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
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
Loading:
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’sPrerequisites:
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.