Oracle Fusion Data Intelligence (FDI) allows us to enhance the out of the box offering by bringing in additional data from the source Fusion Cloud Applications system. From the available data stores, called PVOs, we can select additional columns and create an augmentated dataset which can be used in extraction pipelines. With this technique, additional data can be regularly loaded into custom tables that FDI has created for us in the Autonomous Warehouse and used for analysis.
What is a PVO? It is a “Public View Object” that is essentially a view over a selection of related Oracle Fusion base tables and a useful mechanism for exporting data. We will drop into some more detail about PVOs in this article.
Note we are looking at FDI R24.4 and beyond and as such the SME Options for Data Augmentation is NOT the recommended approach any more. The DataSet augmentation is now the recommended approach and thus we will be focusing on that.
Start with the Admin Console for FDI and select Data Configuration
Within this menu, select Data Augmentation
Here we get to see all of the custom data augmentations. I already have one showing and we shall create another using the workflow.
Start by selecting Augmentation from the Create drop down
The first part of the tramline workflow is displayed so we can see the steps that we will be taking. We will select the source view object, then the columns that we require.
The first step is to select the augmentation type. We want to bring in additional data that is not brought in by default so we will select Supplemental Data.
Now we are required to choose a “Pillar” – and we are given a selection of those that are defined on our FDI system. In this case I will choose Human Capital Management
For the source table, I could have used a data integration tool to bring data intio the warehouse from an external source (including any Fusion pillar) but I want to use a System Provided source table, namely one of the 100’s of view objects that are provided by Oracle. As we mentioned earlier, the view objects (aka PVO’s) cover a huge amount of data and so there is a degree of effort required to identify which PVO to use
When we come to which source table to select, the manual says
“You can supplement the data in your reports by using datasets that you create with specific columns from various data stores (Business Intelligence view objects) of the Oracle Fusion Cloud Applications data sources.
Refer to Review Database Lineage Mappings and View Objects while deciding which view objects to use in the data augmentations”
The drop down gives us a list of all the view objects available – and there are a lot of them. So by referring to the mappings link above, we can make a decision as to which view object would be most suitable for our needs. The drop down allows for some filtering as if we can put in “HCM” and see everything with “HCM” in the name of PVO.
Just as an example ( as we will come back to this ) we select the PartySiteExtractPVO.
I think that this selection experience has the potential for enhancement. I mention this as I have some experience of doing that as within a product we have created called COSMOS (https://www.wegobeyond.co.uk/beyond-cosmos), one aspect of which is an accelerator to create a data platform foundation in OCI and so we have a couple of options for selecting PVOs.
Firstly, we can use GenAI to provide a similarity search, so we can type in something like “People Related” and we get PVOs returned in order of how closely the description of the PVO is related to the search term. In the example below where we are looking at Procurement, so we see PVOs related to “Teams” for example. Employing a Large Language Model (LLM) in the process is a relatively new but interesting approach as we don’t have to search for exact matches or keywords, but rather for results that are more closely associated with the term we are searching for.
With COSMOS on an Oracle 23ai database we can easily embedd an LLM into the database itself, vectorize the data and make things super simple for the user.
We can also provide a faceted search approach, so in the example below we can select a category of “Financials” then further sub category of “General Ledger” and just by checking these checkboxes we then get a list of all the PVO’s categorised like that with full descriptions.
It’s actually cool to combine the two approaches, so use facetied search to narrow down the target candidates and then apply a vectorized search.
With that aside, let’s return back to FDI.
Moving to the next stage of the process we can select the additional columns that we require. We notice that 3 columns are pre-selected, those being the primary key and columns to determine initial and incremental extract dates.
We can keep these, and then also add in any additional columns we want. We will select Addressee. We are just extracting data, so we can run this process through to see what data we would get back.
Actually, technically we can do this in advance. We can go into the Fusion source system and in OTBI, select Administration and then Issue SQL and execute the query to see the PVO data like this example below where I execute a physical query to select all the columns from the cost centre hierarchy PVO, one I selected at random.
I restricted to the “top N” as there could be a lot of records and I just want a sample to look at. Pressing Issue SQL gives me what I want.
Returning our focus back to FDI, and the PVO we are working with, we can change some details about the selected columns (sizes, keys, etc).
On the last step, the source table is carried forward ( being the name of the PVO) and we give our augmentation a name and description. We also set the table suffix, this is important as FDI gives us the name of the table prefix (DW_FA_X_) and our suffix is appended to get a new table name. Note that you can’t duplicate table names as this is checked and an error will be raised as we don’t want to overwrite data from other augmentations.
We also schedule the dataflow run – and i’ll set this to immediate so it runs now.
Completing the wizard we see the augmentation has been created and the pipeline status has been set to scheduled. As we set it to Immediate that means it should be scheduled for now.
Navigating across to the request history, we can see that the DW_FA_X_ADD entity is In Progress and performing a full load.
If we navigate tot he Audit Log I can see the augmentation in progress showing me more detail of what’s happening.
Once the load completes, we get the following Activation Complete message.
Part of the activation is to create and then populate the new table that FDI has created. We can describe it to see the columns.
The W$_INSERT_DT is a timestamp of the time a record was inserted into the table and W$_UPDATE_DT column is the timestamp indicating when a record was updated in the table.
We can use SQL Developer to query the content. When I do so I see that the column I selected as my augmentation is NULL. So my lesson here is :
(1) Did I actually research properly and select the correct PVO?
(2) I should have looked in my Fusion system using the query select_physical technique to ensure that it held the data that I was expecting.
I can quickly run through the process again and this time select a different PVO – and this time I am looking at HCM Action Reasons.
I am greeted with a lot of available columns, where I know the ones selected automatically make up the primary and incremental keys (note not all PVOs allow for incremental refresh).
Let’s give our new augmentation the same Table Suffix just to see what happens:
As anticipated, it is checked and we’re told to select a unique suffix. I will call it ADD_ACTION_REASONS
I do that, follow the rest of the steps and let the pipeline run.
Now when I query the new table DW_FA_X_ADD_ACTION_REASONS I see all the data coming through in the columns that I selected.
We now have additional data that we can use in a number of ways – creating new subject areas or enhancing existing ones – with additional data that we have brought into the supplied Autonomous Warehouse.