Аналитическая витрина (Data Mart) — это представление данных, ориентированное на конкретные бизнес-процессы или задачи. Витрина создается на основе данных, содержащихся в Raw Vault и Business Vault, и служит источником для BI-инструментов, таких как Power BI, Tableau или Excel.

В этом разделе мы рассмотрим, как на основе данных из Business Vault спроектировать и создать витрину, которая предоставит аналитикам удобный доступ к агрегированным и готовым к использованию данным.

Основные этапы построения аналитической витрины

  1. Определение бизнес-задачи: Мы создадим витрину для анализа задержек рейсов по авиакомпаниям, месяцам и причинам задержек. Эта информация поможет выявить тенденции и ключевые проблемы, влияющие на своевременность рейсов.

  2. Проектирование витрины:

    • Выбираются ключевые показатели (например, общее количество задержек, причины задержек).
    • Определяются измерения (авиакомпании, месяцы, годы).

  3. Создание структуры витрины: Витрина проектируется в виде одной или нескольких таблиц в базе данных Business Vault, которые агрегируют данные из Raw Vault.

  4. Загрузка данных в витрину: Реализуется SQL-скрипт для переноса и агрегации данных из таблиц Raw Vault и Business Vault.

Проектирование витрины

Для витрины создадим таблицу Fact_DelayAnalysis, которая будет содержать ключевые метрики, такие как общее количество задержек и распределение причин задержек. Также создадим таблицы измерений (Dim_Airline и Dim_Date) для авиакомпаний и времени.

Создание структуры аналитической витрины

SQL-скрипт для создания таблиц витрины:

-- Создаем таблицу фактов
CREATE TABLE Business_Vault.Fact.DelayAnalysis (
    FactID INT IDENTITY(1,1) PRIMARY KEY,
    Airline_SurrogateKey UNIQUEIDENTIFIER,
    DateKey INT,
    TotalDelays INT,
    CarrierDelays INT,
    WeatherDelays INT,
    NASDelays INT,
    SecurityDelays INT,
    LateAircraftDelays INT
);

-- Таблица измерения для авиакомпаний
CREATE TABLE Business_Vault.Dim.Airline (
    Airline_SurrogateKey UNIQUEIDENTIFIER PRIMARY KEY,
    AirlineName NVARCHAR(255)
);

-- Таблица измерения для дат
CREATE TABLE Business_Vault.Dim.Date (
    DateKey INT PRIMARY KEY,
    Year INT,
    Month INT,
    Day INT,
    MonthName NVARCHAR(50)
);

Загрузка данных в витрину

SQL-скрипт для заполнения таблиц витрины:

  • Заполнение измерений:
-- Загрузка данных в Dim_Airline
INSERT INTO Business_Vault.Dim.Airline (Airline_SurrogateKey, AirlineName)
SELECT DISTINCT h.Airline_SurrogateKey, s.Airline_Name
FROM Raw_Vault.Hub.Hub_Airline h
JOIN Raw_Vault.Sat.Sat_AirlineDetails s ON h.Airline_SurrogateKey = s.Airline_SurrogateKey;

-- Загрузка данных в Dim_Date
INSERT INTO Business_Vault.Dim.Date (DateKey, Year, Month, Day, MonthName)
SELECT DISTINCT 
    YEAR(s.Year * 100 + s.Month) AS DateKey, 
    s.Year, 
    s.Month, 
    1 AS Day, -- Для агрегатов по месяцам Day = 1
    DATENAME(MONTH, DATEFROMPARTS(s.Year, s.Month, 1)) AS MonthName
FROM Raw_Vault.Sat.Sat_AirlineDetails s;
  • Заполнение таблицы фактов:
-- Загрузка данных в Fact_DelayAnalysis
INSERT INTO Business_Vault.Fact.Fact_DelayAnalysis (
    Airline_SurrogateKey, 
    DateKey, 
    TotalDelays, 
    CarrierDelays, 
    WeatherDelays, 
    NASDelays, 
    SecurityDelays, 
    LateAircraftDelays
)
SELECT 
    h.Airline_SurrogateKey,
    YEAR(s.Year * 100 + s.Month) AS DateKey,
    SUM(s.Total_Delays) AS TotalDelays,
    SUM(s.Carrier_Delay) AS CarrierDelays,
    SUM(s.Weather_Delay) AS WeatherDelays,
    SUM(s.NAS_Delay) AS NASDelays,
    SUM(s.Security_Delay) AS SecurityDelays,
    SUM(s.Late_Aircraft_Delay) AS LateAircraftDelays
FROM Raw_Vault.Hub.Hub_Airline h
JOIN Raw_Vault.Sat.Sat_AirlineDetails s ON h.Airline_SurrogateKey = s.Airline_SurrogateKey
GROUP BY h.Airline_SurrogateKey, YEAR(s.Year * 100 + s.Month);

Пример использования витрины в Power BI

После загрузки данных в витрину можно подключить Power BI к базе данных Business Vault и создать следующие визуализации:

  • Гистограмма задержек по причинам.
  • Трендовая линия общего количества задержек по месяцам.
  • Круговая диаграмма доли каждой авиакомпании в общих задержках.

Заключение

Проектирование аналитической витрины — ключевой этап, который обеспечивает доступ к готовым данным для анализа. Использование витрин позволяет эффективно агрегировать данные и предоставлять их в удобном виде для BI-инструментов.