1. Implementing Enterprise Contracts
  2. Modify Data Models

Modify Data Models

A data model defines where data for a report comes from and how that data is retrieved. To create a modified data model if you need additional data not included in the existing model, you can copy and edit an existing data model

You can connect to external data sources using web service or HTTP data sources. Any end point that requires IP's being allowed or proxy configuration may be out of service during maintenance activity. If you select a data source that isn't supported, you will get a warning message. Here are some considerations about external data sources.

  • New modified JDBC connections aren't allowed. Existing modified JDBC connections continue to work.

  • JNDI connections are restricted to audit data only.

  • Web service connections are supported.

  • HTTP connections are supported.

  • File connections are supported in the Data Model Editor.

  • Content Server connections are supported.

Create a Data Model

  1. In the business intelligence (BI) catalog, click New and select Data Model in Published Reporting.

  2. Optionally click the Properties node in the Data Model pane to set properties for the data model. For example, to prevent null values from removing columns from a report, select Include Empty Tags for Null Elements in the XML Output Options section of the properties. This preserves the order of columns when there are null values in an exported report.

  3. Click the Data Sets node in the Data Model pane to create or edit data sets, which determine where and how to retrieve data. Click New Data Set and select a data set type. It's best practice to use the BI repository as a data source, so you should select either:

    • SQL Query: To use a Query Builder tool to define what to use from the repository. Select Oracle BI EE as the data source.

    • Oracle BI Analysis: To use columns from a selected analysis.

    Note: OBIEE data sets are retrieved using logical tables in the BI metadata and joined internally, so there is no need for explicit joins within a subject area. It's recommended to create a single data set with tables from the same subject area.

  4. Optionally, to limit the data included in the report output, click the Parameters node in the Data Model pane to define variables that users can set when they use the report. You can set parameters as mandatory if they're required, for example to filter data by a prompted value to improve query performance. These are indicated by an asterisk when you run the report. Required report parameters are required before you can run a report using the View Data option or online, or schedule it.

    Note: The order of parameters is important if there are job definitions defined for reports that use your data model. If you change the order in the data model, also update the job definitions.

  5. Click Validate to view query and performance warnings.

  6. Save your data model inShared Folders > Custom.

Data Model Supported SQL Statements

Data models support standard SQL SELECT statements and non-standard SQL, including:

  • Database procedures

  • Cursor statements that return nested result sets

  • Reference cursor functions that support explicit static SQL SELECT statements

  • SQL query parameters used in a WHERE clause

  • Conditional queries that execute multiple SQL queries in a single data set using an if-else expression

Data models don't support SQL statements with these keywords: DELETE, INSERT, UPDATE, DROP, EXECUTE, ALTER, MERGE, DBMS_SQL, CREATE, AUTONOMOUS_TRANSACTION, PRAGMA, WRITETEXT, UPDATETEXT, LOAD, UNLOAD, DATABASE, GRANT, REVOKE, TRUNCATE.

Edit a Data Model

  1. Copy the predefined data model.

    1. Find the data model in the BI catalog and click Copy.

    2. Paste within Shared Folders > Custom in a subfolder that has a folder path similar to the folder that stores the original data model.

    3. For the data model you pasted, click More, and select Edit.

  2. Optionally click the Data Model node in the Data Model pane to set properties for the data model.

  3. Click the Data Set node in the Data Model pane to create or edit data sets.

    Most predefined data models are of type SQL Query, and are set up to get application data from the following tables:

    • ApplicationDB_FSCM: Financials, Supply Chain Management, Project Management, Procurement, and Incentive Compensation

    • ApplicationDB_CRM: Sales

    • ApplicationDB_HCM: Human Capital Management

  4. Perform steps 4 through 6 from the preceding Create a Data Model task, as needed.

Related Topics