Improve BIPublisher/SQL DataModel creation possiblities

MaartenDIF

Description

We try to use OTBI subject areas as much as possible, but for some requirements we will need to create custom (SQL based) datamodels.

Currently, we are 'creating' these queries by hand. The only tools we have at our disposal are NotePad++ and the Oracle Documentation site (e.g. https://docs.oracle.com/cd/E83857_01/saas/financials/18b/oedmf/AP_INVOICES_ALL-tbl.html).
This is a very time-consuming and error prone method. Especially making complex joins, autofilling formulas and checking syntax is a very missed functionality.

My suggestion would be to either:

• Allow third party (read-only) developer tools such as Oracle's SQL Developer or Toad to connect to the fusion database schema.
• Create (and update every update) a pre-defined schema file which can be loaded into third-party developer tools.
• Improve BI Publisher functionality to incorporate functions providing extra support to create custom queries from scratch.

Use Case and Business Need

Please improve this feature. Manually typing text in a small square box where it's not even possible to view the whole query is obsolete and quite surprising to find in a cloud application in 2020

More details

We currently follow the steps which are referred to in the following note: 
Note 1910762.1 - Fusion Applications BIP : How can I use BI Publisher to run SQL against the Fusion Applications Database ?
It would be very nice to have some extra functionality between the steps 'At this point the next step is then to define the SQL which we would like to issue.' and 'Once the SQL is defined, click on OK,' as now there is none.

Also found these docs: 
Is There A Way To Connect To The Database From External Tools? ( Doc ID 2178006.1 )
Use Oracle SQL Developer With HCM Cloud Application ( Doc ID 2024775.1 )

Here it is mentioned that it is not possible to connect with external tools. However, it would be nice to just connect read-only and/or load the schemas.

Original Idea Number: c7d2e6d5bd

Mark Johnson

Better SQL query tools would greatly improve reporting capabilities.

We use delivered (payroll) HCM Extracts and at times need to add data that is not available in the delivered Extract XML file.

The extra data is added by adding additional SQL queries to the BIP Report Data Model.

Developing the SQL queries needed with the current Data Model tool is cumbersome and difficult.

David Wright - More4apps

Hi Folks,

There is a way to do this..at the moment. But it is looking like it will be removed in 20C, without providing a way forward to do what you require...

Take a look at: https://cloudcustomerconnect.oracle.com/posts/2c9d3f56e0?page=2

Cheers,

David.