VW_AD_PowerBI_GroupMembers

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

group_name

varchar

500

null

region

varchar

100

√

null

IsEmployeeGroup

int

null

is_supergroup

int

null

datateam_site_id

varchar

√

null

CEU360_Users.id

Implied ConstraintR

company

varchar

√

null

groupNameType

varchar

√

null

groupNameType_Dynamic

varchar

√

null

Relationships

Close relationships within degrees of separation

View Definition

CREATE VIEW dbo.VW_AD_PowerBI_GroupMembers AS
    /*********************************************************************
    Database:		DataCStore
    View:			dbo.VW_AD_PowerBI_GroupMembers
    Created:		9/10/2020
    Description:	Consolidate two different sets of AD groups:
                    - Portal
                    - Scorecard
                    Business rules for deriving additional attributes
                    are pending from Karan.
    *********************************************************************/

    /* NON SC_ set (Portal)*/
    WITH groups_expanded AS
             (SELECT g.cn_ancestor,
                     g.cn,
                     g.distinguishedName,
                     fac.DataTeamCode          AS datateam_site_id,
                     CAST('INF' AS VARCHAR)    AS company,
                     CAST('Portal' AS VARCHAR) AS category
              FROM dbo.AD_PowerBI_Groups AS g
              JOIN (SELECT DISTINCT ADCode,
                                    DataTeamCode
                    FROM dbo.ROX_Cache_Fac
                    WHERE ExportKey <> '') AS fac
              ON LEFT(cn_ancestor, 3) = fac.ADCode
              WHERE cn_ancestor NOT LIKE 'SC_%'

              UNION

              /* SC_ set */
              SELECT cn_ancestor,
                     cn,
                     distinguishedName,
                     CASE WHEN PATINDEX('SC_%[0-9][0-9][0-9][0-9]%', cn_ancestor) > 0 THEN
                     CASE LEFT(cn_ancestor, 6)
                         WHEN 'SC_AVA' THEN SUBSTRING(cn_ancestor, 8, 4)
                         WHEN 'SC_INF' THEN SUBSTRING(cn_ancestor, 8, 3)
                         WHEN 'SC_SIG' THEN SUBSTRING(cn_ancestor, 8, 3)
                         ELSE NULL END
                     ELSE NULL END                                                                 AS datateam_site_id,
                     SUBSTRING(cn_ancestor, 4, 3)                                                  AS company,
                     CAST('SC' AS VARCHAR)                                                         AS category
              FROM dbo.AD_PowerBI_Groups AS g
              WHERE cn_ancestor LIKE 'SC[_]%'),

         /* Karan: A supergroup is defined as a seed group whose members are all groups.
            Conversely, any seed group with at least one user member, is not a supergroup. */
         non_super_groups AS (SELECT DISTINCT g.cn_ancestor
                              FROM dbo.AD_PowerBI_Groups AS g
                              JOIN dbo.AD_PowerBI_GroupUsers AS gu
                              ON g.distinguishedName = gu.group_distinguishedName
                              /* seed groups only */
                              WHERE g.cn_ancestor = g.cn)

    SELECT DISTINCT
           g.cn_ancestor                                       AS group_name,
           /* Change #3 -  If from the group names, we can extract the Region names? Basically,
              using the delimiter '_' and extract text after last '_' */
           SUBSTRING(g.cn_ancestor, LEN(g.cn_ancestor) - CHARINDEX('_', REVERSE(g.cn_ancestor), 1) + 2, 100) AS region,
           /*
            Change#1 - Need a new column "IsEmployeeGroup", each if Text.Contains([group_name], "Employee") then "Yes" else "No"
            */
           CASE WHEN g.cn_ancestor LIKE '%Employee%' THEN 1
                WHEN g.company = 'SIG' AND g.cn_ancestor LIKE '%[_]E' THEN 1
                ELSE 0 END AS IsEmployeeGroup,
           CASE WHEN nsg.cn_ancestor IS NULL THEN 1 ELSE 0 END AS is_supergroup,
           CASE WHEN g.datateam_site_id = '200' THEN 'EVE'
                WHEN g.datateam_site_id = '233' THEN '200'
                ELSE g.datateam_site_id END AS datateam_site_id,
           g.company,
           typelkup.type AS groupNameType,
           /*
            CHange#2 - Rather than using the hard coded mapping for groupNameType,
            let's replace it with this logic -
            if Text.Contains([group_name], "Exec") then "Executive"
            else if Text.Contains([group_name], "_E") then "Building"
            else if Text.Contains([group_name], "DOR") then "Building"
            else if Text.Contains([group_name], "RAA") then "Building"
            else if Text.Contains([group_name], "RDO") then "Regional"
            else if Text.Contains([group_name], "ARD") then "Regional"
            else if Text.Contains([group_name], "RNC") then "Regional"
            else if Text.Contains([group_name], "Head") then "Executive"
            else if Text.Contains([group_name], "VP") then "Executive"
            else if Text.Contains([group_name], "DIR") then "Executive"
            else if Text.EndsWith([group_name], "O") then "Executive" else "Department"
            */
           CASE WHEN nsg.cn_ancestor IS NOT NULL THEN -- Only if not a supergroup
               CASE WHEN g.cn_ancestor LIKE '%Exec%' THEN 'Executive'
                    WHEN g.cn_ancestor LIKE '%[0-9][0-9][0-9]%' THEN 'Building'
                    WHEN g.Company IN ('AVA', 'SIG') AND g.cn_ancestor LIKE '%[_]C' THEN 'Building'
                    WHEN g.cn_ancestor LIKE '%DOR%' THEN 'Building'
                    WHEN g.cn_ancestor LIKE '%RAA%' THEN 'Building'
                    WHEN g.cn_ancestor LIKE '%[_]OPS' THEN 'Department'
                    WHEN g.cn_ancestor LIKE '%RDO%' THEN 'Regional'
                    WHEN g.cn_ancestor LIKE '%ARD%' THEN 'Regional'
                    WHEN g.cn_ancestor LIKE '%RNC%' THEN 'Regional'
                    WHEN g.cn_ancestor LIKE '%Head%' THEN 'Executive'
                    WHEN g.cn_ancestor LIKE '%VP%' THEN 'Executive'
                    WHEN g.cn_ancestor LIKE '%DIR%' THEN 'Executive'
                    WHEN g.cn_ancestor LIKE '%O' THEN 'Executive'
                    WHEN g.cn_ancestor LIKE '%[_]AHS[_]%' THEN 'Executive'
                    WHEN g.cn_ancestor LIKE '%HomeOffice' THEN 'Executive'
                    ELSE 'Department'
               END
           ELSE NULL END AS groupNameType_Dynamic,
           g.category,
           gu.userPrincipalName,
           gu.sAMAccountName,
           gu.givenName,
           gu.sn,
           gu.title,
           /* Logic from Karan's PBI formula, subsequently enhanced. Exact formula order preserved */
           CASE WHEN nsg.cn_ancestor LIKE '%[0-9][0-9][0-9]%' THEN 'Building'

                WHEN gu.title LIKE 'CRP%Float%' OR gu.title LIKE 'CRP%In Training%' THEN 'Building'

                WHEN gu.title LIKE '%Region%' THEN 'Regional'

                WHEN gu.title LIKE 'AHS%' THEN 'Corporate'
                WHEN gu.title LIKE 'CRP%' THEN 'Corporate'
                WHEN gu.title LIKE 'SIG%' THEN 'Corporate'
                WHEN gu.title LIKE 'CC%' THEN 'Corporate'
                WHEN gu.title LIKE 'PDC%' THEN 'Corporate'

                /* For Signature. Micro-order and local context order is important. */
                WHEN gu.title LIKE '%Liaison%' THEN 'Corporate'
                WHEN gu.title LIKE 'SHH%' OR gu.title LIKE 'SHO%' THEN 'Building'

                WHEN gu.title LIKE '%Chief%' THEN 'Corporate'
                WHEN gu.title LIKE '%VP%' THEN 'Corporate'
                WHEN gu.title LIKE '%Director of%' AND g.company = 'INF' THEN 'Corporate'
                WHEN gu.title LIKE '%Compliance%' THEN 'Corporate'
                WHEN gu.title LIKE '%Episode%' THEN 'Corporate'
                WHEN gu.title LIKE '%Sales%' THEN 'Corporate'
                WHEN gu.title LIKE '%President%' THEN 'Corporate'
                WHEN gu.title LIKE '%Knowledge%' THEN 'Corporate'
                WHEN gu.title LIKE '%Principal%' THEN 'Corporate'
                WHEN gu.title LIKE '%Manager%' THEN 'Corporate'

                WHEN gu.title LIKE 'PRE-Director of%' AND g.company = 'INF' THEN 'Corporate'
                WHEN gu.title LIKE '%ARD%' THEN 'Regional'
                WHEN gu.title LIKE '%Staffing%' THEN 'Corporate'
                WHEN gu.title LIKE '%Recruit%' THEN 'Corporate'
                WHEN gu.title LIKE '%Benefits%' THEN 'Corporate'
                WHEN gu.title LIKE '%HR%' THEN 'Corporate'
                ELSE 'Building' END AS jobtitle_type,

           /* Logic from Karan */
           CASE WHEN gu.title LIKE '%diem' OR gu.title LIKE '%PRN' THEN 0 ELSE 1 END AS is_fulltime,
           gu.mail,
           gu.department
    FROM groups_expanded AS g
    JOIN dbo.AD_PowerBI_GroupUsers AS gu
    /*
     Key concepts for Groups table, to help understand join logic below.
        distinct count cn_ancestor = # of original seed groups
        distinct cn_ancestor = unique groups to query
        distinguishedName is for the expanded group (which could also be itself)
     */
    ON g.distinguishedName = gu.group_distinguishedName
    LEFT JOIN non_super_groups AS nsg
    ON g.cn_ancestor = nsg.cn_ancestor
    LEFT JOIN dbo.VW_ActiveDir_TypeLookup AS typelkup
    ON g.cn_ancestor = typelkup.group_cn
    /* Karan: Exclude disabled accounts */
    WHERE gu.userAccountControl <> 514

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views