Введение

Для работы с датасетом Airline_Delay_Cause.csv Источники и Установка программ мы будем строить Data Vault-модель. Она обеспечит гибкость, масштабируемость и возможность интеграции данных из разных источников. Data Vault подходит для аналитических целей, таких как анализ задержек рейсов, причин отмен и производительности авиакомпаний.

Структура Data Vault

Основными компонентами нашей модели будут:

  1. Хабы (Hubs) — хранят уникальные бизнес-ключи.
  2. Линки (Links) — устанавливают связи между сущностями.
  3. Сателлиты (Satellites) — хранят атрибуты, описывающие бизнес-ключи или связи.

1. Хабы (Hubs)

В нашем случае потребуется три хаба:

  1. Hub_Flights

    • Уникальные рейсы.
    • Поля:
      • FlightKey (Surrogate Key).
      • FlightNumber (Business Key).
      • LoadDate.

  2. Hub_Airports

    • Список аэропортов.
    • Поля:
      • AirportKey (Surrogate Key).
      • AirportCode (Business Key).
      • LoadDate.

  3. Hub_Airlines

    • Список авиакомпаний.
    • Поля:
      • AirlineKey (Surrogate Key).
      • AirlineCode (Business Key).
      • LoadDate.

2. Линки (Links)

  1. Link_FlightRoutes

    • Связь рейсов с маршрутами (аэропорт отправления и назначения).
    • Поля:
      • FlightRouteKey (Surrogate Key).
      • FlightKey.
      • OriginAirportKey.
      • DestinationAirportKey.
      • LoadDate.

  2. Link_FlightDelays

    • Связь рейсов с задержками.
    • Поля:
      • FlightDelayKey (Surrogate Key).
      • FlightKey.
      • DelayCategoryKey (например, "weather", "carrier").
      • LoadDate.

3. Сателлиты (Satellites)

  1. Sat_FlightDetails

    • Детали рейсов.
    • Поля:
      • FlightKey.
      • FlightDate.
      • ScheduledDepartureTime.
      • ScheduledArrivalTime.
      • LoadDate.

  2. Sat_AirportDetails

    • Дополнительная информация об аэропортах.
    • Поля:
      • AirportKey.
      • AirportName.
      • City.
      • State.
      • LoadDate.

  3. Sat_DelayDetails

    • Подробности задержек.
    • Поля:
      • FlightDelayKey.
      • DelayMinutes.
      • DelayCause.
      • LoadDate.

Пример данных

Исходный CSV:

FlightNumber Origin Destination DelayMinutes DelayCause FlightDate AirlineCode OriginName DestinationName ScheduledDepartureTime ScheduledArrivalTime
1234 LAX JFK 120 Weather 2025-01-01 AA Los Angeles New York 08:00 16:00

Hub_Flights:

FlightKey FlightNumber LoadDate
1 1234 2025-01-03

Hub_Airports:

AirportKey AirportCode LoadDate
1 LAX 2025-01-03
2 JFK 2025-01-03

Hub_Airlines:

AirlineKey AirlineCode LoadDate
1 AA 2025-01-03

Link_FlightRoutes:

FlightRouteKey FlightKey OriginAirportKey DestinationAirportKey LoadDate
1 1 1 2 2025-01-03

Sat_DelayDetails:

FlightDelayKey FlightKey DelayMinutes DelayCause LoadDate
1 1 120 Weather 2025-01-03

Результат

После реализации Data Vault, студенты смогут:

  1. Анализировать задержки по аэропортам и авиакомпаниям.
  2. Строить маршруты рейсов.
  3. Создавать аналитические отчеты и дашборды.

Практика

скачиваем датасет Airline_Delay_Cause.csv Источники и Установка программ на свой компьютер

Проектирование модели Data Vault – это ключевой этап создания хранилища данных, который позволяет нам структурировать информацию так, чтобы она была гибкой, масштабируемой и соответствовала требованиям бизнеса. В рамках данного раздела мы создадим три основные базы данных:

  1. Staging_Area – для загрузки сырых данных из исходного файла.
  2. Raw_Vault – для хранения модели Data Vault, где данные структурированы в хабы, линки и сателлиты.
  3. Business_Vault – для добавления бизнес-логики и создания витрин данных.

Мы будем использовать следующие инструменты:

  • MS SQL Express – для создания базы данных и реализации структуры Data Vault.
  • SSMS (SQL Server Management Studio) – для работы с базой данных.
  • Pandas – для загрузки исходных данных из файла Airline_Delay_Cause.csv в базу данных Staging_Area.

Важно: цель данного учебного курса – изучение концепций и структуры Data Vault. Мы не будем детально рассматривать использование ETL-инструментов, чтобы сфокусироваться на построении структуры хранилища данных.

Структура базы данных Data Vault

  1. Staging_Area:

    • Содержит сырые данные, загружаемые напрямую из исходного файла.

  2. Raw_Vault:

    • Схема Hub – таблицы хабов, которые содержат уникальные бизнес-ключи.
    • Схема Link – таблицы связей между хабами.
    • Схема Sat – таблицы сателлитов для хранения деталей, изменяемой информации и временных меток.

  3. Business_Vault:

    • Включает бизнес-логику и данные, преобразованные для аналитических задач.

SQL-код для создания баз данных и схем

1. Создание баз данных

-- Создание базы данных Staging_Area
CREATE DATABASE Staging_Area;
GO

-- Создание базы данных Raw_Vault
CREATE DATABASE Raw_Vault;
GO

-- Создание базы данных Business_Vault
CREATE DATABASE Business_Vault;
GO

2. Создание схем в базе данных Raw_Vault

-- Переход в базу данных Raw_Vault
USE Raw_Vault;
GO

-- Создание схем для хабов, линков и сателлитов
CREATE SCHEMA Hub;
CREATE SCHEMA Link;
CREATE SCHEMA Sat;
GO

SQL-код для создания таблиц

1. Таблицы в базе данных Staging_Area

USE Staging_Area;
GO

CREATE TABLE Staging_AirlineData (
    Year INT,
    Month INT,
    DayofMonth INT,
    Carrier NVARCHAR(10),
    OriginAirport NVARCHAR(10),
    DestAirport NVARCHAR(10),
    FlightNum NVARCHAR(50),
    DepDelay INT,
    ArrDelay INT,
    Cancelled BIT,
    CancellationCode NVARCHAR(10),
    WeatherDelay INT
);
GO

2. Таблицы в схеме Hub базы данных Raw_Vault

USE Raw_Vault;
GO

-- Хаб для рейсов (Flight)
CREATE TABLE Hub.Flights (
    FlightKey NVARCHAR(255) PRIMARY KEY,
    FlightNumber NVARCHAR(50) NOT NULL,
    LoadDate DATETIME NOT NULL
);

-- Хаб для аэропортов (Airport)
CREATE TABLE Hub.Airports (
    AirportCode NVARCHAR(10) PRIMARY KEY,
    LoadDate DATETIME NOT NULL
);

-- Хаб для авиалиний (Carrier)
CREATE TABLE Hub.Airlines (
    AirlineCode NVARCHAR(10) PRIMARY KEY,
    LoadDate DATETIME NOT NULL
);

3. Таблицы в схеме Link базы данных Raw_Vault

-- Линк для маршрутов рейсов
CREATE TABLE Link.FlightRoutes (
    FlightRouteKey NVARCHAR(255) PRIMARY KEY,
    FlightKey NVARCHAR(255) NOT NULL,
    Origin NVARCHAR(10) NOT NULL,
    Destination NVARCHAR(10) NOT NULL,
    LoadDate DATETIME NOT NULL
);

4. Таблицы в схеме Sat базы данных Raw_Vault

-- Сателлит для деталей рейсов
CREATE TABLE Sat.FlightDetails (
    FlightKey NVARCHAR(255) NOT NULL,
    FlightDate DATE NOT NULL,
    ScheduledDepartureTime NVARCHAR(10),
    ScheduledArrivalTime NVARCHAR(10),
    LoadDate DATETIME NOT NULL
);

Итог

В этом разделе мы создали три базы данных (Staging_Area, Raw_Vault, Business_Vault) и подготовили структуру для построения модели Data Vault. Также мы настроили схемы в базе данных Raw_Vault для хабов, линков и сателлитов.

Следующим шагом будет загрузка данных из исходного файла Airline_Delay_Cause.csv в базу Staging_Area с использованием Python и Pandas, проектирование и реализация процесса загрузки данных из Staging_Area в Raw_Vault.