OTBI Analysis to BI Publisher Query

Sometimes while writing queries we are not sure about the exact tables or joins to use. In such cases creating a simple OTBI analysis and getting exact BI query out of it is very helpful.

Below steps can be followed to get the BI publisher query from OTBI analysis.

• Create an analysis in OTBI

                                              

                                           

• Go to advanced tab and copy the SQL issued based on subject area

• Click on Administration

• Under "Maintenance and Troubleshooting" click on Issue SQL.

• Paste the SQL issued based on subject area in the box and click on Issue SQL.

• You will get the sample output.
• To get the exact SQL query issued for Oracle BI Publisher click on "view log". The log will get downloaded and you can see the exact SQL query issued in the log.

• The log would look like below. Copy the log into notepad.

• Now remove the unwanted part from the log get the final sql query. This sql wuery will be based on data securities(Roles and data access assigned to user). We will be able to figure out the exact DB tables and joins used in the query. Also we can directly use this query BI Publisher data model.

• The OTBI query would have columns as c1, c2,c3.... We can modify to our need. In the example, they have added column c1 to have distinct 0(this column will have value as 0 always, we can remove this). C2-> invoice date, C3-> Description, C4-> Invoice Number.

• From the extracted query we get the exact DB tables and joins used or we can use the query as it it in BI publisher.