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

[abalasescu2]

Hi @YSE-1 I have checked with new initial build but still doesn t work.

I have also moved configuration to production and can replicate the same exact issue.

Not really sure what to check further. Any ideas please?

Kind regards,

Alex

[YSE-1]

Hi @abalasescu2 - can you please provide the datacenter and instance ID so I can have a look at the configuration? Thanks.

[abalasescu2]

DC33, company ID S001955610P - please keep in mind this one has EUDP flag on...

[YSE-1]

Hi @abalasescu2 - I think it is not only the label that has issue but it can also issue with the Department not listed or available in the MDF object.  Both of these will output ?? as a result.  Have you checked if the department in the Job Info is available or not in the MDF object?

[abalasescu2]

I am not following what you mean by the Department not listed or available in the MDF object.

[abalasescu2]

I have checked in Manage Data and it is there, translations also available

[YSE-1]

@abalasescu2 - Not sure what causing the issue is then if the Department code is available in MDF object and translation is available too. Maybe try running initial load to refresh the data.

[abalasescu2]

tried that already, still nothing..