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
