Remote View Extract - Oracle Databases Only

Remote view extracts are used to force a remote Oracle source system database to perform complex extraction logic in the remote source system database and return the results to the data warehouse.
When specifying a join in an extract (load) over a database link, the Oracle cost-based optimizer decides whether it is better to join the tables in the remote machine; or if the tables should be copied onto the local machine and the join performed locally. If the decision to copy is made for large tables, this can result in huge extract times. The simplest method of forcing the join to occur on the remote machine is to create a view on the remote machine from which the extract is done.
The view is re-created every time the load table is run. The load table then selects all columns from the remote view and inserts the results into the load table over the defined database link.
To create a remote view load table, users need to create a Database Link connection to load the remote view table from that connection. Refer to Creating a Database Link for details.
 
The steps for creating Database Link connection and then creating a remote view Load table are:

1. Create a connection object for the remote system or use an existing connection, populating the following fields:

   Fields	Description
   Connection Name	Name for the connection.
   Connection Type	Set the connection type to Database.
   ODBC Source	The ODBC source used to extract the source data.
   Database ID	The Oracle SID or TNS Name.
   Database Link Name	The name for the database link. This is a required field for establishing the Database Link connection.
   Extract User ID	The user name that has access to the source database.
   Extract User Password	The password for user that has access to the source database

2. Ensure the user name and password being used to connect to the remote system have the following privileges:
   • create view (granted as a specific privilege and not part of a role)
   • compile procedure (granted as a specific privilege and not part of a role)
   • execute on sys.dbms_sql
3. Right-click the Connection to the remote system and select Create Database Link. When the prompt database link has been created is displayed; click OK.
4. Right-click the Connection to the remote system and select Create Remote View Procedure. This creates the remote view procedure in the linked database.
5. When the prompt with the Procedure dss_view_create compiled is displayed, click OK.
6. Browse the Connection in the right pane.
7. Drag and drop a Source table or columns from the source tables to create the required Load table.
8. Create the Load table in the data warehouse database by selecting Create (Recreate) from the right-click context menu.
9. Right-click the Load table and select Source.
10. Enter a name for the remote view to be created into the Source View Name field. For example, use the name of the Load table.
11. Cut or copy any existing 'Where' clause from the Where and Group By Clauses text box and paste it into the Source View WHERE-clause text box. Alternatively, enter the 'Where' clause (including any table joins, if multiple Source tables are being extracted from) into the Source View WHERE-clause text box.
    

    Note

    Once the Load table has a remote view name, the 'Where' clause in the Where and Group By Clauses text box is ignored and the 'Where' clause in the Source View WHERE-clause box is used instead.

12. Click OK.
13. Load or schedule the Load table update.

Example remote view Load table source mapping properties: