How to flatten hierarchy and associations (for data warehousing design and data security in framework manager)

Check list of all posts

Context
By applying data warehouse dimension modelling methodology, we need to make data model more understandable to the people, who use the data. Usually, there are two groups of complicated dimension, or hierarchy and associations.  By flattening hierarchies and associations and not losing any data in the process, we can eliminate snow flaking in the star schema. In addition, row level security can be easily implemented on flattened dimension.  This document is intended to provide a sample dataset to demonstrate this idea.  Please download SQL script for detail review.

Sample

Please see structure below

It presents two relationships:

1. Hierarchy, or parent – child relationship
2. Associations, employee111 can see data of employee112 and employee113

Raw data

These relationships are hosted in two tables

-- Hierachy

CREATE TABLE hierarchy

 (

    id       INTEGER NOT NULL,

    parentid INTEGER NULL,

    name     VARCHAR(20)

 )

go

INSERT INTO hierarchy VALUES (1,NULL, 'CEO')

INSERT INTO hierarchy VALUES (2,1,'Director1')

INSERT INTO hierarchy VALUES (3,2,'Manager11')

INSERT INTO hierarchy VALUES (4,2,'Manager12')

INSERT INTO hierarchy VALUES (5,3,'Employee111')

INSERT INTO hierarchy VALUES (6,3,'Employee112')

INSERT INTO hierarchy VALUES (7,3,'Employee113')

INSERT INTO hierarchy VALUES (8,4,'Employee121')

INSERT INTO hierarchy VALUES (9,4,'Employee122')

INSERT INTO hierarchy VALUES (10,4,'Employee123')

           

-- association

CREATE TABLE association

 (

    id     INTEGER NOT NULL,

    associationid VARCHAR(10) NULL

 )

go

INSERT INTO association VALUES (5, '6')

INSERT INTO association VALUES (5, '7')

INSERT INTO association VALUES (8, '9')

INSERT INTO association VALUES (8, '10')

           

Flatten hierarchy and association

-- Flatten hierarchy and association

WITH ctehierarchy

    AS (SELECT id,

               parentid,

               name                       AS Level1,

               Cast(NULL AS VARCHAR(255)) AS Level2,

               Cast(NULL AS VARCHAR(255)) AS Level3,

               Cast(NULL AS VARCHAR(255)) AS Level4,

               0                          AS Level

        FROM   hierarchy

        WHERE  parentid IS NULL

        UNION ALL

        SELECT Child.id,

               Child.parentid,

               level1,

               CASE

                 WHEN level + 1 = 1 THEN name

                 ELSE level2

               END,

               CASE

                 WHEN level + 1 = 2 THEN name

                 ELSE level3

               END,

               CASE

                 WHEN level + 1 = 3 THEN name

                 ELSE level4

               END,

               level + 1

        FROM   ctehierarchy

               JOIN hierarchy child

                 ON child.parentid = ctehierarchy.id)

-- Concatenation

SELECT ctehierarchy.*,

      association.associationedids

FROM   ctehierarchy

      LEFT JOIN (SELECT id,

                        Stuff((SELECT ',' + a.associationid AS [text()]

                               FROM   association a

                               WHERE  a.id = b.id

                               ORDER  BY a.associationid

                               FOR xml path('')), 1, 1, '') AS associationedIDs

                 FROM   association b

                 GROUP  BY id) association

             ON ( ctehierarchy.id = association.id )

ORDER  BY ctehierarchy.level1,

         ctehierarchy.level2,

         ctehierarchy.level3

          

Row level security

There are many ways to use this dimension for row level security, please see old post (How to implement row level security in framework manager) for detail review.
The easy way to use is to apply data security in framework manager