WFA on HANA dimension labels

[abalasescu2]

Hi @YSE-1 

I have an issue with a few custom pretty basic dimensions - 1 level flat org related like legal entity, dep etc.

I have 4 of those, all with quite a few results that come as "??" which would mean they do not have labels.

But strangely enough they do, I have checked them in manage data as well as canvas reporting and I can find both en_GB and en_US descriptions.

Here is the SQL:

select DISTINCT IFNULL(CAST(JOB_INFO."DEPARTMENT" AS nvarchar(50)), '??') AS Level1Id, 

IFNULL(CAST(DEPARTMENT_LABEL."LABEL" AS nvarchar(50)), '??') AS Level1Name

from ( SELECT DISTINCT EMP_JOB_INFO_T."DEPARTMENT" DEPARTMENT FROM "[%ODS_DATABASE%]"."EMP_JOB_INFO_T" ) JOB_INFO

LEFT OUTER JOIN "[%ODS_DATABASE%]"."MDF_SPLIT_GENERIC_OBJECT_T_Department" DEPARTMENT_CODE ON JOB_INFO."DEPARTMENT" = DEPARTMENT_CODE."INTERNAL_ID" AND CAST(DEPARTMENT_CODE.EFFECTIVE_END_DATE AS date) = '9999-12-31' 

LEFT OUTER JOIN "[%ODS_DATABASE%]"."CONFIG_GENERIC_OBJECT_T_GOLocalizedData" DEPARTMENT_LABEL ON DEPARTMENT_CODE."EXTERNAL_NAME" = DEPARTMENT_LABEL."INTERNAL_ID" AND DEPARTMENT_CODE.ROW_ID = DEPARTMENT_LABEL.PARENT_ROW_ID AND DEPARTMENT_LABEL."LOCALE" = 'en_GB' 

ORDER BY Level1Name

Here is a screenshot of my results for one of the dimensions:

Attaching here the IDs of business unit MDF vs business unit job info - both have the internal ID 17, as per the build as well - the issue is with the label...

Kind regards,

Alex

[marianbuxar]

Hi Alex,

You need distinguish if '??' is code or description. For your Department dimension there is only '??' as code which is used when there is no Department assignement. But I see two nodes of '??' for Legal Entity. One is for code '??' and second for code of '17'. It seems you need check your query for Legal Entity hierarchy: It doens't produce any result for INTERNAL_ID of '17'.

Hope this will help you.

Thanks & Regards,

Marian

[abalasescu2]

Hi Marian, this is all clear for me - I get multiple of "??" but all of them have unique individual IDs in the backend. My issue is why would the labels not come through. They do have labels in the system, I also built one dimension with both en_US and en_GB just to check - still the same result. So the missing labels are my issue basically. Btw for department I have updated the code to "No Department" instead of "??" and you can see there are 4 of those..

[marianbuxar]

Hi,

but see the following: if I added to your query condition (WHERE BUSINESS_UNIT_CODE.INTERNAL_ID = '17') it returned 0 records. But in case I added condition (WHERE JOB_INFO.BUSINESS_UNIT = '17') this will return 1 row. This means there is no Business Unit in the BUSINESS_UNIT MDF meet the conditions. My internal feeling is that questionable condition is where effective end date should be equals to '9999-12-31'. Please check.

Best Regards,

Marian

[abalasescu2]

Hi Marian, I am not sure this is relevant here. 1 - I am missing the labels, the IDs pull through just fine and I can also validate the HC number is correctly distributed to the unlabeled nodes. 2 - the effective end date condition is from the SQL examples from the old group and as far as I know they are used to pull the correct current record if there are historical records in the mdf object 3 - I don t know why your line (WHERE BUSINESS_UNIT_CODE.INTERNAL_ID = '17' would return 0 as I have checked and the IDs are properly matched - attaching screenshots