This topic describes the details of the connection Properties as they apply to Database type connections and specifically to the Data Warehouse or Metadata repository connection. The Data Warehouse connection is the connection that stores the metadata repository, and it is the connection that is used in the drag-and-drop functionality to create the Dimension, Stage, Fact, and Aggregate tables.
This connection is also used to create Cubes.
Connection types also impact the available load methods.
The Data Warehouse connection must exist if you wish to use drag and drop to create Dimensions, Stage tables, Fact tables, Aggregates, and Cubes.
Apart from the Data Warehouse or Repository connection to SQL Server, there must be at least one other connection to the Target Data warehouse database, such as Greenplum, Netezza, or PDW.
General
Options | Description |
|---|---|
Connection Name | Name used to label the connection within WhereScape RED. For target databases like Snowflake, Redshift, Greenplum, Netezza, or PDW, the Data Warehouse connection can be renamed to Repository. |
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 | 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 | Distinguishes the special connection that identifies the WhereScape RED datawarehouse/metadata repository. This option must be enabled for Data Warehouse/Metadata Repository type connections. There must only be one data warehouse/metadata connection in a WhereScapeRED repository. |
Source System
Options | Description |
|---|---|
Database Host/Server | Used by scripts to pass to native tools. |
Database Port | Used by script to pass to native tools. |
Database ID | Database Identifier or Database Name. |
Database Link Name | Optional name of a Database Link that is used to access the database. Only required for Database type connections where the database is not on the same server as the data warehouse. If the server is on the same database, the link doesn't need to be defined and the field can be left blank. For connections to databases located in different servers where a database link load is required:
|
Provider Name | Name of the connection provider/driver used to connect to the database. |
Advanced Connect Parameters
Options | Description |
|---|---|
Session User ID | Session User ID used for Advanced Connect. |
Session Password | Session Password used for Advanced Connect. |
Connection String | Used when establishing a connection, accepts automatic replacements of RED Tokens. |
Other
Options | Description |
|---|---|
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 used 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. |
SSIS Connection String (OLEDB) | The connection string that is used by Microsoft SQL Server Integration Services (SSIS) to connect to the data source using the OLE DB connection manager. |
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. |
Default Transform Function Set | Function Set that is selected by default in the Transformation dialogs. |
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.