Search code examples
ssissql-server-2012ssasnested-sets

SSIS - How to deal with Nested Set struct to build a dimention table?

I'm working on a SSIS project in order to build a datawarehouse (SQL Server 2012).

Using PARENT_ID field to manage parent/child relationship is easy to store in a dimension table and automatically recognized by SSAS when designing a hierarchy structure.

In my project, I need to design a Location dimention based on a Nested Set structure.

enter image description here

What is the best way to store Locations in my dimention table by keeping a star schema ? How to configure my dimention in my SSAS project to be able to browse the tree structure (I don't know the depth).

Should I store these data as a Nested Set then dealing with SSAS ? How to map Nested Set with a hierarchy structure ?

Here is a doc about Nested Set.

Thanks !

Solution

  • Tree structures based on parent_id seems to be the only supported way to deal with SSAS.

    As suggested, I added a parent_id field to my table. One of my SSIS package loads data in the table, then another package focuses on the tree migration by populating the parent_id field after querying the parent element (by using the left, right fields).

    To get something more efficient I added another field to track changes.

    So, my table contains 4 fields (parent_id, left, right and is_outdated).