Data Normalization and warehousing (p2+t2)

What is Data Warehousing?

• Storage of historical data (OLAP)

• Current data = operational data (OLTP)

• Used for Business Intelligence (BI) and decision support

• Helps in trend, pattern, and insight analysis

• Optimized for queries and reporting

• Not used for daily transactions

Key Characteristics of Data Warehousing

1. Subject-Oriented Data Organization (Multiple subjects)

• Data is organized by business subjects

• Focus is on analysis, not operations

• Examples of subjects:

• Sales

• Inventory

• Human Resources (HR)

2. Integrated Data Sources (Multiple formats)

• Different department = different DB so may be different formats

• Data comes from multiple systems

• CRM

• ERP

• Flat files (CSV)

• Data is:
 • Cleaned

• Standardized

• Stored in a unified format

• Example: Customer ID alignment

3. Time-Variant Storage (Multiple year)

• Stores multi-year data

• Supports:

• Trend analysis

• Comparisons over time

• Example:

• Historical monthly sales data

OLTP (Online Transaction Processing)

• It is used to record and process daily business transactions

• Real-time transactional processing

• Company should keep the past data in a separate system and keep the daily data in a
separate system

Functionality

Short operations:

• Insert record (New entry)

• Update record (entry adjusted)

• Delete record (entry deleted is not materialised)

Supports daily operations such as:

• Orders

• Payments

• Inventory

• Require high speed, require accuracy (Through constraints), and integrity

Common Uses of OLTP Systems

Banking

• ATM

• Transfers

E-commerce

• Orders

• Payments

Airlines

• Bookings

• Seats

Retail

• POS sales

• Stock updates

Main features of OLTP system

1. Real-Time Processing

• Data is updated immediately

• No delay in recording transactions

Example

• When a customer places an online order, it is recorded instantly

2. High Transaction Volume

• Handles large number of small transactions

• Many users can work at the same time

Example

• An e-commerce website processes thousands of orders during a sale

3. Atomic Transactions
 • Each transaction is treated as one complete unit

• If one part fails, the whole transaction is cancelled

Example

• If online payment fails, the order is not completed

4. Normalized Database Structure

• Data is stored in separate tables

• Reduces duplication of data

• Improves data consistency

Example

• Customer details, order details, and product details are in different tables

5. Focus on Data Integrity and Concurrency

• Ensures data is accurate and consistent

• Uses:

• Primary keys

• Foreign keys

• Handles multiple users at the same time safely

Example

• Two users cannot book the same seat at the same time

6. Short and Fast Queries

• Queries are simple and quick

• Used to read or update small amounts of data

Example

• Checking product availability in an online store

Benefits of OLTP Systems

• Real-time operational data (sath sath system update for sales and receibles)

• High speed and reliability

• Handle multiple users at once

Limitations of OLTP Systems

• Complex normalized design

• Limited analytics capability (go for OLAP)

• High resource requirement (For real time processing resources are required more)

OLTP vs Data Warehouse (Summary Table)

Feature OLTP Systems Data Warehousing

Purpose Daily transactions Historical analysis

Data Volume Small, current data Large, historical data

Data Structure Highly normalized Usually denormalized

Operations Insert, update, delete Read-only queries

Performance Optimized for speed Optimized for analysis

Query Type Short and simple Complex queries

Data Integrity Very strict Focus on consistency

Example Use Online order processing Sales trend analysis

ETL Process (Extract, Transform, Load)

• ETL stands for Extract, Transform, Load

• It is a core activity in data warehousing

• Used to prepare data for analysis and reporting

Main Stages of ETL Process

1. Data Extraction (from different sources)

2. Data Transformation (convert in uniform format)

1. Data Extraction

• Collect data from multiple systems

Common Sources

• MySQL

• PostgreSQL

• APIs

• CSV files

• Excel files

• SAP

• Salesforce (its an app)

Challenges

• Different data formats

• Data availability (working time data is not available)

• Extraction frequency

Example

• Daily sales data

• CRM data

• Customer feedback extraction

2. Data Transformation

Clean, convert, and standardize data

Key Activities

• Remove duplicates

• Remove blank values

• Convert data types (e.g. dates)

• Apply business rules (Validation rules)

Example

• Convert euros into dollars

• Prepare unified data for reports

3. Data Loading

• Move transformed data into the data warehouse

• Final version is ready for analysis

Types of Loading

• Initial load:

• Full historical data loaded first time

• Incremental load:

• Only new or updated data loaded

Example

• Daily sales data loaded into sales table

Importance of ETL Process

• Consistency:
Combined data with a unified structure

• Accuracy:
Cleaned and reliable data

• Efficiency:
Faster querying and reporting

Types of Data Marts

1. Dependent Data Mart

• Created from an existing data warehouse

• Uses already cleaned and integrated data

Example

• Sales data mart created from the main data warehouse

• Created directly from source systems

• Does not use a central data warehouse

Example

• Marketing team builds its own data mart from CRM data

Benefits of Data Marts

1. Speed

• Smaller data size

• Queries run faster

2. Focus

• Designed for specific department needs

• Unrelated data is ignored

Example

• Marketing data mart stores:

• Campaign performance

• It ignores HR data like attendance