11-22-2023 01:12 PM - last edited on 11-22-2023 02:35 PM by JackLacava
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
11-22-2023 02:38 PM
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!
11-29-2023 11:23 AM - edited 11-29-2023 11:31 AM
@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}")
11-22-2023 02:49 PM
Excellent! Thank you so much!
11-24-2023 01:02 PM
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
11-27-2023 10:40 AM - edited 11-27-2023 10:40 AM
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
11-27-2023 10:47 AM
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.
11-27-2023 02:37 PM
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 🙂
11-29-2023 08:37 AM - edited 11-29-2023 08:41 AM
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
11-29-2023 11:04 AM - edited 11-29-2023 11:30 AM
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
01-24-2024 09:50 PM
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.
11-27-2023 01:20 PM
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. 🙂
02-27-2024 08:22 AM
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: