Search code examples
ssastranslationparenthierarchy

SSAS Translation of the multiple parent hierarchy

in the last days i build an accounting multiple parent hierarchy for my project Image 1. The source view is attached hier Image 2. We have four dimensions in our UDM: the only one that deserves more attention is the one based on Dim_CostCategoryHierarchies. First, we create a named view called vDim_CostCategoryHierarchies , which we will use as a source for the dimension.

SELECT        
    distinct
    h.[PK_CostCenterHierarchy], 
    h.[FK_CostCenter], 
    h.[FK_CostCenterHierarchyParent],
    h.Hierarchy,

COALESCE (h.[CostCategoryOverrideeng], b.accountNo) AS AccountNo, 
COALESCE (h.[CostCategoryOverridedeu], b.AccountNamedeu) AS AccountNameDeu,
COALESCE (h.[CostCategoryOverrideEng], b.AccountNameEng) AS AccountNameEng

FROM            
    [dbo].[Dim_CostCategoryHierarchies] AS h 
    left outer join  dbo.DimCostCategory AS b ON h.[FK_CostCenter] = b.pk_cost

This is necessary because we have to define the name of all the nodes and leaves for all the hierarchies. The table Dim_CostCategoryHierarchies has a screenshot hier Image 3.

I created in SSAS a translation for vDimCostCategoryHierarchies in wich AccountNo it's related for translation to AccountNameEng Image 4, but when I browse the dimension and choos English , the hierarchy remains unchanged as you can see in the image attached hier. Image 5

Can anyone be so kindly do give an ideea of how to deal with this translation in the situation exposed?

Solution

  • You need to look at the definition of the dimension, and to change the caption to an English word.

    Instead of "Kostenkategorie Hierarchy" (I hope I spelled that correctly!) write the correct hierarchy.

    and give English Names (as you did in the code above) in the column "Hierarchy". Because it gives you the words from that field, and it doesn't know how to translate them to English.