Oracle Fusion Technical: Reports, Data, Integrations and Extensions · Module 4 · BI Publisher: data models, templates and bursting
SQL data sets: writing the query
Lesson 31 of 177 · 2 min
The query runs on the Fusion database, as the report, against the tables and views Oracle exposes. Not against your own schema, because you do not have one; not with DML, because it is refused. A SQL data set is a SELECT and nothing else. THE FIRST QUERY MOST PEOPLE WRITE IS CORRECT AND UNUSABLE. It joins the header to the lines to the distributions to the supplier to the payment, returns every column of each, and works beautifully against a hundred test rows. At production volume it either times out or returns a result set so large that the template spends longer rendering than the query did running. The joins that will hurt you are the one-to-many ones you did not notice. One invoice with three lines and two payments returns six rows, and every header value is repeated on all six — so a template summing an amount…
The full lesson is part of the course
The video, the complete written lesson and the module quiz are included in Oracle Fusion Technical: Reports, Data, Integrations and Extensions, with a certificate on completion and a fourteen-day refund window.
In this module: Module 4 · BI Publisher: data models, templates and bursting
- 1What a data model actually isFree preview
- 2SQL data sets: writing the query
- 3Parameters and lists of values
- 4Bursting: one run, many recipients
- 5RTF templates: the practical subset
- 6Excel and eText templates
- 7Sub-templates and reuse
- 8Layout, pagination and the printed page
- 9Localisation: Arabic, dates and numbers
- 10Performance: making a slow report fast
- 11Security in BI Publisher
- 12Scheduling and delivery
- 13What breaks: five BI Publisher failures
- 14Lab briefing · A supplier statement, end to end