Categories
How to query PVO in Oracle BI Publisher Data Model
I need to query PVO data in BI Publisher, here's the PVO name:
HcmTopModelAnalyticsGlobalAM.HcmElementSetupAM.ElementEntryValue
Any screenshots, ideas, hints?
Thanks.
Best Answer
-
@Juan Jesus - OTBI team can assist you with the same. Please create a request in the below forum seeking the information on the query formation for the PVOs you are having issues with.
1
Answers
-
You can try to use BICC Data Lineage and query off the table directly.
Any specific reason why you need to query the PVO instead of the table directly (Assuming it is available in the lineage) ?
0 -
Thank you Krishna for the response.
Yes, we have a data loss on the way from HCM base tables to FAW and trying to query PVO to find out if it returns all data we need.
0 -
If you just want to query the PVO and see the data, then use BICC functionality to create a job and extract the data into Excel to review the data.
0 -
Thanks Krishna.
I believe that creating an extract is much heavier than select * from PVO in OTBI/BIP
0 -
Hi , you can use this query by going to BI Adminstartion , issue SQL :
select * from EXTERNAL('ADF', '"oracle.apps.fscm.model.analytics.applicationModule.FscmTopModelAM_FscmTopModelAMLocal"."Connection Pool"').'FscmTopModelAM.FscmAnalyticsExtensibilityAM.Benefits_Realization_c'
or
select_physical * from "FscmTopModelAM.FscmAnalyticsExtensibilityAM.Benefits_Realization_c"
with replacing FscmTopModelAM.FscmAnalyticsExtensibilityAM.Benefits_Realization_c with your PVO name .
Resources : https://www.ateam-oracle.com/post/bi-cloud-connector-custom-object-data-extraction , https://www.vaamg.com/post/oracle-fusion-pvos
0 -
Thanks a lot Mohamed!
select_physical * from...PVO...
works great.
0 -
Sorry, please, can anyone elaborate to get this query PVO running?
oracle.apps.financials.payables.shared.publicView.ReportsBankAccountPVO
Thanks
Juan
0 -
@Juan Jesus - In a data model you can create a dataset of type SQL with a database
connection to the application databases. For example, application source
ApplicationDB_FSCMTry using the below query as SQL to query the pvo:
select_physical * from "oracle.apps.financials.payables.shared.publicView.ReportsBankAccountPVO"
0 -
Thanks Sumanth,
I get error.
Can you elaborate? some screenshot?
Juan
0 -
@Juan Jesus - Please try using the below syntax:
Example: select *
from
EXTERNAL('ADF','"oracle.apps.fscm.model.analytics.applicationModule.FscmTopModelAM_FscmTopMo
delAMLocal"."Connection Pool"').'FscmTopModelAM.RcvReceiptsAM.ReceivingReceiptTransactionPVOFollowing explains the anatomy of the ADF from clause:
EXTERNAL('ADF','”<PHYSICAL_DB_NAME_FOR_FUSION_PILLAR>.Connection
Pool”).'<FUSION_VO_OBJECT_NAME>'
The ”< PHYSICAL_DB_NAME_FOR_FUSION_PILLAR >” is the DB name in the RPD will vary depending on
the pillar.0
