Validate Data Models

When you validate data models, the validation messages help you correct data models, optimize queries, reduce stuck threads, and enhance the reporting performance.

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

1. Checks the queries used for datasets, 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 message. See Data Model Validation Messages.

Note that if you have upgraded 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 Type	Message Type	Message	Limit
Query	Warning	SQL query contains SELECT *. Use of '*' is restricted. Select the specific columns.
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 datasets.
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.
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
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
Query	Warning	Number of columns in SELECT exceeds the limit of {0}. Select only the required columns.	100
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.
Query	Warning	Selected column name length exceeds the limit of {0}. Length of the column name must not be more than 15 characters. Use short alias for column names.	15
Query	Warning	Number of inline or subquery exceeds the limit of {0}. Remove the additional in-line select queries.	10
Query	Warning	SQL query contains the FROM DUAL clause. SQL query contains too many DUAL tables. Avoid the usage of FROM DUAL clause.
Query	Warning	Number of LOB columns in SELECT exceeds the limit of {0}. Select only the required columns.	2
Query	Error	Query contains DDL or DML keywords. Remove the DDL and DML keywords from the SQL query.
Structure	Warning	Number of group breaks on single dataset exceeds the limit of {0}. Remove multiple groups from the dataset.	2
Structure	Warning	Data model contains group filters. Replace the group filters with the WHERE clause in the SQL query.
Runtime	Error	Data model property is invalid or contains invalid values. Specify the correct data model property and check the property value.
Query	Warning	SQL query execution plan contains full table scans. Provide the required filters on indexed columns in the SQL query.
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.
Query	Warning	SQL query execution plan contains high CPU cycles. Add the required  filters in the SQL query to reduce the data fetch volume.
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.
Query	Warning	Detected function calls in the WHERE clause predicates.
Query	Warning	Detected calls to PL/SQL functions in the SELECT list; such calls may affect performance significantly.
Query	Warning	Scalar subqueries are subqueries in the SELECT list. They must return exactly one value. Using ROWNUM or DISTINCT to restrict the output indicates potential performance problem.
Query	Warning	Too many values in the IN-LIST filter might prevent the optimizer from finding a more efficient plan.
Runtime	Warning	Security predicate is wrapped inside a subquery causing unnecessary nesting. BI Server has generated too many joins between WITH sub-queries using unsupported SYS_OP_MAP_NONNULL function.
Query	Warning	BI Server has generated too many joins between WITH sub-queries using unsupported SYS_OP_MAP_NONNULL function.
Query	Warning	An inline view or table detected was OUTER-joined on optional side of the join, and has no data selected from it.
Query	Warning	Scalar subqueries are subqueries in the SELECT list. Having a WITH clause in scalar subquery might create serious performance problems.
Query	Warning	Tables that are listed in a query and joined to other tables, but never used in the SELECT FROM clause might be redundant. Check if this table is joined on the primary key column to the foreign key columns of other tables.
Query	Warning	Column is defined as scalar correlated subquery and later used in a filter or join expression. This might cause serious performance degradation.
Query	Warning	Predicates that use bind variables in a non-trivial way. For example, (:JCODE IS NULL OR mcd.JCODE LIKE :JCODE).
Query	Warning	CASE Expression contains more than 10 complex WHEN ... THEN expressions.
Query	Warning	Columns defined as constants (literals) in a View Object were referenced in join predicate(s) in a query.
Query	Warning	The SQL statement has more than 10 UNION branches in one sub-query.
Query	Warning	Remove unnecessary table reference as the same column is used in SELECT, JOIN and FILTER.
Structure	Warning	Don’t store data in CLOB and convert to XML. Store in XML for better performance.