The following example illustrates the concepts and UI features that enable tables to be distributed across multiple databases.
The order presented below is the recommended procedural sequence.
In Microsoft SQL Server Management Studio:

Step 1: Create Databases and Schema

Create the required databases and schema and then for each database create a 32-bit ODBC DSN. The table below illustrates a basic example of multiple databases.

Database

Schema

ODBC DSN

Notes

WHERESCAPE

dbo

 
 SQL Server Native Client…
One DSN for each Database




RED Metadata Repository

LOAD

loads

For Load tables

STAGE

stages

For Stage tables

STARSCHEMA

dimsfacts

Two schema:

  • one for dimension tables
  • one for fact tables|

In WhereScape RED:

Step 2: Create Connections and Targets

  1. Create a connection for each of the above databases and set the properties in the usual manner.
    • Connection Type: Database or ODBC
    • Database Type: SQL Server – the multi-database targets feature only applies to SQL Server
    • ODBC DSN: Specify the DSN that applies to the selected Connection.
    • Database ID: Database Name - use the same name specified as the default database within the ODBC DSN. This database ID is used to auto-populate the database field in the target.
  2. The Add new Target Table Location in the Target Settings tab is used to specify the target for each schema within the target database connection.

    Note

    The Create procedures in same database as table option enables you to specify if the procedures associated with new Tables created in Targets of the connection is created in the same database as the Table objects, or in the RED repository meta database. This option is not selected (OFF) by default, e.g. the Table's stored procedures are created in the RED repository meta database. If the Table is created in a different Target location (e.g. distinct schema/database in the same SQL Server instance), you can turn ON this option, if you want the stored procedures to also be located in the same Target database. The location of stored procedures can be changed and locked via the Procedure Properties window.

    Add one or more targets for the connection, specifying a Target Name and the schema to use.

    Note

    The Target Database is auto-populated, using the database ID specified in the connection.

    The list color can be optionally changed as required after the target table has been created.
  3. Repeat actions 1 and 2 of Step 2 for each database and its schema.

Step 3: Create New Objects

Once the connections and targets have been created, Add new objects in the usual manner. However, when using multiple databases, it is important to select the correct database in which to store the object.

The Storage Properties screen shows the storage location details for the selected table.

Step 4: Optional Default Target

Default Target Locations can be optionally specified in Options > Storage > Target Location, e.g. all new Load tables are created in a specific target database/schema you defined. This saves time when creating new tables.

  1. Open the drop down list for the required target and select Set Target.
  2. Open the drop-down list and select the required database target to be used as the default.
  3. Click OK when all the changes are complete.
  • No labels