sql-server - 数据仓库中的自定义/动态分类

Question

我们正在扩展我们的数据仓库，但我们遇到了团队中没有人能够解决的难题。我尝试在 Google 上搜索答案，但很难找到正确的术语。

本质上，问题是这样的。我们有一个跟踪项目的数据仓库。我们必须从这个数据仓库中做很多报告。许多报告都是通过对这些项目进行分类然后根据分类汇总数据来驱动的。有些报告共享分类，有些可能有自己的特定于报告的分类。即使在那种情况下，后续报告或分析(临时)也可能需要重用这些分类。并且可能会有很多这样的分类方案。此外，这些方案可能依赖于来自多个事实表的数据来对项目进行分类(并且由于粒度不同，项目信息跨越多个事实表)。

我们最初的计划是为这些设置维度，每个分类一个，或者可能是一个垃圾维度，每个类别都有一个列。如果分类的数量可能保持不变，那就没问题了。但是，总会有新的报告和分析需求。我们不希望每次要添加新的分类方案时都必须更改架构和 ETL 过程。我们可能还想更改分类逻辑，而不必重新导入数据或重新运行部分 ETL 以重新计算。

所以我们的选择似乎如下:

1. 只需将逻辑放在报告和分析查询中，即使这意味着在查询之间复制和粘贴分类逻辑
2. 使用有缺陷的维度方法
3. 具有计算类别的函数，可以在查询中使用，但成本很高，因为它们可能需要在其中进行额外的查询(性能差)
4. 查看包含额外类别列的事实表；与常规表模式相比， View 可以更容易地更改，并且不需要修改 ETL 过程
5. 使用某种桥接表方案实现类别和每个类别中的项目之间的多对多映射；这增加了查询的复杂性，但减少了对方案的修改； ETL 仍然需要修改，但修改可能在较小的区域(可能是更新类别映射的单个查询或过程)

我们希望这个系统易于访问，这样用户就不需要像访问常规事实和维度字段那样做太多(如果有的话)来访问这些分类字段。我们还希望避免每次将新的分类模式添加到数据库时都必须对模式和 ETL 过程进行大量更改。

所以我的问题是，从本质上讲，有没有比我列出的五种方法更好的方法来解决这个问题？这五个有没有更有效地解决问题的变体？或者这只是一个需要一定程度痛苦的难题？也许我的做法全错了，所以在这方面的任何反馈也会有所帮助。

tl;dr:我们需要在数据仓库中对项目进行大量不同分类的方法，但不确定最有效、易于管理或易于使用的系统。

编辑:附加信息:我们使用 SQL Server，将 SSRS 作为主要报告前端，将 SSAS 作为用于分析或临时查询的辅助前端。

Answer 1

根据以上评论中的讨论，您最好的选择可能是构建一个小型 .NET 应用程序或 Web 应用程序，允许用户根据维度属性自行定义分类。

您可以在“类别表”中定义类别

CategoryID  
Category Name

然后您将构建一组“映射”表，这些表必须从每个维度创建一个类别的雪花:

类别 - Dim1 映射 (CATEGORY_D1)

CategoryID
Dim1ID

类别 - DimX 映射

CategoryID
DimXID

您的小应用程序将在定义类别时维护和构建这些映射表。

然后，当您构建报告时，您定义了事实表、维度表和类别表之间的连接。

如果我想找到类别 3 中的所有项目，那么我会写:

SELECT * 
FROM ITEMS_FACT F
JOIN DIM1 D1 ON (F.DIM1_ID = D1.DIM1_ID)
JOIN CATEGORY_D1 CD2 ON (CD2.DIM1_ID = D1.DIM1_ID 
                         AND CD1.CATEGORY_ID = 3)
JOIN DIM2 D2 ON (F.DIM2_ID = D1.DIM2_ID)
JOIN CATEGORY_D2 CD2 ON (CD2.DIM2_ID = D1.DIM2_ID 
                         AND CD2.CATEGORY_ID = 3)

这将允许用户定义他们想要的任何类别，并且根本没有 ETL 更改(除非您有 SCD 类型 2s - 您可能需要在更新行时应用类别)。

如果有类别管理“异常(exception)”，你可能需要建立一个类别映射表，其中包含所有的dim组合:

CategoryID
Dim1ID
Dim2ID
Dim3ID

然后，您可以让用户在他们的工具中定义逻辑(如果 Item 具有 Dim1 Attr2 = 'A' 和 Dim2 Attr3 = 'B'，则类别 1)，然后基于此构建映射表。

那么您的连接会更简单一些 - 只需将 dims 和 fact 连接到维度键上的类别映射。

SELECT * 
FROM ITEMS_FACT F
JOIN DIM1 D1 ON (F.DIM1_ID = D1.DIM1_ID)
JOIN DIM2 D2 ON (F.DIM2_ID = D2.DIM2_ID)
JOIN CATEGORY_MAP CM ON (D1.DIM1_ID = CM.DIM1_ID 
                         AND D2.DIM2_ID = CM.DIM2_ID
                         AND CD1.CATEGORY_ID = 3)