Categories
- All Categories
- 75 Oracle Analytics News
- 7 Oracle Analytics Videos
- 14K Oracle Analytics Forums
- 5.2K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 40 Oracle Analytics Trainings
- 59 Oracle Analytics Data Visualizations
- 2 Oracle Analytics Data Visualizations Challenge
- 3 Oracle Analytics Career
- 4 Oracle Analytics Industry
- Find Partners
- For Partners
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?
Best Answer
-
@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)
1
Answers
-
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
0 -
0
-
I have tested this . Are you able to see below screen
Check this data lineage mapping sheet for table details.
Regards,
Arjun
0 -
@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.
0 -
Hi @Mallikarjuna Kuppauru-Oracle
Did you tried the bi publisher application to run the queries? if not please use the same.
Regards,
Arjun
0 -
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.
0 -
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
0 -
@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!
0 -
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
1
