GLPI工单状态历史追踪：数据仓库事实表多状态映射方案咨询

核心问题拆解 #

你当前的核心痛点是OLTP库仅保留工单当前状态，无状态变更历史轨迹，同时需要支撑多时段的状态统计、关键事件（关闭/解决）统计，且需避免“一个状态一张事实表”的不合理设计。

一、历史状态数据捕获方案 #

因为ETL每日运行，推荐两种低成本的历史数据捕获方式：

1. 每日全量快照（适合工单规模不大的场景） #

每日ETL执行时，抽取OLTP中所有工单的工单ID、当前状态、快照日期（即ETL运行当天），插入到专门的快照表中。这种方式无需复杂的变更检测，实现简单，能完整保留每一天所有工单的状态快照。

2. 增量变更捕获（适合工单规模较大的场景） #

利用OLTP工单表的修改日期字段，每日仅抽取当天有状态变更的工单，记录工单ID、变更后的状态、变更日期，同时保留之前的状态记录（可通过和前一日快照对比，捕获状态变化的前后值）。

二、数据仓库维度模型设计 #

放弃“一个状态一张事实表”的方案，采用标准维度建模，仅需2张核心事实表+3张维度表即可满足需求：

维度表设计 #

• 工单维度表（dim_ticket）
  存储工单的静态/慢变属性：

  • 主键：ticket_key（自增或工单ID映射）
  • 字段：工单ID、提交人ID、提交日期、所属部门、问题分类、创建人等

• 日期维度表（dim_date）
  存储日期的全量属性，支撑多时段统计：

  • 主键：date_key（格式如YYYYMMDD）
  • 字段：日期、年、月、周、季度、是否工作日、月份名称等

• 状态维度表（dim_status）
  枚举所有工单状态，方便统一维护：

  • 主键：status_key
  • 字段：状态名称（new/in progress/pending等）、状态描述

事实表设计 #

• 工单状态快照事实表（fact_ticket_status_snapshot）
  核心事实表，存储每日工单状态快照：

  • 字段：ticket_key（关联dim_ticket）、date_key（关联dim_date，即快照日期）、status_key（关联dim_status）
  • 无复杂度量，统计时直接通过COUNT(ticket_key)获取对应时段的状态工单数量

• 工单关键事件事实表（fact_ticket_events）
  存储工单的关键生命周期事件（关闭/解决），方便快速统计事件类指标：

  • 字段：ticket_key（关联dim_ticket）、event_type（枚举：关闭/解决）、event_date_key（关联dim_date，即事件发生日期）

三、ETL执行流程 #

1. 每日快照更新：

   • 若用全量快照：每日抽取OLTP工单全量数据，关联维度表生成对应的键值，插入到fact_ticket_status_snapshot
   • 若用增量捕获：对比当日OLTP工单的修改日期与前一日快照，抽取状态变更的工单，插入快照表并记录变更前后状态（可选）

2. 关键事件同步：
   每日抽取OLTP中关闭日期/解决日期等于当日的工单，生成对应的事件记录插入fact_ticket_events

四、仪表盘指标实现示例 #

• 统计上月各状态的工单数量：
  关联fact_ticket_status_snapshot、dim_date、dim_status，过滤dim_date的年份和月份为上月，按dim_status.status_name分组，COUNT(DISTINCT ticket_key)得到各状态的工单数量

• 统计上月关闭的工单数量：
  关联fact_ticket_events与dim_date，过滤event_type='关闭'且dim_date为上月，COUNT(DISTINCT ticket_key)即可

• 追踪工单状态趋势：
  按周/月分组，统计每个时段各状态的工单数量，生成折线图或柱状图展示状态变化趋势

为什么“一个状态一张事实表”不合理？ #

这种设计会导致：

• 数据极度冗余：同一工单的状态变更会分散到多张表，重复存储工单属性
• 维护成本高：新增状态时需要新建事实表，修改ETL流程
• 统计灵活性差：跨状态的聚合查询需要关联多张表，性能和复杂度都很高

内容的提问来源于stack exchange，提问作者anfel andel