Managing SQL databases - 6.1

Talend MDM Platform Installation Guide

EnrichVersion
6.1
EnrichProdName
Talend MDM Platform
task
Installation and Upgrade
EnrichPlatform
Talend Activity Monitoring Console
Talend Administration Center
Talend Artifact Repository
Talend CommandLine
Talend DQ Portal
Talend ESB
Talend Identity Management
Talend Installer
Talend JobServer
Talend Log Server
Talend MDM Server
Talend MDM Web UI
Talend Project Audit
Talend Repository Manager
Talend Runtime
Talend SAP RFC Server
Talend Studio

You can store the MDM repository and your master data records in a SQL database. For licensing reasons, if you want to use any of the supported SQL databases other than the H2 database embedded with the product, you need to download and manually install the database drivers in <TomcatPath>\webapps\talendmdm\WEB-INF\lib before using Talend products.

For more information on compatible databases, see Compatible Databases.

Connecting to the H2 embedded database

MDM Server includes a web console that you can use to access the H2 embedded database directly in order to perform administration tasks. For more information about how to use the H2 database and web console, refer to the H2 database documentation at http://www.h2database.com.

To connect to the H2 web console, do the following.

  1. In a web browser, enter the address of the H2 console (for example, http://localhost:8180/talendmdm/h2console/console).

  2. In the login screen that opens, enter the Username and Password for an MDM user with administrator rights (for example, administrator/administrator, or admin/talend), and then click Login.

    The H2 console connection screen opens.

  3. Enter the connection information relative to your database, and then click Connect.

    The H2 console opens with access to the MDM database.

Working with a Staging Area

Talend MDM implementations where master data records are stored in a SQL database include a Staging Area in which MDM users can load data that is then asynchronously validated.

This Staging Area is a mirror of the SQL storage area containing the master data records. The only difference is that the staging area database does not include constraints in its schema (such as not null values or foreign key integrity). However, this does not mean that records with invalid foreign keys can be loaded into the Master database if Foreign Key Integrity is enabled.

For each data container (a data container being an instance of a SQL storage), a second SQL storage also exists which is the Staging Area for the data container in question. For example, if you have a data container called Product, this means that a data container called Product#STAGING also exists.

MDM users can load data into the Staging Area in the following ways:

  • using SQL components: execute INSERT statements on the database, using JDBC or Data Integration components

  • using MDM components: use the tMDMOuput and tMDMBulkload components and specify that the data container is "data_container_name#STAGING" (for example, "Product#STAGING" instead of "Product")

    Note that tMDMOutput does not allow the insertion or update of non-valid data.

Talend MDM provides both a user interface and REST access to trigger the transfer from the Staging Area to the master database. This transfer is called a staging area validation task because it includes a step where records from the Staging Area are validated against MDM validation rules (such as XSD, Security and Validation rules).

For more details on how to trigger this transfer from the Talend MDM Web User Interface, see the Talend MDM Web User Interface Guide.

Key differences between Staging Area and master database

There are relatively few differences between the database schema of the master database and the Staging Area.

Difference

Description

No Foreign Key relations

In the Staging Area, all Foreign Key relations are disabled. This is to allow users to load data without taking into account the relationships between entities.

Additional column for source

In the Staging Area, the database schema has an additional text column where MDM users can provide details about the origin of the record. This column is free-form and is not mandatory.

For example, when loading data in the Staging Area, a user may indicate that records with the ID 1 come from SAP and the records with the ID 2 come from another legacy system.

Additional column for status

In the Staging Area, this column is used to store actions carried out by MDM on the staging record. The column contains a code:

  • "000" or null means "new record".

  • "2nn" values (200, 201...) mean the record successfully passed a step of record validation.

  • "4nn" values (400, 401...) mean the record failed to pass a step of record validation.