All topics
31 / 55
Publisherintermediate

Data Model: SQL, Sub-Queries & Multiple Data Sets

Explain how BI Publisher data models combine multiple queries, parameters, and linked sub-queries into a single structured XML output.

A document is hierarchical, so its data model is too. The editor composes several queries into one nested XML tree.

It's like assembling a family tree document: one query fetches each family (the parent group), and for each family, a second query fetches its members (the nested child group) — the final document naturally shows members indented under their own family, not as one flat, unsorted list of names.

Key Concepts

1
The master-detail pattern.
    dataset "invoices"   SELECT invoice_id, customer, total FROM ...
    dataset "lines"      SELECT invoice_id, item, qty, amount FROM ...
      link: invoices.invoice_id -> lines.invoice_id
2
The XML nests the lines inside their invoice:
3
    <INVOICE>
      <INVOICE_ID>1001</INVOICE_ID>
      <CUSTOMER>Acme</CUSTOMER>
      <LINES>
        <LINE><ITEM>Widget</ITEM><AMOUNT>600</AMOUNT></LINE>
        <LINE><ITEM>Gadget</ITEM><AMOUNT>400</AMOUNT></LINE>
      </LINES>
    </INVOICE>
4
The template then loops over LINE inside each INVOICE.
LINEINVOICE
5
Why not one joined query. A join flattens it — the invoice columns repeat on every line — and the template has no structure to loop over. You would have to detect when the invoice id changes, which is what the nesting does for you.
6
How the link is executed matters. BI Publisher can run the detail query once per master row, or run it once and group in memory.
7
    per master row   5,000 invoices = 5,001 queries
    once, grouped    2 queries
8
The second is the right setting for volume, and the first is the default in some configurations — which is why a bursting job over thousands of records can take hours until somebody changes it.
9
Independent datasets need no link. A summary chart query alongside the detail is simply a second top-level element in the XML.
10
Parameters flow into the queries.
    SELECT ... WHERE order_date BETWEEN :P_FROM AND :P_TO
11
Bound, not concatenated — the same reason as anywhere else.
12
Value sets on parameters drive the list a user picks from, which can itself be a query.
13
The design point. A data model for a document is shaped by the document, not by what is convenient to query. Ask what the template needs to loop over, and build the tree to match — then write the queries that fill it.
14
What the interviewer is probing.1. "When would you use several datasets rather than one query?" Probing: judgement. Stalls: "Always." Moves up: for genuine master-detail from different sources, or where a join would multiply the parent rows — one query is usually faster when it fits.
15
2. "How are datasets related?" Probing: the mechanism. Stalls: "Automatically." Moves up: by an element-level link, which makes the child execute per parent row — which is exactly why it can be slow.
16
3. "What is the performance risk with linked datasets?" Probing: the depth. Stalls: "None." Moves up: the child query runs once per parent row, so a thousand parents means a thousand executions — a single joined query avoids it.
17
4. "What should you check before blaming the template?" Probing: the method. Stalls: "Nothing." Moves up: how long the data model alone takes to produce XML, which separates query time from formatting time.
Use cases:data-modelmaster-detailparameters