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

Juan Jesus

Thanks Sumanth,

I already know this approach:

But I cannot find the way to query for:

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

or ReportsPayeePVO or ReportsSupplierSitesPaymentContextPVO

I am missing something, how to compose the query for these PVOs? I need the AM, where to find them?

Juan

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

AshishPal

Hi All,

The above SQL is working fine with where it mentioned in PVO with Fscm but when we run the Crm PVO it gives error.

Can anyone know the connection pool string for CRM as well. For e.g i am trying to see the data of this PVO but gives me error.

Luis Villagomez - IMMI

Hello, doing a simple BIP data model trying to query a PVO is not working in my case, below query:

Select_physical t.* FROM "oracle.apps.scm.productModel.items.publicView.ItemEffPVO" t

It returns the error:

Nonexistent table: "oracle.apps.scm.productModel.items.publicView.ItemEffPVO".

Even I tried the same query with several PVOs and get the same error, could you please suggest how to do it ?

Thanks.

Luis.

Nathan CCC

Hi Luis,

Our host has turned off a few years ago in OTBI the OBI feature to issue SQL using select_physical. I assume due to hacking penetration test security concerns or something like that.

But you can still query a PVO. In your data set of type SQL your data source must be Oracle BI EE.

Then you issue sql - see the examples in the answers in this post

select all t.* from EXTERNAL('ADF',"…"."Connection Pool")."…PVO" t order by null

Luis Villagomez - IMMI

Thanks Nathan, thanks for the clarification it helps, I have tried with all the examples in the answers through this post, however I am getting below error now:

java.io.IOException: prepare query failed[nQSError: 43113] Message returned from OBIS. [nQSError: 13076] User 'XXX' doesn't have the access to this connection pool: 'Connection Pool'.

Not sure What is missing to my user but I will try to find out, if you have any idea, please suggest.

Nathan CCC

Test your SQL query out first on page Issue SQL /analytics/saw.dll?issuerawsql. For this as bi administrator on page manage privileges /analytics/saw.dll?PrivilegeAdmin grant to your user or a role your user is grated or inherit the privileges Issue SQL Directly and Manage Sessions.

for example

select all t.* from EXTERNAL('ADF',

'"oracle.apps.hcm.model.analytics.applicationModule.HcmTopModelAnalyticsGlobalAM_HcmTopModelGlobalAMLocal"."Connection Pool"').'HcmTopModelAnalyticsGlobalAM.HCMExtractAM.UserBiccExtractAM.RoleDNExtractPVO'

t

fetch first 7 rows only

Then for it to work in your data model in your report in OTBI you need the users of your report need to be granted access to that data source to issue logical SQL using this database connection. As bi administrator go to manage publisher in OTBI using page Administration /analytics/saw.dll?Admin. Select Data source of type JDBC Connection then select data source row for the out of the box bi metadata repository database using jdbc:oraclebi called "Oracle BI EE". Scroll to the bottom then move to allowed role an available role your users have such as BI Consumer Role then scroll to the top again to Apply. Sorted.

Luis Villagomez - IMMI

Thanks so much Nathan, that helped a lot.

LaurentDup

Hi, it seems that is no longer to add new JDBC connection with the release 24D.

Upcoming Event

Analytics - Fusion Data Intelligence (FDI) : Overview for Fusion SCM Analytics 26.R3 Application Release
Description: Join our Product Management team to learn about new features that are part of 26.R3 Application release of Fusion Data Intelligence (SCM). The session will cover updates on Supply Chain Management and Procurement. Presented by: Anil Sebastian - Senior Director, SCM Product Management, Fusion Data Intelligence…

Top Leaders