数据开发/数仓工程师上手指南(七)CDM-DWS层搭建规范及流程

原创

fanstuck

发布于 2024-08-05 17:38:59

3640

发布于 2024-08-05 17:38:59

数据开发/数仓工程师上手指南(七)CDM-DWS层搭建规范及流程

前言

进入到了CMD公共数据层的结尾最后一层-DWS层了，该层基本就是直接与业务强关联，也就是说产品提出的需求，或是报表、用户画像统计好还是数据大屏都是在这一层给处理好数据，再放入ADS层，然后我们只需要在BI里面配备对应的数据源即可，因此可以说DWS层这一层建模是否清晰明了，简单易建是检验我们ODS层和DWD,DIM层建模的好坏。一般我们也可以从DWS这一层，也就是需求侧结果出发来逆向推理我们需要建立的ODS和DWD层，总之DWS层需要我们仔细琢磨，需要上生产的东西一定得小心再小心。

一、理解DWS层

DWS层旨在为业务分析提供高效、快速的汇总数据访问。这一层是基于分析主题对象进行建模，提供了关键指标的汇总视图，支持上层应用的决策和洞察。通过构建公共粒度的汇总指标事实表，DWS层可以帮助识别趋势、模式和异常。

原始数据可以比作城市地图上每条街道和建筑的详细信息，而DWS层就像导航应用中的路况总结。它告诉你交通拥堵的区域、最畅通的路线和预计到达时间，而不是让你逐个查看每条街道的情况。

如果把详细数据想象成一天中所有发生的新闻事件，DWS层就是晚间的新闻摘要。它从大量的新闻中提取出最重要的头条和趋势，让你在短时间内了解最重要的信息，而不需要阅读所有的新闻报道。

二、DWS设计原则

我们依旧以为招标业务供应商的信用度分析画像这个需求来分析举例，在为招标业务构建每个供应商的分析画像时，DWS层需要通过汇总供应商相关的多维数据，提供综合的分析视角。

其中DWS设计原则总共有五层：

2.1主题驱动的聚集建模

分析主题：基于供应商在招标过程中的行为模式进行建模，提供全方位的分析支持。
- 在构建供应商画像时，分析目标可以包括：
  - 供应商投标次数：了解供应商参与招标的活跃度。
  - 中标率：评估供应商在不同项目中的成功率。
  - 平均投标金额：衡量供应商报价的竞争力。
  - 合作项目类型：识别供应商主要参与的项目类型。
  - 地理分布：分析供应商参与项目的地域分布。
汇总表设计：创建一个以供应商为中心的汇总表，包含多个关键指标。
比如供应商绩效汇总表： - supplier_id：供应商唯一标识 supplier_name：供应商名称 num_bids：投标次数 num_wins：中标次数 win_rate：中标率（num_wins / num_bids） total_bid_amount：总投标金额 avg_bid_amount：平均投标金额 num_project_types：参与的项目类型数量 num_provinces：参与的省份数量 active_period：活跃期（例如，最近一次投标日期）2.2不跨越事实的聚集
数据一致性：确保聚集数据与原始数据模型保持一致，不跨越多个事实表进行汇总。
单一事实表：在单一的事实表（如投标事务表）上进行聚集，保持数据模型的简单和一致。

2.3数据公用性

共享性：确保供应商画像数据可以多个业务部门和分析应用提供支持
常用维度：对常用维度(如地域、项目类型)进行聚集，提高查询性能

2.4不跨数据域

数据隔离：将数据汇总限制在供应商域内，不涉及其他业务域（如项目域或合同域）。

2.5区分统计周期

周期标识：明确供应商画像的统计周期，例如"_1m"标识按月统计。
- 时间维度：按月、季度、年度进行统计
- 地域维度：按省、市进行汇总

三、DWS设计流程

3.1明确分析目标

在设计DWS表之前，首先明确供应商画像分析的目标。常见的分析目标包括：

供应商参与投标的活跃度
供应商的中标率
供应商的平均投标金额
供应商的地理活动分布
供应商参与的项目类型

3.2确定关键指标和维度

识别实现分析目标所需的关键指标（事实）和维度。

关键指标（事实）

投标总次数（Total Bids）：供应商提交的投标总次数。
中标次数（Winning Bids）：供应商成功中标的次数。
中标率（Win Rate）：中标次数占投标总次数的比例。
投标总金额（Total Bid Amount）：所有投标的总金额。
平均投标金额（Average Bid Amount）：每次投标的平均金额。
活跃项目类型数量（Active Project Types）：参与的不同项目类型数量。
地理覆盖范围（Geographical Reach）：涉及的不同地区或省份数量。

维度

时间维度：支持按天、月、季度或年进行分析。
地理维度：分析不同省市的活动。
项目类型维度：分析不同项目类型的参与情况。

3. 3数据模型设计

基于以上指标和维度，设计DWS表的结构。包括字段名称、数据类型以及注释。每个表结构反映了特定的业务分析需求，并包含了必要的字段和维度。

数据类型选择：根据数据的性质选择合适的数据类型，如金额使用DECIMAL以确保精度，文本字段使用VARCHAR。

主键定义：每个表都设置了主键以确保数据的唯一性和完整性。

性能优化：在必要时可以对常用查询字段设置索引，以提高查询性能。

3.3.1供应商绩效汇总表

用途：分析供应商的整体绩效和行为模式。

CREATE TABLE supplier_performance_summary (
    supplier_id INT PRIMARY KEY,                 -- 供应商唯一标识
    supplier_name VARCHAR(255),                  -- 供应商名称
    num_bids INT,                                -- 投标次数
    num_wins INT,                                -- 中标次数
    win_rate DECIMAL(5, 2),                      -- 中标率（中标次数/投标次数）
    total_bid_amount DECIMAL(18, 2),             -- 总投标金额
    avg_bid_amount DECIMAL(18, 2),               -- 平均投标金额
    num_project_types INT,                       -- 参与的项目类型数量
    num_provinces INT,                           -- 参与的省份数量
    last_active_date DATE                        -- 最近活跃日期
);

3.3.2项目类型汇总表

用途：分析不同类型项目的投标和中标情况。

CREATE TABLE project_type_summary (
    project_type_id INT PRIMARY KEY,             -- 项目类型唯一标识
    project_type_name VARCHAR(255),              -- 项目类型名称
    num_projects INT,                            -- 项目数量
    total_bid_amount DECIMAL(18, 2),             -- 总投标金额
    avg_bid_amount DECIMAL(18, 2),               -- 平均投标金额
    num_wins INT,                                -- 中标项目数量
    win_rate DECIMAL(5, 2)                       -- 中标率（中标项目数量/项目数量）
);

3.3.3地域销售汇总表

用途：分析不同地域的销售和投标情况。

CREATE TABLE region_sales_summary (
    province_id INT PRIMARY KEY,                 -- 省份唯一标识
    province_name VARCHAR(255),                  -- 省份名称
    num_bids INT,                                -- 投标次数
    total_sales_amount DECIMAL(18, 2),           -- 总销售金额
    num_projects INT,                            -- 项目数量
    avg_sales_amount DECIMAL(18, 2),             -- 平均销售金额
    top_supplier_id INT,                         -- 销售额最高的供应商ID
    top_supplier_name VARCHAR(255)               -- 销售额最高的供应商名称
);

3.3.4时间周期汇总表

用途：分析不同时间周期内的业务表现。

CREATE TABLE time_period_summary (
    time_period VARCHAR(10) PRIMARY KEY,         -- 时间周期标识（例如 Q1, 2024）
    num_bids INT,                                -- 投标次数
    total_bid_amount DECIMAL(18, 2),             -- 总投标金额
    avg_bid_amount DECIMAL(18, 2),               -- 平均投标金额
    num_wins INT,                                -- 中标次数
    win_rate DECIMAL(5, 2)                       -- 中标率（中标次数/投标次数）
);