遥测数据数仓设计方案

核心需求分析

数据仓库方案设计

我们将采用经典的星型模型（Star Schema）来构建数据仓库，这种模型非常适合OLAP分析。

1. 数据模型设计 (星型模型)

2. 数据仓库 (Data Warehouse) 选型

考虑到数据量、查询性能和可扩展性，以下是一些推荐的选型：

选型建议: 如果预算允许且希望减少运维负担并获得良好的弹性，强烈推荐云数据仓库 (BigQuery 或 Snowflake)。如果倾向于自建或已有PostgreSQL经验，PostgreSQL + TimescaleDB 是一个非常好的选择。

3. OLAP 引擎选型

OLAP引擎负责处理分析查询。

选型建议: 鉴于现代数据仓库的性能，建议优先采用ROLAP方式。直接使用BI工具连接您选择的数据仓库进行查询和可视化。大多数情况下，这足以满足需求。如果未来遇到特定查询的性能瓶颈，可以考虑针对性地构建物化视图 (Materialized Views)（大多数现代数仓都支持）进行预聚合，或者在极端情况下引入MOLAP工具如 Apache Druid 或 Apache Kylin (但会增加系统复杂度)。

4. 数据处理流程 (ETL/ELT)

数据从采集端到数据仓库并支持查询的流程如下：

Code snippet

graph LR
    subgraph Source [数据源]
        FTU[FTU 设备 (7-8 per XTU)] --> XTU[XTU 终端 (5k-10k)];
    end

    subgraph Ingestion [数据接入]
        XTU --> API{数据接收API / MQTT Broker};
    end

    subgraph Processing [数据处理与加载 (ETL/ELT)]
        API --> Stage[Staging Area (e.g., Cloud Storage / Landing Zone)];
        Stage --> Transform[数据转换/清洗 (Spark / Dataflow / Python / SQL)];
        Transform --> DWH[(Data Warehouse)];
        subgraph DWH [数据仓库 (e.g., BigQuery / Snowflake / PG+Timescale)]
            DimTime[(DimTime)];
            DimFTU[(DimFTU)];
            DimXTU[(DimXTU)];
            FactPowerReadings[(FactPowerReadings)];
        end
        Transform -- Lookup --> DimTime;
        Transform -- Lookup/Update --> DimFTU;
        Transform -- Lookup/Update --> DimXTU;
        Transform -- Load --> FactPowerReadings;
    end

    subgraph Analysis [分析与展现]
        DWH --> OLAP{OLAP Engine (ROLAP via DW)};
        OLAP --> BI[BI / 可视化工具 (Superset / Metabase / Tableau)];
        BI --> User[分析师 / 用户];
    end

    %% Optional Pre-aggregation
    %% DWH --> Aggregation[预聚合/物化视图];
    %% Aggregation --> OLAP;

流程说明:

  1. 数据采集: FTU采集数据，通过XTU每15分钟上报一次。
  2. 数据接入: XTU将数据发送到统一的数据接收点，可以是API网关、消息队列（如Kafka/MQTT）等。
  3. 数据暂存 (Staging): 原始数据首先落地到一个临时存储区域（如云存储上的Bucket或数据库中的Staging表）。这一步是为了解耦接入和处理，并提供数据缓冲。
  4. 数据转换 (Transformation):
    • 清洗: 处理异常值、缺失值（例如，使用插值或标记）。
    • 验证: 检查数据格式、范围是否符合预期。
    • 转换:
      • 将时间戳转换为TimestampKey（关联DimTime表）。
      • 根据FTU ID/XTU ID查找或创建DimFTU和DimXTU中的记录，获取对应的FTUKey和XTUKey（处理维度变化，如SCD Type 2）。
      • 将业务ID（FTU_ID, XTU_ID）替换为代理键（FTUKey, XTUKey）。
      • 调整数据类型以匹配事实表和维度表。
  5. 数据加载 (Loading): 将转换后的数据加载到数据仓库的FactPowerReadings事实表和相应的维度表（如果维度有更新）。
  6. OLAP查询: 分析师通过BI工具连接到数据仓库。BI工具生成SQL查询（ROLAP），数据仓库执行查询并返回结果。
  7. 可视化与分析: BI工具将查询结果以报表、图表等形式展示给用户。

ETL/ELT工具选型:

5. OLAP 聚合查询案例

假设我们需要查询 2025年4月20日，每个XTU管理的FTU的平均总有功功率。

SQL

SELECT
    dx.XTU_ID,                 -- XTU 设备ID
    dt.Date,                   -- 日期
    AVG(fpr.ActivePower_Total) AS Avg_TotalActivePower -- 平均总有功功率
FROM
    FactPowerReadings fpr
JOIN
    DimTime dt ON fpr.TimestampKey = dt.TimestampKey
JOIN
    DimFTU df ON fpr.FTUKey = df.FTUKey
JOIN
    DimXTU dx ON df.XTUKey = dx.XTUKey
WHERE
    dt.Date = '2025-04-20' -- 筛选日期
    AND df.IsCurrent = TRUE   -- 确保使用FTU设备的当前有效记录 (如果使用SCD Type 2)
GROUP BY
    dx.XTU_ID,
    dt.Date
ORDER BY
    dx.XTU_ID;

6. 星型模型 Schema Mermaid 图

Mermaid.live渲染

erDiagram
    DimTime ||--o{ FactPowerReadings : "has"
    DimFTU ||--o{ FactPowerReadings : "measures for"
    DimXTU ||--o{ DimFTU : "manages"

    DimTime {
        BIGINT TimestampKey PK "Timestamp Key (PK)"
        DATETIME FullDateTime "Full Date Time"
        DATE Date "Date"
        INT Year "Year"
        INT Quarter "Quarter"
        INT Month "Month"
        INT DayOfMonth "Day of Month"
        INT DayOfWeek "Day of Week"
        INT Hour "Hour"
        INT Minute "Minute (0, 15, 30, 45)"
        BOOLEAN IsWeekend "Is Weekend?"
        VARCHAR TimeOfDay "Time of Day"
        -- ... other time attributes
    }

    DimFTU {
        BIGINT FTUKey PK "FTU Key (PK)"
        VARCHAR FTU_ID UK "FTU Business ID (UK)"
        BIGINT XTUKey FK "XTU Key (FK)"
        VARCHAR FTU_Model "Model"
        DATE InstallationDate "Installation Date"
        VARCHAR LocationDescription "Location Description"
        DECIMAL Latitude "Latitude"
        DECIMAL Longitude "Longitude"
        VARCHAR Status "Status"
        DATE EffectiveStartDate "SCD Start Date"
        DATE EffectiveEndDate "SCD End Date"
        BOOLEAN IsCurrent "SCD Is Current Flag"
        -- ... other FTU attributes
    }

    DimXTU {
        BIGINT XTUKey PK "XTU Key (PK)"
        VARCHAR XTU_ID UK "XTU Business ID (UK)"
        VARCHAR XTU_Model "Model"
        VARCHAR Substation "Substation/Area"
        VARCHAR IP_Address "IP Address"
        DATE InstallationDate "Installation Date"
        VARCHAR Status "Status"
        -- ... other XTU attributes
    }

    FactPowerReadings {
        BIGINT TimestampKey FK "Timestamp Key (FK)"
        BIGINT FTUKey FK "FTU Key (FK)"
        TIMESTAMP ReadingTimestamp "Actual Reading Time"
        FLOAT UAB_Voltage "UAB Voltage"
        FLOAT UBC_Voltage "UBC Voltage"
        FLOAT UCA_Voltage "UCA Voltage"
        FLOAT UA_Voltage "UA Voltage"
        FLOAT UB_Voltage "UB Voltage"
        FLOAT UC_Voltage "UC Voltage"
        FLOAT IA_Current "IA Current"
        FLOAT IB_Current "IB Current"
        FLOAT IC_Current "IC Current"
        FLOAT I0_Current "I0 Current"
        FLOAT ActivePower_Total "Total Active Power"
        FLOAT ActivePower_A "A-Phase Active Power"
        FLOAT ActivePower_B "B-Phase Active Power"
        FLOAT ActivePower_C "C-Phase Active Power"
        FLOAT ReactivePower_Total "Total Reactive Power"
        FLOAT ReactivePower_A "A-Phase Reactive Power"
        FLOAT ReactivePower_B "B-Phase Reactive Power"
        FLOAT ReactivePower_C "C-Phase Reactive Power"
        FLOAT ApparentPower_Total "Total Apparent Power"
        FLOAT ApparentPower_A "A-Phase Apparent Power"
        FLOAT ApparentPower_B "B-Phase Apparent Power"
        FLOAT ApparentPower_C "C-Phase Apparent Power"
        TIMESTAMP LoadTimestamp "DW Load Time"
    }

总结与考虑

这个方案为您提供了一个结构清晰、可扩展且支持OLAP分析的数据仓库基础。您可以根据具体的技术栈偏好和预算进一步细化选型。