sql - 使用 SSIS 将递归层次结构展平为维度

Question

我在关系数据库中有一个递归层次结构，这反射(reflect)了团队及其在层次结构中的位置。

我希望将这个层次结构扁平化为数据仓库的一个维度，它是一个 SQL Server 数据库，使用 SSIS 到 SSAS。

我有一张 table ，团队:

teamid Teamname
1      Team 1
2      Team 2

还有一个表 teamhierarchymapping:

Teamid  heirarchyid
1       4
2       2

和一个表层次结构:

sequenceid  parentsequenceid  Name
1           null              root
2           1                 Level 1.1
3           1                 Level 1.2
4           3                 Level 1.2 1

给予

      Level 1.1 (Contains Team 2)
root <
      Level 1.2 <
                 Level 1.2 1 (Contains Team 1)

我想将其展平为如下维度:

Team Name   Level 1    Level 2    Level 3
Team 1      Root       Level 1.1  [None]
Team 2      Root       Level 1.2  Level 1.2 1

我已经尝试了各种讨厌的 SQL 集来尝试将它们组合在一起，以及在 SSIS 中进行各种管道(我刚刚开始学习)，但我没有找到将它们组合在一起的解决方案。

任何人都可以帮忙吗？

(我认为用示例数据编辑更正的问题)

Answer 1

你的样本数据有错误吗？我看不到层次结构映射如何连接到层次结构表以获得您想要的结果，除非层次结构映射是 teamid 1 => hierid 2 和 teamid 2 => hierid 4。

SSIS 可能无法(轻松)做到这一点，因此最好创建一个执行以下格式的 SQL 的 OLEDB 源。请注意，这确实假设您使用的是 SQL Server 2008，因为在那里引入了“PIVOT”功能...

WITH hier AS (
    SELECT parentseqid, sequenceid, hiername as parentname, hiername FROM TeamHierarchy
    UNION ALL
    SELECT hier.parentseqid, TH.sequenceid, hier.parentname, TH.hiername FROM hier
        INNER JOIN TeamHierarchy TH ON TH.parentseqid = hier.sequenceid
    ),
    teamhier AS (
        SELECT T.*, THM.hierarchyid FROM Teams T
            INNER JOIN TeamHierarchyMapping THM ON T.teamid = THM.teamid
    )
    SELECT *
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY teamname ORDER BY teamname, sequenceid, parentseqid) AS 'Depth', hier.parentname, teamhier.teamname
                FROM hier
                    INNER JOIN teamhier ON hier.sequenceid = teamhier.hierarchyid
        ) as t1
        PIVOT (MAX(parentname) FOR Depth IN ([1],[2],[3],[4],[5],[6],[7],[8],[9])) AS pvtTable
        ORDER BY teamname;

这有几个不同的元素，可能有更好的方法来做到这一点，但对于扁平化层次结构，CTE 是理想的。

创建了两个 CTE:'hier' 负责扁平化层次结构，而 'teamhier' 只是一个辅助“ View ”，使稍后的连接更简单。如果您只是采用 hier CTE 并运行它，您将获得扁平化 View :

WITH hier AS (
    SELECT parentseqid, sequenceid, hiername as parentname, hiername FROM TeamHierarchy
    UNION ALL
    SELECT hier.parentseqid, TH.sequenceid, hier.parentname, TH.hiername FROM hier
        INNER JOIN TeamHierarchy TH ON TH.parentseqid = hier.sequenceid
    )
    SELECT * FROM hier ORDER BY parentseqid, sequenceid

它的下一部分基本上采用这种扁平化 View ，将其加入您的团队表(以获取团队名称)并使用 SQL Server 的 PIVOT 将其旋转并根据需要对齐所有内容。更多关于枢轴的信息 is available on the MSDN .

如果您使用的是 SQL Server 2005，那么您可以只采用层次结构扁平位，并且您应该能够使用 SSIS 的 native “PIVOT”转换块来完成肮脏的旋转工作。