Sorry I forgot to add the Level into the second Select statement which should read like this

Select Dim_CostCentre_SK

,Dim_CostCentre_ParentBK

,Dim_CostCentre_Desc

,0 Level

from Dim_CostCentre

Where Dim_CostCentre_ParentBK IS NULL

/*

Get all the children

*/

UNION ALL

SELECT d.Dim_CostCentre_SK

,d.Dim_CostCentre_ParentBK

,p.DimcostCentre_Desc+'\'+d.Dim_CostCentre_Desc

,p.Level+1 Level

FROM Dim_CostCentre d

JOIN HierarchyBuilder_Cte p on d.Dim_CostCentre_ParentBK=p.Dim_CostCentre_SK