Data Warehouse: ETL, Architecture, Big Data

• These notes cover the data warehouse process, ETL, OLAP, OLTP, Data Marts, Metadata, Data Warehouse Architecture, Big Data, and different architecture types

Data Warehouse Process

• First, data is extracted from sources like transactional systems, spreadsheets, and flat files
• Data is cleaned to rid any inconsistencies, errors, or duplicates
• Cleaning includes data validation to ensure data accuracy and completeness
• Next, extracted and cleaned data is transformed into a format suitable for loading into the data warehouse
• Transformation may involve converting data types, combining data from multiple sources, or creating new data fields
• Transformed data is loaded into the data warehouse
• Data loading involves creating physical data structures and loading the data
• After loading, data is indexed for easier search and retrieval
• Indexing involves creating summary tables and materialized views to improve query performance
• Data maintenance is the final step for ensuring data is accurate and up-to-date
• Maintenance involves periodically refreshing data, archiving old data, and monitoring for errors or inconsistencies

ETL: Extract, Transform, and Load

• ETL is a process that extracts data from various sources, transforms it, then loads it into a target data warehouse
• Popular ETL tools include 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, and SAS Data Management

OLAP (Online Analytical Processing)

• It is a flexible way to make complicated analyses of multidimensional data
• Data in a Data Warehouse is accessed through OLAP queries; databases use OLTP operations
• OLAP activities convert multi-dimensional data in a warehouse into an OLAP cube

OLTP (Online Transaction Processing) vs. OLAP

• OLTP is for Online Transaction Processing
• OLTP queries perform DDL operations on a database
• OLTP systems use data stored in two-dimensional tables with rows and columns
• OLAP performs real-time analysis of multidimensional data in a Warehouse, dealing with De-normalized data

OLTP Characteristics

• Contains current data
• Useful in running a business
• Based on Entity Relationship Model
• Provides primitive and highly detailed data
• Used for writing data into the database
• Database size ranges from 100 MB to 1 GB
• Fast, providing high performance
• Number of records accessed is in tens
• Example: All bank transactions made by a customer

OLAP Characteristics

• Contains historical data
• Useful in analyzing a business
• Based on Star, Snowflake, and Fact Constellation Schema
• Provides summarized and consolidated data
• Used for reading data from the data warehouse
• Data Warehouse size ranges from 100 GB to 1 TB
• Highly flexible, but not fast
• Number of records accessed is in millions
• Example: Bank transactions made by a customer at a particular time

OLTP Examples

• A supermarket server records every single product purchased at that market
• A bank server records every time a transaction is made for a particular account
• A railway reservation server records the transactions of a passenger

OLAP Examples

• A bank manager wants to know how many customers are utilizing the ATM of his branch in order to decide whether to continue with the ATM or relocate it.
• An insurance company wants to know the number of policies each agent has sold in order to improve performance management of agents

Data Marts (DM)

• Data marts are smaller versions of data warehouses, focused on a single subject or area
• Data marts draw data from a limited number of sources since they are focused
• Time taken to build data marts is less than data warehouses

Data Mart vs. Data Warehouse

• Data warehouses have enterprise-wide data and multiple subject areas, while data marts have department-wide data and a single subject area
• Data warehouses use multiple data sources, while data marts use limited data sources
• Data warehouses occupy large memory and take longer to implement, while data marts occupy limited memory and take a shorter time to implement

Types of Data Marts

• Dependent data marts are built by drawing data directly from an existing data warehouse
• Business data is stored in a centralized repository, and a well-defined set of data is extracted for analysis- Specific data set is aggregated, restructured, and populated for querying
• Independent data marts are stand-alone systems built without a central data warehouse and ideal for smaller units within an organization
• Data is obtained from internal/external sources, processed, loaded, and stored until queried for business analytics
• Hybrid data marts combine data from a data warehouse and other operational sources and suit multiple database environments with fast implementation

Metadata

• Metadata is defined as data about data
• In a DWH, metadata defines the source data like Flat Files and Relational Databases
• Metadata defines which table is source/target, and which concept is used to build business logic for transformation

Data Warehouse Architecture

• Data Sources (ERP, Legacy, POS, OLTP/Web, External Data) go through an ETL Process (Extract, Transform, Load) before loading to Data Warehouse
• Data Marts (Marketing, Operations, Finance) support Applications (Visualization, Routine Business Reporting, Data/Text Mining, OLAP, Dashboard, Web, Custom-Built Applications)

Big Data and ELT (Extract, Load & Transform)

• ELT extracts all data, loads it, and then transforms it, using tools like Hadoop
• ELT is used in Big Data

Data warehouse and big data architecture

• Data goes through HDFS (Hadoop distributed file system) to hive to Analytical Data Mart, and the output is Reporting tools and Custom Apps

Data Warehouse Construction Approaches

• Top-Down Approach: Begins with designing the overall data warehouse architecture, then creating individual data marts
• Bottom-Up Approach: Data marts are built first, and integrated it into a central data warehouse

Top-Down Approach

• Data Marts are created directly from the data warehouse which, in turn, connect to staging areas and external sources.

Advantages of Top-Down Approach

• Consistent Dimensional View: Data marts are created directly from the central data warehouse, ensuring a consistent dimensional view across all departments
• Improved Data Consistency: Sourcing all data marts from a single data warehouse promotes standardization
• Easier Maintenance: Centralizing data management simplifies maintenance
• Updates/changes propagate to all connected data marts, reducing effort and time
• Better Scalability: Highly scalable; organizations can add new data marts seamlessly
• Enhanced Governance: Centralized control of data ensures better governance and the organizations can manage data access, security, and quality from a single point
• Reduced Data Duplication: Storing data only once minimizes duplication, saving space
• Improved Reporting: Consistent view of data across all data marts enables more accurate and timely reporting
• Better Data Integration: Integrating data from multiple sources is easier

Disadvantages of Top-Down Approach

• High Cost and Time-Consuming: Requires significant investment
• Complexity: Can be complex, especially for large organizations
• Lack of Flexibility: Adapting to new/changing business needs can be difficult
• Limited User Involvement: Led by IT, which may limit involvement from business users
• Data Latency: May introduce delays in data processing/availability
• Data Ownership Challenges: Centralizing data can create ambiguity around data ownership
• Integration Challenges: Integrating data from diverse sources can be difficult
• Not Ideal for Smaller Organizations: Less suitable due to its high cost

Bottom-Up Approach

• ETL connects staging area and external sources to the Data Marts
• The Data Marts connect to the Data Warehouse, and then to Data Mining

Advantages of Bottom-Up Approach

• Faster Report Generation: Data marts are created first, so reports can be generated quickly
• Incremental Development: Supports incremental development by allowing creation of data marts one at a time
• Organizations achieve quick wins and improve data reporting and analysis over time
• User Involvement: Encourages active user involvement during the design and implementation process
• Flexibility: Highly flexible, as data marts are designed based on unique business function requirements
• Faster Time to Value: Delivers faster time to value, especially useful for smaller organizations
• Reduced Risk: Creates and refines individual data marts before integrating them into a larger data warehouse
• Scalability: Scalable, allowing organizations to add data marts as needed
• Clarified Data Ownership: Each data mart is owned and managed by a specific business unit
• Lower Cost and Time Investment: Less upfront cost and time compared to the Top-Down approach

Disadvantages of Bottom-Up Approach

• Inconsistent Dimensional View: May not provide a consistent dimensional view of data marts
• Data Silos: Results in data silos, where different business units develop their own data marts independently
• Integration Challenges: Integrating multiple data marts into a unified data warehouse can be challenging since Data structures, formats, and granularity may cause inconsistencies
• Duplication of Effort: Different business units may inadvertently duplicate efforts by creating overlapping data marts
• Lack of Enterprise-Wide View: May not provide a comprehensive, enterprise-wide view of data
• Complexity in Management: Managing multiple data marts can be more challenging
• Risk of Inconsistency: Differences in data structures and definitions can make it difficult to compare or combine data
• Limited Standardization: May lack uniformity in data formats and definitions

Types of Architectures

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

Single-Tier Architecture

• It reduces number of data stored in a data warehouse by building a more compact data set
• Helps remove data redundancies and improves data quality
• Inefficient for agencies with large data volumes and multiple data streams
• Has three layers: source, data warehouse, and analysis
• Only the source layer is physical
• Data warehouse layer is virtual, providing data in a multidimensional view
• Lacks a component that separates analytical and transactional processing
• It depends physically only on the source layer

Two-Tier Architecture

• Contains a data staging area "ETL” that ensures data loaded into the warehouse is cleansed and in the right format
• ETL is located between the source layer and data warehouse layer
• It depends physically on source layer and data warehouse layer
• Comprises two tiers - The Data Tier and The Client Tier

Two-Tier Architecture Components

• The Data Tier: The layer where actual data is stored and where various ETL processes are used to load data into the database or the data warehouse.
• Includes Staging Area to ensure data loaded into warehouse is cleansed and in appropriate format.
• The Client Tier: The layer where data is stored in the data warehouse and used to generate insights for the organization to invent/transform solutions based on analyzed trends

Three-Tier Data Warehouse Architecture

• Most widely used architecture
• Data is physically divided in three layers: source, reconciled, and data warehouse
• Source layer: Contains heterogeneous source systems
• Reconciled layer: Reconciliation occurs when data from different sources is matched/cross-checked Sales data from a CRM system may need reconciliation with finance data
• Data warehouse layer: Contains both data marts and data warehouses

Three-Tier Architecture Tiers

• The Bottom Tier: Consists of the database of Data warehouse servers as bottom tier; defined as a relational database system
• Responsible for cleaning, transforming, and loading data using back-end tools
• This can also be termed the data warehouse layer
• The Middle Tier: Consists of the OLAP server, implemented by using either the ROLAP or the MOLAP model which acts as a mediator between the end-user and the database
• This tier is presented as an application layer with an abstract view of the database and a reconciled layer residing between the source data and the data warehouse, and also separates the problems of data extraction at the source end
• The Top-Tier: Consists of the front-end client layer for the architecture
• Consists of the API and tools used for connecting and gathering data out from the data warehouse and also used as Query tools, reporting tools, managed query tools, Analysis tools, and Data mining tools
• This layer can be considered as the source layer consisting of heterogeneous source systems