A powerful feature within WhereScape RED is the ability to migrate a metadata repository from one RDBMS platform to another. This will allow you to take an existing data warehouse environment on one database, such as SQL Server, and move it to another like Oracle. While rarely used, this functionality is of immense value should you need to move RDBMS platforms at any stage after the data warehouse has been created.
The following steps can be performed to move an existing WhereScape RED metadata repository from one RDBMS platform to another. Note: this only moves the metadata structures and content, it does not move the data contained in the data warehouse.
Prior to performing the metadata migration you may wish to perform the following tasks to ease the process;
- Upgrade to the latest version of WhereScape RED, to ensure you have the latest functionality.
- Archive the Scheduler Audit and Detail log tables if you do not need to bring across this information. The archive process moves the records from the audit trail log to an audit trail archive table. This will reduce the amount of data that needs to be imported into the target metadata repository. To perform the archive, go to the Scheduler tab within WhereScape RED and choose from the menu: Logs à Archive the audit and detail logs.
In the example below we are moving from SQL Server to Oracle;
- Create an Oracle metadata repository via WhereScape Setup Administrator tool, using the same version as the SQL Server repository you are going to migrate.
- From the SQL Server repository, unload the metadata. This can be done within WhereScape RED using menu option: Backup à Unload the metadata to disk.
- Connect to the Oracle metadata repository using WhereScape RED. Load the SQL Server metadata unload, ignoring the warning about the database being different. This can be done using menu option: Backup à Load the metadata from disk. This process can take a long time for a large metadata environment.
- Verify column data types have been converted. WhereScape RED will convert most data types for you during the load process. However, there may be some data types that still need to be manually adjusted. This can be easily verified by creating all of the tables (Load, Dimension, Stage, Fact, and Aggregate). Those tables with bad data types will fail during table creation. If any data types need to be manually converted, please inform WhereScape Support and they will ensure that future releases of WhereScape RED accommodate those data types.
- Change all transformations to Oracle supported functions. Common transformations will be OK. However, RDBMS specific transformations will need to be converted to the appropriate syntax on the target RDBMS.
- Regenerate or Rebuild all unmodified procedures.
- If you created simple join statement in SQL Server using ANSI join syntax, this will not translate in an Oracle environment. You will need to rebuild the join syntax in Oracle via the wizard.
- Outer join syntax will need to be rebuilt as the SQL syntax is different.
- Custom procedures will need to be recreated using the appropriate procedural language. eg: PL/SQL
- Change Load Table where clauses if required.
- Change View where clauses if required.
- Change Connection details if required.
- Change Load Table connections (for file and script loads) to UNIX if a UNIX scheduler is being used.
- Change Script connections (for script loads) to UNIX if a UNIX scheduler is being used.
- Regenerate any Load scripts.
While not a common situation, it shows the flexibility of WhereScape RED and the advantage of using a tool to generate the bulk of the underlying code. What can be achieved in a very short time using WhereScape would be a substantial undertaking, if not impossible, in any hand-coded or proprietary environment.