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 | | RED Metadata Repository |
LOAD | loads | For Load tables | |
STAGE | stages | For Stage tables | |
STARSCHEMA | dimsfacts | Two schema:
|
In WhereScape RED:
Step 2: Create Connections and Targets
- 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.
- 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.
Add one or more targets for the connection, specifying a Target Name and the schema to use.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.
The list color can be optionally changed as required after the target table has been created.Note
The Target Database is auto-populated, using the database ID specified in the connection.
- 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.
- Open the drop down list for the required target and select Set Target.
- Open the drop-down list and select the required database target to be used as the default.
- Click OK when all the changes are complete.