Let's see how to create a Value Set in Oracle Fusion/ERP Cloud.
- Go to Setup and Maintenance -
- Set the desired business area e.g. Financials -
- Search for task Manage%Value%Set% and locate it as below -
- Open the task
- Click on + sign to create a new Value Set -
- Click on Manage Values to maintain Independent Value Set values
- Click on + sign to create new values -
- Click on + sign to create new values -
So you want to customize a seeded report, say add a new column, but when you try to edit the query, it shows a pop-up asking you to enter values for Lexical References for all lexical parameters in the seeded query.
Now as we don't know the values for these lexical references and we cannot eliminate these from seeded query otherwise the report will stop working, we are not able to proceed with our custom changes.
It usually looks something like this:
Here's the solution -
Oracle recommends to perform the data model customization in the following manner:
- Navigate to xmlpserver and download the data model in the custom folder as xdmz file
- Unzip this file and modify _datamodel.xdm using text editor
- In case you are adding a new column, make sure to create a new XML tag for this new columns. The new column won't appear in the Data Model (in front-end) without this step.
- Zip it back and rename it back to xdmz
- Upload xdmz back to the custom folder
Note: The changes to the data model should be done gradually and by iterating the process until the desired effect is achieved.
Using this approach, you will avoid getting the pop-ups requesting values for lexical parameter when the customization is done through the front-end in traditional way.
So, you have defined a bunch of DFFs but wondering why they don't appear in the corresponding subject areas when you build an OTBI Analysis ?
Well, let's see how you can enable them for BI analysis purposes and use in OTBI.
Below are the steps to enable a DFF for BI / OTBI Analysis/Reporting
- Navigate to Setup and Maintenance
- Change Functional area to the desired one.e.g. Financials
- Search for something like Manage%<module>%Flexfields
e.g. Manage%Receivables%Flex%
- Open the Task
Enter the desired Flexfield Code (e.g. RA_CUSTOMER_TRX%)
Click Search
- Select the desired DFF and click Edit
- Select the Segment and click Edit
- Scroll all the way down and Enable the option ‘BI Enabled’
- Save and Close
Now, navigate to Scheduled Processes
Schedule New Process –
Import Oracle Fusion Data Extensions for Transactional Business Intelligence
Run it for desired functional area (e.g. Financial Reporting)
Let the program finish. (It may take some time to complete).
If Process completes in Warning -
As per Doc ID 1616574.1, even if the process ends in a Warning, the Flexfield import will complete therefore the functionality of the job is not adversely affected.
Once process is finished -
- Navigate to Catalog and open the desired subject area
- You should see the DFF fields are now available in the subject area –
In my previous post, I explained how to retrieve database query for BICC VO in Oracle Fusion Cloud
Sometimes this method won't work as not all VOs are designed the same way.
There's a chance that you'll get below error:
ERROR
-----------------------
. [nQSError: 27047] Nonexistent table
This is expected behavior as the old VO's are available in the RPD repository (metadata of the BI Server) and hence SELECT_PHYSICAL works fine.
The VO which used above is trying to access in this case is a BICC VO and these BICC PVO names end with the string (.ExtractPVO). These BICC VO's are not part of RPD and directly based on ADF. So SELECT_PHYSICAL doesn't work.
So to overcome this error, you need to use the syntax as below and you should get the results without any error:
select * from
EXTERNAL('ADF','"oracle.apps.fscm.model.analytics.applicationModule.FscmTopModelAM_FscmTopModelAMLocal"."Connection Pool"').'<VO Name>' as t
FETCH FIRST 100 ROWS ONLY
e.g.
select * from
EXTERNAL('ADF','"oracle.apps.fscm.model.analytics.applicationModule.FscmTopModelAM_FscmTopModelAMLocal"."Connection Pool"').'FscmTopModelAM.FinExtractAM.ArBiccExtractAM.ReceivableActivityExtractPVO' as t
FETCH FIRST 100 ROWS ONLY
So, you are wondering how to retrieve the actual database query behind any VO in BI Cloud Connector (BICC) ? Here's how we can do it.
- Log in to BI Analytics as user with BI Administration privilege.
Navigation path: BI domain → Analytics → Administration
