I am working on Oracle Fusion Data Intelligence (FDI) HCM Analytics and need guidance on how to expose multiple cost allocation rows per employee assignment in the HCM - Workforce Core subject area.
Background:
We have a custom EIT (Extra Information Type) called 'IPSOS ASG Cost Allocation' stored in PER_ASSIGNMENT_EXTRA_INFO_M. Each assignment can have multiple cost allocation rows - for example, 80% allocated to one cost center (Primary Flag = No) and 20% to another (Primary Flag = Yes).
We created a Dataset Data Augmentation (type: Dataset, as recommended by Oracle) that extracts these rows into a custom table: DW_BIP_X_DIM_WORKER_ASSIGNMENT_COST_EXT with columns: ASSIGNMENT_ID, PERSON_ID, COST_ALLOCATION_PERCENTAGE, LEGAL_ENTITY, ALLOCATED_CENTER, PRIMARY_FLAG.
Current Implementation:
We configured this as Extend Dimension in the Semantic Model Extension, joining on ASSIGNMENT_ID + PERSON_ID to Dim - Assignment Details with Left Outer join. The query runs successfully but returns only 1 row per assignment (the 20% allocation), losing the second row (80% allocation). This is confirmed in the OBIS log showing Rows returned to Client: 1.
We also identified in the OBIS physical SQL that FDI automatically applies ROW_NUMBER() = 1 on the Extend Dimension result, which explains why only one row per assignment is returned regardless of how many rows exist in the augmentation table.
Question:
- Is it possible to expose all allocation rows per assignment (multiple rows) using Extend Dimension, or is this fundamentally limited to 1:1 relationships?
- Should we switch to Add Fact in the Semantic Model Extension instead? If yes, can we keep the Data Augmentation type as Dataset or do we need to recreate it as Fact type?
- What is the correct join configuration to connect the custom fact to Dim - Assignment Details and Dim - Worker in HCM - Workforce Core?
- Is there any alternative approach (e.g. creating a view in OAX_USER schema) that would allow multiple rows without changing to Add Fact?
