Database Systems Overview

Introduction to Data Warehouses and ETL and Data Manipulation (1)

• Data warehouses are single, complete, and consistent stores of data from various sources.
• They are designed to be readily understood and used for business contexts.
• A DBMS (Database Management System) is used to maintain and access data efficiently.
• OLTP (Online Transaction Processing) is typical use of DBMS for fast & frequent transactions like insertions, updates, deletions for active data.
• Old data is archived.
• Systems ensure data security and integrity.
• Databases can be queried using query languages like SQL.
• An example query is listing student IDs and grades for a specific course with grades at or above "B."

Relational Model

• Normalized tables are created to minimize redundancies and aid data management.
• Each table aligns with an entity (e.g., student, class).
• Attributes describe entity specifics (e.g., student name, address).
• A primary key uniquely identifies each entry within a table (e.g., student ID).

Problem: Heterogeneous Information Sources

• Data sources (personal databases, scientific databases, digital libraries, web) have differing interfaces, data representations, and information inconsistencies.

Problem: Data Management in Large Enterprises

• Informational systems are often fragmented (vertical stovepipes).
• User-driven development of operational systems commonly leads to this fragmented design.

Goal: Unified Access to Data

• An integration system brings together diverse data sources for consistent access.
• The system consolidates information, provides a uniform user interface, and facilitates data sharing.

What is a Data Warehouse? (A Practitioner's Viewpoint)

• A data warehouse is a single, complete, consistent store of data from various sources.
• It allows end-users to understand and use the data for business contexts.

Data Warehouse

• Data warehouse software facilitates information processing through a platform of consolidated, historical data.
• It is designed for quick data retrieval and analysis.
• Subject-oriented: Contains data based on subjects of interest for decision support (e.g., sales by region).
• Integrated: Data from multiple heterogeneous sources (e.g., sales, inventory) are combined.
• Time-variant: Includes historical data that extends beyond current operational systems.
• Non-volatile: Data isn't directly updated within the warehouse itself (typically ETL processes happen in a batch fashion.)

A Data Warehouse is...

• It is a repository for diverse data.
• It addresses data integration problems.
• Data is organized by subject in contrast to being organized by application.
• It is used for analysis and data mining
• It is optimized differently from transaction-oriented databases.
• Executive-oriented user interfaces

A Data Warehouse is... (additional characteristics)

• Large volumes of non-volatile, historical data.
• Time attributes are important, along with infrequent data updates, and may be append-only.
• Data warehouse examples include customer history at an insurance firm, and financial information/portfolios.

Why a Warehouse?

• Two approaches: Query-Driven (Lazy), Warehouse (Eager).

The Traditional Research Approach

• Query-driven systems—requests for data are executed on query occurrence
• Data retrieval happens as needed

Disadvantages of Query-Driven Approach

• Slow query processing is possible.
• Information sources may be slow or temporarily unavailable.
• Filtering and integrating data can be inefficient for frequent queries.
• Compete with local processing at sources
• Not as widespread as other methods

The Warehousing Approach

• Information is integrated beforehand.
• Data is stored directly to query and analyze.

Advantages of Warehousing Approach

• Query performance is good.
• Data warehouse doesn't interfere with local processes
• Data is modified, annotated, summarized, and restructured.
• Historical data can be stored securely.
• Warehousing is used in the industry today.

Not Either-Or Decision

• Query-driven systems work well for rapidly changing data, large data volumes, and diverse data sources with evolving needs.

Generic Warehouse Architecture

• High-level architecture showing the key components for a data warehouse system.
• Design phase, Maintenance, Loading, and Optimization.
• Extractors/monitors bring in, transform, and load data into the warehouse.

Data Warehouse Architectures: Conceptual View

• Single-layer architecture: Every data element exists only once in the virtual warehouse.
• Two-layer architecture: Real-time data and derived data are stored separately.

Three-layer Architecture: Conceptual View

• The transformation of real-time data into derived data requires two steps.

Issues in Data Warehousing

• Warehouse Design
• Extraction (Wrappers, monitors)
• Integration (Cleansing, merging)
• Warehousing specification & Maintenance (Optimizations)
• Miscellaneous considerations

Data Warehouse vs OLTP Summary

• Data warehouses and OLTP (online transaction processing) systems handle data differently.
• Data warehouses: Work with enterprise-wide information, using a non-volatile database optimized for read-only querying and analysis.
• OLTP: Handle transaction details in real-time, with data often normalized.

Multi-Dimensional Data

• Measures: Numerical data being tracked.
• Dimensions: Business parameters specifying transactions (e.g., geography, time, product).
• Dimensional modeling is a technique for structuring data around business concepts.
• ER models depict entities and relationships.
• Dimensional models show measures and dimensions.

The Dimensional Model

• Illustrates the design of data structures for analysis (e.g., sales by product line, sales by store).
• Fact tables hold numerical measurements, while dimension tables provide descriptive information.

Dimensional Modeling

• Dimensions are categorized into hierarchies (e.g., days, weeks, quarters).
• Product dimensions can include product, product line, or brand.
• Dimensions have attributes that describe characteristics.

Basic Building Blocks

• Star Schema: Multi-dimensional database design utilizing a traditional relational database.
• Dimensions tables
• Fact tables

Dimension Tables

• Describe specifics of a business perspective.
• Consist of relatively static data.
• Are linked to other tables through foreign key references

Fact Tables

• Contain numeric business metrics.
• May have calculated measures.
• Data records are not deleted.
• Often include time-stamped data; may include aggregated data.
• Use composite keys linking to dimension tables.

Star Schema Example

• Diagram showing basic relationships among tables in a star schema. (Products, sales, locations.)
• Different dimension tables and fact tables are linked together.

Star Schema with Sample Data

• Actual data values to show how tables function. (Example Product, Period, Store IDs.)

Data Warehouse Usage

• Information processing: Querying, basic analytics, reports.
• Analytical processing: Multi-dimensional analysis (OLAP), drilling, pivoting.
• Data mining: Knowledge discovery, constructing models.

The Big Picture: DBs, Warehouse, & OLAP

• Flow diagram of data integration and analysis processes, from source data to analysis through the data warehouse.
• Data is transformed.
• OLAP engine is the data warehouse system.

On-Line Analytical Processing (OLAP)

• Front-end system for data warehouse.
• Allows easy manipulation of data in various levels of abstraction.
• Aggregations are pre-calculated to deliver fast results.

OLAP: Data Cube

• OLAP employs a multidimensional format, like a cube, to support efficient data queries and response times
• Data can be viewed from various angles (Product, Date, Country). The example shows data on overall sales of TVs in the US.

Typical OLAP Operations

• Roll up (drill up): Summarize data across hierarchies/dimensions.
• Drill down (roll down): Reverse of rollup; drill further into detail.
• Slice and dice: Extract or filter sections of the cube (2 dimensional)
• Pivot/Rotate: Reorganize to change the orientation or presentation of the data
• Drill across/through: Exploring multiple fact tables or the data sources.

OLAP: Data Cube Operations

• Slicing: Selecting dimensions to view.
• Dicing: Specifying values along dimensions (Example, Sales of PCs in different Countries in each quarter.)
• Drilling down: Viewing highly detailed data by navigating through data hierarchy.
• Rolling up: Summarizing data based on higher levels.

OLTP vs. OLAP

• Comparison table outlining key differences in user roles, database design, data characteristics, frequency and operational use cases.

Warehouse is a Specialized DB

• Comparison of standard databases (OLTP) and data warehouses (OLAP):
• OLTP: Mostly updates, many small transactions, Mb-Gb data volumes, raw data, less complex queries, thousands of users).
• OLAP: Mostly reads, complex queries, Gb-Tb data, summarized data, hundreds of users.

Data Integration Issues

• Problems identified in data integration, including differing data names and keys across multiple data sources.

Data Integrity Problems

• Problems like inconsistent spellings, multiple names for the same entity.
• Different account numbers assigned to the same customer.
• Inconsistent record keeping and omissions in data entry.

Factors driving the DW explosion

• Moore's Law improvements in storage and computing, along with commodity networking, increasing data volumes, and improved analysis solutions, have pushed the growth of data warehouse systems.
• This growth has outpaced traditional systems.

10,000 km. view: Complexity vs Cost

• Comparison of various data warehouse deployment models. (Cloud Data Warehouse, Buy an appliance, etc.)

Instant gratification

• Data warehouse deployment model comparisons showing the time it takes to bring the system online to different points on the product maturity spectrum. (weeks, months, minutes)

Scalability and the price of agility

• Comparison of various data warehouse deployment models: time required to make needed adjustments vs cost.

Unfortunately, no “free lunch”

• Trade-offs in performance and cost for different data warehouse deployment models. (Cloud Data Warehouse vs. Appliance vs. on-premises)

Why Cloud DW?

• Rationale for using cloud-based data warehouses: no capital expenditure, fast insights, lower storage costs, flexible scaling of computing resources, and streamlined upgrades.

Shared Disk/Storage

• Detailed approach to how shared disk data warehouses work. (Commodity servers interconnected through shared storage devices.)

Shared-Nothing

• Explains architecture where storage is local and separate networks (like a server farm), allowing for parallel data processing and improved scalability. (Commodity clusters, local databases)

Table Partitioning

• Describe method for effectively spreading data across multiple storage devices, so data I/O (Input/Output) and processing is accelerated. (Hash partitioning, etc.)

Partitioned Parallelism

• Use of partitioning across multiple systems or computers for parallelization relational operation, like selecting, joining and other aggregated data processes.

Amazon (AWS) Redshift

• Description of Amazon's data warehouse service (classic shared nothing with local attachment storage, leveraging AWS services for processing and storage)
• Popular for its market adoption.

A Redshift Instance

• Internal structure showing how Redshift breaks down data storage into partitioned slices for improved query speed and parallel processing.

Redshift Summary

• Detailed summary of Redshift services noting its success, design, its strengths and weaknesses.

Snowflake Elastic DW

• Description of Snowflake data warehouse system, noting its shared-storage attributes, separation of compute and storage, its capability to handle unstructured data, and its schema-less nature.

Snowflake Architecture

• Architecture breakdown of the Snowflake system, showing the interaction between components and the flow of data (shared cloud services, infrastructure management, metadata, compute layers, security)

Snowflake Summary

• Summary of Snowflake services, and its design features and benefits. (e.g., designed for the cloud, independent compute/storage, direct JSON query capability.)

Microsoft Azure SQL Data Warehouse

• Microsoft's shared disk data warehouse design.
• Uses SQL Server technology.
• Leveraging Azure storage.

A SQL DW Instance

• Internal structure of a SQL DW instance showing partitioning of storage and control nodes.

SQL DW Wrap Up

• Features and benefits of Microsoft's data warehouse service are described.

Google BigQuery

• Describing Google's cloud data warehouse service noting the separation of compute and storage features.

BigQuery Tables

• Discusses table structure within Google BigQuery and data storage formats. (Column format, etc.)

Elasticity

• Explanation of how AWS Redshift, SQL DW, Snowflake, and BigQuery offer elasticity with varying degrees of control over scaling compute or storage.

What about ... (Spark, Impala, Hive, Presto, etc.?)

• Note that those systems are not described as "SAAS" offerings in this presentation.
• All use shared storage, like HDFS.

Questions?

• Placeholder for questions.