Exercise 7: Modifying the physical data model and generating a DDL script

Before you begin, you must complete Exercise 6: Modifying transformation properties to roll down.

In this exercise, you will modify the original HR_physical.dbm physical data model that you created in Exercise 3 to add an index. After you modify the physical data model, you will generate a DDL script that can be used for later deployment.

Modifying the physical data model

1. Double-click HR_physical.dbm in the Data Models folder in the Data Project Explorer.
2. Expand the Database node, the HR_schema node, and the EMPLOYEE table node to see EMPLOYEE_PK.
3. Select EMPLOYEE_PK.

   On the Members page in the Properties view, you can see the EMPLOYEE_PK column that maps to this primary key.

4. Back in the Data Project Explorer, right-click EMPLOYEE_PK and select Add Data Object > Index.

   A new index named EMPLOYEE_EMP_ID_IDX is generated and displayed in the Data Project Explorer.

   Note: The default names for new data objects is set in the Preferences window, on the Naming Standard page

5. Save the HR_physical.dbm physical data model.

Generating a DDL script

1. Right-click the HR_schema schema in the Data Project Explorer and select Generate DDL.

   A wizard opens.

2. On the Options page, keep the defaults and click Next.
3. On the Objects page, select Tables and Indexes only, because there are no other types of objects in the HR_schema schema.

   Tip: Click Deselect All, then click the check boxes next to Tables and Indexes.

4. Click Next.
5. On the Save and Run DDL page, specify the following settings:
   • Ensure that the Design data design project is specified in the Folder field.
   • Type myddl in the File name field.
   • View the generated DDL in the Preview DDL field.
   • Select Open DDL file for editing, then click Next.
   • View your options on the Summary page, then click Finish.

   If you wanted to run the DDL script on the server, you could select Run DDL on server. If you select this option, you must provide connection information on the following pages of the wizard.

6. Click Finish.

   The myddl.sql file is created and displayed in the Data Project Explorer in the SQL Scripts folder. Because you selected to open the file for editing, the file also opens in the SQL editor, where you can modify the script.

Finish your tutorial by reviewing the materials in the Summary.