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: