Validate Data Models

You can use error and warning messages when validating data models to help you correct data models, optimize queries, reduce stuck threads, and enhance the reporting performance.

When you create or edit a data model that's created in the current or previous releases, if you click Validate, BI Publisher:

  1. Checks the queries used for data sets, LOVs, and bursting definitions.

  2. Generates the execution plan for SQL queries.

  3. Displays a list of error and warning messages.

Take the required action based on the validation messages. See Data Model Validation Messages.

Note that when you upgrade BI Publisher from a previous release, the existing data models are marked as not validated.

Data Model Validation Messages

This topic lists the data model validation messages for your reference.

Message Types

  • Error – You must resolve the data model errors if you want to use the data model to run a report.

  • Warning – Make the correction suggested in the warning message. Reporting performance might get affected if you choose to run the report ignoring the warning.

Message Reference

Validation Code Validation Type Message Type Message Limit
DM01_USE_OF_SELECT_STAR Query Warning

SQL query contains SELECT *. Use of '*' is restricted. Select the specific columns.

 
DM02_BIJDBC_NESTED_QUERY Query Warning

Data model contains nested BI JDBC queries. Linking logical queries is restricted. Use OTBI instead of Publisher reports or remove the link between OBIEE data sets.

 
DM03_MERGE_CARTESIAN_JOINS Query Warning

SQL query execution plan contains merge cartesian joins. Generate the explain plan for the SQL query and identify the merge cartesian joins. Add the required filters in the SQL query.

 
DM04_NUM_BIND_VALUES_PER_PARAM Runtime Warning

Number of bind values per parameter more than the limit of {0} results in poor performance. Reduce the number of bind values.

100
DM05_NUM_COLUMNS_WITHOUT_PRUNING Query Warning

Number of columns in SELECT exceeds the limit of {0}. Select only the required columns and enable pruning.

See Publisher Best Practices for SaaS Environments (Doc ID 2145444.1).

30
DM06_NUM_COLUMNS_WITH_PRUNING Query Warning

Number of columns in SELECT exceeds the limit of {0}. Select only the required columns.

100
DM07_WHERE_CLAUSE_NOTIN_NOTEQUAL Query Warning

SQL query contains non-equi joins. Intermediate row spawning can cause performance issues. Replace non-equi joins with equi join or outer join.

 
DM08_COLUMN_ALIAS_LENGTH Query Warning

Selected column length name exceeds the limit of {0}. Length of the column name must not be more than 15 chars. Use short alias for column names.

15
DM09_NUMBER_OF_INLINE_QUERY Query Warning

Number of inline or subquery exceeds the limit of {0}. Remove the additional in-line select queries.

10
DM10_USE_OF_DUAL Query Warning

SQL query contains the FROM DUAL clause. SQL query contains too many DUAL tables. Avoid the usage of FROM DUAL clause.

 
DM12_NUM_OF_CLOB_COLUMNS Query Warning

Number of LOB columns in SELECT exceeds the limit of {0}. Select only the required columns.

2
DM14_DML_DDL_KEYWORD Query Error

Query contains DDL or DML keywords. Remove the DDL and DML keywords from the SQL query.

 
DM15_NUM_GROUP_BREAKS Structure Warning

Number of group breaks on single dataset exceeds the limit of {0}. Remove multiple groups from the dataset.

2
DM16_GROUP_FILTERS_CHECK Structure Warning

Data model contains group filters. Replace the group filters with the WHERE clause in the SQL query.

 
DM17_DM_PROPERTY_CHECK Runtime Error

Data model property is invalid or contains invalid values. Specify the correct data model property and check the property value.

 
DM18_FULL_TABLE_SCAN Query Warning

SQL query execution plan contains full table scans. Provide the required filters on indexed columns in the SQL query.

 
DM19_HIGH_BUFFER_READS Query Warning

SQL query execution plan contains high buffer reads. Buffer reads exceed the limit of 1GB. Add filters in the SQL query to reduce the data fetch volume.

 
DM20_HIGH_CPU_COST Query Warning

SQL query execution plan contains high CPU cycles. Add the required  filters in the SQL query to reduce the data fetch volume.

 
DM22_FUNC_USAGE_ON_FILTERS Query Warning

SQL query execution plan contains function calls on filter columns. Use of SQL function calls on index columns results in poor performance. Remove function calls on filter columns.