Oracle Analytics and AI

Victor H

Is it possible to have fragmented data sources for FDI metrics?

We want data to come from FDI for 2022 onwards and use another source for data before 2022

This feature has been part of OBIEE for a long time but we have not been able to find something similar in FDI

• Semantic Model Extensions do not allow data fragmentation
• The FDI semantic model download used for external application merge does not allow you to change fact tables, it is not possible to add/change the logical table sources for fragmentation

Even if we create a custom RPD with the external data source. There is no way to include this table as an additional data source for the existing logical fact table. It would be considered a different metric /subject area

Our goal is to have the user create a simple report of HCM Gains and losses and have events pre-2022 come from a different data source.

With the external application merge it seems that the only option would be to add a separate subject area with the pre 2022 hires/terminations and have conformed dimensions with the out of the box Gains and losses subject area. But this is not what we are looking for, we do not want separate subject areas.

Has anyone found a workaround for this?

Sushanta Chakraborty-Oracle

If I understand your situation .. you want pre-2022 event rows coming from a different source (let's say EBS), while post-2022 event rows coming from the source that FDI currently uses (let's say Fusion HCM cloud).

If so, here is one (convoluted) option you can try.

1. Understand the fact table design/grain etc. of the FDI Gains & Losses fact table. You need to be thorough about this.
2. Create a custom table X_GAINS_N_LOSSES in the ADW custom schema with the same structure as the FDI fact.
3. Write a custom ETL (any tool) to bring in data from EBS and transform it in the right structure and grain as you have created. It might involve several intermediate tables. Basically, you have to own the pre-2022 data movement and its incrementals, etc..
4. Create a view union-ing your final fact and FDI's fact. This is going to be your final LTS.
5. Now you can use Semantic modeler and create a new custom logical fact feeding it with the view that we just created, recreate all the measures using same logic as FDI, bring in all the required conformed dimensions and so on, build a new custom subject area.
6. Ignore FDI's Gains and Losses subject area, and use your own.

There can be complications, but should work in theory. Again, if I misunderstood your issues, apologies. But I think your main concern is that some older records happens to not have been migrated over to the new HCM system that FDI is sourcing from. This happens all the time. Customers sometimes choose to migrate data to new systems for the last 2 or 3 years. Not realizing that there can be some analysis/measures that go across time. Gains and Losses area is notorious about this. Regorgs, Manager hierarchy changes before 2022, and all kinds of this affect gains and losses. Hope I got it right.