You can use Power*Architect to create a Kettle job, which you can then use to create multiple transformations based on a data model you've created in Power*Architect. You would typically create a Kettle job to copy data to a new database you've created through reverse engineering.
Note: The Kettle ETL tool is provided by Pentaho as free and open source software. SQL Power does not maintain or distribute Kettle. To obtain a copy, visit kettle.pentaho.org .
Before you create a Kettle job, you must use reverse and forward engineering to create a new data model and database.
Create a new data model in Power*Architect using reverse engineering (see Chapter 7, Reverse Engineering a Data Model ).
Forward engineer the data model into a new database (see Chapter 8, Forward Engineering a Data Model ). This creates the tables and relationships in the target database.
Before creating a Kettle job, ensure you've completed the prerequisites (see the section called “Before Creating a Kettle Job” ).
Note: You can view or change the location of the Kettle (ETL) log file in user preferences. For more information, see the section called “Setting User Preferences” .
Open the project containing the data model you want to use for the Kettle job.
Select ETL » Create Kettle Job. The Create a Kettle Job dialog box appears.
Enter the following information:
In this field ...
Do this ...
Job Name
Enter a name for the job.
Target Database
Select the database connection for the target database.
Click Properties to view the connection and modify it if necessary. Ensure the connection contains the following information:
General tab - Enter all the required connection properties for the database platform. (See the section called “Setting up Database Connections” .)
Kettle tab - Enter the hostname, port, and database for the target database, if applicable. If a field does not apply to the database platform, it will be disabled. You do not have to enter a login name and password.
Note: The hostname, port, and database information may be entered automatically based on the information on the General tab.
Schema Name
Enter the name of the schema in the target database that contains the target tables. If the target database doesn't contain any schemas, or the target tables are in the default schema, you can leave this field blank.
Default Join Type
Select the join type to use in all merge-joins. Merge-joins are used to create tables with multiple sources.
Note: Merge-joins that are created in transformations from Power*Architect will usually have to be updated manually, since Power*Architect cannot tell which fields to compare during the join.
Save Job to File
Select this option to save the Kettle job settings and transformations to a file. Click Browse and select the location and filename.
Save Job to Repository
Select this option to save the Kettle job settings and transformations in a repository.
In the Repository list, select the database connection for the repository. You can use a connection you have set up previously (if the database contains a repository) or you can set up a new connection to a repository. (See the section called “Setting up Database Connections” .)
Click Properties to view the connection and modify it if necessary. Ensure the connection contains the following information:
General tab - Enter all the required connection properties for the database platform. (See the section called “Setting up Database Connections” .)
Kettle tab - Enter the hostname, port, and database for the repository, if applicable. If a field does not apply to the database platform, it will be disabled. Enter the repository login name and password.
Note: The hostname, port, and database information may be entered automatically based on the information on the General tab.
Click OK to create the Kettle job and transformation files.
If you are using a repository, you are prompted to select the directory location in the repository where the files will be saved.
Once the job has been created, a window appears with the steps you need to complete before running the Kettle job.
Note: The transformation files are stored in the same location as the Kettle job. You must use Kettle to run the job.