Hi,

i am just working on a financial model that contains a lot of parent-child hierarchies
so i have to run the same procedure for each table to to create that hierarchy in my tabular model:
1) create a calculated column [FullPath]=PATH([Key], [Parent])
2) create one column for each level as [LevelX]=PATHITEM([FullPath], X)
3) create a hierarchy using those new columns

as those 3 steps are quite simple i think it would be ease to write a little wizzard for that
It would take 3 parameters: [Key-column], [Parent-column] and the number of Levels (which may also be automated)
additionally a naming-template would be nice :)

this would save a lot of people a lot of stupid work :)

what do you think about it?

kind regards,
gerhard

That's a good feature suggestion. I've created a feature request for it: http://bidshelper.codeplex.com/workitem/33697

Another approach is doing the flattening of the parent-child hierarchy in T-SQL. For example, the BIDS Helper Parent-Child Dimension Naturalizer creates the following view on top of the Adventure Works DimEmployee table:

CREATE VIEW [dbo].[DimNaturalized_DimEmployee] AS

WITH PCStructure(Level, ParentEmployeeKey, CurrentMemberID, Level2, Level3, Level4, Level5, Level6, Level7)

AS (SELECT 3 Level, ParentEmployeeKey, EmployeeKey, EmployeeKey as Level2,

null as Level3,

null as Level4,

null as Level5,

null as Level6,

null as Level7

FROM [dbo].[DimEmployee] WHERE ParentEmployeeKey IS NULL UNION ALL SELECT Level + 1, e.ParentEmployeeKey, e.EmployeeKey, CASE Level WHEN 2 THEN e.EmployeeKey ELSE Level2 END AS Level2,

CASE Level WHEN 3 THEN e.EmployeeKey ELSE Level3 END AS Level3,

CASE Level WHEN 4 THEN e.EmployeeKey ELSE Level4 END AS Level4,

CASE Level WHEN 5 THEN e.EmployeeKey ELSE Level5 END AS Level5,

CASE Level WHEN 6 THEN e.EmployeeKey ELSE Level6 END AS Level6,

CASE Level WHEN 7 THEN e.EmployeeKey ELSE Level7 END AS Level7 FROM [dbo].[DimEmployee] e INNER JOIN PCStructure d ON e.ParentEmployeeKey = d.CurrentMemberID)

select CurrentMemberSubselect.*,

Level2Subselect.*, Level3Subselect.*, Level4Subselect.*, Level5Subselect.*, Level6Subselect.*, Level7Subselect.*

from PCStructure a

left outer join (select EmployeeKey CurrentMemberID

from [dbo].[DimEmployee]) CurrentMemberSubselect on CurrentMemberSubselect.CurrentMemberID = a.CurrentMemberID

left outer join (select EmployeeKey Level2,

[EmployeeNationalIDAlternateKey] [Level2_EmployeeNationalIDAlternateKey],

[ParentEmployeeNationalIDAlternateKey] [Level2_ParentEmployeeNationalIDAlternateKey],

[SalesTerritoryKey] [Level2_SalesTerritoryKey],

[FirstName] [Level2_FirstName],

[LastName] [Level2_LastName],

[MiddleName] [Level2_MiddleName],

[NameStyle] [Level2_NameStyle],

[Title] [Level2_Title],

[HireDate] [Level2_HireDate],

[BirthDate] [Level2_BirthDate],

[LoginID] [Level2_LoginID],

[EmailAddress] [Level2_EmailAddress],

[Phone] [Level2_Phone],

[MaritalStatus] [Level2_MaritalStatus],

[EmergencyContactName] [Level2_EmergencyContactName],

[EmergencyContactPhone] [Level2_EmergencyContactPhone],

[SalariedFlag] [Level2_SalariedFlag],

[Gender] [Level2_Gender],

[PayFrequency] [Level2_PayFrequency],

[BaseRate] [Level2_BaseRate],

[VacationHours] [Level2_VacationHours],

[SickLeaveHours] [Level2_SickLeaveHours],

[CurrentFlag] [Level2_CurrentFlag],

[SalesPersonFlag] [Level2_SalesPersonFlag],

[DepartmentName] [Level2_DepartmentName],

[StartDate] [Level2_StartDate],

[EndDate] [Level2_EndDate],

[Status] [Level2_Status]

from [dbo].[DimEmployee]) Level2Subselect on Level2Subselect.Level2 = a.Level2

left outer join (select EmployeeKey Level3,

[EmployeeNationalIDAlternateKey] [Level3_EmployeeNationalIDAlternateKey],

[ParentEmployeeNationalIDAlternateKey] [Level3_ParentEmployeeNationalIDAlternateKey],

[SalesTerritoryKey] [Level3_SalesTerritoryKey],

[FirstName] [Level3_FirstName],

[LastName] [Level3_LastName],

[MiddleName] [Level3_MiddleName],

[NameStyle] [Level3_NameStyle],

[Title] [Level3_Title],

[HireDate] [Level3_HireDate],

[BirthDate] [Level3_BirthDate],

[LoginID] [Level3_LoginID],

[EmailAddress] [Level3_EmailAddress],

[Phone] [Level3_Phone],

[MaritalStatus] [Level3_MaritalStatus],

[EmergencyContactName] [Level3_EmergencyContactName],

[EmergencyContactPhone] [Level3_EmergencyContactPhone],

[SalariedFlag] [Level3_SalariedFlag],

[Gender] [Level3_Gender],

[PayFrequency] [Level3_PayFrequency],

[BaseRate] [Level3_BaseRate],

[VacationHours] [Level3_VacationHours],

[SickLeaveHours] [Level3_SickLeaveHours],

[CurrentFlag] [Level3_CurrentFlag],

[SalesPersonFlag] [Level3_SalesPersonFlag],

[DepartmentName] [Level3_DepartmentName],

[StartDate] [Level3_StartDate],

[EndDate] [Level3_EndDate],

[Status] [Level3_Status]

from [dbo].[DimEmployee]) Level3Subselect on Level3Subselect.Level3 = a.Level3

left outer join (select EmployeeKey Level4,

[EmployeeNationalIDAlternateKey] [Level4_EmployeeNationalIDAlternateKey],

[ParentEmployeeNationalIDAlternateKey] [Level4_ParentEmployeeNationalIDAlternateKey],

[SalesTerritoryKey] [Level4_SalesTerritoryKey],

[FirstName] [Level4_FirstName],

[LastName] [Level4_LastName],

[MiddleName] [Level4_MiddleName],

[NameStyle] [Level4_NameStyle],

[Title] [Level4_Title],

[HireDate] [Level4_HireDate],

[BirthDate] [Level4_BirthDate],

[LoginID] [Level4_LoginID],

[EmailAddress] [Level4_EmailAddress],

[Phone] [Level4_Phone],

[MaritalStatus] [Level4_MaritalStatus],

[EmergencyContactName] [Level4_EmergencyContactName],

[EmergencyContactPhone] [Level4_EmergencyContactPhone],

[SalariedFlag] [Level4_SalariedFlag],

[Gender] [Level4_Gender],

[PayFrequency] [Level4_PayFrequency],

[BaseRate] [Level4_BaseRate],

[VacationHours] [Level4_VacationHours],

[SickLeaveHours] [Level4_SickLeaveHours],

[CurrentFlag] [Level4_CurrentFlag],

[SalesPersonFlag] [Level4_SalesPersonFlag],

[DepartmentName] [Level4_DepartmentName],

[StartDate] [Level4_StartDate],

[EndDate] [Level4_EndDate],

[Status] [Level4_Status]

from [dbo].[DimEmployee]) Level4Subselect on Level4Subselect.Level4 = a.Level4

left outer join (select EmployeeKey Level5,

[EmployeeNationalIDAlternateKey] [Level5_EmployeeNationalIDAlternateKey],

[ParentEmployeeNationalIDAlternateKey] [Level5_ParentEmployeeNationalIDAlternateKey],

[SalesTerritoryKey] [Level5_SalesTerritoryKey],

[FirstName] [Level5_FirstName],

[LastName] [Level5_LastName],

[MiddleName] [Level5_MiddleName],

[NameStyle] [Level5_NameStyle],

[Title] [Level5_Title],

[HireDate] [Level5_HireDate],

[BirthDate] [Level5_BirthDate],

[LoginID] [Level5_LoginID],

[EmailAddress] [Level5_EmailAddress],

[Phone] [Level5_Phone],

[MaritalStatus] [Level5_MaritalStatus],

[EmergencyContactName] [Level5_EmergencyContactName],

[EmergencyContactPhone] [Level5_EmergencyContactPhone],

[SalariedFlag] [Level5_SalariedFlag],

[Gender] [Level5_Gender],

[PayFrequency] [Level5_PayFrequency],

[BaseRate] [Level5_BaseRate],

[VacationHours] [Level5_VacationHours],

[SickLeaveHours] [Level5_SickLeaveHours],

[CurrentFlag] [Level5_CurrentFlag],

[SalesPersonFlag] [Level5_SalesPersonFlag],

[DepartmentName] [Level5_DepartmentName],

[StartDate] [Level5_StartDate],

[EndDate] [Level5_EndDate],

[Status] [Level5_Status]

from [dbo].[DimEmployee]) Level5Subselect on Level5Subselect.Level5 = a.Level5

left outer join (select EmployeeKey Level6,

[EmployeeNationalIDAlternateKey] [Level6_EmployeeNationalIDAlternateKey],

[ParentEmployeeNationalIDAlternateKey] [Level6_ParentEmployeeNationalIDAlternateKey],

[SalesTerritoryKey] [Level6_SalesTerritoryKey],

[FirstName] [Level6_FirstName],

[LastName] [Level6_LastName],

[MiddleName] [Level6_MiddleName],

[NameStyle] [Level6_NameStyle],

[Title] [Level6_Title],

[HireDate] [Level6_HireDate],

[BirthDate] [Level6_BirthDate],

[LoginID] [Level6_LoginID],

[EmailAddress] [Level6_EmailAddress],

[Phone] [Level6_Phone],

[MaritalStatus] [Level6_MaritalStatus],

[EmergencyContactName] [Level6_EmergencyContactName],

[EmergencyContactPhone] [Level6_EmergencyContactPhone],

[SalariedFlag] [Level6_SalariedFlag],

[Gender] [Level6_Gender],

[PayFrequency] [Level6_PayFrequency],

[BaseRate] [Level6_BaseRate],

[VacationHours] [Level6_VacationHours],

[SickLeaveHours] [Level6_SickLeaveHours],

[CurrentFlag] [Level6_CurrentFlag],

[SalesPersonFlag] [Level6_SalesPersonFlag],

[DepartmentName] [Level6_DepartmentName],

[StartDate] [Level6_StartDate],

[EndDate] [Level6_EndDate],

[Status] [Level6_Status]

from [dbo].[DimEmployee]) Level6Subselect on Level6Subselect.Level6 = a.Level6

left outer join (select EmployeeKey Level7,

[EmployeeNationalIDAlternateKey] [Level7_EmployeeNationalIDAlternateKey],

[ParentEmployeeNationalIDAlternateKey] [Level7_ParentEmployeeNationalIDAlternateKey],

[SalesTerritoryKey] [Level7_SalesTerritoryKey],

[FirstName] [Level7_FirstName],

[LastName] [Level7_LastName],

[MiddleName] [Level7_MiddleName],

[NameStyle] [Level7_NameStyle],

[Title] [Level7_Title],

[HireDate] [Level7_HireDate],

[BirthDate] [Level7_BirthDate],

[LoginID] [Level7_LoginID],

[EmailAddress] [Level7_EmailAddress],

[Phone] [Level7_Phone],

[MaritalStatus] [Level7_MaritalStatus],

[EmergencyContactName] [Level7_EmergencyContactName],

[EmergencyContactPhone] [Level7_EmergencyContactPhone],

[SalariedFlag] [Level7_SalariedFlag],

[Gender] [Level7_Gender],

[PayFrequency] [Level7_PayFrequency],

[BaseRate] [Level7_BaseRate],

[VacationHours] [Level7_VacationHours],

[SickLeaveHours] [Level7_SickLeaveHours],

[CurrentFlag] [Level7_CurrentFlag],

[SalesPersonFlag] [Level7_SalesPersonFlag],

[DepartmentName] [Level7_DepartmentName],

[StartDate] [Level7_StartDate],

[EndDate] [Level7_EndDate],

[Status] [Level7_Status]

from [dbo].[DimEmployee]) Level7Subselect on Level7Subselect.Level7 = a.Level7

GO