Setting Up Physical Data Sources

This section provides instructions for specifying the connection pool information for the transactional and data warehouse databases.

To specify transactional and data warehouse data source connections

1. In the Setup view, select the Physical Data Sources tab.
   The Physical Data Sources tab displays a precreated record for the data warehouse with the name DataWarehouse, and one or more records for the transactional sources.
2. For each record, enter the following information in the Edit subtab:
   

   Field	Description
   Name	Logical name for the data warehouse or transactional database connection pool. You should not change the names of the precreated records. Note: When you create an execution plan to perform an ETL, you need to use the data warehouse and transactional data source names that you specify here as the Parameter values for the execution plan parameters DBConnection_OLTP and DBConnection_OLAP.
   Type	Source. Select this option for a transactional (OLTP) database. Warehouse. Select this option for a data warehouse (OLAP) database. Informatica Repository. Select this option for the Informatica repository database. DAC Repository. Select this option for the DAC repository database. Other
   Connection Type	Database type.
   Service Name, TNS Name, Connection String, or Database Name (Note: Field name changes based on the Connection Type selection.)	Enter the value appropriate for your database. For an Oracle TNS Name, enter the TNS name that is specified in the tnsnames.ora file in \network\admin\.
   Table Owner	Valid database user.
   Password	Password for the database user.
   Num Connections per EP	Number of connections the DAC Server will open to this physical data source during the ETL process. Note: Typically, DAC needs more connections for data warehouse databases on which it truncates and analyzes tables, and drops and creates indexes. DAC also requires more connections for source systems, such as Siebel, for which it performs change capture operations. In such cases, the value of the Num Connections per EP property determines how many change capture processes can run concurrently. If you have a powerful transactional database server and are going to run ETL processes during off-peak times, you can increase the Num Connections per EP value to 15 or 20 (10 is the default). If you have a less powerful transactional database server, you should not overload the system with ETL processes. Therefore, you should set the value below 10. Source systems for which DAC does not perform transactions usually do not require as many connections. For data warehouse databases, when the DAC Server is analyzing and truncating tables and dropping and creating indexes, the Num Connections per EP value can be higher than the Num Parallel Workflows per EP parameter value (set in the Informatica Servers tab) because when DAC creates indexes for a table in parallel, it needs additional relational connections. For example, if you are running 10 workflows, and you want to create two indexes per table in parallel, you will need 20 connections. This is true even though not all 10 tasks will be doing database-related activities (such as dropping and creating indexes, analyzing tables, and so on) at the same time.
   Host	Machine name or instance where the database resides.
   Port	Port number where the database listens (for example, 1521 is the default for an Oracle database).
   Source Priority	Priority of the physical data source connection. Used for multi-source execution plans to resolve conflicts when data is extracted into common data warehouse tables. The DAC dependency algorithm ensures that no more than one task writes to a target table at a time (unless overridden in task groups), and that the reads and writes are staggered. For example, if you have multi-source scenario in which you want to extract data from Oracle EBS and Siebel transactional sources, and you want to extract data for the common data warehouse entities from Oracle EBS first and then from Siebel, the source priority for Oracle EBS should be 1 and Siebel should be 2.
   Data Source Number	Unique number assigned to the data source category so that the data can be identified in the data warehouse. If you are editing a data source template for a data source type, Oracle recommends that you do not change the default value. If you are specifying a data source without using a pre-defined template, you must use the correct value for that data source category. This value is passed as a parameter to the Informatica workflows. If you are using multiple sources, each data source has a unique number. Typically, all source dependent extracts will use this parameter to populate the DATASOURCE_NUM_ID column, and the source independent workflows will carry these values to the final dimension and fact tables.
   Default Index Space	(Oracle specific) Specifies the table space in which DAC creates indexes on the database. To use the default table space, leave this field blank.
   Num Parallel Indexes per Table	Specifies how many indexes are to be created in parallel for each table associated with the physical data source connection. For example, if you give this property a value of 2, then two indexes will be created in parallel per table during the ETL process. Note: The number of indexes that can be created in parallel is limited by the value you set in the Num Connections per EP property and the Max Num Workflows per EP property on the Informatica Servers tab. Each task that is creating an index on a table requires a connection. Therefore, if you have two tables and each table has three indexes, and you have set the Num Parallel Indexes per Table property to 3, the Num Connections per EP property should have a value of at least 6 in order for all the indexes to be created in parallel. Note: Use caution when setting this property. Setting this number too high as a default for every table that participates in the ETL process can put stress on the database. You may want to consider setting the Number of Parallel Indexes property (in the Parallel Indexes subtab) for individual tables after you identify tasks that are creating bottlenecks or blockages.
   JDBC Driver (Optional)	A JDBC driver for the data source connection. The value in this field must conform to the database specifications. Use this option to specify a unique driver for a particular physical data source.
   URL (Optional)	A JDBC URL for the data source connection. The value in this field must conform to the database specifications. Use this option to specify a unique URL for a particular data source. For example, this option can be useful if this physical data source uses Oracle RAC and other data sources use a different database type.

3. Click Test Connection to make sure the connection works.
4. Click Save.