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.
In the spirit of completeness I converted the sql (plus RobbSalzmann's order by addition) to a vb.net function and added comments. Enjoy
#Region "Get Dimension DataTable"
'------------------------------------------------------------------------------------------------------------
'Referenced Code: GetDimensionDataTable -> DataTable
'
'Description: Function to return DataTable
'
'Notes: Recursive CTE that caters for extensibility (All Dims) and outputs hierarchy and level
' for parent, child relationships
'
'Usage: DataTable = Me.GetDimensionDataTable(si, "CorpEntities")
'
'Created By: OneStream
'Date Created: 29-09-2023
'------------------------------------------------------------------------------------------------------------
Public Function GetDimensionDataTable(ByVal si As SessionInfo, ByVal dimensionName As String) As DataTable
Try
' Set dbParamInfo
Dim dbParameterInfo As List(Of dbParamInfo) = New List(Of dbParamInfo) From {New DbParamInfo("@dim", dimensionName)}
' Declare Recursive CTE that handles extended dimensions
Dim sql As New Text.StringBuilder
' Declare and set the @dimTypeId variable
sql.AppendLine(";DECLARE @dimTypeId INT = (SELECT DimTypeId FROM Dim WHERE Name = @dim); ")
' Declare and set the @dimId variable
sql.AppendLine(";DECLARE @dimId INT = (SELECT DimId FROM Dim WHERE Name = @dim); ")
' Declare the @dimIds variable
sql.AppendLine(";DECLARE @dimIds NVARCHAR(MAX); ")
sql.AppendLine(" ")
' Use an IF statement to conditionally build and execute a dynamic SQL query
sql.AppendLine("IF @dimTypeId = 0 ")
sql.AppendLine(" 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
sql.AppendLine(" ;WITH CTE ")
sql.AppendLine(" AS ( ")
sql.AppendLine(" SELECT ")
sql.AppendLine(" cr.ReferencedEntityDimId InheritedEntityDimId ")
sql.AppendLine(" FROM Dim d ")
sql.AppendLine(" INNER JOIN CubeDim cd ")
sql.AppendLine(" ON d.DimTypeId = cd.DimTypeId AND d.DimId = cd.DimId ")
sql.AppendLine(" INNER JOIN CubeReference cr ")
sql.AppendLine(" ON cd.CubeId = cr.CubeId ")
sql.AppendLine(" WHERE d.Name = @dim ")
sql.AppendLine(" ) ")
sql.AppendLine(" SELECT @dimIds = STRING_AGG(InheritedEntityDimId, ',') FROM CTE; ")
sql.AppendLine(" SET @dimIds = ISNULL(@dimIds + ',' + CONVERT(NVARCHAR(10), @dimID), @dimId); ")
sql.AppendLine(" ")
sql.AppendLine(" ;WITH RecursiveCTE ")
sql.AppendLine(" AS ( ")
' Declare Anchor Member
sql.AppendLine(" SELECT DISTINCT ")
sql.AppendLine(" m.Name Child, ")
sql.AppendLine(" p.Name Parent, ")
sql.AppendLine(" 0 Level, ")
sql.AppendLine(" m.MemberId ChildId, ")
sql.AppendLine(" @dim Dimension ")
sql.AppendLine(" FROM Relationship r ")
sql.AppendLine(" LEFT JOIN Member m ")
sql.AppendLine(" ON r.DimId = m.DimId ")
sql.AppendLine(" AND r.DimTypeId = m.DimTypeId ")
sql.AppendLine(" AND r.ChildId = m.MemberId ")
sql.AppendLine(" LEFT JOIN Member p ")
sql.AppendLine(" ON r.DimId = p.DimId ")
sql.AppendLine(" AND r.DimTypeId = p.DimTypeId ")
sql.AppendLine(" AND r.ParentId = p.MemberId ")
sql.AppendLine(" WHERE r.DimTypeId = @dimTypeId ")
sql.AppendLine(" AND r.DimId = @dimId ")
sql.AppendLine(" AND p.Name IS NULL ")
sql.AppendLine(" ")
sql.AppendLine(" UNION ALL ")
' Declare Recursive Member
sql.AppendLine(" SELECT ")
sql.AppendLine(" m.Name, ")
sql.AppendLine(" CTE.Child, ")
sql.AppendLine(" Level + 1, ")
sql.AppendLine(" m.MemberId, ")
sql.AppendLine(" CTE.Dimension ")
sql.AppendLine(" FROM Relationship r ")
sql.AppendLine(" INNER JOIN RecursiveCTE CTE ")
sql.AppendLine(" ON CTE.ChildId = r.ParentId ")
sql.AppendLine(" INNER JOIN Member m ")
sql.AppendLine(" ON m.DimId IN (SELECT value FROM STRING_SPLIT(@dimIds, ',')) ")
sql.AppendLine(" AND r.DimTypeId = m.DimTypeId ")
sql.AppendLine(" AND r.ChildId = m.MemberId ")
sql.AppendLine(" ) ")
' Select Results from CTE and replace NULL values with 'Root'
sql.AppendLine(" SELECT COALESCE(Parent, 'Root') Parent, Child, Level, Dimension ")
sql.AppendLine(" FROM RecursiveCTE ")
sql.AppendLine(" ORDER BY Level; ")
' End of IF block for @dimTypeId = 0
sql.AppendLine(" END ")
sql.AppendLine("ELSE ")
sql.AppendLine(" 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
sql.AppendLine(" ;WITH CTE ")
sql.AppendLine(" AS ( ")
sql.AppendLine(" SELECT DimTypeId, DimId, Name, InheritedDimId, CAST(DimId AS VARCHAR(MAX)) Path ")
sql.AppendLine(" FROM Dim ")
sql.AppendLine(" WHERE InheritedDimId NOT IN (SELECT DimId FROM Dim) ")
sql.AppendLine(" AND DimTypeId = @dimTypeId ")
sql.AppendLine(" UNION ALL ")
sql.AppendLine(" SELECT d.DimTypeId, d.DimId, d.Name, d.InheritedDimId, CONCAT(r.Path, ',', d.DimId) ")
sql.AppendLine(" FROM Dim d ")
sql.AppendLine(" JOIN CTE r ON d.InheritedDimId = r.DimId ")
sql.AppendLine(" ) ")
sql.AppendLine(" SELECT @dimIds = Path FROM CTE WHERE Name = @dim; ")
sql.AppendLine(" ")
sql.AppendLine(" ;WITH RecursiveCTE ")
sql.AppendLine(" AS ( ")
' Declare Anchor Member
sql.AppendLine(" SELECT DISTINCT ")
sql.AppendLine(" m.Name Child, ")
sql.AppendLine(" p.Name Parent, ")
sql.AppendLine(" 0 Level, ")
sql.AppendLine(" m.MemberId ChildId, ")
sql.AppendLine(" @dim Dimension ")
sql.AppendLine(" FROM Relationship r ")
sql.AppendLine(" LEFT JOIN Member m ")
sql.AppendLine(" ON r.DimId = m.DimId ")
sql.AppendLine(" AND r.DimTypeId = m.DimTypeId ")
sql.AppendLine(" AND r.ChildId = m.MemberId ")
sql.AppendLine(" LEFT JOIN Member p ")
sql.AppendLine(" ON r.DimTypeId = p.DimTypeId ")
sql.AppendLine(" AND r.ParentId = p.MemberId ")
sql.AppendLine(" WHERE r.DimId IN (SELECT value FROM STRING_SPLIT(@dimIds, ',')) ")
sql.AppendLine(" AND p.Name IS NULL ")
sql.AppendLine(" ")
sql.AppendLine(" UNION ALL ")
' Declare Recursive Member
sql.AppendLine(" SELECT ")
sql.AppendLine(" m.Name, ")
sql.AppendLine(" CTE.Child, ")
sql.AppendLine(" Level + 1, ")
sql.AppendLine(" m.MemberId, ")
sql.AppendLine(" CTE.Dimension ")
sql.AppendLine(" FROM Relationship r ")
sql.AppendLine(" INNER JOIN RecursiveCTE CTE ")
sql.AppendLine(" ON CTE.ChildId = r.ParentId ")
sql.AppendLine(" INNER JOIN Member m ")
sql.AppendLine(" ON m.DimId IN (SELECT value FROM STRING_SPLIT(@dimIds, ',')) ")
sql.AppendLine(" AND r.DimTypeId = m.DimTypeId ")
sql.AppendLine(" AND r.ChildId = m.MemberId ")
sql.AppendLine(" ) ")
' Select Results from CTE and replace NULL values with 'Root'
sql.AppendLine(" SELECT COALESCE(Parent, 'Root') Parent, Child, Level, Dimension ")
sql.AppendLine(" FROM RecursiveCTE ")
sql.AppendLine(" ORDER BY Level; ")
' End of ELSE block for @dimTypeId <> 0
sql.AppendLine("END ")
' Execute Parameterized Query on App DB
Using dbConnApp As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
Using dt As DataTable = BRAPi.Database.ExecuteSql(dbConnApp, Convert.ToString(sql), dbParameterInfo, True)
' Return
Return dt
End Using
End Using
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
#End Region
Good stuff! I love all the comments.
I have a couple of thoughts on this...
I think OneStream sets a less then optimal example using StringBuilder for basic SQL queries when a simple multiline string would efficient and (arguably) more readable. StringBuilder makes the code noisy, and in this particular case is significantly more active on the proc, ~100 AppendLine() calls vs 1 String assignment. Ultimately the goal should be code readability when perfomance gains/losses are negligable.
I prefer to use stringbuilders only in loops that build a string over many iterations. Even then, it usually works out better to store the strings in a List and use String.Join to efficently manage things like leading and trailing commas and delimiters.
Dear MMomentoff: Please don't use StringBuilders for fully constructed SQL query strings. 🙂
Regarding the returned Datatable on line 158 above, consider a better approach to not return an object from within its Using block to a consuming method. The reason is Dispose() is called on the returned Datatable as soon as it leaves the Using block. This can lead to unexpected results. It may seem like it works because the Garbage Collector thread may not come around to destroy the datatable before the consuming method parses it - however this is not guaranteed. This is refactored below to provide a safe return of the DataTable object to the calling method by allowing the datatable to be GC'd when it goes out of scope:
#Region "Get Dimension DataTable"
'------------------------------------------------------------------------------------------------------------
'Referenced Code: GetDimensionDataTable -> DataTable
'
'Description: Function to return DataTable
'
'Notes: Recursive CTE that caters for extensibility (All Dims) and outputs hierarchy and level
' for parent, child relationships
'
'Usage: DataTable = Me.GetDimensionDataTable(si, "CorpEntities")
'
'Created By: OneStream
'Date Created: 29-09-2023
'------------------------------------------------------------------------------------------------------------
Public Function GetDimensionDataTable(ByVal si As SessionInfo, ByVal dimensionName As String) As DataTable
Try
Dim dt As DataTable = Nothing ' the return object
' Set dbParamInfo
Dim dbParameterInfo As List(Of dbParamInfo) = New List(Of dbParamInfo) From {New DbParamInfo("@dim", dimensionName)}
' Declare Recursive CTE that handles extended dimensions
Dim sql As String = "
-- Declare Recursive CTE that handles extended dimensions
-- 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 a dynamic 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
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
-- Declare Recursive Member
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 Results from CTE and replace NULL values with 'Root'
SELECT COALESCE(Parent, 'Root') Parent, Child, Level, Dimension
FROM RecursiveCTE
ORDER BY Level;
END
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 (
-- Declare Anchor Member
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
-- Declare Recursive Member
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 Results from CTE and replace NULL values with 'Root'
SELECT COALESCE(Parent, 'Root') Parent, Child, Level, Dimension
FROM RecursiveCTE
ORDER BY Level;
END
END "
' Execute Parameterized Query on App DB
Using dbConnApp As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
dt = BRAPi.Database.ExecuteSql(dbConnApp, Convert.ToString(sql), dbParameterInfo, True)
End Using
Return dt
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
#End Region
argh yes stringbuilder SQL queries kill me. I have mentioned it a few times in these forums also.
Perhaps it IS faster (but noticeably faster?) or better on memory to add 50 lines together with stringbuilder compared to concatenating 50 strings BUT you don't need to concatenate 50 strings; you can just have a single multiline string. And the person that has to debug your code doesn't hate you when they have to put the SQL back together to test it outside of the BR.
