You can optimize the data sources in a join view for better performance. You can choose to select a subset of data in the data source and aggregate the rows for a relational data source.
To improve read performance of the join view, you can provide a WHERE clause. The WHERE clause ensures that the data source uses a subset of data that satisfies the condition specified in the WHERE clause.
Data Validation Option does not check the WHERE clause syntax. If the PowerCenter Integration Service executes the WHERE clause, any valid PowerCenter expression, including expressions that use PowerCenter functions, is allowed. If the PowerCenter syntax is not valid, a mapping installation error occurs.
Use PowerCenter expression in cases where you do not push down the WHERE clause in to the data source.
Use the following guidelines if the data source executes the WHERE clause:
Relational data source. The WHERE clause must be a valid SQL statement. If the SQL statement is not valid, a runtime error occurs.
SAP data source. The WHERE clause must be a valid SAP filter condition in the ERP source qualifier.
Salesforce data source. The WHERE clause must be a valid SOQL filter condition.
SAS data source. The WHERE clause must be a valid Where clause Overrule condition in the SAS source qualifier.
You can choose one of the following optimization levels when you configure a data source in a join view:
Default. Data Validation Option converts all test logic to a PowerCenter mapping and applies sorting to the data source.
WHERE clause, Sorting, and Aggregation in DB. Data Validation Option pushes the WHERE clause, sorting logic for joins, and all aggregate tests to the database. Data Validation Option converts all other test logic to a PowerCenter mapping. You can choose this option with relational data sources.
Already Sorted Input. PowerCenter mapping does not sort the input. Ensure that you sort the data so that the tests run successfully.