I need to query PVO data in BI Publisher, here's the PVO name:
HcmTopModelAnalyticsGlobalAM.HcmElementSetupAM.ElementEntryValue
Any screenshots, ideas, hints?
Thanks.
@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.
https://community.oracle.com/products/oracleanalytics/categories/otbi
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) ?
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.
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.
Thanks Krishna.
I believe that creating an extract is much heavier than select * from PVO in OTBI/BIP
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
Thanks a lot Mohamed!
select_physical * from...PVO...
works great.
Sorry, please, can anyone elaborate to get this query PVO running?
oracle.apps.financials.payables.shared.publicView.ReportsBankAccountPVO
Thanks
Juan
@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_FSCM
Try using the below query as SQL to query the pvo:
select_physical * from "oracle.apps.financials.payables.shared.publicView.ReportsBankAccountPVO"
Thanks Sumanth,
I get error.
Can you elaborate? some screenshot?
@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.ReceivingReceiptTransactionPVO
Following 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.
