The data model is a modelization of the structure of the data in a data source. It contains all the elements necessary to be able to structure the data.
Database models in DOT Extract contain the following elements:
- tables, as well as the different columns that populate each table, and their corresponding data,
- foreign keys, and
- primary keys.
The data model is generated when the data source connection is established. DOT Extract detects the data structure in the data source and creates a model of its structure. The model is stored in the Extract Server in XML format.
When the connection to the data source is modified, the data model is recalculated automatically and updated. If the data structure in the data source is modified, you need to launch the update of the data model manually. Click the Update button to launch the generation of the data model update.
Data models are accessed and managed in the Data Sources view. Click the View Model button to access each data source model.
When the data source connection is created, DOT Extract accesses the database and creates the corresponding data model. The data source creation status is In Progress.
Once the data model is created, the status of the data source is Available. Click the Refresh button in case your data source is not displayed yet in the list, or to check its status at all times.
If DOT Extract cannot access the database or create a data model, the status is Failed. When this is the case, you can click the View log button to display the data source description and the details of the log, providing information about why the connection failed.
If required, you can manually modify the foreign keys in a data model. To do so, click the View button to view the details of the data model, then click the Edit button appearing next to the foreign keys.
Click the Update button to save the changes.
To remove foreign key links, click the Delete button.
DOT Extract enables you to add or remove relations in the data model of a data source. These modifications on the data model do not impact the foreign keys. As the extraction relies on the data model relations, the customization of these relations influences the extraction process and gives you the possibility to control how tables are extracted and how they are linked.
If a table has a relation with another, in a different schema, the data model must be a multi-data model that contains the two schemas.
DOT Extract offers two methods for the addition of a relation:
- Simple: this method is based on column values,
- Complex: this method applies an SQL function on column values, that you set.
Follow the subsequent steps to add a relation in the model of a data source.
Step 1 Open the Data Model view from the Data Sources tab, by clicking on the View Model button of a data source.
Step 2 Click the Add button to open the relation creation wizard and define the required properties of the new relation.
- Enter a unique Name for the new relation. This name must be unique.
- Referenced table / Dependent table
- Select a table from the drop-down list. The tables displayed are the ones available in the data source.
Step 3 For each Referenced and Dependent table, select Simple or Complex.
Data relations via DOT Extract can be set between two simple or two complex or tables, as well as between a simple and complex table. Fields are dynamic and adapted to your choices.
Step 4 Select a column for each table. Select one or several columns for complex relations.
Step 5 In the Function field, set an SQL function to set the relation between the columns.
Result The data relation is created and taken into account in the extraction process.