nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle AI and Analytics

Child Item

How to query PVO in Oracle BI Publisher Data Model

Valeriy Gorbunov

I need to query PVO data in BI Publisher, here's the PVO name:

HcmTopModelAnalyticsGlobalAM.HcmElementSetupAM.ElementEntryValue

Any screenshots, ideas, hints?

Thanks.

Find more posts tagged with

Accepted answers

Sumanth V -Oracle

@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

All comments

Krishna Prasad Kotti-Oracle

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) ?

Valeriy Gorbunov

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.

Krishna Prasad Kotti-Oracle

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.

Valeriy Gorbunov

Thanks Krishna.

I believe that creating an extract is much heavier than select * from PVO in OTBI/BIP

Mohamed Faramawy dev.-Oracle

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'

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

Valeriy Gorbunov

Thanks a lot Mohamed!

select_physical * from...PVO...

works great.

Juan Jesus

Sorry, please, can anyone elaborate to get this query PVO running?

oracle.apps.financials.payables.shared.publicView.ReportsBankAccountPVO

Thanks

Juan

Sumanth V -Oracle

@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"

Juan Jesus

Thanks Sumanth,

I get error.

Can you elaborate? some screenshot?

Juan

Sumanth V -Oracle

@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.