Importing Updated Metadata with a SAS Metadata Bridge

Problem

You want to import a data model for a set of tables. The model is in a format that is accessible with a SAS Metadata Bridge. It is possible that some of the imported metadata contains updates for existing metadata.

Solution

You can use the Import Metadata Wizard and select the Compare import metadata to repository option on the Import Selection page. This option specifies that metadata in the selected file is imported and compared to existing metadata. Differences in tables, columns, indexes, and keys are detected.

Under change management, imported metadata is compared to the checked in metadata that is associated with the library that you selected in the wizard. Without change management, imported metadata is compared to the metadata in the default repository that is associated with the selected library. Differences are stored in a comparison result library. You can view the changes in the Differences window.

Perform the following tasks:

Tasks

Import the Metadata to be Compared

The following preparation makes it easier to import the metadata that you need to compare to existing metadata:

Identify the folder in the Folders tree that contains the existing metadata that are updated with the imported metadata.
Identify the path to the file that contains the metadata to be imported.
Identify the library that contains the differences between the imported metadata and existing metadata (the comparison result library). Register a new library, if necessary.
Identify the library in the target environment that contains the imported metadata. Register a new library, if necessary. (This library is generally created when the library metadata is first imported.)

Perform the following steps to compare imported metadata to existing metadata:

Right-click the folder in the Folders tree that stores the imported metadata. Then, select ImportMetadata to access the Select an import format page of the Metadata Import Wizard. This page lists the formats that are licensed for your site.

Note: If you select the wrong folder, the imported metadata is not compared to the appropriate existing metadata. Some or all of the imported metadata might then show up incorrectly as new in the Differences window.
From the Metadata Import Wizard, select the format of the file that you want to import. For example, a sample job could use the commonly used OMG CWM (Common Warehouse Metamodel) format. Click Next to access the File Location page.
Specify a path to the file that contains the metadata to be imported in the File name field. The path must be accessible to the default SAS Application server or to a server that you select with the Advanced button on this page. Click Next to access the Meta Integration Options page.
Review the information on the Meta Integration Options page. Typically, you accept the default values.

Note: The Meta Integration Options page enables you to specify how the wizard imports various kinds of metadata in the source file. To see a description of each option, select the option in the Name field, and a description of that option appears in the pane at the bottom of the page. Typically, you can accept the defaults on this page. The following display shows the Meta Integration Options page for the sample job.

Meta Integration Options

Click Next to access the Import Selection page.
The Import Selection page enables you to select whether the metadata is imported as new or compared to existing metadata in the target environment. Because the sample job compares the imported metadata to existing metadata, select Compare import metadata to repository.

Note: If the wizard detects that the metadata to be imported is similar to existing metadata in the folder that you selected when you began the import, it selects Compare import metadata to repository by default. If this option is not selected, select it now. The Comparison results library field becomes active.
Use the drop-down menu to select a comparison result library in the Comparison results library field. You can change the default options for the comparison by clicking Advanced to display the Advanced Comparison Options window. Click Next to access the Metadata Location page.
The Metadata Location page enables you to specify the library in the target environment that should contain the imported metadata. You should select the same library that contains the existing metadata that is compared to the imported metadata. If necessary, you can click the ellipsis button in the Library field to select the library. Note that the content in the DBMS and Schema fields is based on the library that you select. Click Next to access the Finish page.
Review the metadata. Click Finish to import the metadata. When prompted to view the import log, respond as needed. After you skip or view the log, the Import Metadata wizard will close. Verify that the metadata was imported to the appropriate library and folder.
If you are working under change management, it is a good practice to check in the comparison result metadata before viewing or applying the results. From the Checkouts tree, right-click the Project repository icon and select Check In Repository.

If you are not working under change management, all tables that are specified in the imported metadata are registered to the target metadata repository. Verify that the table metadata was imported into the correct folder and library.

Also, be aware that if you are working under change management, the imported tables might not appear in the Checkouts tree until you refresh the tree. Right-click the Checkouts tree and select Refresh.

Compare the Imported Metadata to the Existing Metadata

Perform the following steps to view the results of an import metadata comparison.

Select ToolsComparison Results from the menu bar on the desktop to access the Comparison Results window. The following display shows the Comparison Results window for a sample job.

Comparison Results Window

The Comparison Results window enables you to select the results of a compare import metadata to repository operation. There is one record for each successful comparison operation. Select the desired comparison record. Then, click the View differences found icon in the toolbar to access the Differences window.

Note: The comparison results object is named after the imported file, and it has an XML extension.
Expand the folders in the Differences window to determine whether any metadata has changed. A sample Differences window is shown in the following display.

Differences Window

Continue to expand folders and view the metadata until you are satisfied that you understand the differences between existing metadata and the imported metadata. To perform impact analysis or reverse impact analysis on an item, select the check box by that item, then click the Impact Analysis or Reverse Impact Analysis icons on the toolbar on the Differences window. (For a detailed description of all options and controls in the Differences window, press F1.) In this example, the triangle icons in the next display indicate that the imported metadata contains updates to three tables. The star icon indicates that the imported metadata contains one new table.

The Differences window is divided into two panes: Import Metadata and Repository Metadata. The Import Metadata pane displays metadata that is being imported. Under change management, the Repository Metadata pane displays any matching metadata in the change-managed repository. Only the checked-in metadata displays. Without change management, the Repository Metadata pane displays any matching metadata in the default repository.
To apply a change, select the check box next to it in the Differences window. Then click the Applies the checked changes icon in the toolbar. A dialog box displays, prompting you to verify the change.
Click OK to accept the changes. The selected changes are applied. When finished, close the Differences window and the Comparison Results window.

Applying Changes to Tables with Foreign Keys

When you import metadata about a set of tables that are related by primary keys or foreign keys, and the keys have been either added or updated in the imported metadata, do one of the following:

apply all changes in the imported metadata
apply selective changes, making sure to select all tables that are related by primary keys or foreign keys

Otherwise, the key relationships are not preserved.

Restoring Metadata for Foreign Keys

When you apply changes from imported metadata, a warning message is displayed if foreign key metadata is about to be lost. At that time, you can cancel or continue the apply operation. However, if you accidentally lose foreign key metadata as a result of an apply operation, it is possible to restore this metadata.

Assuming that the imported metadata correctly specifies the primary keys or foreign keys for a set of tables, you can compare the imported metadata to the metadata in the repository. In the Comparison Results window, select the icon for the appropriate comparison result. Then, click Redo the comparison in the toolbar. In the Differences window, accept all changes, or select the primary key table and all related foreign key tables together and apply changes to them.

After you import the metadata for a table, you can view the metadata for any keys by displaying the properties window for the table and clicking the Keys tab.

Deleting an Invalid Change Analysis Result

When you perform change analysis on imported metadata, it is possible to import the wrong metadata or compare the imported metadata to the wrong current metadata. If this happens, the comparison result metadata in the Comparison Result tree are not valid, as well as the data sets for this comparison in the comparison result library.

If you are not working under change management, delete the invalid comparison result metadata.

If you are working under change management, perform the following steps to delete an invalid change analysis result:

Check in the invalid comparison result metadata. From the Checkouts tree, right-click the Project repository icon and select Check In Repository. This makes the comparison result metadata available to others, such as the administrator in the next step.
In SAS Data Integration Studio, have an administrator open the repository that contains the invalid comparison result metadata.
Have the administrator delete the invalid comparison result from the Comparison Results tree. This deletes both the metadata and the data sets for a comparison result.