Working with Metadata

Semarchy Convergence for DI uses metadata to design, generate and run the data integration processes. For example, the structure of the tables, text or XML files taken into account in the data integration flows.

A metadata file handled by Semarchy Convergence for DI represents generally a data model. For example a database schema, a folder, etc, storing tables, files.
A metadata file is created by connecting to the database server, file system, etc, to retrieve the structure of the tables, files, etc.. This mechanism is called reverse-engineering.

The following sections explain how to create the three main types of metadata files.

Defining a Database Model

Creating and Reversing a Database Model

This process uses a wizard that performs three steps:

  1. Create a new data server
  2. Create a new schema
  3. Reverse-engineer the datastores

To create a new data server:

  1. Click on the New Metadata button in the Project Explorer toolbar. The New Model wizard opens.
  2. In the Choose the type of Metadata tree, select RDBMS > <DBMS Technology> where <DBMS Technology> is the name of the DBMS technology that you want to connect.
  3. Click Next.
  4. Select the parent folder or project for your new resource.
  5. Enter a Metadata Model Name and then click Finish. The metadata file is created and the Server wizard opens.
  6. In the Server Connection page, enter the following information:
  7. (Optional) Modify the following options as needed:
  8. Click the Connect button to validate this connection and then click Next. The Schema Properties page opens.

To create a new schema:

  1. In the Schema Properties page, enter the following information:
  2. Click Next. The Reverse Datastore page opens.

To reverse-engineer the datastores into a schema:

  1. In the Reverse Datastore page, optionally set an object filter. Use the _ and % wildcards to represent one or any number of characters.
  2. Optionally filter the type of objects that you want to reverse-engineer: All, synonyms, tables and views.
  3. Click the Refresh button to refresh the list of datastores.
  4. Select the datastores that you want to reverse engineer in the list.
  5. Click Finish. The reverse-engineering process retrieves the structure of these datastores.
  6. Press CTRL+S to save the editor.

Adding a New Schema

To add a new schema to an existing data server:

  1. In the metadata file editor, select the root node.
  2. Right-click and select Action > Launch DataSchema Wizard.
  3. Follow the steps described in the "To create a new schema" section of Creating and Reversing a Database Model.

Reverse-engineering an Existing Schema

To retrieve metadata changes from an existing schema, or to retrieve new table definitions, you must perform a new reverse-engineering.

To reverse-engineer an existing schema:

  1. In the metadata file editor, select the node corresponding to the schema.
  2. Right-click and select Action > Launch DataSchema Wizard.
  3. Click Next in the first page of the wizard.
  4. On the second page follow the steps described in the "To reverse-engineer the datastores in a schema" section of Creating and Reversing a Database Model.

Table Name Masks

Table name masks define name patterns for the temporary objects created at run-time.

Table Name masks can be any string parameterized using the following variables:

Note that the resulting string must be a valid table name.

Example: L_[targetName]_[number] would create Load tables named L_CUSTOMER_1, L_CUSTOMER_2, etc for a mapping loading the CUSTOMER table.

Work and Reject Schemas Selection

When defining a schema (with optionally a Name for this schema), you optionally refer to two other schemas, the Work Schema and Reject Schema.
These two schemas store respectively temporary load/integration tables (Staging Area) and the error (reject) tables for the data tables stored in the schema being defined. In the mappings, the work schema is also called the Staging Area.The value for these two schemas may be:

Tip: It is recommended to configure by default two separate temporary (for example, SEM_TEMP) and error (for example SEM_ERRORS) schemas for each database server and set them as the Work Schema and the Reject Schema for all the data schemas. This avoids mixing application data (data schemas) and Convergence for DI tables in the same schemas.

Defining a File Model

Creating a File Model

To create a new File metadata file:

  1. Click on the New Metadata button in the Project Explorer toolbar. The New Model wizard opens.
  2. In the Choose the type of Metadata tree, select File > File Server.
  3. Click Next.
  4. Select the parent folder or project for your new resource.
  5. Enter a Metadata Model Name and then click Finish. The editor is created and the File Wizard open automatically.
  6. In the Directory page, provide a user-friendly Name for the directory and select its Path.
  7. Click Next.
  8. In the File Properties page:
    1. Use the Browse button to select the file within the directory and set the Physical Name for the file.
    2. Set a logical Name for the file datastore.
    3. Select the file Type: Delimited or Positional (fixed width fields).
  9. Follow the process corresponding to the file type for reverse-engineering.

Reverse-Engineering a Delimited File

To reverse-engineer a delimited file:

  1. In the File Properties page, use the Refresh button to view the content of the file in the preview. Expand the wizard size to see the file contents.
  2. Set the following parameters to match the file structure:
  3. Click Next.
  4. Click Reverse. If the parameters set in the previous page are correct, the list of columns detected in this file is automatically populated.
  5. Click Finish for finish the reverse-engineering.
  6. Press CTRL+S to save the file.

Reverse-Engineering a Fixed-Width File

To reverse-engineer a fixed-width file:

  1. In the File Properties page, use the Refresh button to view the content of the file in the preview. Expand the wizard size to see the file contents.
  2. Set the following parameters to match the file structure:
  3. Click Next.
  4. Click Refresh to populate the preview.
  5. From this screen, you can use the table to add, move and edit column definitions for the file. As you add columns, the preview shows the position of the columns in the file.
  6. Click Finish to finish the reverse-engineering.
  7. Press CTRL+S to save the file.

Defining an XML Model

To create a new XML metadata file:

  1. Click the New Metadata button in the Project Explorer toolbar. The New Model wizard opens.
  2. In the Choose the type of Metadata tree, select XML > XML Schema.
  3. Click Next.
  4. Select the parent folder or project for your new resource.
  5. Enter a Metadata Model Name and then click Finish. The editor is created and the XML Wizard opens.
  6. In the Name field, enter a name for this schema.
  7. In the XML Path field, enter the full path to the XML file. This file does not need to physically exist at this location if you have the XSD, and can be generated as part of a data integration process.
  8. In the XSD Path field, enter the full path to the XSD describing the XML file. If this XSD does not exist, click Generate to generate an XSD from the content of the XML file provided in the XML Path.
  9. Click Refresh and then select the root element for this schema. If the XSD has several root nodes, it is possible to repeat this operation to reverse-engineer all the hierarchies of elements stored in the XML file. Each of these hierarchies can point to a different XML file specified in the properties of the element node.
  10. Click Reverse. The reverse-engineering process retrieves the XML structure from the XSD.
  11. Click Finish to close the wizard and return to the editor.
  12. Press CTRL+S to save the editor.

Defining a Generic Model

A Generic model is useful when you want to have custom Metadata available in order to parameterize your developments.

To define a Generic Model :

  1. Click the New Metadata button in the Project Explorer toolbar. The New Model wizard opens.
  2. In the Choose the type of Metadata tree, select Generic > Element.
  3. Click Next.
  4. Select the parent folder or project for your new resource.
  5. Enter a File Name for your new metadata file and then click Finish. The metadata file is created and the editor for this file opens.
  6. Select the Element node and enter the Name for this element in the Properties view.

A Generic Model is a hierarchy of Elements and Attributes. The Attribute values can be retrieved for an element thanks to the Semarchy Convergence for DI usual XPath syntax.

To create a new Element :

  1. Right-Click on the parent element.
  2. Select New > Element
  3. In the Properties view, enter the name of the new Element.

To add an attribute to an Element

  1. Right-Click on the parent element.
  2. Select New > Attribute
  3. In the Properties view enter the Name and the Value of the Attribute. This name will be useful to retrieve the value of your attribute.

Working with Configurations

Configurations allow to parameterize metadata for a given context. For example, a single data model declared in Semarchy Convergence for DI may have two configurations, Production and Development. Used in the Development configuration it would point to a development server and used in the Production configuration it would point to a production server. Both servers contain the same data structures (as defined in the model), but not the same data, and have different connection information.

Creating a Configuration

To create a configuration:

  1. In the Semarchy Convergence for DI Designer toolbar, click the Edit button.
  2. The Configuration Definition editor ( conf.cfc) opens.
  3. Right-click the root node ( Cfc), then select New > Configuration.
  4. In the Properties view, enter the new configuration’s properties:
  5. Press CTRL+S to save the configuration.

Using Configurations

In a metadata file, it is possible to define configuration-specific values for certain properties. The following section describes the most common usage of the configurations in metadata files.

Using Configuration for Databases

In databases, you can customize the connection information to the data server as well as the data schema definitions using configuration.

To create a data server configuration:

  1. In the database metadata file editor, select the root node corresponding to your data server.
  2. Right-click and select New > DataServer Configuration.
  3. In the Properties view:
  4. Press CTRL+S to save the database metadata file.

To create a data schema configuration:

  1. In the database metadata file editor, select the node corresponding to your data schema.
  2. Right-click and select New > DataServer Configuration.
  3. In the Properties view:
  4. Press CTRL+S to save the database metadata file.

Note: You can define configurations at all levels in the database metadata file for example to define configuration-specific structural features for the datastores, columns, etc.

Using Configuration for Files

In files, you can customize the directory location as well as the file names depending on the configuration using a directory or a file configuration.

For example:

To create a directory configuration:

  1. In the File metadata file editor, select the node corresponding to your directory.
  2. Right-click and select New > Directory Configuration.
  3. In the Properties view:
  4. Press CTRL+S to save the File metadata file.

To create a file configuration:

  1. In the File metadata file editor, select the node corresponding to your file.
  2. Right-click and select New > File Configuration.
  3. In the Properties view:
  4. Press CTRL+S to save the File metadata file.

Note: You can define configurations at all levels in the File metadata file for example to define configuration-specific structural features for flat files.

Using Configuration for XML

In XML files, you can customize the path of the XML and XSD files depending on the configuration using a schema configuration.

To create a schema configuration:

  1. In the XML metadata file editor, select the root node.
  2. Right-click and select New > Schema Configuration.
  3. In the Properties view:
  4. Press CTRL+S to save the XML metadata file.

Note: You can define configurations at all levels in the XML metadata file for example to define configuration-specific structural features in the XML file.