Originally Posted on December 15 2011
Just for one time, I dismiss my usual business attire to publish a very technical post, rooted in the real life of my “other” job. I hope it could be of help to someone.
Few days ago I had to implement a new conformed dimension. For reasons too long to describe here, I couldn’t load a table but I had to use a view from existing tables in a SQL server linked server.
The dimension hierarchy was available as a string, with separators among the elements.
For example “Food/Fruits/Peaches/Nectarines”
Also I had rows like “Food/Mineral Water”
There were also rows like “Food/Confectionary/Desserts/Pudding/Chocolate Pudding”
In a word, the hierarchy was unbalanced. So, to add it to a conformed dimension, I had to split the string and balance the hierarchy to look like the table below; and I had to do it on the fly.
My solution was to implement a function in T-SQL.
USE [<your database>]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE FUNCTION dbo.[H_Element_Get]
RETURNS NVARCHAR (MAX)
DECLARE @Iter INT
DECLARE @sstring nvarchar (max)
SET @Iter = 1
DECLARE @FoundIndex INT
SET @FoundIndex = CHARINDEX(@Sep,@RowData)
@sstring = LTRIM(RTRIM(SUBSTRING(@RowData, 1, @FoundIndex - 1)))
SET @RowData = SUBSTRING(@RowData,@FoundIndex + DATALENGTH(@Sep) / 2,LEN(@RowData))
SET @FoundIndex = CHARINDEX(@Sep, @RowData)
SELECT @sstring = LTRIM(RTRIM(@RowData))
SET @Iter = @Iter + 1
The [H_Element_Get] function accepts 3 parameters:
@RowData = the string of chained elements
@sep = the separator
@Ord = the hierarchy level to retrieve
The maximum hierarchy depth is known, of course, and it is not subject to change without notice.
The select statement for, say, a four level hierarchy may look like the following:
H_Element_Get(<string>,<separator>,1) as [Level 1],
H_Element_Get(<string>,<separator>,2) as [Level 2],
H_Element_Get(<string>,<separator>,3) as [Level 3],
H_Element_Get(<string>,<separator>,4) as [Level 4]
Let me know if I can be of further help.
See you next time.
290 views and 2 responses
Dic 16 2011, 9:11 PM
Yuri Fal liked this post.
Dic 20 2011, 3:22 AM
Ellie K liked this post.