This topic describes the details of the connection Properties as they apply to the Database type connections.
When coming to the Data Warehouse from an OLTP source system, the connection defines the path to get to those source tables. A connection object must be defined for each source system.
The fields presented in the Connection Properties screen below, depend on the connection and database types selected. See below for a description of all possible fields.
SQL Server: when running a SQL Server data warehouse it is possible to create database links (linked server) to other SQL Server instances and most of the other major databases, such as DB2, etc.
- Database link load - from a table in the current SQL Server database.
- Database link load - from a table in another database on the same SQL Server instance - the OBDC Data Source Name must be defined in the Windows 32-bit ODBC Data Source Administrator and selected on the ODBC DSN field, as well as the Database ID that also needs to be populated in the Database ID field in the connection properties.
- Database link load - through a linked server.
- Integration Services load.
Examples for each of RED's database-type connection screens for SQL Server, DB2 Greenplum, Netezza, and PDW are provided below the connection properties description.
The connection Properties window has the following fields:
General
Options | Description |
|---|---|
Connection Name | Name used to label the connection within WhereScape RED. |
Connection Type | Indicates the connection source type or the connection method, such as Database, ODBC, Windows, or Unix. Select the Database connection type. |
Database Type | Type of database, the default is (local). |
ODBC Data Source Name (DSN) | ODBC Data Source Name (DSN) as defined in the Windows 32-bit ODBC Data Source Administrator. The ODBC Source Name defined in RED must be the same on all machines that use the corresponding connection. |
WhereScape RED Metadata Connection Indicator | Only required for the Data Warehouse/metadata repository connection. Leave this check box unselected. |
Source System
Options | Description |
|---|---|
Provider Name | Name of the connection provider/driver used to connect to the database. |
Database ID | Database Identifier or Database Name. |
Database Host/Server |
|
Database Port |
|
Database Link Name | Optional name of a Database Link that is used to access the database.
|
Provider Name | Name of the connection provider/driver used to connect to the database. |
Full Database Path Name | This field is used when you enter Microsoft.Jet.OLEDB.4.0 as the Provider Name, e.g. when the source database is from MS Access or Excel. Enter the full database path name. |
Database Credentials
Options | Description |
|---|---|
Extract User ID | Database User that has access to SELECT from the source system tables to extract data. |
Extract User Password | The password of the data warehouse user. For SQL Server blank if a trusted login or the server login password. |
Administrator User ID | Database User ID to log in when using WhereScape SQL Admin via the 'Connect using SQL Admin (as SQL Admin User)' context menu item (optional). |
Administrator User Password | Optional Database User Password to log in when using WhereScape SQL Admin via the 'Connect using SQL Admin (as SQL Admin User)' context menu item. |
Other
Options | Description |
|---|---|
Default Database for Browsing |
|
Default Schema for Browsing | Optional comma-delimited list of schemas for the browser pane filter. Enter the schema(s) you want the connection to browse by default on the right browser pane. |
New Table Default Load Type | The default Load Type for new Load tables is created using this connection as a source. Select the desired default load type from the list, e.g. Database Link Load, Script-based load, Integration Services Load or Externally Loaded. The available options in this drop-down list are configured from Home > Options > Available Load Types. |
New Table Default Load Script Connection | The default Script Connection to use when a Script-based load type is defined for a Load table object that is sourced from this connection. |
New Table Default Load Script Template | The default Script Template to use when a Script-based load type is defined for a Load table object that is sourced from this connection. |
Data Type Mapping Set | Mapping Set to use when converting from a source database data type to a destination database data type. Setting this field to (Default) makes RED automatically select the relevant mapping set, otherwise, you can choose one of the standard mapping sets from the drop-down list or create a new set. |
Once the connection has been set up, you can right-click the connection in the middle pane or double-click the connection name from the left pane to view or edit the connection's Properties.