1 Using the Data Model Editor

This chapter describes the components and features supported by BI Publisher's data model editor.

This chapter includes the following sections:

1.1 What Is a Data Model?

A data model is an object that contains a set of instructions for BI Publisher to retrieve and structure data for a report. Data models reside as separate objects in the catalog.

At the very simplest, a data model can be one data set retrieved from a single data source (for example, the data returned from the columns in the employees table). A data model can also be complex, including parameters, triggers, and bursting definitions as well as multiple data sets.

To build a data model, you use the data model editor.

1.2 Components of a Data Model

A data model supports the following components:

  • Data set

    A data set contains the logic to retrieve data from a single data source. A data set can retrieve data from a variety of data sources (for example, a database, an existing data file, a Web service call to another application, or a URL/URI to an external data provider). A data model can have multiple data sets from multiple sources.

  • Event triggers

    A trigger checks for an event. When the event occurs the trigger runs the PL/SQL code associated with it. The data model editor supports before data and after data triggers as well as schedule triggers. Before data and after data triggers consist of a call to execute a set of functions defined in a PL/SQL package stored in an Oracle database. A schedule trigger is executed for scheduled reports and tests for a condition that determines whether or not to run a scheduled report job.

  • Flexfields

    A flexfield is a structure specific to Oracle Applications. The data model editor supports retrieving data from flexfield structures defined in your Oracle Application database tables.

  • Lists of values

    A list of values is a menu of values from which report consumers can select parameter values to pass to the report.

  • Parameters

    A parameter is a variable whose value can be set at runtime. The data model editor supports several parameter types.

  • Bursting Definitions

    Bursting is a process of splitting data into blocks, generating documents for each data block, and delivering the documents to one or more destinations. A single bursting definition provides the instructions for splitting the report data, generating the document, and delivering the output to its specified destinations.

1.3 Features of the Data Model Editor

Use the data model editor to combine data from multiple data sets from different data sources, such as SQL, Excel files, Web services, HTTP feeds, and other applications into a single XML data structure. Data sets can either be unrelated or a relationship can be established between them using a data link.

The data model editor enables you to perform the following tasks:

  • Link data — Define master-detail links between data sets to build a hierarchical data model.

  • Aggregate data — Create group level totals and subtotals.

  • Transform data — Modify source data to conform to business terms and reporting requirements.

  • Create calculations — Compute data values that are required for your report that are not available in the underlying data sources.

1.4 About the Data Source Options

BI Publisher supports a variety of data source types for creating data sets. These can be categorized into three general types:

The first type are data sets for which BI Publisher can retrieve metadata information from the source. For these data set types, the full range of data model editor functions is supported. These data set types are:

For the second type, BI Publisher can retrieve column names and data type information from the data source but it cannot process or structure the data. For these data set types, only a subset of the full range of data model editor functions is supported. These data set types are:

For the third type, BI Publisher retrieves data that has been generated and structured at the source and no additional modifications can be applied by the data model editor. These data set types are:

1.5 Process Overview for Creating a Data Model

Table 1-1 lists the process overview for creating a data model.

Table 1-1 Process of Creating a Data Model

Step Reference

Launch the data model editor.

Section 1.6, "Launching the Data Model Editor"

Set properties for the data model. (Optional)

Section 1.7, "Setting Data Model Properties"

Create the data sets for the data model.

Chapter 2, "Creating Data Sets"

Define the data output structure. (Optional)

Chapter 3, "Structuring Data"

Define the parameters to pass to the query, and define lists of values for users to select parameter values. (Optional)

Section 4, "Adding Parameters and Lists of Values"

Define Event Triggers. (Optional)

Section 5.1, "About Triggers"

(Oracle Applications Only) Define Flexfields. (Optional)

Chapter 6, "Adding Flexfields"

Test your data model and add sample data.

Section 2.16, "Testing Data Models and Generating Sample Data"

Add a bursting definition. (Optional)

Chapter 7, "Adding Bursting Definitions"

1.6 Launching the Data Model Editor

Launch the data model editor from the BI Publisher global header or Home page in one of the following ways:

To launch the Data Model Editor from the global header:

  1. Click New and then click Data Model to open the data model editor.

To launch the Data Model Editor from the Home page:

  1. Under the Create region, click Data Model.

The Data Sets page is the default page displayed as shown in Figure 1-2.

Figure 1-1 Data Sets Page

Data Model Editor interface

1.6.1 About the Data Model Editor Interface

The data model editor is designed with a component pane on the left and work pane on the right. Selecting a component on the left pane launches the appropriate fields for the component in the work area.

The data model editor toolbar, shown in Figure 1-2, provides the following functions:

Figure 1-2 Data Model Editor Toolbar

Data Model Editor Toolbar
  • Manage Private Data Sources — Launches the Manage Private Data Sources dialog to create a private data source connection for use in SQL data sets.

  • View Data — Displays the Data tab where you view and generate sample data.

  • Create Report — Launches the Report Wizard to create a report and automatically populates the Data Model field with the current data model name.

  • Save / Save As — Select Save to save your work in progress to the existing data model object or select Save As to save the data model as a new object in the catalog.

  • Help —View online help for the data model editor.

1.7 Setting Data Model Properties

To access the Data Model Properties page as shown in Figure 1-3, click the Data Model node in the components pane.

Figure 1-3 Data Model Properties

Data Model Editor interface

Enter the following properties for the data model:

Description —  The description that you enter here displays in the catalog. This description is translatable.

Default Data Source — Select the data source from the list. Data models can include multiple data sets from one or more data sources. The default data source you select here is presented as the default for each new SQL data set you define. Select Refresh Data Source List to see any new data sources added since your session was initiated.

Oracle DB Default Package — If you define a query against an Oracle database, then you can include before or after data triggers (event triggers) in your data model. Event triggers make use of PL/SQL packages to execute RDBMS level functions. For data models that include event triggers or a PL/SQL group filter, you must enter a default PL/SQL package here. The package must exist on the default data source.

Database Fetch Size — Sets the number of rows fetched at a time through the JDBC connection. This value overrides the value set in the system properties. If neither this value nor the server setting is defined, then a default value of 100 is used.

For more information, see "Setting General Properties" in Oracle Fusion Middleware Administrator's Guide for Oracle Business Intelligence Publisher.

Enable Scalable Mode — Processing large data sets requires the use of large amounts of RAM. To prevent running out of memory, activate scalable mode for the data engine. In scalable mode, the data engine takes advantage of disk space when it processes the data.

Backup Data Source — If you have set up a backup database for this data source, select Enable Backup Connection to enable the option; then select it when you want BI Publisher to use the backup.

  • To use the backup data source only when the primary is down, select Switch to Backup Data Source when Primary Data Source is unavailable. Note that when the primary data source is down, the data engine must wait for a response before switching to the backup.

  • To always use the backup data source when executing this data model, select Use Backup Data Source Only. Using the backup database may enhance performance.


This feature requires that a backup data source has been enabled for the selected data source. For more information, see "About Backup Databases" in Oracle Fusion Middleware Administrator's Guide for Oracle Business Intelligence Publisher.

1.7.1 XML Output Options

These options define characteristics of the XML data structure. Note that any changes to these options can impact layouts that are built on the data model.

  • Include Parameter Tags — If you define parameters for your data model, select this box to include the parameter values in the XML output file. See Section 4, "Adding Parameters and Lists of Values" for information on adding parameters to your data model. Enable this option when you want to use the parameter value in the report.

  • Include Empty Tags for Null Elements — Select this box to include elements with null values in your output XML data. When you include a null element, then a requested element that contains no data in your data source is included in your XML output as an empty XML tag as follows: <ELEMENT_ID\>. For example, if the element MANAGER_ID contained no data and you chose to include null elements, it would appear in your data as follows: <MANAGER_ID />. If you do not select this option, no entry appears for MANAGER_ID.

  • Include Group List Tag — (This property is for 10g backward compatibility and Oracle Report migration.) Select this box to include the rowset tags in your output XML data. If you include the group list tags, then the group list appears as another hierarchy within your data.

  • XML Tag Display — Select whether to generate the XML data tags in upper case, in lower case, or to preserve the definition you supplied in the data structure.

1.7.2 Attachments to the Data Model

The Attachment region of the page displays data files that you have uploaded or attached to the data model. Attaching Sample Data

After you build your data model, it is required that you attach a small, but representative set of sample data generated from your data model. The sample data is used by BI Publisher's layout editing tools. Using a small sample file helps improve performance during the layout design phase.

The data model editor provides an option to generate and attach the sample data. For more information, see Section 2.16, "Testing Data Models and Generating Sample Data." Attaching Schema

The data model editor enables you to attach sample schema to the data model definition. The schema file is not used by BI Publisher, but can be attached for developer reference. The data model editor does not support schema generation. Data Files

If you have uploaded a local Microsoft Excel, CSV, or XML file as a data source for this report, the file displays here. Use the refresh button to refresh this file from the local source. For information on uploading files to use as data sources, see Chapter 2, "Creating Data Sets".

Figure 1-4 shows the Attachments region with sample data and data files attached:

Figure 1-4 Attachments Region with Attached Sample Data and Files

Attachments region

1.8 Managing Private Data Sources

Data model developers can create and manage private JDBC, OLAP, Web service, and HTTP data source connections without having to depend on an Administrator user. However, Administrator users can still view, modify, and delete private data source connections, if needed.

To create a private data source connection:

  1. From the data model editor toolbar, click Manage Private Data Sources.

  2. Select the connection type tab, and click Add Data Source as shown in Figure 1-5.

    Figure 1-5 Creating Private Data Source Connections

    Manage Private Data Source Connections page


    If you are logged in as an Administrator, all data source connections will display for you in this dialog; however, you can only create or modify JDBC, OLAP, HTTP, and web service data sources from this dialog.

  3. Enter the private connection name, and the required fields.

  4. Click Test Connection. A confirmation is displayed.

  5. Click Apply. The private data source connection is now available for use in your data sets.

Private data source connections are identified by the word (Private) appended to the end of the data source name. For example, if you create a private JDBC connection called My JDBC Connection, it is displayed as My JDBC Connection (Private) in the data source drop-down lists.

If your user has the Administrator role, you can only create public data sources, even if you create the data source from the Manage Private Data Sources page. For more information about private data source connections, see "About Private Data Source Connections" in Oracle Fusion Middleware Administrator's Guide for Oracle Business Intelligence Publisher.

For more information on setting up the data source types, see "Setting Up Data Sources" in Oracle Fusion Middleware Administrator's Guide for Oracle Business Intelligence Publisher.