Oracle Communities

Oracle Analytics and AI

Child Item

Error in SQL query from a connection to Fusion ERP

I established a connection with Fusion ERP, I want to make a manual query through SQL but when I try to do it I get the following message:

"There was an unexpected error while processing this request. Please try again"

How can I make a direct query to the database using SQL from FDI?

Find more posts tagged with

Connection

SQL

OTBI

Fusion ERP Analytics

Subject Areas

Accepted answers

Ram-Oracle

@SERGIO BARRETO From otbi analytics , please access bipublisher or append /xmlpserver instead of /analytics and create a bip data model based on the query and in the query please write hz_parties table with where clause filters .Then use this report as a source for faw instance.

Use data augmentation to point to bip report and final table will be created at adw db. Then use this hz_parties in oac dataset using manual query and check further.

This is one more approach which you may try.

Some useful documents for extracting data from bip reports and use bip data in faw for reporting.

Please see if it helps

FAW:BIP: FAW FA BIP Connection to Load Data from Fusion Oracle BI Publisher (Doc ID 2977983.1)

https://www.youtube.com/watch?v=6FXuRKrjSUw

All comments

Mallikarjuna Kuppauru-Oracle

Hi @SERGIO BARRETO

Seems like issue with your connection settings.

What is your connect type selected during creation? For example - Oracle Applications…etc

https://docs.oracle.com/en/cloud/paas/analytics-cloud/acubi/add-table-dataset-using-sql-statement.html

Regards,

Arjun

SERGIO BARRETO

@Mallikarjuna Kuppauru-Oracle, yes, the connection is with "Oracle Applications"

Mallikarjuna Kuppauru-Oracle

Hi @SERGIO BARRETO

I have tested this . Are you able to see below screen

Check this data lineage mapping sheet for table details.

https://community.oracle.com/customerconnect/discussion/40268/r13-financials-otbi-subject-area-to-database-lineage-mapping-spreadsheet/p1

Regards,

Arjun

Ram-Oracle

@SERGIO BARRETO From oac dataset ,we are querying direct fusion tables which will not be allowed by otbi analytics.Please check the oracle applications connection and we use otbi analytics url.

Otbi analytics has it own rpd datamodel and it will not allow even direct simple query.

From otbi real time subject areas which holds hz_parties data ,pull the required columns which you would intend to see and apply filters as well and from advanced tab of the otbi report ,take out the logical sql and please use this logical sql at oac datasets.

Mallikarjuna Kuppauru-Oracle

Hi @Mallikarjuna Kuppauru-Oracle

Did you tried the bi publisher application to run the queries? if not please use the same.

Regards,

Arjun

SERGIO BARRETO

Hi,

@Mallikarjuna Kuppauru-Oracle, I can see the image, but you can do the query? I checked a file from the URL you send to extract other tables, for example: AP_INVOICES_ALL, but I still have the same result.

@Ram-Oracle, The problem with the last alternative you mention, is that no OTBI subject area has the information I am looking for, for example, from ERP I am trying to create a report with the "Manage Payment Process Requests" information, but no OTBI subject area has this information. So I am trying to try to build it with the tables.

Ram-Oracle

Use data augmentation to point to bip report and final table will be created at adw db. Then use this hz_parties in oac dataset using manual query and check further.

This is one more approach which you may try.

Some useful documents for extracting data from bip reports and use bip data in faw for reporting.

Please see if it helps

FAW:BIP: FAW FA BIP Connection to Load Data from Fusion Oracle BI Publisher (Doc ID 2977983.1)

https://www.youtube.com/watch?v=6FXuRKrjSUw

Mallikarjuna Kuppauru-Oracle

Hi @SERGIO BARRETO

As per your question, bestway is to use the xmlpserver (bi publishser) to run the manual queries.

BIP Publisher DM will support DA so you can pull the data and use the same in FDI OAC.

Regards,

Arjun

SERGIO BARRETO

@Ram-Oracle I have already established the connection with BI Publisher to use the custom report I created using SQL.

But when I try to do the data augmentation from FDI, I don't have the “Create dimension” option in the “Augmentation Type” field, as it appears in the video

Video:

My instance: Only the “Create Dataset” option appears.

why? should I enable a feature? please help me to continue with this. Thanks for your help!

Mallikarjuna Kuppauru-Oracle

Hi @SERGIO BARRETO

As per your screenshot your are using Data source as "Fusion" select the bi publisher from data source selection and try again?

If you think we have answered your question, can you mark this Thread as Answered so that other community user can refer the same.

Regards,

Arjun