Data Warehouse Overview and Analytics

Data Warehouse Overview

• A data warehouse aggregates data from one or more sources into a single, consistent data store.
• This is done to support data analytics.
• Objectives include defining a data warehouse, identifying use cases, and listing benefits.

Data Warehouse Analytics

• Data warehouse systems support data mining, artificial intelligence, machine learning, front-end reporting, and OLAP (Online Analytical Processing).
• The initial location of data warehouses was on-premises, within enterprise data centers.
• The first systems were hosted on mainframes, then on Unix, Windows, and Linux systems.
• The 2000's saw larger data volumes, data warehousing appliances (DWAs) (consisting of specialized hardware with pre-integrated software), and the development and increased use of appliances.
• More recently, cloud data warehouses have become popular, offering scalable service pay-as-you-go.

Who Uses Data Warehouses?

• Data warehousing is used by practically every industry, including e-commerce, transportation, medical, banking (including fintech), social media, and government.
• Transportation optimizes routes, travel times, equipment needs, and staffing requirements.
• Banking and fintech use data warehouses to evaluate risks, detect fraud, and cross-sell services.
• Data warehousing is used to centralize data from disparate sources, creating a single source of truth that yields better data quality and speedier business insight, facilitating better decision making.

Benefits of Data Warehouses

• Data warehouses centralize data from disparate sources, creating a single point of truth.
• Data warehouses leverage all data while enhancing speed of access.
• Data warehouses facilitate smarter decisions using business intelligence (BI).
• Data warehouses create competitive advantages and gains.

Data Marts Overview

• Data marts are subsets of enterprise data warehouses.
• Data marts support tactical decisions, focusing end-users on relevant data, saving time otherwise spent searching the data warehouse for answers.

What is a Data Mart?

• Data marts are specialized data repositories containing data for tactical decision making.
• Examples include Shipping, Manufacturing, Sales, Finance, Warranty and Marketing Data Marts.

Data Mart Structure

• Data marts typically employ relational databases.
• They commonly use star or snowflake schemas.
• A central fact table of business metrics usually exists, surrounded by associated dimension tables.

Data Repository Comparisons

• Data marts are OLAP (Online Analytical Processing) systems, prioritizing read-intensive operations.
• They use transaction databases (databases) or warehouses for data sources, containing clean, validated analytical data and accumulating history for trend analysis.
• Data warehouses are large repositories with a broad, strategic scope, generally prioritizing writes, containing raw, unprocessed (and not always historical data) from operational applications.

Types of Data Marts

• Dependent Data marts - inheriting security from the EDW, using cleaned and transformed data, and having simpler data pipelines.
• Independent Data marts - require custom ETL data pipelines (and potentially additional security measures).
• Hybrid Data Marts combine these approaches.

Data Mart Purpose

• Data marts provide timely, relevant data.
• Data marts facilitate rapid query responses and cost efficiency, ensuring secure access.

Data Lakes Overview

• Data lakes store large amounts of structured, semi-structured, and unstructured data in their native formats.
• Data can be loaded without defining a schema or knowing the use case beforehand.
• Data lakes are repositories of raw data straight from their sources.

Data Lake Benefits

• Handling all types of data (unstructured, semi-structured, and structured).
• Providing scalable storage capacity.
• Saving time by allowing data to be loaded without first defining structures and schemas.
• Quickly repurposing data for various use cases.

Data Lake Vendors

• Amazon, Cloudera, Google, IBM, Informatica, Microsoft, Oracle, SAS, Snowflake, Teradata, and Zaloni are some popular vendors.

Data Lakes Versus Data Warehouses

• Data lakes contain raw data, whereas data warehouses contain processed data.
• Data lakes generally do not need a schema defined beforehand.
• Data warehouses use pre-defined schemas.
• Data lakes use a flexible approach to data quality.
• Data warehouses are more rigid in their data curation and data governance.
• Data lakes are often used by data scientists, data developers, and business analysts using curated data, while data warehouses tend to be used by business analysts and data analysts.

Data Warehouse Architecture Overview

• Data warehouse architecture details depend on use cases like generating reports/dashboards, exploratory data analysis, automation, and self-service analytics.

General EDW Architecture

• The general architecture includes: Data Sources, Staging Area/Sandbox, Enterprise Data Warehouse Repository, Data Marts and Analytics & BI Tools, and ETL (Extract, Transform, Load) processes.

EDW Reference Architectures

• Vendor-specific reference architectures may exist to adapt the general model, or for interoperability and testing tool integrations.

Cubes, Rollups and Materialized Views

• Data cubes are often used in OLAP systems.
• Data cube operations include slicing, dicing, drilling up/down, pivoting, and rolling up.
• Materialized views are "snapshots" of queries to speed data retrieval, and can automatically keep results up-to-date or refresh on demand.

Fact and Dimension Tables

• Fact tables hold the facts of a business process, and include foreign keys to dimensions tables.
• Fact tables can contain levels of detail data, or summary aggregates.
• Dimension tables categorize facts, using variables that answer specific questions related to the facts.

Star Schema

• Star schemas connect facts with dimensions, radiating from a central fact.
• Dimensions are linked using keys to the fact table.

Snowflake Schema

• Snowflake schemas are normalized star schemas.
• Snowflake dimension tables may be split into child tables.

Modeling Business Processes (with star schema)

• Selecting a business process.
• Choosing the appropriate level of detail.
• Identifying relevant dimensions and facts for analysis.

Star and Snowflake Schema Comparison

• Star schemas are faster for read operations but lack some normalization features.
• Snowflake schemas are better suited for write performance and normalization but may be slower for read operations.

This quiz covers the fundamentals of data warehousing, including its definition, objectives, and use cases. Learn how data warehouses support analytics, machine learning, and reporting, along with their evolution from on-premises systems to cloud solutions. Explore who utilizes these critical data systems in the modern business landscape.