Data Warehousing: ETL process

• The data warehouse process involves several steps: data extraction, data cleaning, data transformation, data loading, data indexing, and data maintenance

Data Extraction

• The initial step involves extracting data from various sources like transactional systems, spreadsheets, and flat files

Data Cleaning

• Extracted data undergoes cleaning to eliminate inconsistencies, errors, and duplicates
• This phase includes data validation to ensure data accuracy and completeness

Data Transformation

• Extracted and cleaned data is transformed into a format suitable for loading into the data warehouse
• This transformation can involve converting data types, combining data from multiple sources, or creating new data fields.

Data Loading

• Transformed data is loaded into the data warehouse
• This involves creating the data structures and moving the data into the warehouse

Data Indexing

• After loading, data is indexed to facilitate easy searching and retrieval
• The process includes creating summary tables and materialized views to improve query performance

Data Maintenance

• The final step ensures data accuracy and currency
• Maintenance involves periodic refreshing, archiving old data, and monitoring for errors or inconsistencies

ETL: Extract, Transform, and Load

• ETL is the process of extracting data from various sources, transforming it to meet specific requirements, and loading it into a target data warehouse

Popular ETL Tools

• Informatica PowerCenter
• Talend Studio
• DataStage
• Oracle Warehouse Builder
• Ab Initio
• Data Junction
• SQL Server Integration Services (SSIS)
• SAP Data Services
• Data Migrator (IBI)
• IBM Infosphere Information Server
• Elixir Repertoire for Data ETL
• SAS Data Management

OLAP: Online Analytical Processing

• OLAP provides a flexible approach for complex analysis of multidimensional data
• Data in a Data Warehouse is accessed by running OLAP queries
• OLAP activities convert multidimensional data in a Warehouse into an OLAP cube

OLTP vs OLAP

• OLTP (Online Transaction Processing) systems use data stored in two-dimensional tables with rows and columns

• OLAP is used for real-time analysis of multidimensional data in a Warehouse and deals with De-normalized data

• OLTP contains current data and is useful in running the business whereas OLAP contains historical data and is useful in analyzing the business

• OLTP is based on the Entity Relationship Model and provides primitive and highly detailed data, while OLAP is based on Star, Snowflake, and Fact Constellation Schema and provides summarized and consolidated data

• OLTP is used for writing data into the database, and OLAP is used for reading data from the data warehouse

• OLTP database sizes range from 100 MB to 1 GB, and OLAP Data Warehouse sizes range from 100 GB to 1 TB

• OLTP is fast and provides high performance, whereas OLAP is highly flexible but not as fast

• OLTP number of records accessed is in tens while OLAP number of records accessed is in millions

• OLTP involves all bank transactions made by a customer, while OLAP involves bank transactions made by a customer at a particular time

• TP stands for Transaction Processing while AP stands for Analytic Processing

OLTP examples

• A supermarket server records every single product purchased
• A bank server records every time a transaction is made for a particular account.
• Railway reservation server which records the transactions of a passenger

OLAP examples

• Bank manager wants to know how many customers are utilizing the ATM of his branch for decision-making
• An insurance company wants to know the number of policies each agent has sold for performance management and better planning

Data Marts

• A data mart is a smaller version of a data warehouse, focusing on a single subject or area
• Data marts are focused on one area, they draw data from a limited number of sources
• The time needed to build data marts is generally less compared to building a data warehouse
• Data warehouses contain enterprise-wide data, multiple subject areas, and multiple data sources. Data marts contain department-wide data, a single subject area, and limited data sources
• Data warehouses occupy large memory, while data marts occupy limited memory and Data warehouses have longer implementation times, while data marts have shorter implementation times

Types of Data Marts

• Dependent data marts which are built by drawing data directly from an existing data warehouse
• Independent data marts which are stand-alone systems built without the use of a central data warehouse
• Hybrid data marts which combine data from the data warehouse and other operational sources
• Data from OLTP is extracted and populated into central DWH and the data travels from DWH to data mart in Dependent Data Mart
• Data is directly received from the source system for Independent Data Mart and is suitable for small organizations
• For Hybrid Data Mart, the data is fed from OLTP systems as well as the Data Warehouse

Metadata

• Metadata is defined as data about data
• In a DWH, metadata defines the source data such as flat files and relational databases
• Metadata is used to define source and target tables, as well as the business logic, called transformation, to the actual output

Data Warehouse Architecture

• Data sources like ERP, legacy systems, and external data flow through an ETL process to an enterprise data warehouse
• Metadata is stored and replication is performed
• Data marts can be created for marketing, operations, and finance
• Applications utilize visualization for routine business reporting, data/text mining, OLAP dashboards, web interfaces, and custom applications

Big Data and ELT

• ELT (Extract, Load, & Transform) is used in big data contexts

• ELT involves extraction and loading of all data into Hadoop, followed by transformation for analytics

• HDFS (Hadoop Distributed File System) is used, and analytical data marts are created for reporting

Approaches to Constructing a Data Warehouse

• Top-Down Approach: Start with designing the overall data warehouse architecture first and then later creating individual data marts
• Bottom-Up Approach: Data marts are built first to meet specific business needs, and later integrated into a central data warehouse

Top-Down Approach

• Top-Down Approach involves designing the overall data warehouse structure first, then creating individual data marts

Advantages of Top-Down Approach

• Consistent Dimensional View: Provides a consistent dimensional view across departments, minimizing discrepancies and aligning data reporting

• Improved Data Consistency: Promotes standardization, reducing errors and inconsistencies in reporting for more reliable business insights

• Easier Maintenance: Centralized data management simplifies maintenance, with updates propagating to all connected data marts

• Better Scalability: Highly scalable, allowing seamless addition of data marts as needs evolve, beneficial for rapidly expanding businesses

• Enhanced Governance: Centralized control ensures better data governance, managing access, security, and quality from a single point

• Reduced Data Duplication: Storing data once in the central warehouse minimizes duplication, saving storage space and reducing inconsistencies

• Improved Reporting: Consistent data view across data marts enables accurate, timely reporting, enhancing decision-making

• Better Data Integration: Easier integration of data from multiple sources, providing a more comprehensive view and improving analytics capabilities

Disadvantages of the Top-Down Approach

• High Cost and Time-Consuming: Requires significant investment, making it challenging for smaller organizations

• Complexity: Can be complex, especially for large organizations with diverse needs, demanding expertise and careful planning

• Lack of Flexibility: Adapting to new or changing business requirements can be difficult due to pre-design

• Limited User Involvement: Often led by IT departments, resulting in limited involvement from business users and less effective data marts

• Data Latency: May introduce delays in data processing and availability

• Data Ownership Challenges: Centralizing data may create ambiguity around ownership and responsibilities

• Integration Challenges: Integrating data from diverse sources with different formats can be difficult, resulting in data inconsistencies

• Not Ideal for Smaller Organizations: High cost and resource requirements make it less suitable for smaller organizations

Bottom-Up Approach

• Bottom-Up Approach is where data marts are constructed first to suit particular business needs, then merged into a central data watehouse

Advantages of Bottom-Up Approach

• Faster Report Generation: Reports can be generated quickly since data marts are created first, providing immediate value
• Incremental Development: Supports development of data marts one at a time, allowing for quick wins and gradual improvement
• User Involvement: Encourages active user involvement during design and implementation
• Flexibility: Highly flexible, as data marts are designed based on unique business requirements
• Faster Time to Value: Delivers faster time to value with quicker implementation, useful for smaller organizations
• Reduced Risk: Creating and refining individual data marts before integration reduces the risk of failure and helps identify data quality issues
• Scalability: Scalable, allowing organizations to add new data marts as needed, suitable for businesses experiencing growth
• Clarified Data Ownership: Each data mart is typically owned and managed by a specific business unit, clarifying data ownership
• Lower cost and time investment: Requires less upfront cost and time to design and implement

Disadvantages of Bottom-Up Approach

• Inconsistent Dimensional View: Bottom-Up Approach may not provide a consistent dimensional view of data marts

• Data Silos: Can result in data silos due to independent development by different business units

• Integration Challenges: Integrating multiple data marts into a unified data warehouse can be challenging

• Duplication of Effort: Different business units may duplicate efforts by creating data marts with overlapping data

• Lack of Enterprise-Wide View: May not provide a comprehensive, enterprise-wide view of data

• Complexity in Management: Managing multiple data marts with varying complexities make it challenging.

• Risk of Inconsistency: Increases the risk of data inconsistency and makes it difficult to compare data for analyses.

• Limited Standardization: May lack uniformity in data formats and definitions.

Data warehouse Architectures

• Single-Tier Architecture
• Two-Tier Architecture
• Three-Tier Architecture

Single-Tier Architecture

• Reduces the amount of data stored in a data warehouse by building a compact dataset by removing data redundancies
• Not ideal for large data volumes or multiple data streams due to inefficiency
• A primary drawback is the absence of a component to separate analytical and transactional processing
• It has a source layer, data warehouse layer and analysis layer
• The data warehouse layer is virtual and provides data in a multidimensional view
• It depends physically only on the source layer

Two-Tier Architecture

• Architecture contains a data staging area "ETL” that ensures any data you load into the warehouse is cleansed and in the right format
• It depends physically on source layer and data warehouse layer
• Comprises the Data Tier, and the Client Tier
• The Data Tier stores data after ETL processes
• The Client Tier has all the datawarehouse data and is used to generate insights to invent or transform results based on generated reports

Three-Tier Data Warehouse Architecture

• The most widely used architecture for data warehouse systems, physically divided into three layers: source, reconciled, and data warehouse

• Involves a source layer, a reconciled layer which is the actual "reconciliation" making for accuracy and consistency, and the data warehouse layer

• The three layers of architecture are Bottom Tier, Middle Tier, and Top Tier

• The bottom tier consists of the database of the data warehouse servers implemented as a relational database system

• It cleans, transforms, and loading the data present in the database for integration

• The middle tier consists of the OLAP server where this acts as a mediator and has a reconciled layer for the data

• Integrates the data extraction to transform it into better data warehouse

• The top tier consists of the front-end client layer and has all the API and tools for connecting and gathering data