Flattening a Recursive Hierarchy Into a Dimension with SSIS

Question

I have a recursive hierarchy in a relational database, this reflects teams and their position within a hierarchy.

I wish to flatten this hierarchy into a dimension for data warehousing, it's a SQL Server database, using SSIS to SSAS.

I have a table, teams:

teamid Teamname
1      Team 1
2      Team 2

And a table teamhierarchymapping:

Teamid  heirarchyid
1       4
2       2

And a table hierarchy:

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

Giving

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

I want to flatten this to a dimension like:

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

I've tried various nasty sets of SQL to try and bring that together, and various piping around in SSIS (which I am just starting to pick up), and I'm not finding a solution that brings it together.

Can anyone help?

(Edit corrected issue with sample data, I think)

score 2 · Accepted Answer · answered Nov 03 '10 at 20:08

Do you have an error in your sample data? I can't see how the hierarchy mapping connects to the hierarchy table to get the results you want, unless the hierarchy mapping is teamid 1 => hierid 2 and teamid 2 => hierid 4.

SSIS may not be able to do it (easily), so it may be better to create a OLEDB Source that executes SQL of the following format. Note this does assume you're using SQL Server 2008 as the 'PIVOT' function was introduced there...

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;

There's a few different elements to this, and there may be a better way to do it, but for flattening hierarchies, CTE's are ideal.

Two CTEs are created: 'hier' which takes care of flattening the hierarchy and 'teamhier' which is just a helper "view" to make the joins later on simpler. IF you just take the hier CTE and run it, you'll get your flattened 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

The next part of it basically takes this flattened view, joins it to your team tables (to get the team name) and uses SQL Server's PIVOT to rotate it round and get everything aligned as you want it. More information on PIVOT is available on the MSDN.

If you're using SQL Server 2005, then you can just take the hierarchy flattening bit and you should be able to use SSIS's native 'PIVOT' transformation block to hopefully do the dirty pivoting work.

score 1 · Answer 2 · answered Nov 04 '10 at 10:12

1

Is there a reason why you need to flatten the hierarchy? Consider the parent-child hierarchy dimension type in SSAS, this handles variable-depth hierarchies, and would allow extra functions/features that a flattened hierarchy would not:

http://msdn.microsoft.com/en-us/library/ms174846.aspx

http://msdn.microsoft.com/en-us/library/ms174935.aspx

answered Nov 04 '10 at 10:12

Meff

5,889
27
36

1

Ah interesting have to look into the SSAS support for this. Kimble's advice is to flatten them in for performance. – THEMike Nov 05 '10 at 13:43
@THEMike, interesting, I hadn't heard that - I have Kimball's book "The Microsoft Data Warehouse Toolkit" and it mentions Parent/Child dimensions - It says they are a valuable part of SSAS, BUT you may want to launch with a flattened dimension if your system is very large, or you don't have the expertise at the launch. Great book by the way, I'd recommend it (I'm hoping the 2008 version comes soon) http://www.amazon.com/MicrosoftData-Warehouse-Toolkit-MicrosoftBusiness-Intelligence/dp/0471267155 – Meff Nov 08 '10 at 09:45
A bit of reading and it seems parent-child isn't so simple in an SCD2, due to the cascading changes when the top entry changes. SCD1 looks simpler, but that's an unlikely choice in a employee dim. There's a good article here from Joy (From Kimball University) on the subject http://intelligent-enterprise.informationweek.com/showArticle.jhtml;jsessionid=KXRPYHZA3FQXVQE1GHOSKHWATMY32JVN?articleID=219400252&pgno=1 – Meff Nov 08 '10 at 10:01

Flattening a Recursive Hierarchy Into a Dimension with SSIS

2 Answers2