Organization Hierarchy is missing in the FAW OOTB DW table OAX$OAC.DW_ORGANIZATION_CF_DH

Question

Hi,

I'm facing a really critical issue :

The organization hierarchy table does not have the values for certain organizations and this is impacting the headcounts in my reports. The sample queries and data screenshots are as follows

select * from OAX$OAC.DW_ORGANIZATION_CF_DH where ORGANIZATION_ID IN (300000041860168,300000041860281);

select * from DW_INTERNAL_ORG_D_TL where

ORGANIZATION_ID IN (300000041860168,300000041860281);

If I understood well, both the Hierarchy Table and Lookup tables are loaded as part of the FAW pipelines. The code and value are loaded in the lookup table (or) detailed table  DW_INTERNAL_ORG_D_TL to give the name of the organization. But the core driving table is DW_ORGANIZATION_CF_DH  which holds the hierarchy structure. This still does not have the department value in this…

I am missing almost 180 people in my report.

Can someone help me with that please ? I also raised an SR…

Thank you,

Regards,

Lauriane

Ashish-Oracle · Accepted Answer

@Lauriane Massin Whitaker DW_ORGANIZATION_CF_DH is sourced from the Fusion source table PER_ORG_TREE_NODE_CF. Can you please check in Fusion if this table has entries for the mentioned ORGANIZATION_IDs?

If they do not have these entries, it means the column flattening has not been done in the Fusion side. In that case, make sure to run the column flattening process in Fusion to populate this table PER_ORG_TREE_NODE_CF.

Refer: Flatten and Publish Hierarchies

https://docs.oracle.com/en/cloud/saas/analytics/24r2/faiae/chapter-introduction.html#GUID-6D35F707-DE8D-481A-AA28-4AEE85C8A1B6

In case the column flattening has been done, Fusion table is populated and you still do not see the right data in FDI, I would suggest you to go for a Reset of the functional area and have the cause investigated over the SR you have raised.