BI Publisher Data Models: Design and Best Practices

Data model components

A BI Publisher data model is an XML artifact stored in the BIP catalog. It contains:

• Data sets: SQL queries, REST calls, or Oracle BI subject area connections
• Parameters: runtime inputs that filter or control the report
• List of Values (LOV): parameter value lists (dropdowns)
• Bind links: parent-child relationships between datasets
• Aggregates: calculated totals and subtotals

SQL dataset best practices

The data model SQL should be read-only, parameterised, and efficient:

-- Good: parameterised, filtered, aliased
SELECT
  po.po_number,
  po.creation_date,
  s.supplier_name,
  SUM(pol.amount) AS total_amount
FROM po_headers po
JOIN po_lines pol ON po.po_id = pol.po_id
JOIN suppliers s ON po.supplier_id = s.supplier_id
WHERE po.status = :P_STATUS
  AND po.org_id = :P_ORG_ID
  AND po.creation_date BETWEEN :P_FROM_DATE AND :P_TO_DATE
GROUP BY po.po_number, po.creation_date, s.supplier_name
ORDER BY po.creation_date DESC

Parameter types

• Menu: user selects from a pre-defined list
• Text: free-text input
• Date: date picker
• Hidden: default values not shown to user (useful for org_id from session variables)

Define default values and mark parameters as required or optional.

Parent-child datasets (bind links)

For master-detail reports (PO header with lines), use two datasets linked by key:

• Dataset 1 (G_PO): PO header query, key = PO_ID
• Dataset 2 (G_LINES): PO lines query filtered by PO_ID = :PO_ID
• Bind link: G_PO.PO_ID → G_LINES.PO_ID (parent-child)

BIP executes the child query once per parent row, building nested XML. The template then iterates parent rows and their children.

Using session variables

Access Fusion session context in data model SQL:

WHERE gl.ledger_id IN (
  SELECT ledger_id FROM gl_access_set_ledgers
  WHERE access_set_id = :xdo_user_id  -- current user's access set
)

Testing and generating sample data

Always generate sample XML before creating templates: Data Model → View Data → run → export XML. This sample is used by Template Builder for Word during template design — without it, you’re designing blind.