Categories
- All Categories
- 70 Oracle Analytics News
- 7 Oracle Analytics Videos
- 13.9K Oracle Analytics Forums
- 5.2K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 38 Oracle Analytics Trainings
- 56 Oracle Analytics Data Visualizations
- 2 Oracle Analytics Data Visualizations Challenge
- 2 Oracle Analytics Career
- 4 Oracle Analytics Industry
- Find Partners
- For Partners
Ability to Run SQL Query from BI Publisher Report
Description
Creating a BI Publisher Data Model that can dynamically accepts SQL query from a BI Publisher Report. passed as a Parameter from Report.
Use Case and Business Need
With no direct access to Fusion cloud database and with announcement from Oracle on retirement of OTBI direct query option. Technical and Functional team have challenges in making some simple query for validating the data and fixing defects in Fusion Application. So if we can have an option in BI Publisher that can have a data model that shall accepts requests from BI Publisher Report that can dynamically pass the SQL from the report (like SQL queries in the Report prompts).
Data model has limitation of maximum record outputs of 200.
Or Oracle should re-consider limiting 200 records maximum from a data model query.
Original Idea Number: 9c8d93e5fb
Comments
-
Definitely needed.
0 -
I agree that the "top200" is really annoying. It should output all the lines, as long as the memory allows it... It always depends on how many columns you select, but when troubleshooting and trying to pinpoint what happened in an extraction, it is super limitative.
0 -
Would like to understand this 200 row limitation here that you are seeing in BI Publisher. The data model editor has a 200 rows as max limit during design time, which allows you to create a sample data for your data model. Once data model is created, you should be able to view the complete set of rows of data at runtime while viewing the report online or as a scheduled job.
0 -
Yes you may need to create a report for that, currently there is no option like SQL server client for validating/querying Oracle Fusion tables/data.
We have OTBI Direct SQL option (https://cloudcustomerconnect.oracle.com/posts/2624d2cb7e), but this will be discontinued by Oracle by end of this year. So Oracle has to provide some option to connect to Oracle Fusion DB. And removing this 200 rows limitation is one of the option in hand.
0 -
Troubleshooting issues is currently a nightmare.. Without delivered reports in OTBI, customers are forced to created BIP reports ..On SAAS without sql net connectivity its extremely time consuming and challenging for my development team to deliver reports on time.. This request is absolutely necessary and atleast removing a constraint to limit 200 rows should be accommodated immediately., thanks,Padmaja
0 -
Agreed, the 200 row limit is asinine. Users should be able to see all rows in edit data model mode, or at least have the option to export to excel all rows. As of now, you have to tediously create a BIP Report template.
0 -
Hi,
As quick workaround you could create report based on data model -> in wizard do not move any column to layout. then edit layout Output Format to leave only csv. Executing the report now you will have all rows in csv format without 200k limitation. Usually to create such report take around 1 min.
Regards,
Henrihs
0 -
That's a helpful work-around (emphasis on the work-around lol). If you added/removed columns, you'd probably have to go add/remove those, or either create a new report every time you change your columns.Thanks, Henrihs!
0 -
thanks for the tip!
0 -
Workaround is good but if we have the flexibility to run the SQL and extract outputs without 200 rows restriction, it will be help IT users who are supporting the business teams.
0