This topic describes the details of the connection Properties as they apply to Database type connections and specifically of 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.

Note

The Data Warehouse connection must exist if you wish to use drag and drop to create Dimensions, Stage tables, Fact tables, Aggregates and Cubes.


SQL Server, Oracle and DB2 Data Warehouse - the connection for the Metadata repository.
Greenplum, Netezza and PDW Data Warehouse - the connection for the Metadata repository which is always stored in SQL Server.
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.
can have its metadata repositories in Oracle, SQL Server and Teradata data warehouses.

General

Options

Description

Connection Name

Name used to label the connection within WhereScape RED. Typically for SQL Server, Oracle and DB2 this is Data Warehouse. 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, Unix. Select the Database connection type.

Database Type

Type of database such as DB2, Greenplum, Hive, Netezza, Oracle, SQL Server, Teradata. Default is (local).

  • If the database or metadata repository is located on the same server as the RED instance, leave the (local) default option selected.
  • If the database or metadata repository is located on a different server select the relevant database type of SQL Server, Oracle or DB2.

ODBC Data Source Name (DSN)

ODBC Data Source Name (DSN) as defined in the Windows 32-bit ODBC Data Source Administrator.

  • Select the relevant ODBC connection to the data warehouse database or metadata repository.

Note

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.

Note

There must only be one data warehouse/metadata connection in a WhereScapeRED repository.

Source System

Options

Description

Database ID

Database Identifier (e.g. Oracle SID or TNS Name, Teradata TDPID) or Database Name (e.g. as in DB2 or SQL Server).

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:

  • If the database link already exists to this source database, then enter that link name in this field.
  • If the link doesn't exist then enter a link name.

Enter the defined SQL LINK name that points to the correct server, the link name does not need to be the same name as the server name.

Under some circumstances Oracle insists that this link name is the same as the tnsnames entry for the database, so it may be good practice to use the source SID as the database link name. If this is a new link then see the notes later in this section on database link creation).

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.

Big Data Adapter Settings

Options

Description

JDBC Connection String (JDBC URL)

Connection string used by the WhereScape Big Data Adapter to access this database.

JDBC Driver Class Name

JDBC driver class to be used by the WhereScape Big Data Adapter. This field must be set if the JDBC URL is set.

  • Select the appropriate JDBC Driver class name from the drop-down list. If this is left empty, this is not specified in generated commands.

Omit Sqoop Driver Option

  • If set, the --driver option to Sqoop is omitted. This is required for certain connection types such as Oracle connections.
  • If you select the Omit Sqoop Driver Option check-box, the driver parameter is not used in Sqoop command line. This is a requirement for Oracle at the moment, as suggested by Sqoop documentation for 1.4.5.

Sqoop Connection Manager Class

Custom Sqoop connection manager class. Corresponds to the --connection-manager command line argument. Leave blank if this is not required.

Include Database/Schema Name in Sqoop Table Option

  • If set, the --table option to Sqoop includes the database/schema name of the destination table when performing Apache Sqoop loads into this connection. This is incompatible with some connection managers, for example the Cloudera Connector Powered by Teradata.
  • If this is not set, users must ensure that the database/schema is otherwise communicated to Sqoop, for example by using the $OBJECT_DATABASE$ token in the in the JDBC Connection String.

Include Sqoop Columns Option

  • If set, the --columns option to Sqoop is included when performing Apache Sqoop loads into this connection. This is incompatible with some connection managers, for example the Cloudera Connector Powered by Teradata.
  • If this is not set, users must ensure that the order of columns in the loads match the order in the metadata.

Database Credentials

Options

Description

Extract User ID

Database User that has access to SELECT from the source system tables to extract data.

For SQL Server, this field can be left blank if using a trusted login, or the server login password.

Refer to Remote View Extract - Oracle Databases Only for details.

Administrator User Password

Optional Database User Password to login, when using WhereScape SQL Admin via the 'Connect using SQL Admin (as SQL Admin User)' context menu item.

JDBC User ID

User ID to login when using JDBC via WhereScape Big Data Adapter (optional).

JDBC Password

Password to login when using JDBC via WhereScape Big Data Adapter (optional).

Extract User Password

The password of the data warehouse user.

For SQL Server, this field can be left blank if using a trusted login, or the server login password.

Administrator User ID

Database User ID to login, when using WhereScape SQL Admin via the 'Connect using SQL Admin (as SQL Admin User)' context menu item (optional).

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 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.

Note

The available options in this drop-down list is configured from Tools > 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.

SSIS Connection String (OLEDB)

Connection string to be used by Microsoft SQL Server Integration Services (SSIS) to connect to the data source or destination. For details on how to use the wizard to build an SSIS Connection String, refer to Loading Data into RED Load Tables using SSIS.

Note

A connection string is typically composed of multiple property name/value pairs that are semi-colon delimited.

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.

When Connection is an OLAP Data Source

This section of fields is only relevant and will only be visible from a Data warehouse connection (where the Data warehouse field is enabled). These fields are required so that the data warehouse can be used as a source for the Analysis Services cubes.

MSAS Connection String

Connection string to be used by Microsoft Analysis Services (MSAS) to connect to the data warehouse. For details on how to use the wizard to build the OLAP connection string, refer to OLAP Defining Data Source for the OLAP Cube.

Note

A connection string is typically composed of multiple property name/value pairs that are semi-colon delimited.

Connection Provider/Driver

Name of the Connection Provider/Driver to use to connect to the data warehouse database, when it is used as the data source for OLAP cubes.

  set to SQLOLEDB.

  set to MSDAORA.

  set to IBMDADB2.

Data Warehouse Server

Data Warehouse Server Name, which is used when the data warehouse is used as the data source for OLAP cubes.

Data Warehouse Database ID

Data Warehouse Database Identifier (e.g. Oracle SID or TNS Name, Teradata TDPID) or Database Name (e.g. as in DB2 or SQL Server), which is used when the data warehouse is used as the data source for OLAP cubes.

Tip

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.


  • No labels