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