WhereScape RED has an advanced retrofitting wizard for migrating an existing data warehouse from one relational database to another.

The process of migrating an existing data warehouse includes:

  1. Creating a connection object to the existing warehouse database.
  2. Creating Retro objects based on the source tables in the existing warehouse database.
  3. Setting the Retro objects as Retro Copy type objects.
  4. Running a Scheduler task to build the Retro Copy objects from the source tables.
  5. Setting the Retro objects back to Retro (Retro Definition) type objects.
  6. Converting the Retro objects to the Target Object types.

The steps to use this wizard are as follows:

  1. Create a connection object for the old data warehouse database, populating the following fields:
    • Connection Name
    • Connection Type => ODBC
    • ODBC Source
    • Work Directory
    • Extract user name
    • Extract password
  2. Ensure all naming standards in Home> Options are set to match the objects being retrofitted. This saves work later.
  3. Ensure Enable Retro is selected in the Home > Options > Object Types menu.
  4. Right-click the Retro object group in the object tree in the left pane and select Select Source Tables.

  5. The Retrofit Tables window appears. In the Source Connection drop-down list choose the connection set up in step 1. A list of databases appears in the left pane.

  6. Double-click the database/user/schema in the left pane list. A list of tables in the database is displayed in the middle pane.

  7. Select all the required tables from the middle pane list and click > to move them to the right pane. Then click the Add Ancillary Columns (e.g. dss_update_time) check-box and click OK.

  8. WhereScape RED acquires the metadata for the tables being migrated and creates a new WhereScape RED Retro object for each.
  9. Double-click the Retro object group in the left pane. Select all Retros in the middle pane. Right-click and select Set Table Type to Copy. This enables the data in the legacy data warehouse to be copied across to the new data warehouse.

  10. Click the Scheduler button from the toolbar.
  11. Create a new job to run straight away and click OK. 

    A scheduler must be running on the data warehouse connection for this job to complete, refer to the Scheduler Installation and Configuration section of the RED Installation Guide.

  12. Add all Retro objects created in steps (3) to (9) and then click Group Object Types.

  13. Once the job is completed, return to the WhereScape RED Builder window. Double-click the Retro object group. Select all objects in the middle pane and then from the right-click context menu select Set Table Type to Definition. This indicates the data has been copied into the Retro objects and the Retros can then be converted to the target objects.

  14. In the middle pane, select all objects. Right-click and select Convert to Target Object. WhereScape RED converts the Retro objects to the appropriate object types.

    If the appropriate Target Object Type has not been set for one or more Retro objects; from the right-click context menu select Change Target Object Type and select the correct Object Type.


  15. There are no longer any Retro objects. They have been converted to Load, Stage, Dimension, or Fact objects.

  16. Change the source table and source column values on all of the retrofitted objects using either the Re-target source table window or by editing column properties. Refer to Re-Targeting Source Tables for details.
  17. Convert the old data warehouse code to WhereScape RED procedure in the new data warehouse database. Refer to Integrate Procedures
  18. for details.
  19. If necessary, create new connections to be used with any migrated Lad tables. Attach a connection to each Load table. Refer to Loading Data for details.


  • No labels