About Specifying a SQL Bypass Database

The Oracle BI Server can automatically create composite View Objects at runtime enabling an ad-hoc BI query to reference multiple View Objects in the ADF layer.

For improved performance, a SQL bypass query is generated that incorporates the projection columns, filters, and joins required by the BI query.

The SQL Bypass feature directly queries the database so that aggregations and other transformations are pushed down where possible, reducing the amount of data streamed and worked on in Oracle Analytics Server. When using a SQL Bypass database, the system gets the view object query from the ADF data source and then wraps it with the aggregations in the Logical SQL query. The query, including the aggregations, is then run in the database. Because the database computes the aggregation and fewer rows are streamed back to Oracle Analytics Server, using a SQL Bypass database can result in significant performance gains.

Multiple View Objects are modeled as separate BI physical tables and are connected with dummy complex joins. These joins only represent the ViewLinks in the ADF model and aren't run by the Oracle BI Server.

You can specify the name of the SQL Bypass database in the connection pool for the ADF data source. The SQL Bypass database must be a physical database in the Physical layer of the repository. The database object for the SQL Bypass database must have a valid connection pool, with connection information that points to the same database that's being used by the JDBC Data source defined in the Oracle WebLogic Server that runs the ADF application.

The SQL Bypass database doesn't need to have any tables under it. After a valid database name is supplied, the SQL Bypass feature is enabled for all queries against that ADF database.