在维度建模中处理层级-平芜编程栈

原文：towardsdatascience.com/handling-hierarchies-in-dimensional-modeling-176156f20f61

对于层级，存在各种建模技术。它们在数据仓库的维度建模中哪一个表现最佳？以及如何使用它们来处理各种类型的层级？让我们来探究一下。

层级在数据仓库的维度建模中起着至关重要的作用，影响着数据分析的结构和效率。借鉴我在为各种公司实施数据解决方案时的经验，本文探讨了处理维度建模中各种类型层级的最佳实践和技术。通过详细的例子和实际指南，我将引导处理不同类型层级的复杂性，以确保稳健且可扩展的数据仓库设计。

在处理层级时，识别它们的特性和所有相关细微差别非常重要。因此，在深入研究建模技术之前，让我们看看在现实生活中的层级场景中我们可以找到哪些怪癖。本文中的例子是虚构的，但灵感来源于我为一家全球制药公司实施的一个项目中遇到的实际案例。尽管它们被显著简化，但它们仍然展示了数据建模的有趣方面。

样层层级的概述

让我们考虑以下样本层级：全球公司的内部组织结构、地理和产品（药品）层级。

https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/158c20e6f74bb507a14902f2290907ff.png

Pharma 领域样本层级的逻辑分解 | 图片由作者提供

组织层级

一家全球制药公司有几个业务单元。每个业务单元由部门组成。一些较大的部门有子部门（但并非所有都有）。在最低层，有绩效单位。如果一个部门有子部门，那么它下面没有直接的绩效单位。

地理层级

该公司在全球范围内销售其产品。它跟踪区域和国家层面的表现。在每个国家，都有一个或多个绩效单位。每个绩效单位在一个单一的国家内运营。一些较大的国家被划分为次国家地区（根据国家不同，这些可能是“州”、“省”、“府”等）。

产品层级

公司生产的药品在最低级别被标识为 SKU。SKU 是本地的，即特定于国家的。每个 SKU 都有自己的本地名称和其他属性，如包装类型、尺寸、标签语言等。SKU 被分组为本地产品，然后进入本地治疗领域。所有本地项目都是特定于国家的。同时，本地产品映射到全球（国际）品牌。一些全球品牌被认为是关键品牌。所有全球品牌都被分组到全球治疗领域。[SKU > 本地产品 > 本地治疗领域]层次结构被认为是本地产品层次结构。[全球产品 > 全球品牌 > 全球治疗领域]被认为是全球产品层次结构。

样本事实

层次结构的另一个重要方面是理解要跟踪的事实以及它们如何与层次结构相联系。为了本文的目的，让我们假设我们有以下类型的事实：

销售实际– 在 SKU 和绩效单位层面报告，
销售活动– 在绩效单位、国家或次国家地区（取决于国家）和本地产品层面报告，
销售目标– 在部门和全球产品层面定义，
竞争销售– 在全球品牌和国家层面收集（从数据供应商如 IQVIA 收集，允许追踪自身市场份额）。

下面的图示显示了四种样本事实类型及其与层次结构的关系。

https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/6ef12208a48d301d8d117dee142788e2.png

事实与样本层次结构逻辑模型 | 作者图片

在设置好阶段之后，让我们看看如何物理建模层次结构的数据结构。在这篇文章中，我专注于维度建模技术，因此我们将层次结构建模为维度。

最后一点：为了专注于层次结构表示的数据结构建模，而不是在本文的上下文中用无关的细节杂乱无章，我假设所有维度都是 SCD1（尽管在现实生活中它们都应该是 SCD2）。

命名维度表

在开始建模维度表之前，让我们建立一个命名约定。这不仅增强了清晰度和组织性，还确保了表易于识别和维护。

维度表前缀

首先，我们将为所有维度表使用特定的前缀来区分它们与事实表和其他数据库对象。常用的做法是在这些表前加上d_或dim_前缀。我将采用d_前缀以简化。

层次级别通用前缀

接下来，对于同一层级内的所有表格使用一个共同的表前缀是有益的。这种做法通过将相关的表格分组，进一步增强了清晰度。例如，如果我们正在模拟一个组织层级，我们可以使用d_org_作为前缀。这个层级内的表格可能被命名为d_org_business_unit、d_org_division等。同样地，对于产品层级，我们可能会使用d_prd_，从而产生如d_prd_sku、d_prd_local_product等表格。对于地理数据，可以使用d_geo_，导致如d_geo_region、d_geo_country等表格。

当表格在数据库客户端应用程序中显示时，它们通常按字母顺序排序，因此这个约定将保持相关表格在一起。此外，当使用具有自动完成功能的客户端应用程序时，输入共同的前缀将显示与给定层级相关的所有表格。

列命名约定

当涉及到这些表格中的列命名时，采用一致且描述性的约定同样重要。一种有效的方法是使用 Classwords 约定。如果你不熟悉它，我建议阅读我的另一篇文章，我在其中解释了它：

Classwords – 我最喜欢的数据库列命名约定

此外，我使用_sk作为 PK 列的名称。想知道为什么吗？我在这篇文章中解释了它：

数据库设计中技术列的最佳实践

建模物理维度

确定外键引用层级的识别

为层级建模物理维度时的第一步是确定需要通过外键（FKs）引用的层级。这些层级通常是对于业务分析和报告至关重要的主要实体。对于这些实体中的每一个，都应该创建一个相应的维度表，并分配一个代理键（SK）。这个代理键将作为维度表中每条记录的唯一标识符，并在事实表中用于建立维度与事实之间的关系。

例如，在组织层级中，只有绩效单位级别从事实表中引用。另一方面，在产品层级中，我们有四个通过 FK 引用的级别：SKU、本地产品、全球产品和全球品牌。

在下面的图中，所有需要 SKs（以便它们可以作为 FK 约束的目标）的维度层级都被标记为金色钥匙符号。这些层级构成了相应的维度表的基础。它们决定了它们的粒度，而层级的高层可以通过它们的属性进行建模。

https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/3996e4f54992e87c1e1e95e2b817dfa6.png

识别作为外键引用目标的层次结构级别 | 图片由作者提供

建模剩余的级别

在某些情况下，层次结构中的某些级别可能仅用于报告中的动态聚合目的，而不是作为主要实体。这些级别可以被视为现有维度表中的属性，而不是为它们创建单独的维度表。

当然，层次结构中的所有逻辑级别都可以建模为单独的维度表。然而，在由数十个级别组成的复杂现实场景中，这可能会使数据库模式变得杂乱，充斥着过多的表。

将它们作为属性保留的方法与起源于编程的开放-封闭原则相一致，该原则指出软件实体应该对扩展开放，但对修改封闭。它同样适用于数据建模领域。如果这些剩余的级别中的任何一个需要成为外键引用的目标，可以临时创建一个单独的维度表。需要将指向这个新维度表的外键添加为前一个维度的新列，其中该级别作为属性。这将创建冗余：该级别将在传统维度和新维度中同时作为属性。但这是为了最初简化的好处而付出的微小代价。

那么，让我们看看下面的图表，看看产品层次结构的物理模型是什么样的。

https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/297792fc86c2b93821a3c4a75f7909ed.png

产品层次结构的逻辑到物理模型映射 | 图片由作者提供

产品层次结构的六个级别（本地和全球）映射到四个物理维度表，它们之间通过外键关系（一对多）相互关联。每个作为外键关系目标级别的都有其自己的物理维度表。

处理零散层次结构

零散层次结构是一种层次结构类型，其中级别的深度可以变化。这意味着层次结构的每个分支并不都下降到相同的级别。深度的变化可能会使建模和查询过程复杂化。上述定义的组织层次结构提供了一个清晰的例子。公司被划分为几个业务单元，每个业务单元包含多个部门。其中一些部门有额外的子部门层，但这在所有部门中并不统一。在层次结构的最低级别，我们找到绩效单元，它们直接向部门或子部门报告，具体取决于分支。

零散层次结构作为自引用维度

这种结构的变化需要灵活的建模方法，以确保所有层次关系都能被准确捕捉并有效分析。管理此类不规则层次结构通常涉及使用父子关系表，其中每个实体指向其直接父实体，允许无论层次深度如何都能遍历层次结构。这种建模技术也被称为邻接表模型。

https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/e555c225808b0001de5d4287ee355ade.png

不规则组织层次结构自引用维度表的样本物理结构 | 作者图片

上图展示了此类自引用维度表的组织层次结构的样本结构。它包含以下对于层次结构建模重要的列：

_sk是项目（层次结构元素）的主键，
org_hierarchy_parent_sk是一个指向父节点的外键（对于顶级单位除外，即业务单元，其值为NULL），
level_number是一个表示项目所代表层次级别的整数（例如，顶级（业务单元）为1，二级项目（部门）为2等）；我建议使用连续编号，因此绩效单位将在3级和4级（取决于它们所属的部门是否有子部门），
level_code是层次级别的技术缩写（例如，绩效单位为PU，子部门为SD等），
unit_name是组织特定单位的实际名称。

使用此类列，可以轻松获取所有绩效单位及其父单位的列表（无论这些是子部门还是部门）：

SELECT pu._sk AS performance_unit_sk,pu.unit_name AS performance_unit_name,parent._sk AS parent_unit_sk,parent.unit_name AS parent_unit_name,parent.level_code AS parent_level_code FROM d_org_hierarchy pu LEFT JOIN d_org_hierarchy parent ON pu.org_hierarchy_parent_sk=parent._sk WHERE pu.level_code='PU'

还可以从任何级别上的任何单位开始迭代遍历层次结构：

WITH RECURSIVE org_hierarchy_cte AS(--Anchor member:startfromthe given unit SELECT _sk,hierarchy_parent_sk,level_code,unit_name FROM d_org_hierarchy WHERE sk=@starting_unit_sk--Replace @starting_unit_skwiththe actual starting unit SK UNION ALL--Recursive member:traverse up the hierarchy SELECT parent.sk,parent.org_hierarchy_parent_sk,parent.level_code,parent.unit_name FROM d_org_hierarchy parent INNER JOIN org_hierarchy_cte cte ON cte.org_hierarchy_parent_sk=parent.sk)SELECT sk,level_number,level_code,unit_name FROM org_hierarchy_cte

这种建模不规则层次结构的方法存在重大限制：

无法从事实创建到层次结构特定级别的外键关系（例如，从销售目标到绩效单位）。
无法有效管理层次级别的属性（因为每个级别可能具有不同的属性，并且有一个通用的表包含所有这些属性）。

如何解决这些限制？

作为级别特定维度的不规则层次结构

模型不规则层次结构的另一种选择是使用特定级别（或逻辑组特定级别）的维度。在组织层次结构的例子中，将有两个维度表：第一个用于绩效单位和子部门，第二个用于部门和业务单元。

https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/828b156e1983582567fee12746aaba54.png

不规则组织层次结构专用维度表的样本物理结构 | 作者图片

使用这种方法，可以轻松解决自引用维度表的两个限制。但是，对于可选的子部门级别怎么办呢？

对于没有子部门级别的情况，填充subdivision_name列有三种方法。最简单且最明显的方法是将它们设置为NULL。然而，在报告方面可能会有些挑战，因为在聚合/钻取/钻透场景中，这样的可选级别通常没有得到很好的支持。其他两种选择是相应地从层次结构的较低或较高级别上上移或下移值。两者都确保了层次结构级别的连续性。上移涉及从较低级别重新使用值来填补缺失的级别。下移假设从较高级别重新使用值。下表比较了这三种方法。

https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/f802b70c962020162c1b23fed189c2dc.png

填充缺失层次结构级别的三种方法 | 图片由作者提供

虽然 up-/down-shifting 方法可能会掩盖真实的层次结构，但它们将参差不齐的层次结构转换为具有固定级别的正规层次结构。例如，如果你想报告子部门级别的指标值，而对于没有子部门的部门使用部门级别，你应该选择 down-shifting 方法。然后，以下这样一个简单的查询就能解决问题：

SELECT pu.subdivision_name,SUM(sa.sales_amount_eur)AS total_sales_amount_eur FROM f_sales_actual sa LEFT JOIN d_org_performance_unit pu ON pu._sk=sa.performance_unit_sk GROUP BY pu.subdivision_name

请记住，对于具有多个可选级别的更复杂参差不齐的层次结构，对于不同级别混合使用上移和下移是完全可以的。

我还建议为每个具有可选子级别的父级别添加一个指示器，以识别子级别名称是其实际名称还是已经上移或下移。这就是为什么在上述组织层次结构的物理结构图中，我为d_org_division维度表添加了has_subdivisions_indicator列。

统一层次结构视图

我还发现了一个相当有用的技巧，那就是为每个由多个维度表组成的层次结构创建一个统一（且非规范化）的视图。这对于大型多表层次结构特别有用。这个视图是整个层次结构中所有维度之间所有连接的简单物化。

https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/246d9c9ff5014b482dabc3372bd5e0b8.png

产品层次结构的统一（非规范化）视图 | 图片由作者提供

使用它的好处是什么？首先，当你不需要手动重写所有连接时，它可以节省任何分析或故障排除操作的时间。你可以轻松地使用视图来查看从任何叶节点到根节点的完整路径（包括所有级别的所有属性）。例如，对于产品层次结构和某些叶级别的 SKU 的 SQL 查询：

SELECT*FROM d_prd_hierarchy WHERE sku_number='ABC-XYZ-123'

此外，对于任何与质量保证相关的任务来说，这也是有用的。为此，我建议在视图中使用所有全外连接，以确保不会丢失任何信息，即使层次结构级别的父子关系被破坏。除了容易发现任何断开的链接外，你还可以使用这样的视图，例如，对于组织层次结构，查找从部门到子部门（在两个物理维度表之间）的错误下移逻辑：

--Selectallinvalid application of down-shifting logicforSub-Divisions SELECT DISTINCT subdivision_name,division_name FROM d_org_hierarchy WHERE division_has_subdivisions_indicator=0AND subdivision_name<>division_name

去规范化基础层维度 + 收缩汇总一致维度

上述统一层次视图的另一种方法是创建一个去规范化基础层维度（统一层次视图的等价物，但以常规表的形式）然后从中派生出所有以视图形式存在的一致维度。

https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/137b011c7383b995ac2298c7dd82a797.png

以去规范化基础层维度和派生收缩汇总一致维度表示的产品层次结构 | 图片由作者提供

这种方法基于 Ralph Kimball 的收缩汇总维度技术。其想法是有一个去规范化基础层维度表，包含层次结构的所有可能级别及其属性。一个重要方面，Kimball 的技术没有涵盖的是，包括所有作为外键目标的高层 SK 的列。并且有两个重要原因：

层次结构较高层的收缩汇总维度可以作为简单的视图（见下面的代码示例）从去规范化基础层维度生成（因此当基础维度更新时，你不需要实现任何自定义逻辑来更新它们）。
计算整个层次结构的完整逻辑被保留为一个单独的代码单元，该单元负责生成去规范化基础层维度。

这种方法与在设计技巧 #137 创建和管理收缩维度 – Kimball Group中描述的方法非常相似。

从d_prd_sku生成d_prd_global_brand的示例代码非常简单：

CREATE VIEW d_prd_global_brand AS SELECT DISTINCT global_brand_sk AS _sk,global_brand_name AS brand_name,global_brand_is_key_brand_indicator AS is_key_brand_indicator,global_therapeutic_area_name,...--a placeholderforadditional dimension levels/attributes FROM d_prd_sku

在实践中，出于性能考虑，你应该将收缩汇总维度创建为物化视图或等效对象（取决于所使用的数据库引擎的功能）。

指南摘要

在本文中，我根据我的实际经验展示了处理维度建模中层次结构的实用指南。下次当你正在处理包含一些层次结构的数据模型时，请考虑以下建议：

在为维度表建立命名约定时，考虑使用一个公共前缀为作为公共层次结构级别的维度服务。
识别层次结构中的关键级别用于外键引用，并确定何时将级别建模为单独的维度表而不是属性。
当处理错落有致的层次结构时，考虑使用特定级别的维度将它们建模为常规层次结构，并通过上移和/或下移值来对可选的层次结构级别进行上移和/或下移。
创建非规范化视图，通过提供整个层次结构的综合视角来简化分析和故障排除任务。
或者，将整个层次结构作为非规范化基础级别维度建模，并从中派生出维度的更高级别（只有那些将作为外键目标的服务级别）作为收缩的汇总符合维度。

🖐 🤓👉 有趣的事实

尽管本文讨论的技术为处理维度建模中的层次结构提供了一个快速概述，但还有许多针对各种特定应用设计的专门用于层次结构建模的方法。例如：嵌套集模型、物化路径模型、路径枚举模型、闭包表模型等。

嵌套集模型特别有趣，因为它能够非常高效地执行复杂的层次结构查询。通过为树中的每个节点分配预先计算的“左”和“右”值，它允许快速检索层次结构中的整个子树。它可以作为邻接集模型（即自引用表）的扩展应用。

https://en.wikipedia.org/wiki/File:Clothing-hierarchy-traversal-2.svg和这里)](…/Images/0a0c28e792780e8de4bbcb1d876b4b23.png)

嵌套集模型技术中“左”和“右”值计算的可视化 | 来源：维基百科公共([这里](https://en.wikipedia.org/wiki/File:NestedSetModel.svg)和这里))

如果你想了解更多关于嵌套集模型的信息，请参阅：👉 嵌套集模型 – 维基百科。

在维度建模中处理层级