大家好,我是Petter Guo

一位热爱探索的全栈工程师。在这里，我将分享个人的Technical essentials，带你玩转前端、后端到 DevOps 的硬核技术，解锁AI，助你打通技术任督二脉，成为真正的全能玩家!！

如果对你有帮助, 请点赞+ 收藏 +关注鼓励下, 学习公众号为 全栈派森。

通过本节课程你将学习:

1. 数据仓库用途有哪些。
2. 如何进行数据模型搭建。
3. 怎么样进行数据指标加工。

本文内容是基于python+airflow项目基础进行开发, 感兴趣可以看一看。

数据仓库用途

1. 统一和整合数据

数据在企业里通常是分散的，存储在不同的业务系统里（比如CRM、ERP、财务系统、电商平台等），格式和标准也各不相同。数据仓库就像一个集中的数据枢纽，能把来自不同源头的数据抽取过来，进行清洗、转换和整合，然后统一存储。这解决了数据孤岛问题，确保了所有部门都能在同一个“数据版本”上进行分析，避免了因为数据不一致而产生的矛盾。

2. 支持商业智能（BI）和分析

这是数据仓库最核心的用途。BI工具（如Tableau、Power BI）可以直接连接到数据仓库，进行快速的查询和分析。通过数据仓库，业务分析师、管理层和决策者可以轻松地回答以下问题：

• 业务表现分析： 上个季度的销售额是多少？哪个地区的业绩最好？
• 趋势分析： 过去三年用户的购买行为有什么变化？
• 客户行为洞察： 哪些客户群体流失率最高？哪类产品最受新用户欢迎？
• 预测建模： 基于历史数据，预测下个月的销售额或用户增长情况。

3. 历史数据存储和归档

业务系统通常只存储近期的交易数据，以保证性能。数据仓库则可以长期存储海量的历史数据，这对于长期趋势分析、年度对比和合规性审计至关重要。例如，公司需要分析五年前的营销活动效果，这些数据通常只存在于数据仓库中。

4. 提升查询性能

数据仓库中的数据经过预聚合和分层设计，特别针对分析查询进行了优化。这比直接在OLTP（在线交易处理）系统上进行复杂查询要快得多。例如，BI报表需要统计每天、每周的销售总额，数据仓库可以提前计算好这些结果，当用户查询时直接返回，大大缩短了等待时间。

做的最核心的用途也是支持商业智能BI和分析, 提供给管理层进行数据趋势掌控, 接下来就进行数据模型搭建, 本人也是不断学习的过程, 在这个过程中, 对数据仓库有了一定的了解, 这篇文章仅做本人对数据仓库的理解与学习分享。欢迎指导

数据模型搭建

将数据模型分为公共维度（Public Dimensions）、指标集（Metric Sets）和指标统计结果集（Metric Statistics Results）这三层，是为了更好地构建一个结构化、可复用、可扩展的数据仓库。这种分层架构不仅能提升开发效率，还能确保数据的一致性和准确性，是现代数据BI平台的核心实践。

这里声明下, 以下会有创建DB的代码, 仅供参考, 这里的数据模型理念, 也是非常实用的, 可供参考学习。

1. 公共维度 (PD)

作用： 维度表是数据仓库的基础，它描述了业务的各个方面，是BI分析的“切面”。公共维度表定义了业务的核心实体和属性，比如公司,部门,年龄, 性别等。

CREATE TABLE `bi_dim` (
    `dimension_type` varchar(30) COLLATE utf8mb4_general_ci NOT NULL COMMENT '維度類別',
    `dimension_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '維度ID',
    `dimension_name` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '維度名稱',
    `additional` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
    PRIMARY KEY (`dimension_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '公共維度'

2. 指标集 (MS)

作用： 指标集是基于业务过程（Business Processes）或事件（Events）的度量，它记录了原始、原子性的数据。这些数据是进行所有统计和分析的基础。

CREATE TABLE `bi_indicator` (
    `first_level_domain` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '一級域',
    `second_level_domain` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '二級域',
    `third_level_domain` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '三級域',
    `indicator_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '指標ID',
    `indicator_name` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '指標名稱',
    `indicator_definition` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '指標定義',
    `indicator_type` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '1' COMMENT '指標類型;1 - 原子指標 , 2 - 複合指標 , 3 - 派生指標',
    `source_atomic_indicator` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '來源原子指標;A原子指標ID + B原子指標 ; A原子指標 / B原子指標 ; A原子指標 - B原子指標 ; A原子指標 / （B原子指標 + C原子指標）.....',
    `group_by` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '聚合粒度;A欄位,B欄位,....',
    `where_condition` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '限量維度;A欄位,B欄位,....',
    `indicator_attribute` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '1' COMMENT '指標屬性;1 - 定量 , 2 - 定性',
    `measurement_unit` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '度量單位',
    `statistic_cycle` varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '統計週期;Y - 年 , M - 月 , D - 日 可複選',
    `indicator_guide` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '1' COMMENT '指標導向;1 - 趨勢向上 , 2 - 趨勢向下 , 3 - None',
    `red_alert_threshold_upper` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '紅燈告警閾值上限',
    `red_alert_threshold_lower` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '紅燈告警閾值下限',
    `yellow_alert_threshold_upper` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '黃燈告警閾值上限',
    `yellow_alert_threshold_lower` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '黃燈告警閾值下限',
    `process_type` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '1' COMMENT '加工類型;1 - 累計 , 2 - 滾動',
    `statistic_frequency` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '1' COMMENT '統計頻率;1 - 離線 , 2 - 實時',
    PRIMARY KEY (`indicator_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '指標集'

3. 指标统计结果集 (MSR)

作用： 这是数据仓库的最后一层，它包含了预先计算好的、聚合后的指标数据。这一层的核心目的是提升查询性能，直接为BI报表和数据看板提供支持。

CREATE TABLE `bi_statistic_result` (
    `statistic_dttm` datetime NOT NULL COMMENT '統計時間',
    `first_level_domain` varchar(60) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '一級域',
    `second_level_domain` varchar(60) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '二級域',
    `third_level_domain` varchar(60) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '三級域',
    `fourth_level_domain` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '四級域',
    `fifth_level_domain` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '五級域',
    `indicator_id` varchar(30) COLLATE utf8mb4_general_ci NOT NULL COMMENT '指標ID',
    `dimension_id` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '維度ID',
    `indicator_value` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '指標值',
    `statistic_script` mediumtext COLLATE utf8mb4_general_ci COMMENT '統計腳本;用來反查明細資料',
    `op_date` datetime DEFAULT NULL COMMENT '跑批時間',
    PRIMARY KEY (
        `statistic_dttm`,
        `indicator_id`,
        `dimension_id`
    )
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '指標統計結果'。

到这里数据BI仓库的模型就已经创建完成, 完成三层架构设计

• 最底层

  原始数据（Data Source）来自业务系统，如ERP、CRM、APP、小程序、H5, 日志等。

• 第二层

  公共维度 和 指标集，这是数据仓库的中间层。它经过ETL（提取、转换、加载）处理，数据干净、标准化、结构化，但仍然保持较高的粒度，便于复用。

• 最上层

  指标统计结果集，这是数据仓库的应用层或宽表层。数据经过高度聚合，直接服务于BI报表、数据看板和业务分析。

数据指标加工

• 第一步：准备数据

  这里直接使用测试环境DB, 进行对应表数据获取

import pandas as pd
import numpy as np

# 创建一个示例 DataFrame
data = connection(DB)
df = pd.DataFrame(data)

print("原始数据：")
print(df)

• 第二步：清洗与预处理

• 第三步：计算核心指标

• 第四步：入库

# 数据库插入的结果集
def build_insert_table(**args):
    """
      参数: builder_data
    """
    try:
        builder_data = args.get('builder_data')
        table_name = args.get('table_name')
        engine = get_target_db_engine()
        pd.DataFrame(builder_data).to_sql(table_name, con=engine, if_exists='append', index=False)
    except Exception as e:
        print(f"[Error] {e}")
        return print("[Log] build competition dimension data failed...")
    return print("[Log] build insert data success!")

到此ETL抽取的整个流程就完成了, 最难的便是指标加工, 需要使用pandas, numpy, mysql等主要技术框架

可以动手尝试下, 下一节分享一个google的BI工具, 进行dashboard搭建。