Welcome to Semarchy xDI!

This tutorial shows you how to create your first data flow with Semarchy xDI Designer. This integration flow will load data from a file to a database.

What you'll learn

Before you start

For this tutorial, we assume that you have already installed Semarchy xDI Designer.

If you have not yet installed the Designer, refer to the installation guide for detailed instructions.

Enjoy this tutorial!

Before designing your first data flow, you must start the tutorial environment, composed of

To start the tutorial environment:

  1. Open the Runtime view and click the Environment button.

  1. The Environment dialog opens. Click the Start local Runtime button. The runtime process starts in a shell window.
  1. Click the Start demo Databases button. The tutorial databases are started in separate shell windows.
  2. Click OK to close the dialog.
  3. In the Runtime view, select Connect to connect to the local runtime you just started. The icon appears when the connection is established.

  1. Make sure that the Refresh Diagram checkbox is selected so that xDI Designer refreshes its views and editors (statistics, variables, colors, statuses...) in real-time during the execution of your data flows.

In the next step, you will create your first project.

A Project is a directory inside your xDI workspace that contains your development resources. All the resources in a project (metadata, mappings, processes, etc) are stored in this directory and can be archived and manipulated just like standard files. You can also organize them into folders.

Create the project for this tutorial:

  1. In the File menu, select New > Project... The New Project wizard opens.

  1. Expand the General node, select Project, and then click Next.

  1. In the Project name field, enter Tutorial - Fundamentals and then click Finish.

In the next step, you will add the first metadata to your project.

Semarchy xDI leverages Metadata to design, generate and run data integration processes. Metadata typically represent the applications, databases, APIs or file directories containing data that want to integrate.

The basic element in a metadata is called a datastore. A datastore corresponds to a data structure, for example, a table, a file, etc that you integrate. Datastores are used as sources and/or targets in mappings.

You define the structure of an xDI metadata by connecting to the database server, file system, etc, and retrieving the structure of its tables, files, etc. This operation is called reverse-engineering.

In this step, you will:

Create the metadata

First, create the metadata in xDI:

  1. In the Project Explorer view, right-click the Tutorial – Fundamentals project.
  2. Select New > Metadata... - The New Metadata wizard opens.

  1. Select Hypersonic SQL in the list of technologies and then click Next.

  1. Enter Datamart in the Metadata Model name and then click Next.

  1. In the Module Selection/Creation step, select the HSQL [0] Type (it is selected by default), select <new HSQL Module> in the Module pane, and finally click Finish to create the module and the metadata.

  1. The metadata is created and the Server Wizard opens.

In the next section, you will connect to the database and reverse-engineer its tables.

Define the connection and schemas properties

The Server Wizard allows connecting to the database server and entering the properties for the schemas xDI will use on this database.

  1. On the Connection page of the wizard, enter the following values and then click Connect to test your connection.

  1. Semarchy xDI Designer opens a JDBC connection to the database. Click Next to define the schema containing the tables.
  2. In the properties page, click Refresh Values to fetch the list of schemas in this database.
  3. Select the HOTEL_DATAMART Schema Name and then click Next to launch the reverse-engineering process.

Reverse-engineer the tables

On the Reverse page:

  1. Click Refresh to retrieve the list of tables from the HOTEL_DATAMART schema.
  2. Click Select All and then click Finish to start the reverse-engineering process and complete the wizard.

Review the tables

The wizard closes and the metadata editor opens.

The asterisk next to the metadata's file name indicates that the metadata is modified but not saved. Click the icon in the Designer toolbar, or press CTRL+S to save the metadata.

Explore the data model you reverse-engineered:

  1. In the metadata editor, Expand the Datamart root node and then expand the HOTEL_DATAMART schema node.
  2. The tables you reverse-engineered are now available as datastores in the metadata.
  3. Expand each datastore to review its structure: columns, primary key, foreign keys, etc.

Congratulations

Great job! You have successfully created your first metadata and reverse-engineered its structure.

In the next step, you will create another metadata for a source file.

You will now create a metadata for a local file directory. Files in this directory will be available as datastores in the mappings.

Create the metadata

Start by creating the metadata:

  1. In the Project Explorer view, right-click the Tutorial – Fundamentals project.
  2. Select New > Metadata...
  3. Select the File metadata type and then click Next.

  1. Set the Metadata Model name to Reference Files and click Finish.
    The File Wizard wizard opens.
  2. Enter the following Name for the root directory: Reference Files Folder.
  1. Click Browse to select the local folder where the tutorial files are stored. This directory is located in your xDI Designer installation:
  1. Click Next.

You can now define the file format.

Define the file format

On the Properties page of the wizard:

  1. Click Browse and select the DiscountRanges.txt file. This file contains comma-separated fields.
  2. Make sure that the selected Type is DELIMITED.
  3. Set the Field Separator to , (a single comma).
  4. Click Refresh to preview the file contents.

  1. The Preview shows that the file contains a header line with the name of each field.
  2. Set the Header Line Position to 1 to use the first line of the file as the header.
  3. Click Refresh again. The first line is no longer visible in the preview.
  4. Click Next to define the column for this datastore.

Reverse-engineer the columns

On the Reverse page of the wizard:

  1. Click Reverse in order to reverse-engineer the file columns.
  2. Check that the columns are correctly defined as shown below

  1. Click Finish to complete the definition of this datastore.

Review the file datastore

The wizard closes and the metadata editor opens, unsaved. Click the icon in the Designer toolbar or press CTRL+S to save the metadata.

To review the data in the file datastore:

  1. In the metadata editor, expand the Server node and then the Reference Files Folder node.
  2. Right-click the DiscountRanges datastore and select Actions > Consult Data (console).
  3. The data in the file appear in the Console view.

Semarchy xDI Designer also includes a feature to query datastores, using SQL:

  1. Right-click DiscountRanges in the metadata editor and select Actions > Consult Data.
  2. The Query Editor for DiscountRanges opens and shows the query used to retrieve data from the file.
  3. Click or press CTRL+Enter to run the query.

In the next step, you will design a first mapping that uses the two metadata you have created.

A mapping defines how to load data from source datastores to target datastores, possibly with transformation. This definition includes templates that generate an optimized data integration process.

In this step, you will create a simple mapping to load the DIM_DISCOUNT table with data from the DiscountRanges file.

Create the mapping

Create the mapping:

  1. In the Project Explorer view, right-click the Tutorial – Fundamentals project
  2. Choose New > Mapping...
  3. Set the File name to Load DIM_DISCOUNT and then click Finish.
  4. The mapping is created and the mapping editor opens.

Add the datastores

You must first add the datastores to the mapping:

  1. In the Project Explorer view, expand Tutorial – Fundamentals > Datamart > Datamart > HOTEL_DATAMART.
  2. Expand the HOTEL_DATAMART node and then drag and drop the DIM_DISCOUNT datastore from the Project Explorer view into the mapping editor.
    The datasource is added to the mapping editor.

  1. In the Project Explorer, expand Reference Files > Server > Reference Files Folder and then drag and drop the DiscountRanges datastore into the mapping editor.

In this mapping, DIM_DISCOUNT will be the "Target" datastore, and DiscountRanges the "Source" datastore.

Define the column mappings

You will now define in the mapping the expressions to load target columns from source data.

For this first mapping, the expressions are simple: each column of the DIM_DISCOUNT table is populated with the value of a corresponding column in the DiscountRanges file, as described below.

Target column

Expression

DIS_RANGE

DiscountRanges.range

DIS_MIN

DiscountRanges.min

DIS_MAX

DiscountRanges.max

To define the transformation expressions:

  1. In the mapping diagram, drag and drop the range column from the DiscountRange datastore onto the DIS_RANGE column of the DIM_DISCOUNT datastore.
  2. A dialog opens to select the type of link you want to define between the datastores. Select Map - current field.

  1. Repeat the previous operation for the min and max columns. You will not have to select the type of link since it was already done.
  2. Click the icon in the Designer toolbar or press CTRL+S to save the mapping.

  1. Click the icon in the Designer toolbar or press CTRL+S to save the mapping.

Your mapping is now ready to be run.

Your mapping will run in the local runtime you started during the Start the tutorial environment step.

To run the mapping:

  1. Make sure that you have saved the mapping.
  2. Right-click on an empty area of the mapping editor and select Run in the context menu.

  1. Semarchy xDI Designer runs the mapping and opens a new tab showing the generated process. The process is made up of steps. The color of each step indicates its execution status:

  1. When the execution is finished, check that all steps appear in blue (finished successfully).

Review the Results

The execution of this mapping produced a Session Log in the Runtime's Session Logs database. This session log contains detailed information on the executed steps.

You can review the session logs in the Designer's Sessions view. The mapping that you ran appears there.

Select the Statistic view, which shows the latest execution statistics. It shows 18 total rows and 6 rows inserted into the target datastore

Run the mapping a second time. The statistics now differ, with no row inserted or updated. The source data has not changed, so no change is applied to the target.

Check the data in the target table:

  1. In the mapping editor, right-click the target datastore (DIM_DISCOUNT) and then select Action > Consult data
  2. The Query Editor opens and is initialized with a select query. Run it by clicking the Run button in the editor toolbar or by pressing CTRL+Enter.

Congratulations! You have successfully created and tested your first mapping. In the next and last step of this tutorial, you will organize metadata and mappings in your workspace.

Resources in Semarchy xDI are organized in projects and folders.

All the metadata and the mapping you have created until now are located in the root folder of the Tutorial – Fundamentals project.

In this section, you will:

Create a distinct project for metadata

Grouping metadata in a dedicated project is a best practice since metadata can be shared and re-used by many projects.

  1. Follow the same instructions as in the Create the tutorial project step to create a new project: Tutorial - Common Metadata
  2. In the Project Explorer, select the Datamart metadata, hold the CTRL key (Command key under MacOS), select the Reference Files metadata, and then drag and drop the selection into the Tutorial - Common Metadata project.

  1. The new Tutorial - Common Metadata project now contains all the metadata you have created:

Organize development resources with projects and folders

You will now create 2 folders under the Tutorial – Fundamentals project to store mappings and processes.

  1. In the Project Explorer, right-click the Tutorial – Fundamentals project, select New > Folder.
  2. Set the Folder name to Mappings and click Finish.
  3. In the Project Explorer, drag and drop the Load DIM_DISCOUNT mapping into the Mappings folder.
  4. Follow the same steps to create a Processes folder.

You workspace now shows the following structure:

Congratulations! You have successfully created your first data flow.

What we've covered

What's next?

In the next tutorial unit, Work with mappings, you will learn about the Semarchy xDM's advanced mapping features such as filters, joins, and stages.