Database Update Wizard

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 or 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. Sign into the project you want to compare to the standard HQ schema.
2. Run the Database Update Wizard as follows:
   • Transfer Type=Perform Comparison Only
   • Select database format to transfer in from the drop down list.
   • Select: Next
   • Specify Tables = Data Dictionary Tables (un-check all other tables except for Data Dictionary)
   • Select: Next.
   • Click the check-box to select all tables.
   • 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
   • Select: Next
   • Specify Tables = Data Dictionary Tables (un-check all other tables except for Data Dictionary)
   • Select: Next
   • Click the check-box to select all tables
   • Start Export
3. Sign out of Project A.
4. Sign into Project B.
5. Run the Database Update Wizard as follows:
   • Transfer Type=Perform Comparison Only
   • Select personalized-database from the drop-down list
   • Select: Next
   • Specify Tables = Data Dictionary Tables (un-check all other tables except for Data Dictionary)
   • 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 Field 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 Field 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"

• Field has circular reference
• Default Value not in Enum
• Database Value not in Enum
• Field is new
• Table is New
• Table is only in project
• Field is only in project
• Is NULL?
• AFM Type
• Attributes
• Comments
• Data Type
• Decimals
• Dependent Columns
• Default Value
• Edit Group
• Edit Mask
• Enum List
• Primary Key
• Field Grouping
• Is Asset text
• Is Tc_Traceable
• Maximum Val
• Minimum Val
• Field Heading (Multiline Heading)
• Numeric Format
• Primary Key
• Reference table
• Review Group
• Size
• Single Line Heading
• String Format
• Validate Data

• 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

Database Update Wizard: Comparing Data Overview

Database Update Wizard Overview