Hi Team,
I am working in Fusion Data Intelligence (FDI) using the CX - Opportunity Revenue Line subject area and need some clarification on Sales Account Hierarchy behavior.
My requirement is to display the following for each Opportunity:
- Opportunity ID
- Sold-to Customer Number
- Top Registry ID
- Parent Registry ID (Level 8)
- Child Registry ID (Level 6)
I am using the following attributes from:
Sales Account > Sales Account Hierarchy
- Top Level Sales Account Party Identifier
- Level 8 Sales Account Party Identifier
- Level 6 Sales Account Party Identifier
From the generated logical SQL, I can see the hierarchy table being used is:
DW_CXS_SALES_ACCOUNT_CF_DN_DH
and the join appears to be:
T3962.BASE_LEVEL_ACCOUNT_PARTY_ID =
T5810.CUSTOMER_PARTY_ID
Example 1:
When I filter:
Level 6 Sales Account Party Identifier = 300000372652499
I get the following result:
Sold-to Customer Number = 1278969
Top Level Sales Account Party Identifier = 300000004264760
Level 8 Sales Account Party Identifier = 300000372652499
Level 6 Sales Account Party Identifier = 300000372652499
Example 2:
When I filter:
Sold-to Customer Number = 9371
I get:
Top Level Sales Account Party Identifier = 300000004264760
Level 8 Sales Account Party Identifier = 300000004264760
Level 6 Sales Account Party Identifier = 300000004264760
Business Requirement:
The Sold-to Customer Number may represent different levels within the customer hierarchy (Top-Level Account, Parent Account, or Child Account).
I need to show, for each Opportunity:
- Top Registry ID
- Parent Registry ID
- Child Registry ID
regardless of which hierarchy level the Sold-to Customer belongs to.
My questions are:
- Are the Sales Account Hierarchy attributes (Top Level, Level 8, Level 6) always resolved relative to the current Base Level Account Party ID?
- Is it expected behavior that when the Sold-to Customer corresponds to a Top-Level Account, all three hierarchy attributes return the same value?
Example:
Top Level Party ID = 300000004264760
Level 8 Party ID = 300000004264760
Level 6 Party ID = 300000004264760
- If the Sold-to Customer can exist at Top, Parent, or Child levels, what is the recommended FDI approach to consistently derive:
- Top Registry ID
- Parent Registry ID
- Child Registry ID
for every Opportunity?
- Is the standard CX - Opportunity Revenue Line subject area sufficient for this requirement, or is a custom dataset/semantic model required?
- Is there a hierarchy bridge table or recommended dimension in FDI that provides a normalized mapping such as:
Registry ID
-> Top Registry ID
-> Parent Registry ID
-> Child Registry ID
for all Sales Account hierarchy levels?
Any guidance on the intended behavior of Sales Account Hierarchy in FDI would be greatly appreciated.
