< Previous | Next >

Comparing and merging the changed physical data model with the original schema

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:

  1. Refresh the GSDB database in the Data Source Explorer. Right-click the sample GSDB database in the Data Source Explorer, and select Refresh.
  2. Set the filtering criteria to display only the columns and related objects in the Compare editor:
    1. 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 Compare With > Original Source. The Filtering Criteria window opens.

      You can use this page to select the types of objects that you want to compare.

    2. 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.
  3. Compare the updated schema to the source GSDB database:
    1. 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.
    2. Expand the EMP_EMPLOYEE_DIM table in the Structural Compare view to see the changes that you made to the physical data model.
      Screen capture showing the Structural Compare view of the Compare editor, as described.
    3. In the Structural Compare view, navigate through the differences. You can use the Next Difference Next Difference toolbar icon and Previous Difference Previous Difference toolbar icon toolbar buttons on the main toolbar to navigate or you can use the arrow keys on the keyboard.
  4. Specify how to merge the changes that you made to the source GSDB database:
    1. Select each difference and click the Copy from Left to Right toolbar button (Copy from Left to Right toolbar icon) 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.

    2. Find objects that might be impacted by your changes by clicking the Analyze Left Impact (Analyze Left Impact toolbar icon) or Analyze Right Impact (Analyze Right Impact toolbar icon) 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.

  5. Generate the DDL script to capture the changes:
    1. Click the Generate Right Delta DDL (Generate Right Delta DDL toolbar icon) 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.
    2. 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:
      Screen capture showing the wizard page settings as described in this step.
    3. 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.
Screen capture showing the SQL script open in the editor, as described.
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.

< Previous | Next >

Feedback