Table of Contents

Search

  1. Preface
  2. Introduction to Data Validation Option
  3. Repositories
  4. XML Data Source
  5. Tests for XML Data Sources
  6. Connections
  7. Expressions
  8. Table Pairs
  9. Tests for Table Pairs
  10. Single-Table Constraints
  11. Tests for Single-Table Constraints
  12. Examples of Tests from Spreadsheets
  13. SQL Views
  14. Lookup Views
  15. Join Views
  16. Aggregate Views
  17. Business Intelligence and Reporting Tools Reports
  18. Dashboards
  19. DVOCmd Command Line Program
  20. Troubleshooting
  21. Datatype Reference
  22. Reporting Views
  23. Metadata Import Syntax
  24. Jasper Reports
  25. Glossary

Data Validation Option User Guide

Data Validation Option User Guide

Database Optimization in a Join View

Database Optimization in a Join View

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.