Metadata comparison

The Metadata comparison Module compares two metadata definitions against each other:

The actual, current table definition
The definition in existing Metadata TestSheets previously created with the Vital Checks Wizard

The comparison allows you to check whether column definitions have changed over time.

The Metadata comparison Module is included in the Tosca Data Integrity Modules And Samples.tsu subset and located in the folder Modules->Data Integrity Testing.

To compare your metadata, follow the steps below:

Establish a metadata baseline for your tables:
- Utilize existing Metadata TestSheets you created with the Vital Checks Wizard.
- If you do not have Metadata TestSheets yet, create them (see chapter "The Vital Checks: File to Database Wizard" and chapter "The Vital Checks: Database to Database Wizard ").
Create an SQL statement containing the actual, current metadata information of a table.

This statement has to include the following columns for source and target definitions: Column Name, DataType, Length and Precision. If you have a Tosca Data Integrity license, you can use the SQL Editor to create and test SQL statements.

The example below is a sample SQL statement designed to work with a MS SQL Server database. It uses the Source information from the corresponding TestSheet.


SELECT COLUMN_NAME AS 'ColumnName',
       data_type AS 'DataType',
       CASE
           WHEN s.Data_TYPE IN ('int','bigint','smallint','tinyint','money') THEN NULL
           ELSE CASE
               WHEN s.CHARACTER_MAXIMUM_LENGTH > 0 THEN s.CHARACTER_MAXIMUM_LENGTH
               ELSE s.NUMERIC_PRECISION
           END
       END AS 'Length',
       CASE
           WHEN s.DATA_TYPE IN  ('int','bigint','smallint','tinyint','money') THEN NULL
           ELSE s.NUMERIC_SCALE
       END AS 'Precision'
FROM information_schema.columns s
WHERE table_schema = '{XL[Source.Schema]}'
AND table_name = '{XL[Source.Table]}'

Compare the source and target metadata definition with the Metadata comparison Module.

The Module contains the following ModuleAttributes:

Property	Description
DSN	Specify the data source you want to use for your comparison. Enter the Data Source Name specified in the ODBC Data Source Administrator. After a connection is established, this is also used as ConnectionName. This ModuleAttribute has the following sub-Attributes: User ID: User of the database which you want to use Password: Password of the user Make sure the user has at least read access to the system tables.
ConnectionString	Uses the defined connection string instead of the ModuleAttributes DSN, User ID and Password.
Connection	Use this ModuleAttribute if you want to use one of the connections you have defined in the Connection Manager. The Connection ModuleAttribute replaces the ModuleAttributes DSN and ConnectionString. To specify which connection you want to use in your test, click into the Value field and select a connection from the drop-down menu. Note that you can't override the specified connection with dynamic expressions such as buffers or test configuration parameters.
SQL Statement	SQL statement containing the actual, current metadata information (see above).
Columns to ignore	Specify a comma-separated list of columns to exclude from the comparison.
Base Field Definitions	Baseline information from the Vital ChecksMetadata TestSheet: {XL[Source.Fields]} or {XL[Target.Fields]}

Instantiate your TestCase Templates.