Parent Child data hierarchies are great for storing hierarchical data, however it becomes a major pain when you need to convert hierarchy structure into a flat view (e.g. to be used in reporting or SSAS cubes). While SSAS provide a build in support for parent-child dimensions, it only allows for one parent attribute per dimension.

I’ve looked into various techniques to convert parent-child into flat table and found a tool designed by Jon Burchel specifically for normalizing SSAS parent-child dimensions. The original tool is available on codeplex at http://pcdimnaturalize.codeplex.com

Base on this, I came up with my own version – a SQL Script that creates a SQL Server view for each lookup table in the database that contains parent-child hierarchy and converts it into a flat hierarchy with up to 3 levels.

The script assumes that lookup tables have “lu_” prefix. And it detects parent-child structure by searching for a column that starts with name “parent_”.

Declare @Sql varchar(8000)


Declare @SrcTableName varchar(250), @AttributeName varchar(250)


Declare Cur Cursor For
Select distinct TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME like ‘lu_%’ and COLUMN_NAME like ‘parent_%’
Open Cur


Fetch Next From Cur Into @SrcTableName


While @@FETCH_STATUS = 0
Begin


Set @AttributeName = SUBSTRING(@SrcTableName, 4, 250)


Set @Sql =
‘
CREATE VIEW [dbo].[vw_naturalized_’ + @SrcTableName + ‘] AS
WITH 
PCStructure(Level, [parent_’ + @AttributeName + ‘_id], [‘ + @AttributeName + ‘ Name_KeyColumn], [‘ + @AttributeName + ‘ 01_KeyColumn], [‘ + @AttributeName + ‘ 02_KeyColumn], [‘ + @AttributeName + ‘ 03_KeyColumn])
AS
(
SELECT 
3 Level, [parent_’ + @AttributeName + ‘_id], [‘ + @AttributeName + ‘_id], [‘ + @AttributeName + ‘_id] as [‘ + @AttributeName + ‘ 01_KeyColumn], [‘ + @AttributeName + ‘_id] as [‘ + @AttributeName + ‘ 02_KeyColumn], [‘ + @AttributeName + ‘_id] as [‘ + @AttributeName + ‘ 03_KeyColumn] 
FROM 
[dbo].[‘ + @SrcTableName + ‘] 
WHERE [parent_’ + @AttributeName + ‘_id] IS NULL OR [parent_’ + @AttributeName + ‘_id] = [‘ + @AttributeName + ‘_id] 

UNION ALL 

SELECT 
Level + 1, e.[parent_’ + @AttributeName + ‘_id], e.[‘ + @AttributeName + ‘_id], 
CASE Level WHEN 2 THEN e.[‘ + @AttributeName + ‘_id] ELSE [‘ + @AttributeName + ‘ 01_KeyColumn] END AS [fetal_therapy 01_KeyColumn], 
CASE Level WHEN 2 THEN e.[‘ + @AttributeName + ‘_id] WHEN 3 THEN e.[‘ + @AttributeName + ‘_id] ELSE [‘ + @AttributeName + ‘ 02_KeyColumn] END AS [‘ + @AttributeName + ‘ 02_KeyColumn],
CASE Level WHEN 2 THEN e.[‘ + @AttributeName + ‘_id] WHEN 3 THEN e.[‘ + @AttributeName + ‘_id] WHEN 4 THEN e.[‘ + @AttributeName + ‘_id] ELSE [‘ + @AttributeName + ‘ 03_KeyColumn] END AS [‘ + @AttributeName + ‘ 03_KeyColumn] 
FROM [dbo].[‘ + @SrcTableName + ‘] e 
INNER JOIN PCStructure d ON e.[parent_’ + @AttributeName + ‘_id] = d.[‘ + @AttributeName + ‘ Name_KeyColumn] AND e.[parent_’ + @AttributeName + ‘_id] != e.[‘ + @AttributeName + ‘_id]
)


select 
Level4Subselect.*
from PCStructure a, 
(select [‘ + @AttributeName + ‘_id] [‘ + @AttributeName + ‘ 03_KeyColumn], [‘ + @AttributeName + ‘_name] [‘ + @AttributeName + ‘ 03_NameColumn], [‘ + @AttributeName + ‘_order_num] [‘ + @AttributeName + ‘ 03_’ + @AttributeName + ‘ Order Num_KeyColumn], Level3Subselect.*
from [dbo].[‘ + @SrcTableName + ‘] b,
(select [‘ + @AttributeName + ‘_id] [‘ + @AttributeName + ‘ 02_KeyColumn], [‘ + @AttributeName + ‘_name] [‘ + @AttributeName + ‘ 02_NameColumn], [‘ + @AttributeName + ‘_order_num] [‘ + @AttributeName + ‘ 02_’ + @AttributeName + ‘ Order Num_KeyColumn] , Level2Subselect.*
from [dbo].[‘ + @SrcTableName + ‘] b,
(select [‘ + @AttributeName + ‘_id] [‘ + @AttributeName + ‘ 01_KeyColumn], [‘ + @AttributeName + ‘_name] [‘ + @AttributeName + ‘ 01_NameColumn], [‘ + @AttributeName + ‘_order_num] [‘ + @AttributeName + ‘ 01_’ + @AttributeName + ‘ Order Num_KeyColumn], CurrentMemberSubselect.* 
from [dbo].[‘ + @SrcTableName + ‘] b,
(select [‘ + @AttributeName + ‘_id] as [original_’ + @AttributeName + ‘_id], [‘ + @AttributeName + ‘_name] [original_’ + @AttributeName + ‘_name], [parent_’ + @AttributeName + ‘_id] [original_parent_’ + @AttributeName + ‘_id], [‘ + @AttributeName + ‘_order_num] [original_’ + @AttributeName + ‘_order_num]
from [dbo].[‘ + @SrcTableName + ‘] b) CurrentMemberSubselect
) Level2Subselect
) Level3Subselect
) Level4Subselect
where
Level4Subselect.[‘ + @AttributeName + ‘ 03_KeyColumn] = a.[‘ + @AttributeName + ‘ 03_KeyColumn] and
Level4Subselect.[‘ + @AttributeName + ‘ 02_KeyColumn] = a.[‘ + @AttributeName + ‘ 02_KeyColumn] and
Level4Subselect.[‘ + @AttributeName + ‘ 01_KeyColumn] = a.[‘ + @AttributeName + ‘ 01_KeyColumn] and
Level4Subselect.[original_’ + @AttributeName + ‘_id] = a.[‘ + @AttributeName + ‘ Name_KeyColumn]
GO
‘
Print @Sql


Fetch Next From Cur Into @SrcTableName
end


close cur
deallocate cur