Forward Engineering is the process of generating a database schema from a physical model. It enables the synchronization of the data model with the database when physical changes have been made to the data model, such as adding, removing, or changing entities, fields, indexes, physical names, and relationships. It also employs all the changes that are made in the data model while restructuring it. While it executes physical changes, logical changes are not reflected in forward engineering.
To forward engineer a data model, click on the forward engineering icon in the toolbar of the data model designer. By default, this option generates a DDL script when selected.
When you click the arrow next to the Forward Engineer icon, a drop-down menu appears, that shows the four options to forward engineer the data model.
These options are:
Apply DDL Script
Apply Diff Script
Generate DDL Script
Generate Diff Script
Let’s consider the following data model to explore each of these forward engineering options one by one.
This data model represents a simple star schema, with one Fact and four Dimensions.
The Apply DDL Script option generates an SQL Script for the entire data model schema and automatically executes it on the specified database.
Specify the database connection where you want to execute the script. To do that, click on the Change Database Connection Info icon in the toolbar. Alternatively, press Shift+Alt+C to open the Database Connection window.
Specify the database connection information and click OK.
Select the Apply DDL Script option from the Forward Engineer option dropdown menu. Alternatively, press Shift+Alt+F5.
The Apply Scripts window shows all the operations that will be applied to the database once the script is executed. It gives the summary of the following:
Operation
Object Name
Type
View all the changes and click Execute.
The script will be executed, and the operations will be performed on the specified database. The results of the transaction can be seen in the Output window.
Note: If the Output window does not appear, go to View > Output in the main menu. Alternatively, you can press Ctrl+Alt+O to open the Output window.
A Diff Script is a SQL script that reflects any differences between the data model and the corresponding database. The Apply Diff Script option generates a Diff Script for all the differences between the data model and the corresponding database and automatically executes it on the specified database.
Specify the database connection where you want to execute the script.
Select the Apply Diff Script option from the Forward Engineer option dropdown menu. Alternatively, press Shift+Alt+F1.
If you select Apply Diff Script and there are no differences between the data model and the corresponding database, a message that ‘No changes were detected’ will be shown.
Let’s add a new Dimension to the data model and link it to the Fact with a non-identifying relationship. The data model now looks like this.
Select the Apply Diff Script option after making the changes specified above.
The Apply Scripts window shows all the changes that were made to the data model. In this case, it shows that a new dimension has been added. View all the changes and click Execute.
The script will be executed, and the changes will be made to the specified database. The results of the transaction can be seen in the Output window.
The Generate DDL Script option generates an SQL Script for the entire data model schema. It does not execute the script automatically. However, the generated script can be executed in the database.
To generate a DDL script for the data model, select Generate DDL Script option from the Forward Engineer option dropdown menu. Alternatively, press Shift+Alt+F2.
You can also go to Data Model > Generate DDL Script from the main menu.
In the Save window that shows up, navigate to the desired directory, provide a name for the DDL script, and select Save.
The DDL script has been generated and saved. You can execute this script in the database server if required.
The Generate Diff Script option generates a Diff Script for all the differences between the the data model and the corresponding database. It does not execute the script automatically. However, the generated script can be executed in the database.
To generate a Diff Script for the data model, select Generate Diff Script option from the Forward Engineer option dropdown menu. Alternatively, press Shift+Alt+F3.
In the Save window that shows up, navigate to the desired directory, provide a name for the Diff script, and select Save.
The Diff script has been generated and saved. You can execute this script in the database if required.
Note: If you select Generate Diff Script and there are no differences between the data model and the corresponding database, a message stating that ‘No changes were detected’ will be shown.
Each of the four forward engineering options can also be applied to a single entity or a set of entities selected. Select the entity or the set of entities that you want to forward engineer and select one of the four forward engineering options. The script will be generated and/or applied only for the selected entities.
This concludes our discussion on forward engineering a data model in Astera Data Stack.
