1 Using the Data Model Editor
What Is a Data Model?
A data model is an object that contains a set of instructions to retrieve and structure data for a pixel-perfect report. Data models reside as separate objects in the catalog.
A data model can be simple with one data set retrieved from a single data source (for example, the data returned from the columns in the employees table) or can be complex with parameters, triggers, and bursting definitions and using multiple data sets.
Use the data model editor to build a data model.
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.
-
Custom Metadata (for Web Content Servers)
If you have configured a Web content server as a delivery destination and enabled custom metadata, the Custom Metadata component displays in the data model editor. Use this component to map data fields from your data model to the custom metadata fields set up for a set of rules defined in a Content Profile.
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. You can use data sets that are unrelated or establish a relationship between the data sets 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.
About the Data Source Options
Data source types supported for creating data sets can be categorized into three general types.
Data set types that can use thee full range of data model editor functions
The full range of data model editor functions are supported for these data set types:
-
SQL queries submitted against Oracle BI Server, an Oracle Database, or other supported databases. BI Publisher can retrieve the metadata information from these SQL queries.
See Creating Data Sets Using SQL Queries.
For information on supported databases, see System Requirements and Certification.
-
Multidimensional (MDX) queries against an OLAP data source
See Creating a Data Set Using a MDX Query Against an OLAP Data Source.
-
Queries against your LDAP repository to retrieve user data
You can report on this data directly, or join this to data retrieved from other sources. See Creating a Data Set Using an LDAP Query.
-
Microsoft Excel spreadsheet data sources
You can store the Excel spreadsheet in a file directory set up as a data source by your administrator, or you can upload it directly from a local source to the data model. See Creating a Data Set Using a Microsoft Excel File.
-
XML data file data sources
You can store the XML file stored in a file directory set up as a data source by your administrator, or you can upload it directly from a local source to the data model. See Creating a Data Set Using a XML File.
-
CSV (comma separated value) file data sources
You can store the CSV file in a file directory set up as a data source by your administrator, or you can upload it directly from a local source to the data model. See Creating a Data Set Using a CSV File.
Data set types that can use partial data model editor functions
BI Publisher can retrieve the column names and data type information from the data source of these data set types, but it cannot process or structure the data. Only a subset of the full range of data model editor functions are supported for data set types:
-
Oracle BI Analysis
-
View objects created using Oracle Application Development Framework (ADF).
Data set types that cannot be modified in the data model editor
For these data set types, BI Publisher can retrieve the data generated and structured at the source. You cannot apply additional modifications in the data model editor for these data set types:
-
HTTP XML feeds off the Web
-
Web services
See Creating a Data Set Using a Web Service.
To use a Web service to return data for the report, supply the Web service WSDL to BI Publisher and then define the parameters in BI Publisher.
Process Overview for Creating a Data Model
Follow the steps below to create a data table.
| Step | Reference |
|---|---|
|
Launch the data model editor. |
|
|
Set properties for the data model. (Optional) |
|
|
Create the data sets for the data model. |
|
|
Define the data output structure. (Optional) |
|
|
Define the parameters to pass to the query, and define lists of values for users to select parameter values. (Optional) |
|
|
Define Event Triggers. (Optional) |
|
|
(Oracle Applications Only) Define Flexfields. (Optional) |
|
|
Test your data model and add sample data. |
|
|
Add a bursting definition. (Optional) |
|
|
Map Custom Metadata for documents to be delivered to Web Content Servers (Optional) |
Launching the Data Model Editor
Launch the data model editor from the header or from the Home page.
To launch the Data Model Editor:
- Use one of these ways:
-
Click New and then click Data Model.
-
Under the Create region, click Data Model.
-
The Data Sets page displays as shown below.
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 below, provides the following functions:
-
Manage Private Data Sources — Connect to private data sources for your personal use that do not require setup by an administrator.
-
View Data — Display the Data tab where you view and generate sample data.
-
Create Report — Create a new report with this data model.
-
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.
Note:
If you create a data model and then navigate out of the data model editor without saving it, a draft or temporary data model entry may display in the Recents section of the Home page. These entries cannot be manually deleted, but are automatically deleted after 24 hours. -
Help — View online help.
Setting Data Model Properties
You can access the Data Model Properties page when you click Properties in the components pane.
Enter the following properties for the data model:
| Property | Description |
|---|---|
|
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 the server default value of 20 is used. If the server property Enable Auto DB fetch size mode is set to True, this value is ignored. |
|
Query Time Out |
Applies to SQL query-based data models. If the SQL query is still processing when the time out value is met, the error Failed to retrieve data xml. is returned. Enter a value in seconds. If you do not enter a value for this data model, the server property value is used. |
|
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. Setting this to On will impact performance, but guard against out of memory errors. Note that Enable Data Model Scalable Mode is also a server-level property therefore by default the data model-level property is set to Instance Level to inherit the server or instance level setting. To turn scalable mode on or off for this particular data model, select On or Off from the list. |
|
Enable SQL Pruning |
Applies to Oracle Database queries only that use standard SQL. If your query returns many columns but only a subset are used by your report template, SQL pruning returns only those columns required by the template. Setting this property enhances processing time and reduces memory usage. Note that Enable SQL Pruning is also a server-level property therefore by default the data model-level property is set to Instance Level to inherit the server or instance level setting. To turn SQL pruning on or off for this particular data model, select On or Off from the list. SQL pruning is not applicable for PDF, Excel, and E-text template types. |
|
Enable SQL Session Trace |
Applies to Oracle Database queries that use standard SQL. If you enable trace, for each SQL statement, the trace contains:
|
|
SQL Trace Name |
Name for the SQL trace. |
|
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 Oracle BI Publisher to use the backup.
You must enable a backup for the data source. See Setting Data Engine Properties and About Backup Databases in Administrator's Guide for Oracle Business Intelligence Publisher. |
|
Enable CSV Output |
Select this option to generate report output only in a CSV file. |
XML Output Options
These options define the characteristics of the XML data structure. 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 option to include the parameter values in the XML output file. See Adding Parameters and Lists of Values for 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 option 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 is displayed for MANAGER_ID.
-
Include Open & Close Tags — Select this option to include the open and close tags in your output XML data.
-
Include Group List Tag — (This property is for 10g backward compatibility and Oracle Report migration.) Select this option to include the rowset tags in your output XML data. If you include the group list tags, then the group list displays as another hierarchy within your data.
-
XML Tag Display — Select this option to generate the XML data tags in uppercase, in lowercase, or to preserve the definition you supplied in the data structure.
Adding 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, you must 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. See Testing Data Models and Generating Sample Data.
The administrator can set a limit to the size of the sample data file. See Setting Data Engine Properties in Administrator's Guide for Oracle Business Intelligence Publisher.
Attaching Schema
The data model editor enables you to attach sample schema to the data model definition.
Oracle BI Publisher does not use the schema file. However, you can attach the schema 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 Creating Data Sets.
The figure below shows the Attachments region with sample data and data files attached:
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.
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. See About Private Data Source Connections and Setting Up Data Sources in Administrator's Guide for Oracle Business Intelligence Publisher.
To create a private data source connection:
- Select the connection type tab, and click Add Data Source as shown below.
Note:
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.
