Oracle Fusion AI Data Platform Forum

Home› Oracle Analytics and AI› Oracle Analytics and AI Forums› Oracle Fusion AI Data Platform Forum

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Reporting Either Project Org Hierarchy or Task Org Hierarchy Within One Set of Dashboard Filters

Accepted answer
30
Views
10
Comments
User_WJ43I
User_WJ43I Rank 3 - Community Apprentice
in Oracle Fusion AI Data Platform Forum

I'm apologizing ahead of time for the length of this post. I'm sorry.

I am currently working on a Fusion ERP/HCM/FDI/OAC implementation project. We are not live yet but will be live soon. I am trying to build an OAC workbook visualization that shows Backlog Amount (Project Funding - Project Revenue). The issue I am having is that when we converted funding and revenue amounts for active projects from our legacy system, we loaded both amounts at the Project level. However, moving forward, post go-live, all new projects will be required to load both funding and revenue at the task level.

For this dashboard, the business wants to see backlog by Task Org Hierarchy levels 1-4. The problem is that for our converted active projects, there is no task on the funding or revenue lines due to the funding and revenue being loaded at the project level only. To solve for this problem, I wrote a view that calculates the org hierarchy values using a case statement that checks to see if the task on the funding row is -99999. If it is -99999 then it uses the Project Org hierarchy, if the task is not -99999 then it uses the Task Org Hierarchy. The issue is that when I join this view to the Funding fact, I have to join it by both the task ID and the project ID. When I do this, in my OAC workbook, the revenue fact is no longer outer joined to the Funding fact. So my funding amounts are correct, but the full revenue amount shows against each hierarchy level. I even wrote the same view for the revenue fact and joined it as well. It still will not outer join.

Has anyone else come across this requirement before? If so, how did you solve for it? Fusion allows you to load funding/revenue at either the task or project level, so I'm hoping our scenario/ask is not unique.

Any advice or help would be greatly appreciated.

Tagged:

Best Answer

  • RVohra
    RVohra Mod
    Answer ✓

    @User_WJ43I , If I understood the question, the underlying issue is that your revenue fact at project level is being joined to task-level hierarchies, and when you join both facts using project_id+task_id, project-level revenue rows don’t have tasks to join, so the outer join fails.

    For this implementation you can try following and see if it works

    1. Create synthetic task IDs for project-level data (-99999 → project_id * 1000).
    2. Adjust your hierarchy view to map this synthetic task to project-level org hierarchy.
    3. Join funding & revenue facts by project + task surrogate.
    4. This ensures:
      • Outer joins work
      • Backlog is correct
      • Reporting logic stays consistent for future task-level projects

Answers

  • Daniel Ryan
    Daniel Ryan Rank 3 - Community Apprentice

    Have you considered creating a new hierarchy that concatenates the child org and task at the detail level? There is a blog from Rohan Lobo that shows how to do this for cost centres. Maybe something similar could work in this area?

    Create a Custom Alternate Presentation Hierarchy in Oracle Fusion Data Intelligence | analytics

  • User_WJ43I
    User_WJ43I Rank 3 - Community Apprentice

    The issue isn't with creating the custom hierarchy. I can do that. The issue is that for the converted projects, their funding/revenue rows have task -99999, so in order for me to join the custom hierarchy to the funding/revenue rows, I have to join but both the task ID and the project ID. When I do that, OAC no longer allows to outer join funding and revenue. I cannot just join my hierarchy to task or only to project. I have to join it to both in order for the results to be accurate.

  • User_WJ43I
    User_WJ43I Rank 3 - Community Apprentice

    This sounds like it would work. I'm going to give that a try and see how it goes.

  • RVohra
    RVohra Mod

    @User_WJ43I, sure, let us know how it goes. Also, as per community guidelines, please mark the Answer as Accepted Answer so that it can be easier for community members to find replies that might help them in a similar situation. Thanks

  • User_WJ43I
    User_WJ43I Rank 3 - Community Apprentice

    @RVohra In your proposed option, am I joining the custom hierarchy dimension to the funding and revenue fact by both project_id and task_id?

  • RVohra
    RVohra Mod

    Yes, hope it works for you.

  • User_WJ43I
    User_WJ43I Rank 3 - Community Apprentice

    @RVohra I created the synthetic task ID, but it cannot join to the revenue table because the synthetic task ID isn't found on the revenue table. It's only present in my custom dimension. Am I misunderstanding something?

  • User_WJ43I
    User_WJ43I Rank 3 - Community Apprentice

    @RVohra I tried another version of what you recommended, and it worked. Your tip was very helpful. Thank you so much!!

  • RVohra
    RVohra Mod

    @User_WJ43I , I am glad to see it worked for you. Appreciate if we can document the changes here, it will definitely help someone in the future. Thanks