Quick and simple aggregation of Hierarchical Data in SQL Server

Sometimes data containing hierarchies have to be aggregated and total calculated on different levels. The following script will help archive fast calculation of totals on each level of hierarchy.

INPUT DATA STRUCTURE AND VALUES

0100***Level 1*****$21
0110*****Level 2***$6
0111******Level 3**$1
0112******Level 3**$2
0113******Level 3**$3
0120*****Level 2***$15
0121******Level 3**$4
0122******Level 3**$5
0123******Level 3**$6


-- define codes table with hierarchy

 Declare @Code Table (Code varchar(10), Hierarchy varchar(50), Description varchar(100))

 INSERT @Code (Code, Hierarchy, Description) VALUES ('0100', '1.', 'Total')
 INSERT @Code (Code, Hierarchy, Description) VALUES ('0110', '1.01.', 'Total of 0110')
 INSERT @Code (Code, Hierarchy, Description) VALUES ('0111', '1.01.01.', 'Value of 0111')
 INSERT @Code (Code, Hierarchy, Description) VALUES ('0112', '1.01.02.', 'Value of 0112')
 INSERT @Code (Code, Hierarchy, Description) VALUES ('0113', '1.01.03.', 'Value of 0113')
 INSERT @Code (Code, Hierarchy, Description) VALUES ('0120', '1.02.', 'Total of 0120')
 INSERT @Code (Code, Hierarchy, Description) VALUES ('0121', '1.02.01.', 'Value of 0121')
 INSERT @Code (Code, Hierarchy, Description) VALUES ('0122', '1.02.02.', 'Value of 0122')
 INSERT @Code (Code, Hierarchy, Description) VALUES ('0123', '1.02.03.', 'Value of 0123')

-- create table to record values for codes

 Declare @CodeValues Table (Code varchar(10), Value money)

 INSERT @CodeValues (Code, Value) VALUES ('0111', 1)
 INSERT @CodeValues (Code, Value) VALUES ('0112', 2)
 INSERT @CodeValues (Code, Value) VALUES ('0113', 3)
 INSERT @CodeValues (Code, Value) VALUES ('0121', 4)
 INSERT @CodeValues (Code, Value) VALUES ('0122', 5)
 INSERT @CodeValues (Code, Value) VALUES ('0123', 6)

-- create temp table that will combine values with code hierarcies and will be used in the rollup

 Declare @TmpValues Table (Code varchar(10), CodeHierarchy varchar(10), Value money)

 INSERT @TmpValues (Code, Value, CodeHierarchy)
 Select
   v.Code,
   v.Value,
   c.Hierarchy
 From
   @CodeValues v
   Inner Join @Code c On c.Code = v.Code

-- Rollup Data

 Select
  c.Code,
  c.Description,
  Sum(v.Value)
 From
  @TmpValues v
  Join @Code c On v.CodeHierarchy Like c.Hierarchy+'%'
 Group By
  c.Code,
  c.Description
 Order By
  c.Code

This method was originally suggested by Scot Smith, Toronto.

Script to de-normalize Parent - Child Hierarchy

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