TRAINING (DW-2) - DATA WAREHOUSE CONCEPTS - Part2 (OLTP vs OLAP & DW Architecture)

• What is Data
  Anything which is giving some meaningful information is known as data

• Types of Data
  Data is of two types
• Transactional Data
• Is run time data or day to data
• Is current and detail
• Is useful to RUN the business
• Is stored in OLTP (On Line Transactional Processing)
• Source of transactional data is Applications.
• Examples: ATM, Share market transcations etc
• Analytical Data
• Is useful to ANALYSE the business
• Is Historical and Summarized
• Is stored in OLAP (On Line Analytical Processing) or DW (Data Warehouse) or DSS (Decision Support System)
• Source of analytical data is OLAP

• Types of Databases
• OLTP (On Line Transactional Processing)
• OLAP (On Line Analytical Processing)

OLTP	OLAP
Is useful to store Transactional data	Is useful to store Analyatical data
Is useful to run the business	Is useful to Analyze the business
The nature of data is current and Detail	The nature of data is historical and summarized
OLTP Supports CRUD(Create , Partially read, update and delete)	OLAP supports only read
It is a application oriented DB	It is subject oriented DB (For example Sales or Marketing *Subject Area or Business Area or Data Mart) *Subject Area (Grouping particular type of tables. For example Sales tables)
It is volatile	It is non volatile
In OLTP data storage time is fixed	In OLAP data storage time is *variant * Keep changing due to incremental load. For example at the start/beginning of any business date will be for zero month but after three month of business operations OLAP DB is going to have three month of data
OLTP DB are isolated as Applications	OLAP is integrated as per subject area
Number of users are more(customers + emp)	Number of users are less (MM+HM)
In OLTP we will use normalizes schema (No duplicate data or redundency)	In OLAP we will use Denormalized Schema
In OLTP we will use Primary/Natural Key (Unique and Not null)	In OLAP we won't use Primary/Natural Key since we can have duplicate data for historical records. An alternate to primary key is to use *surrogate key. * In OBIEE surrogate key is ROW_WID.

• DW Architecture

End User Application > Source DB > Staging Area (Transformation) > Target DB > Symantic Layer (rpd) > Reporting Layer > Dashboard

• Date Warehouse Tools
  Two type of Data Warehouse tools
• ETL Tools
• Reporting Tools

  ETL Tools	OLAP Tools
  Informatica Data Stage Ab-Initio SSIS ODI (ELT tool) OWB BODI	OBIEE BI Publisher Cognos SAP-BO DOMO Qlick View MSTR