Indexed parent-child hierarchy table to fact table

Question

I have a potential fact table that i want to use as the center of a star schema. It has a indexed parent-child hierarchy structure which i want to flatten using SQL. The table looks like this:

Id	Description	ParentId	DataValue
1	Root	0	0
2	Component A	1	0
3	Component B	1	0
4	Component A	1	1
5	Component B	1	1
6	Capacity	2	10.5
7	Temperature	2	22
8	Sub Component A	2	0
9	Viscosity	8	0.8
10	Sub Component A	2	1
11	Viscosity	10	1.2

As you can see a record can have multiple children. If a record has children an index is stored in the DataValue column. Child records that do not have children of their own (leafs in hierarchy tree) store actual measurement data in the DataValue column. Here some examples how the paths could look like:

• Root[0] > Component A[0] > Capacity = 10.5

• Root[0] > Component A[0] > Sub Component A[0] > Viscosity = 0.8

• Root[0] > Component A[0] > Sub Component A[1] > Viscosity = 1.2

I would like to flatten the data so each level has its own description and index:

Lvl1Description	Lvl1Index	Lvl2Description	Lvl2Index	Lvl3Description	Lvl3Index	LeafDescription	DataValue
Root	0	Component A	0	NULL	NULL	Capacity	10.5
Root	0	Component A	0	Sub Component A	0	Viscosity	0.8
Root	0	Component A	0	Sub Component A	1	Viscosity	1.2

Eventually i want to create a fact table using the DataValue column as factual data. The hierarchy would be stored in a dimension.

I have tried using a recursive CTE but i was not able to get the data as shown above. I would appreciate if somebody could give me some tips or a fitting solution for what i am trying to accomplish.

Answer 1

With fixed parent-child hierarchy deep, can use sequential join's.

As first attempt, see example

select t1.Description Lvl1Description,t1.Id Id1,t1.ParentId ParentId1,t1.DataValue Datavalue1
  ,t2.Description Lvl2Description,t2.Id Id2,t2.ParentId ParentId2,t2.DataValue Datavalue2
  ,case when t4.Id is not null then t3.Description end Lvl3Description
  ,t3.Id Id3,t3.ParentId ParentId3
  ,case when t4.Id is not null then t3.Datavalue end Datavalue3
  ,case when t4.Id is not null then t4.Description
   else t3.Description end LeafDescription
  ,t4.Id Id4,t4.ParentId ParentId4
  ,case when t4.Id is not null then t4.Datavalue
   else t3.Datavalue end Datavalue
from test t1
left join test t2 on t2.ParentId=t1.Id
left join test t3 on t3.ParentId=t2.Id
left join test t4 on t4.ParentId=t3.Id
where t1.ParentId=0
order by Lvl1Description,Lvl2Description,DataValue2

fiddle