WFA on HANA supervisior dimension older configuration overrides new config

[Abed]

Hello,

I have A WFA on HANA configuration issue.

I've been trying to change the configuration for supervisor dimension because even though the build was successful and running in TEST, I was running into errors if I click on supervisor dimension in WFA landing page in prod (despite the process load being successful, it was also failing to sync if I enable RBP). It couldn't be expanded or reported by etc.. The initial dimension was using the default SQL to build the hierarchy. I assumed there is some manager cyclic issue and since this is a new client in SF, their orginal hire data load hire record had NO_MANAGER in manager id and then the goLive records after had proper manager id. I initially assumed it would work regardless because the supervisor hierarchy was being created based on latest record so manager id would be there for all records except CEO and it was working in TEST.

Anyways, I deleted the older supervisor dimension and created new analysis dimension instead called it supervisiorv2 and put in SQL below just as a test to see if that works. I've also tried various SQL that excluded data from dates before certain date or exclude certain events or even creating a dummy mapping for all employees except CEO that have no manager id or simply just selecting the manager id into a dimension etc..

all were successful in process load except I could never expand the supervisor dimension or any manager Id related dimension no matter how simple the SQL is. Note the default SQL is fully functional in TEST instance but not in prod. An example is below SQL is successful when I run initial load process.

SELECT DISTINCT
    J.MANAGER_ID,
    CASE 
        WHEN J.MANAGER_ID = 'NO_MANAGER' THEN 'NO MANAGER'
        ELSE IFNULL(P.FIRST_NAME, '') || ' ' || IFNULL(P.LAST_NAME, '')
    END AS MANAGER_NAME
FROM "[%ODS_DATABASE%]"."EMP_JOB_INFO_T" J
LEFT JOIN "[%ODS_DATABASE%]"."EMP_EMPLOYMENT_INFO" E
    ON J.MANAGER_ID = E.USERS_SYS_ID
LEFT JOIN "[%ODS_DATABASE%]"."EMP_PERSONAL_INFO_T" P
    ON E.PERSON_ID = P.PERSON_ID

But then in edit hierarchies, the newer dimension(i add only one dimension in each run and remove others as tests) its still loading the older hierarchy based SQL into the new dimension. Also, the old structural supervisor dimension seems to have remained in the config cache or something as I still see it in edit hierarchies and  if expand on it I see the proper manager hierarchy that fails to expand in landing page.

Point is, it seems like the older initial SQL supervisor hierarchy is staying no matter how many times I run process initial load even if:

1- I remove supervisor dimension and run it after, it stays as picture below/above

2- if I add new supervisor dimension with different SQL or even different type(structural/analysis), it shows the older SQL hierarchy instead of the new SQL and then fails in landing page or even freezes if I try to edit it in edit hierarchies(if i choose manual option in SQL).

Is there a way to clear the old supervisor dimension config ? it seems to be stuck and any dimension I create based on manager id loads older structural dimension ? I have no errors in initial load processes, but if you would like me to share a load with SQL example above or default SQL hierarchy supervisor, let me know.

Thank you,

[Abed · Accepted solution]

Hello, I understand that, but example sql above still returned the hierarchy, why is it returning a hierarchy when the SQL doesn't define one and Its the only dimension using manager id column.

Also, I deleted the hierarchy supervisor dimension created a new analysis supervisor dimension added the following simple SQL as a test and ran initial process and sync artifact afterwards successfully.

SELECT DISTINCT
    MANAGER_ID
FROM "[%ODS_DATABASE%]"."EMP_JOB_INFO_T" 

Added dimension options( note manually maintained SQL)

Yet, in edit Hierarchies, I still see the manager ids in supervisor dimension bundled in a hierarchy like structure, is this normal behavior ? See image below returned as result of SQL above ( no other manager ID dimensions exist in the system)

Thank you!

Edit: to add to above, I deleted the above dimension and created supervisor analysis dimension using manager ID and NONE as the query option with no SQL whatsoever, ran initial process and sync afterwards  on analysis, measures and structural dimensions and I still see the hierarchy above as the result.

[YSE-1 · Accepted solution]

@Abed - can you provide the DC and instance ID for this? I'll see if I have access to check the configuration.

[YSE-1 · Accepted solution]

Hi @Abed - I think that structure you see under the Unmapped category for Supervisor dimension is coming from initial build when you initially created a generated dimension. When you change to manually generated, it keeps whatever already show up under Unmapped category as is. What you can do is delete everything from Unmapped category in Dimension Editor and re-run the initial load, see what values coming out under the Unmapped category after that.

[Abed · Accepted solution]

@YSE-1I deleted the unmapped values however if i click save afterwards in edit hierarchies, The UI becomes stuck in a loading/saving loop for a long time(10+ minutes.. and exiting afterwards makes WFA on HANA data Factory inaccessible so I have to close browser and wait a bit) with nothing happening. If I log out and back in the data comes back so deleting and trying to save afterwards so I can rerun initial build does not work since its not applying the delete and saving. That and the result of Manager ID with none Dimension should have returned 100s of single ids, however it just has the old hierarchy and like 7 ids in unmapped so its not even returning correct result for current dimension + result from old query. 

Edit: I have deleted and waited forever for it to try to finish, finally I got a popup (The current function cannot be completed because of a network issue). I tried again and same result. I'm unable to remove that hierachy from unmapped.

[YSE-1 · Accepted solution]

Hi @Abed - I think you need to delete in small batches. Because there are many supervisor nodes there, it is going to take time to delete everything. Another thing that you can do is change to be generated dimension again, but just display a single dummy value and use something like employee status from job info to map to the dimension. This is just to clear up those under Unmapped category. After that you can change back to manually generated for supervisor and you can easily delete the dummy values later from unmapped category as there won't be that many.

[Abed · Accepted solution]

Hi @YSE-1, thank you for the reply, see answer with pictures above.

[Abed]

HI @YSE-1, I edited the supervisor dimension and changed it to use employment status as mentioned and ran initial load. What happened is that unmapped now contains employee status + old hierarchy dimension + Ids from the recent select distinct. (update: missed the option of changing it to generated, that did delete the records)

it didn't overwrite anything, instead its appending. they now all exist in unmapped. I tried going into the hierarchy nodes, went 3 levels and deleted one node at leaf level and clicked save. same network issue. Also, If i choose to ignore the unmapped and move one id from unmapped to normal results and try to save it doesn't work either. 

If i go to landing page and filter by supervisor dimension, it errors out.

Note, I have attempted creating many custom supervisor dimensions before thinking that maybe the default supervisor dimension got corrupted somehow. Anything I connect to Manager ID ends up with that hierarchy whatever SQL is used.

I will try to delete one single ID at the absolute bottom of a node and save and see if that works. Update: Deleting one by one didn't work either, network error for deleting a single leaf record.

Update 2: I changed the dimension to use company ID and set as generated since I noticed that as an option to try in your message and it seems to have removed them. I'm now changing it back to use select distinct manager ID as a test and to use manager id to connect to and see what happens. Hopefully, that works and only fetches list of ids without the hierarchy, Thank you for all the help.

[Abed]

Ok, The hierachy has finally been removed if i change the option to generated and replace with a simple dimension -> load -> replace back to what is needed -> load again. Thank you.

[YSE-1]

Hi @Abed - when you delete the supervisor hierarchy dimension, it will stay in the "Branches" table and cannot be clear out.  However, it should not affect the site if you ran WFA artifact sync after deleting the hierarchy dimension.  This hierarchy should be gone from the WFA site.

[Abed]

Hello, please see my reply in answers as I couldn't add images in comments. Thank you!