SQL query to retrieve base members of a hierarchy

Forum|Forum|2 years ago
November 22, 2023
7 replies
2 views

Hi, All

Just wanted to share a SQL query I wrote to retrieve base members and parent of a given hierarchy from within a dashboard grid. (Note that the parameter supplies the member ID of the top member in the chosen hierarchy.)

Any thoughts on how to improve are welcome!

With TEST
AS (SELECT childid,
           parentID,
           dimtypeid,
           0 AS level
    FROM relationship
    WHERE parentid = |!SelectDimension!|
    UNION ALL
    SELECT ft.childid,
           ft.parentID,
           ft.dimtypeID,
           level + 1
    FROM relationship ft
        JOIN TEST d
            ON ft.parentid = d.childid
   )
SELECT m.name As Child_Name,
       m.description as Child_Description,
       P.name AS Parent_Name,
       Description as Parent_Description
FROM TEST d
    JOIN relationship a
        ON d.parentid = a.parentid
    JOIN member AS M
        ON a.childID = m.memberid
    JOIN member as P
        on d.parentid = p.memberid
where NOT EXISTS
(
    SELECT NULL FROM TEST d WHERE d.parentID = m.memberID
)
group by m.name,
         m.description,
         P.name,
         p.description

JackLacava

Thank you!

Just fyi, there is a handy SQL formatter you can use when copying around complex queries like this, and you can also use the technique described in this post to make it look all nice and tidy on these forums - which I've just done for you, hope you like it!

RobbSalzmann

JackLacava 's SQL formatter is nice because it also checks syntax.

In certain situations I prefer Format SQL code Online - SmallDev.tools, because it doesn't check syntax. This is helpful when my query has variables in it and when using interpolated strings ( $"select * where id = {idVariable}")

NASAuthor

Excellent! Thank you so much!

sameburn

Hey NAS. Thanks for sharing. I have done something similar with a recursive CTE. In this use case we pass in the Dimension Name e.g. CorpEntities and the logic handles extensibility for all dimensions. In this example with Entity, CorpEntities belongs to a Top Level cube

;DECLARE @dim NVARCHAR(100) = 'CorpEntities'; 
;DECLARE @dimTypeId INT = (SELECT DimTypeId FROM Dim WHERE Name = @dim); 
;DECLARE @dimId INT = (SELECT DimId FROM Dim WHERE Name = @dim); 
;DECLARE @dimIds NVARCHAR(MAX); 
 
IF @dimTypeId = 0 
    BEGIN 
        ;WITH CTE 
        AS ( 
            SELECT 
            cr.ReferencedEntityDimId InheritedEntityDimId 
            FROM Dim d 
            INNER JOIN CubeDim cd 
                ON d.DimTypeId = cd.DimTypeId AND d.DimId = cd.DimId 
            INNER JOIN CubeReference cr 
                ON cd.CubeId = cr.CubeId 
            WHERE d.Name = @dim 
           ) 
        SELECT @dimIds = STRING_AGG(InheritedEntityDimId, ',') FROM CTE; 
        SET @dimIds = ISNULL(@dimIds + ',' + CONVERT(NVARCHAR(10), @dimID), @dimId); 
 
        ;WITH RecursiveCTE 
        AS ( 
            SELECT DISTINCT 
            m.Name Child, 
            p.Name Parent, 
            0 Level, 
            m.MemberId ChildId, 
            @dim Dimension 
            FROM Relationship r 
            LEFT JOIN Member m 
                ON r.DimId = m.DimId 
				AND r.DimTypeId = m.DimTypeId 
				AND r.ChildId = m.MemberId 
            LEFT JOIN Member p 
                ON r.DimId = p.DimId 
				AND r.DimTypeId = p.DimTypeId 
				AND r.ParentId = p.MemberId 
            WHERE r.DimTypeId = @dimTypeId  
			AND r.DimId = @dimId 
			AND p.Name IS NULL   
 
            UNION ALL 
            SELECT 
            m.Name, 
            CTE.Child, 
            Level + 1, 
            m.MemberId, 
            CTE.Dimension 
            FROM Relationship r 
            INNER JOIN RecursiveCTE CTE 
				ON CTE.ChildId = r.ParentId 
            INNER JOIN Member m 
				ON m.DimId IN (SELECT value FROM STRING_SPLIT(@dimIds, ',')) 
				AND r.DimTypeId = m.DimTypeId 
                AND r.ChildId = m.MemberId 
           ) 
        SELECT COALESCE(Parent, 'Root') Parent, Child, Level, Dimension 
        FROM RecursiveCTE; 
    END 
ELSE 
    BEGIN 
        ;WITH CTE 
        AS ( 
            SELECT DimTypeId, DimId, Name, InheritedDimId, CAST(DimId AS VARCHAR(MAX)) Path  
            FROM Dim  
            WHERE InheritedDimId NOT IN (SELECT DimId FROM Dim) 
			AND DimTypeId = @dimTypeId  
            UNION ALL  
            SELECT d.DimTypeId, d.DimId, d.Name, d.InheritedDimId, CONCAT(r.Path, ',', d.DimId) 
            FROM Dim d  
            JOIN CTE r ON d.InheritedDimId = r.DimId 
            )  
        SELECT @dimIds = Path FROM CTE WHERE Name = @dim; 
 
        ;WITH RecursiveCTE  
        AS ( 
			SELECT DISTINCT 
			m.Name Child, 
			p.Name Parent, 
			0 Level, 
			m.MemberId ChildId, 
			@dim Dimension 
            FROM Relationship r 
            LEFT JOIN Member m 
                ON r.DimId = m.DimId 
                AND r.DimTypeId = m.DimTypeId 
                AND r.ChildId = m.MemberId 
            LEFT JOIN Member p 
                ON r.DimTypeId = p.DimTypeId 
                AND r.ParentId = p.MemberId 
            WHERE r.DimId IN (SELECT value FROM STRING_SPLIT(@dimIds, ',')) 
			AND p.Name IS NULL 
 
            UNION ALL  
            SELECT 
            m.Name, 
            CTE.Child, 
            Level + 1, 
            m.MemberId, 
            CTE.Dimension 
            FROM Relationship r 
            INNER JOIN RecursiveCTE CTE 
	            ON CTE.ChildId = r.ParentId 
            INNER JOIN Member m 
                ON m.DimId IN (SELECT value FROM STRING_SPLIT(@dimIds, ',')) 
				AND r.DimTypeId = m.DimTypeId 
				AND r.ChildId = m.MemberId 
          )  
		SELECT COALESCE(Parent, 'Root') Parent, Child, Level, Dimension 
		FROM RecursiveCTE; 
END

RobbSalzmann

Couple of very small changes to order by Level:

;DECLARE @dim NVARCHAR(100) = 'CorpEntities'; 
;DECLARE @dimTypeId INT = (SELECT DimTypeId FROM Dim WHERE Name = @dim); 
;DECLARE @dimId INT = (SELECT DimId FROM Dim WHERE Name = @dim); 
;DECLARE @dimIds NVARCHAR(MAX); 
 
IF @dimTypeId = 0 
    BEGIN 
        ;WITH CTE 
        AS ( 
            SELECT 
            cr.ReferencedEntityDimId InheritedEntityDimId 
            FROM Dim d 
            INNER JOIN CubeDim cd 
                ON d.DimTypeId = cd.DimTypeId AND d.DimId = cd.DimId 
            INNER JOIN CubeReference cr 
                ON cd.CubeId = cr.CubeId 
            WHERE d.Name = @dim 
           ) 
        SELECT @dimIds = STRING_AGG(InheritedEntityDimId, ',') FROM CTE; 
        SET @dimIds = ISNULL(@dimIds + ',' + CONVERT(NVARCHAR(10), @dimID), @dimId); 
 
        ;WITH RecursiveCTE 
        AS ( 
            SELECT DISTINCT 
            m.Name Child, 
            p.Name Parent, 
            0 Level, 
            m.MemberId ChildId, 
            @dim Dimension 
            FROM Relationship r 
            LEFT JOIN Member m 
                ON r.DimId = m.DimId 
				AND r.DimTypeId = m.DimTypeId 
				AND r.ChildId = m.MemberId 
            LEFT JOIN Member p 
                ON r.DimId = p.DimId 
				AND r.DimTypeId = p.DimTypeId 
				AND r.ParentId = p.MemberId 
            WHERE r.DimTypeId = @dimTypeId  
			AND r.DimId = @dimId 
			AND p.Name IS NULL   
 
            UNION ALL 
            SELECT 
            m.Name, 
            CTE.Child, 
            Level + 1, 
            m.MemberId, 
            CTE.Dimension 
            FROM Relationship r 
            INNER JOIN RecursiveCTE CTE 
				ON CTE.ChildId = r.ParentId 
            INNER JOIN Member m 
				ON m.DimId IN (SELECT value FROM STRING_SPLIT(@dimIds, ',')) 
				AND r.DimTypeId = m.DimTypeId 
                AND r.ChildId = m.MemberId 
           ) 
        SELECT COALESCE(Parent, 'Root') Parent, Child, Level, Dimension 
        FROM RecursiveCTE order by Level; 
    END 
ELSE 
    BEGIN 
        ;WITH CTE 
        AS ( 
            SELECT DimTypeId, DimId, Name, InheritedDimId, CAST(DimId AS VARCHAR(MAX)) Path  
            FROM Dim  
            WHERE InheritedDimId NOT IN (SELECT DimId FROM Dim) 
			AND DimTypeId = @dimTypeId  
            UNION ALL  
            SELECT d.DimTypeId, d.DimId, d.Name, d.InheritedDimId, CONCAT(r.Path, ',', d.DimId) 
            FROM Dim d  
            JOIN CTE r ON d.InheritedDimId = r.DimId 
            )  
        SELECT @dimIds = Path FROM CTE WHERE Name = @dim; 
 
        ;WITH RecursiveCTE  
        AS ( 
			SELECT DISTINCT 
			m.Name Child, 
			p.Name Parent, 
			0 Level, 
			m.MemberId ChildId, 
			@dim Dimension 
            FROM Relationship r 
            LEFT JOIN Member m 
                ON r.DimId = m.DimId 
                AND r.DimTypeId = m.DimTypeId 
                AND r.ChildId = m.MemberId 
            LEFT JOIN Member p 
                ON r.DimTypeId = p.DimTypeId 
                AND r.ParentId = p.MemberId 
            WHERE r.DimId IN (SELECT value FROM STRING_SPLIT(@dimIds, ',')) 
			AND p.Name IS NULL 
 
            UNION ALL  
            SELECT 
            m.Name, 
            CTE.Child, 
            Level + 1, 
            m.MemberId, 
            CTE.Dimension 
            FROM Relationship r 
            INNER JOIN RecursiveCTE CTE 
	            ON CTE.ChildId = r.ParentId 
            INNER JOIN Member m 
                ON m.DimId IN (SELECT value FROM STRING_SPLIT(@dimIds, ',')) 
				AND r.DimTypeId = m.DimTypeId 
				AND r.ChildId = m.MemberId 
          )  
		SELECT COALESCE(Parent, 'Root') Parent, Child, Level, Dimension 
		FROM RecursiveCTE order by Level; 
END

RobbSalzmann

Not sure why my suggestion was rejected, going to try once more:
Adding "ORDER BY Level" to Lines 55 and 111 will sort the parent-child list into an order that is loadable to something that creates a hierarchy. Clients usually want this order.

sameburn

Thanks RobbSalzmann. Thanks for input, For my use case I did not want to order by level. But I can see why you (or a customer) would want to 🙂

NASAuthor

I love these additions! There is one main distinction. My version allows the user to choose the particular hierarchy member and descendants, but not the entire dimension. (I also need to supply the ID within the parameter which is a flaw I need to address.) The additional versions provide an entire dimension extract (my version cannot extract that level) which is very useful for admins, etc. Great stuff, thanks, again!

P.S. I wanted to point out that my code can pull the full list of descendants (not just base members) by eliminating the "where" clause. I added the "where" because the end user just wanted base members. 🙂

sameburn

Hi. Just to return to this topic. I've added some small modifications to the query logic after changing use case. The main changes are that we have added a column called Path that helps drive the sibling sort order (this can get skewed on big dimensions) and this column serves as a control to ensure we are observing the OS SiblingSortOrder. Secondly, using similar approach I've added a column called Ancestry which again uses Path in SQL to output the Ancestry of a particular member / row. Finally there is a column called RowNumber that can be used as a Sort Order on the entire result, for example.

/* Declare and set the value of the @dim variable */
;DECLARE @dim NVARCHAR(100) = 'CorpEntities';
/* Declare Root member */
;DECLARE @root NVARCHAR(5) = 'Root';
/* Declare and set the @dimTypeId variable */
;DECLARE @dimTypeId INT = (SELECT DimTypeId FROM Dim WHERE Name = @dim);
/* Declare and set the @dimId variable */
;DECLARE @dimId INT = (SELECT DimId FROM Dim WHERE Name = @dim);
/* Declare the @dimIds variable */ 
;DECLARE @dimIds NVARCHAR(MAX);

/* Use an IF statement to conditionally build and execute SQL query */ 
IF @dimTypeId = 0
    BEGIN
        /* This block will execute if the dimension type is 0, which means it belongs to the Entity dimension */ 
        /* The logic here is to find all the inherited entity dim ids from the cube reference table and append them to the @dimIds variable */ 
        /* Then use a recursive CTE to get the parent-child relationship of the members in this dimension */      
        ;WITH CTE AS (
            SELECT
            cr.ReferencedEntityDimId InheritedEntityDimId
            FROM Dim d
            INNER JOIN CubeDim cd
                ON d.DimTypeId = cd.DimTypeId AND d.DimId = cd.DimId
            INNER JOIN CubeReference cr
                ON cd.CubeId = cr.CubeId
            WHERE d.Name = @dim
           )
        SELECT @dimIds = STRING_AGG(InheritedEntityDimId, ',') FROM CTE;
        SET @dimIds = ISNULL(@dimIds + ',' + CONVERT(NVARCHAR(10), @dimID), @dimId);

        ;WITH RecursiveCTE
        AS (
			/* Declare Anchor Member */
            SELECT DISTINCT
			/* Create Ancestry column using Path */
			CAST(COALESCE(p.Name, @root) AS VARCHAR(MAX)) Ancestry,
            m.Name Child,
            p.Name Parent,
            0 Level,
            m.MemberId ChildId,
            @dim Dimension,
			/* Use Path to ensure sibling order is correct */
			CAST(r.SiblingSortOrder AS VARCHAR(MAX)) Path
            FROM Relationship r
            LEFT JOIN Member m
                ON r.DimId = m.DimId
				AND r.DimTypeId = m.DimTypeId
				AND r.ChildId = m.MemberId
            LEFT JOIN Member p
                ON r.DimId = p.DimId
				AND r.DimTypeId = p.DimTypeId
				AND r.ParentId = p.MemberId
            WHERE r.DimTypeId = @dimTypeId 
			AND r.DimId = @dimId
			AND p.Name IS NULL        

            UNION ALL
			/* Declare Recursive Member */
            SELECT
			/* Create Ancestry column using Path */
			CTE.Ancestry + ' > ' + CAST(CTE.Child AS VARCHAR(MAX)),
            m.Name,
            CTE.Child,
            Level + 1,
            m.MemberId,
            CTE.Dimension,
			/* Use Path to ensure sibling order is correct */
			CTE.Path + '.' + CAST(r.SiblingSortOrder AS VARCHAR(MAX))
            FROM Relationship r
            INNER JOIN RecursiveCTE CTE
				ON CTE.ChildId = r.ParentId
            INNER JOIN Member m
				ON m.DimId IN (SELECT value FROM STRING_SPLIT(@dimIds, ','))
				AND r.DimTypeId = m.DimTypeId
                AND r.ChildId = m.MemberId
           )
        /* Select Results from CTE and replace NULL values with 'Root' */
        SELECT 
		COALESCE(Parent, @root) ParentName, 
		Child ChildName, 
		Level, 
		Dimension,
		ROW_NUMBER() OVER (ORDER BY Level, Path) RowNumber,
		Ancestry
        FROM RecursiveCTE
		/* Generate Row Number */
		ORDER BY Level, Path;

    END /* End of IF block for @dimTypeId = 0 */
ELSE
    BEGIN
        /* This block will execute if the dimension type is not 0, which means it is not the Entity dimension */
        /* The logic here is to find the path of dim ids from the dim table using a recursive CTE and assign it to the @dimIds variable */
        /* Then use another recursive CTE to get the parent-child relationship of the members in this dimension */              		
        ;WITH CTE AS (              
            SELECT DimTypeId, DimId, Name, InheritedDimId, CAST(DimId AS VARCHAR(MAX)) Path        
            FROM Dim       
            WHERE InheritedDimId NOT IN (SELECT DimId FROM Dim)        
			AND DimTypeId = @dimTypeId       
            UNION ALL           
            SELECT d.DimTypeId, d.DimId, d.Name, d.InheritedDimId, CONCAT(r.Path, ',', d.DimId)        
            FROM Dim d        
            JOIN CTE r ON d.InheritedDimId = r.DimId        
            )            
		SELECT @dimIds = Path FROM CTE WHERE Name = @dim;         
             
        ;WITH RecursiveCTE        
        AS (
			/* Assign Anchor Member */
			SELECT DISTINCT 
			/* Create Ancestry column using Path */
			CAST(COALESCE(p.Name, @root) AS VARCHAR(MAX)) Ancestry,			
			m.Name Child,       
			p.Name Parent,        
			0 Level,       
			m.MemberId ChildId,        
			@dim Dimension,
			/* Use Path to ensure sibling order is correct */
			CAST(r.SiblingSortOrder AS VARCHAR(MAX)) Path
            FROM Relationship r        
            LEFT JOIN Member m
                ON r.DimId = m.DimId
                AND r.DimTypeId = m.DimTypeId
                AND r.ChildId = m.MemberId
            LEFT JOIN Member p
                ON r.DimTypeId = p.DimTypeId
                AND r.ParentId = p.MemberId
            WHERE r.DimId IN (SELECT value FROM STRING_SPLIT(@dimIds, ','))
			AND p.Name IS NULL

            UNION ALL       
			/* Assign Recursive Member */	        
            SELECT 
			/* Create Ancestry column using Path */
			CTE.Ancestry + ' > ' + CAST(CTE.Child AS VARCHAR(MAX)),			
            m.Name,        
            CTE.Child,        
            Level + 1,        
            m.MemberId,        
            CTE.Dimension,
			/* Use Path to ensure sibling order is correct */
			CTE.Path + '.' + CAST(r.SiblingSortOrder AS VARCHAR(MAX))
            FROM Relationship r        
            INNER JOIN RecursiveCTE CTE
	            ON CTE.ChildId = r.ParentId
            INNER JOIN Member m
                ON m.DimId IN (SELECT value FROM STRING_SPLIT(@dimIds, ','))
				AND r.DimTypeId = m.DimTypeId
				AND r.ChildId = m.MemberId
          )        
		/* Select Results from CTE and replace NULL values with 'Root' */        
        SELECT 
		COALESCE(Parent, @root) ParentName, 
		Child ChildName, 
		Level, 
		Dimension,
		/* Generate Row Number */
		ROW_NUMBER() OVER (ORDER BY Level, Path) RowNumber,
		Ancestry
        FROM RecursiveCTE
		ORDER BY Level, Path;
        
END /* End of ELSE block for @dimTypeId <> 0 */

The updated output looks like this:

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded