In this lesson, you will use the Compare editor to compare
the modified physical data model with the database source of the model.
Tip: If you store the data design project that
contains the physical data model in one of the supported source code
management tools, you can also compare the local version of the physical
data model with the stored version that is under source code control.
If you make changes to a model object in the Compare editor, the
changes are automatically added to the model. You must save the model
to save the changes. You can also choose not to save the changes,
and generate delta DDL statements instead.
In this exercise,
you will make changes to a server object. If you make changes to a
server object in the Compare editor, you must generate delta DDL statements
for the changes and deploy them to the server to save the changes.
Best practice: Before you perform the comparison, you should
refresh the database metadata in the Data Source Explorer, to ensure that you are working with the most current information.
This step is important if you are working in a team environment, and
other team members are making changes to the database.
To compare and merge the changed physical data model
with the original schema:
- Refresh the GSDB database in the Data Source
Explorer. Right-click the sample GSDB database
in the Data Source Explorer, and select Refresh.
- Set the filtering criteria to display only the columns
and related objects in the Compare editor:
- Open the Filtering Criteria window
to compare the updated schema with the source database. In the Data Project Explorer, right-click the
GOSALESDW schema in the sample_model.dbm file
and select . The Filtering Criteria window opens.
You can use this page
to select the types of objects that you want to compare.
- Select only the columns and the related objects. Click the Deselect All button, then
select the Columns check box. Click OK to set the filtering criteria for the Compare editor.
By default, the objects that are related to the selected object types
are also selected. You can also save these settings as your global
preference.
The Compare editor opens.
- Compare the updated schema to the source GSDB database:
- Double-click the Compare tab in the Compare editor to
maximize the Compare editor view. When you compare data
objects, the Compare editor is customized to include a Structural
Compare view and a Property Compare view. The physical data model
is displayed on the left, and the original source from the database
is displayed on the right. The columns at the top of the Structural
Compare view display the name of each object.
- Expand the EMP_EMPLOYEE_DIM table in the Structural
Compare view to see the changes that you made to the physical data
model.
- In the Structural Compare view, navigate through the
differences. You can use the Next Difference
and Previous Difference
toolbar buttons on the main toolbar to navigate
or you can use the arrow keys on the keyboard.
- Specify how to merge the changes that you made to the source
GSDB database:
- Select each difference and click the Copy
from Left to Right toolbar button (
) in the Property Compare view of the Compare editor. You can verify which row is highlighted in the Structural
Compare view by using the Property Compare view. You should see the
properties for the highlighted object.
- Optional: Find objects that might be impacted
by your changes by clicking the Analyze Left Impact (
) or Analyze Right Impact (
) toolbar buttons. The Impacted Objects view opens, and you can view what objects
are impacted. If you select the new MIDDLE_INITIAL column and analyze
the impact, you see that you are adding the column to the GOSALESDW.EMP_EMPLOYEE_DIM
table.Because you are making changes to the server object in this
case, you must now generate a DDL script to capture the changes that
you made in the Compare editor.
- Generate the DDL script to capture the changes:
- Click the Generate Right Delta DDL (
) toolbar button. This toolbar button is enabled only after you make changes to the
right object in the Compare editor. The Generate DDL wizard opens.
- Complete the Generate DDL wizard:
- Folder
- Ensure that the EMPLOYEE data design project is specified.
- File name
- Type mydeltaddl.sql in the field.
- Open DDL file for editing
- Select the check box.
- Preview DDL
- View the generated DDL statements in the field.
Your settings should now look like
something like the following image:
- Click Finish.
The
mydeltaddl.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.
Tip: After you
generate the SQL script, you can return your workspace to its normal
view by double-clicking the Compare tab.
The tutorial ends here, but you can modify the SQL script before
you run it on the server. After you complete your changes, you can
run the script on the database server by right-clicking in the editor
and selecting Run SQL.