You can use reverse engineering to obtain a data model from an existing database, then work with the data model in Power*Architect. You can also use Power*Architect to create an upgrade script for the original database (for more information, see the section called “Comparing Data Models” ).
You can also use reverse engineering for data warehouse design, where your objective is to unify several data models and then import the data from the multiple source systems. To do this, you would typically reverse engineer one table at a time from several different source systems, then make modifications in Power*Architect, using the playpen. You can then forward engineer the new data warehouse data model to a new, separate database (for more information, see Chapter 8, Forward Engineering a Data Model , then use an ETL tool to transfer the data from the source systems to the data warehouse.
For more information, on ETL tools in Power*Architect, see the following sections:
To reverse engineer a data model:
To create a new Power*Architect project, select File » New Project.
If necessary, create a connection for the database you want to reverse engineer. For more information, see Chapter 5, Setting up Database Support .
Add the database connection to your project. For more information, see the section called “Setting up Database Connections” .
A database node is added to the database tree. Expand this node to view the hierarchy of objects in the database (such as catalogues and schemas, tables, columns, indices, and relationships). The hierarchy is presented the same way a native database tool for the source database platform would present the hierarchy.
As you click objects in the database tree, the object changes from grey to black to indicate you've viewed it. All viewed items are saved with the project so you can view them later without having to reconnect to the source system.
Note: If you want to save the entire hierarchy in the project, enable the snapshot option in project settings. For more information, see the section called “Defining Project Settings” .
You can now create a new data model using the objects from the database tree. Simply drag objects from the tree into the playpen.
If you drag higher-level containers (such as a schema, catalogue, or the entire database), individual tables, or multiple tables, all items within the container will be added to the playpen. For example, if you drag a table into the playpen, all of the columns within the table will be added as well. You can also drag individual or multiple columns from the database tree into tables in the playpen. Just drag the columns to the position within the table where you want to insert them.
In addition to using objects from the database tree, you can create new objects (tables, columns, etc.) in the playpen. For more information on working with the playpen, see Chapter 3, Creating a relational Data Model .
You can also do the following:
Create a report listing the source tables used for the tables in the playpen. For more information, see the section called “Creating a Visual Mapping Report” .
Compare your current data model to the original database. For more information, see the section called “Comparing Data Models” .
Forward engineer the schema. For more information, see Chapter 8, Forward Engineering a Data Model .
Use a Kettle job to move data from the original database to your new database. For more information, see the section called “Using Kettle Jobs” .