Comparing Data Dictionaries

In situations where the schema has been modified to meet a site's particular needs, the database manager may need to compare the data dictionaries between different projects or between a project and a standard schema.

Comparing data dictionaries is useful:

• as a precursor to migrating development work from a development/test environment to the production instance.
• when considering upgrading to a new version of the ARCHIBUS schema. Knowing the scope of your schema differences can help you evaluate the complexity and risk involved in an upgrade.

When comparing data dictionaries,the results will be displayed much the same as they would if you were performing a data dictionary transfer in. The difference is that there are no recommended actions and no buttons to perform actions.

Comparing a Project Data Dictionary to a Standard Data Dictionary

Follow this procedure to compare the data dictionaries of the standard HQ project and another project.

1. Log into a project such as HQ that has an unmodified data dictionary.
2. Run the Database Update Wizard as follows to create the export files afm_tbls.csv and afm_flds.csv for a standard schema.
   • Transfer Type = Transfer Out
   • Specify Tables = Data Dictionary Tables
   • Start Transfer
3. Sign out of the HQ project.
4. Sign into the project you want to compare to the standard HQ schema.
5. Run the Database Update Wizard as follows:
   • Transfer Type=Perform Comparison Only
   • Specify Tables = Data Dictionary Tables
   • Select: Next.
   • Select: Start Compare.

Comparing the Data Dictionaries of Two Projects

1. Sign into a Project A.
2. Run the Database Update Wizard as follows to create afm_tbls.csv and afm_flds.csv files for the Project A schema.
   • Transfer Type = Transfer Out
   • Specify Tables = Data Dictionary Tables
   • Start Transfer
3. Sign out of Project A.
4. Sign into Project B.
5. Run the Database Update Wizard as follows:
   • Transfer Type=Perform Comparison Only
   • Specify Tables = Data Dictionary Tables
   • Select: Next.
   • Select: Start Compare.

Controlling the Comparisons

When you compare the data dictionaries, you can control the data that is compared.

Fields to Show

These options hide the common changes so that you can concentrate on the unique conditions and errors. Choose the changes that you don't want the system to show in the Per-field Dictionary Changes panel.

• Hide Multi-line Heading Changes
• Hide Field Size Changes
• Hide New Tables and Fields
• Hide All Data Dictionary Differences
• Hide All SQL Table Differences -- that is, hide differences between the data dictionary and the structure of the table as currently defined in the physical database

Per-field Dictionary Changes

Each row represents one type of change to one ARCHIBUS Field entry. For instance, if a field differs in both Data Type and Multi-line Heading, these changes would show as different rows. The exception is new fields, which are represented by a single row.

The Per-field Dictionary Changes panel presents the following columns:

• Table Name -- The name of the table with the difference.
• Field Name -- The name of the field with the difference.
• Field Differs In -- Specifies the type difference found. The type of difference is related to whether the difference is a data dictionary change or a SQL table change.
• Data Dictionary Differences -- This lists differences in key parameters between:

• the ARCHIBUS Data Dictionary (this project’s ARCHIBUS Fields table, afm_flds)
• the extract file (the afm_flds.csv file extracted from the prototypical database).

The grid shows the project value -- the definition as it exists in the current data dictionary of the project you are logged into -- and the corresponding definition in the extract file (afm_tbls.csv). These are the differences that will be implemented when you select one of the panel actions.

Specifically, the compare function during a data dictionary merge checks for differences listed below. The type of difference would be shown in the column "Field Differs In"

• Data Type
• Size
• Decimals
• Allow NULL
• Primary Key
• Validating Table
• Fields to Validate
• ARCHIBUS Type
• String Format
• Default Value
• Numeric Format
• Enumeration List

Additionally the compare checks for:

• Missing Table- If an entire table is missing, the system does not list the individual fields of this table as missing, but simply lists the table.
• Missing Field in Project - If the field exists in the project database but not in the extract.
• Missing Field in Database - If the field exists in the extract but not in the project database.
• Default Value - if the Default Value does not exist in the extract.
• Enumerations - If the database uses values not in extract’s list of enumerated values. For example, consider a field in the extract file with enumeration list values of "Open" and "Closed". A project database containing a record with the value "Canceled" would cause this difference to be reported. It is likely that you will also have a difference reported for Enumeration List because the project definition for the field probably includes the enumeration value "Canceled".
• Circular References - A circular reference is a field that refers to a table that ultimately refers back to the current field. This sometimes occurs with improperly specified validating tables.

• SQL Table Differences -- This lists the differences between

• the ARCHIBUS Data Dictionary (this project’s ARCHIBUS Fields table as it exists at this instant)
• the SQL database tables (the structure of the table as currently defined in the physical database)

These are the differences that would be implemented if you run the Schema Change Wizard. (There is no need to ever compare the extract file to the SQL database tables – you will always read the extract files into the afm_flds and afm_tbls tables before running the Schema Change Wizard.)

Specifically, the compare function during a data dictionary merge checks for these types of differences which are shown in the "Fields Differ In" column.

• Data Type
• Data Size
• Allow NULL
• Primary Keys
• Foreign Keys
• Default Value
• A change to an auto-numbered default

If you need to produce a results data set that can be manipulated (such as searched or sorted) or included in a report, you can create a view of the afm_flds_trans table in Smart Client.

See Also

Comparing Data: Overview