Parent-child - how to transform and get ready for TARGIT Data Model Editor

This example shows how to read from a database table where parent-child is implemented in a traditional way, and transform this into a dimension table that can be utilized directly in the TARGIT Data Model Editor (including a column that identifies the level of the hierarchy of a particular row).

Script:

DATASOURCE[XX Master]= SQLSERVER 'Persist Security Info=False;Data source=markXX;Initial Catalog=Update2_XX_Master;Integrated Security=SSPI'

IMPORT Items=[XX Master].{SELECT Id, Name FROM Items} 

IMPORT[tmp_ItemHierarchy]=[XX Master].{WITH MyTest as
(
  SELECT P.Id, P.ParentID,CAST(P.Id ASVarChar(Max))as Level
  FROM Items P
  WHERE P.ParentID ='11111111-1111-1111-1111-111111111111'

  UNIONALL

  SELECT P1.Id, P1.ParentID,CAST(P1.Id ASVarChar(Max))+', '+ M.Level
  FROM Items P1  
  INNERJOIN MyTest M
  ON M.Id = P1.ParentID
 )
 select*from myTest}
declare @maxlevels asint 

set @maxlevels ={
selectmax(level)as MaxLevel from 
(
selectlen(level)-len(replace(level,',','')) level from tmp_ItemHierarchy orderbylen(level)-len(replace(level,',',''))  desc
) a
}

DECLARE @i asint
declare @levelcnt asint
declare @leveltxt asstring
declare @newleveltxt asstring
declare @reallevel asint
declare @levelname asstring
declare @begininsert asstring
declare @insertsql asstring
declare @createtablesql asstring
set @createtablesql=''

FOR @i=0TO @maxlevels
 set @createtablesql=@createtablesql+'[Level'+@i+'] string, [LevelName'+@i+'] string,'
NEXT

set @createtablesql='CREATE TABLE HierarchyTable ([Id] string,[ParentId] string,[Level] string,'+@createtablesql+' [dummy] string)'

print @createtablesql

scriptexec @createtablesql

declare @feltdata asstring


WHILE tmp_ItemHierarchy.EOF=false
 
 set @feltdata=tmp_ItemHierarchy.Level+', '
 set @levelcnt =len(@feltdata)-len(replace(@feltdata,',',''))
 set @reallevel=@levelcnt-1
 set @insertsql=''
  
  for @i=0to @levelcnt-1 
  
  set @leveltxt=trim(left(@feltdata,charindex(@feltdata,',')-1))   
  set @feltdata=substring(@feltdata,charindex(@feltdata,',')+1)
  set @levelname={select Name from items where id=trim(@leveltxt)}  

  set @insertsql=@insertsql+''''+@leveltxt+''','''+@levelname+''','
  set @reallevel=@reallevel-1
    
  next  
  
  set @begininsert=''
  
   set @reallevel=@levelcnt-1
  for @i=0to @levelcnt-1
      set @begininsert=@begininsert+'[Level'+@reallevel+'],[LevelName'+@reallevel+'],'
      
 set @reallevel=@reallevel-1
  next
  
  set @begininsert=left(@begininsert,len(@begininsert)-1)
  set @insertsql=left(@insertsql,len(@insertsql)-1) 
 
 scriptexec 'INSERT INTO HierarchyTable (id,parentid,level,'+@begininsert+') VALUES ('''+tmp_itemHierarchy.Id+''','''+tmp_itemHierarchy.ParentId+''','''+tmp_itemHierarchy.Level+''','+@insertsql+')' 
 
movenext tmp_ItemHierarchy
LOOP

COLUMNIZE HierarchyTable

IMPORT wdLandingPageHierarchyTable=me.{SELECT*FROM HierarchyTable}

Download: Parent-child.improj

Note: The script serves only as inspiration and starting point - it cannot be executed out-of-the-box when you download it.