давай будем использовать инструменты: База данных - SQL Server Express Инструменты - ETL/ELT dbt (Data Build Tool) BI-инструменты - Power BI Desktop Средства разработки и администрирования - VS Code, MS SQL источник данных - Airline Dataset (Bureau of Transportation Statistics, США)

Источники и Установка программ

Сценарий использования Airline Dataset (Bureau of Transportation Statistics, США) в обучающем курсе

Airline Dataset предоставляет богатую информацию о рейсах, задержках, аэропортах и авиалиниях, что делает его идеальным для построения учебного сценария, ориентированного на реализацию Data Vault. Ниже представлен пример сценария, который можно использовать в курсе.

Описание сценария

Учащиеся выступают в роли инженеров данных, работающих над построением аналитической платформы для авиакомпании. Цель — создать Data Vault, который позволит анализировать:

  1. Статистику по рейсам и аэропортам.
  2. Задержки рейсов и их причины.
  3. Популярные маршруты и направления.

Данные поступают из нескольких источников:

  • Flight System: Содержит информацию о рейсах (даты, маршрут, авиакомпания).
  • Airport System: Список аэропортов.
  • Delay Reports: Данные о задержках и их причинах.

6.1. Проектирование модели Data Vault

Хабы (Hubs):

  1. Hub_Flights: Хранит уникальные рейсы.

    • Поля: FlightKey (Surrogate Key), FlightID (Business Key), SourceSystem, LoadDate.

  2. Hub_Airports: Хранит данные об аэропортах.

    • Поля: AirportKey (Surrogate Key), AirportID (Business Key), Name, City, Country, SourceSystem, LoadDate.

  3. Hub_Airlines: Хранит данные об авиакомпаниях.

    • Поля: AirlineKey (Surrogate Key), AirlineID (Business Key), Name, SourceSystem, LoadDate.

Линки (Links):

  1. Link_FlightRoutes: Связь рейсов с маршрутами.

    • Поля: FlightRouteKey, FlightKey, OriginAirportKey, DestinationAirportKey, LoadDate.

  2. Link_FlightAirlines: Связь рейсов с авиакомпаниями.

    • Поля: FlightAirlineKey, FlightKey, AirlineKey, LoadDate.

Сателлиты (Satellites):

  1. Sat_FlightDetails: Сателлит для деталей рейсов.

    • Поля: FlightKey, DepartureTime, ArrivalTime, LoadDate, SourceSystem.

  2. Sat_AirportDetails: Сателлит для аэропортов.

    • Поля: AirportKey, RunwayLength, TerminalCount, LoadDate.

  3. Sat_DelayReports: Сателлит для данных о задержках.

    • Поля: FlightKey, DelayTime, DelayReason, LoadDate, SourceSystem.

6.2. Разработка ETL ELT-процесса

Этапы:

  1. Staging Area:

    • Загрузка сырых данных из CSV/JSON файлов.
    • Очистка и стандартизация данных.

  2. Raw Vault:

    • Создание хабов, линков и сателлитов.
    • Использование surrogate keys для связи данных.

  3. Business Vault:

    • Добавление бизнес-логики, например, классификация задержек на краткосрочные и долгосрочные.

6.3. Построение аналитической витрины

На основе Data Vault создаются Data Marts для аналитики:

  1. Маршрутная аналитика:

    • Самые популярные маршруты (по числу рейсов).
    • Аэропорты с наибольшей загруженностью.

  2. Анализ задержек:

    • Топ причин задержек.
    • Среднее время задержки по аэропортам.
    •  

4. Визуализация данных

Использование Power BI:

  • Дашборд 1: Анализ рейсов
    • Карта с отображением маршрутов.
    • График с количеством рейсов по авиакомпаниям.
  • Дашборд 2: Задержки рейсов
    • Таблица причин задержек.
    • Диаграмма с распределением времени задержек.