Data Exchange Configuration (.sdxc)

In the AdapLink Framework Data Exchange Configuration application performs the functions of ETL(Extract, Transform, and Load).

Using AdapLink DX Configuration you can perform the following:

1. Extract - Extracting data from outside sources. AdapLink DX is able to extract data from common data source such as relational databases and flat files. It also supports extraction from various ERP sources such as SAP / JD-Edwards / Mfg-pro / AS400,etc .
2. Transform - The Transformation step in AdapLink DX is referred as Migration Kind. It supports two kinds of migration which are based on :

• Tabular Mapping - (Also Called Column Based Mapping / direct One-to-One Mapping)

  In this migration kind, the transform stage applies a series of business rules or functions to the extracted data at each column level from the source, to derive meaningful set of data for loading into the end target..

• Rules Based Mapping : (Also called Row Based Mapping / RBA Projects)

  In this migration kind, the transform stage applies a series of business rules or functions to the extracted data at each row level from the source, to derive meaningful set of data for loading into the end target.

3. Load - Loading the transformed set of data into the end target (database/Files).

Navigating to Data Exchange Configuration Application

Click on the Data Exchange Configuration application in the ‘Navigation’ window.

Scenario 1 : Using AdapLink DX for a Tabular Based Mapping

An easy way to understand AdapLink DX Configuration is to consider a scenario. Here we will consider a simple scenario on transferring Data from an Excel Spreadsheet (Source) to MS-SQL Database(Target). In this example, we will walk through the different components of the AdapLink DX Configuration.

An Overview of the AdapLink DX Screen

As shown in the screen-shot above, following are the important fields in AdapLink DX profile Editor:

1. Profile List Tab : is the main tab.
2. Profile List box : displays all list of DX-profiles.
3. Profile Snapshot : is a read-only editor, to provide the details of a DX-Profile.
4. Fetch Button : is like a refresh-button to fetch profile list.
5. New/Edit/Copy/Delete : are used to create new and edit DX-profile.
6. Export/Import XML: are used to download DX-profile as configuration into XML format.

Note

Find below step-by-step instructions for AdapLink DX flow to transfer data from excel file to database.

Create a new DX-Profile for the Tabular-Mapping Scenario

1. Start accessing the DX-Profile Editor on the Profile List Tab
2. Click on NEW Button to create a DX-Profile for the following scenario outlined above.

A pop-up screen will appear as shown above to create a new DX-Profile.

3. Provide a Suitable Name for the scenario. (In AdapLink context, we call this as DX-profile)
4. Click OK.

5. The application takes you to Profile Parts of the DX-Config editor.
6. Provide a meaningful description for the new DX-Profile.

Define Source-Master

In this section, we configure the Data-Source for the scenario. In this case its an Excel file.

7. In the Profile-Parts tab
8. Highlight the cursor on the Source Master section.
9. Next, in the Attributes-Details Section, define File-type for the source.
10. Provide the source file-location.

Define Target-Master

In this section, we configure the Target-System for the scenario. In this case its a MS-SQL Database.

Note

• For the Scenario, Create a MS-SQL database “demo”.

• Create a logical system LOG_DEMO to point to the “demo” database above.

11. For defining the Target-system, in the Profile-Parts tab
12. Highlight the cursor on the Target-Master section.
13. Next, in the Attributes-Details Section, assign the Logical-System from the drop-down.

TS-PAIR List

In this section, we establish the relationship between the source and the target.

In AdapLink-DX Context, we call this as TS-PAIR Mapping.

14. To add a TS-Pair, in the Profile-Parts tab
15. Highlight the cursor on the TS-Pair List section.
16. Click ADD button, to add a TS-Pair Mapping for the scenario.

Configure TS-Pair Mapping : Header Information

Some of the important fields in the TS-Pair Header Attributes, are listed below

Note

TS-PAIR header information for this scenario

Field names	Explanation
Migration_Kind	Its of 2 kind. Tabular RBA. In this example we choose Tabular, because its direct column level mapping.
Update_Mode	Update Mode has 3 modes. I (Insert) : Insertion for a new record. M (Modify) : Modify will update existing records,if records does not exist then it inserts new records. Ensure that all the primary key columns are selected U (Update) : Updates existing records.Ensure that all the primary key columns are selected
Package_Size	This sets the batch size for Selecting source data and writing target data in Batch size. (0 means no Batch-size)
Max_Record	This Sets the Maximum output records. For Example, if you set this to 10, then only 10 records are written to the Target)
Insert_Hidden_Cols	All the columns which does not have target mapping will be inserted with default values. (0 for integer , and “” for Strings,Text etc.)
Update_Statistics	This calls the “Update Statistics Query” where applicable. (For example in MS-SQL, this calls the Update Statistics query for better performance)
TS-Pair_Name	In a profile, as you can have multiple TS-Pairs, each should have a Unique name

TS-PAIR Mapping: Configure Source-Meta:

In this section, we define more attributes of the source-system.

Source-File Details

Note

Lets take a look at the source-file. This will help to configure source-meta section.

Configure Source-Meta based on the source-file above

17. Highlight the cursor on Source-Meta section.
18. In the Attribute-details section, enable the Inherit Master data check-box for Source-meta. This step will inherit the configuration that were configured in steps 8, 9 and 10 above.
19. This step will display the Source-master configuration, that were inherited.
20. Click Fetch Button to configure the Sheet Name of the source excel-file. (See the Note above)
21. In the Actual-Table-name, select the sheet-name of the source excel-file.(See the Note above)
22. In the Header-Row, provide the row-number from where the header starts. (See the Note above)
23. In the Data-Row, provide the row-number from where the data needs to be extracted from. (See the Note above)
24. In the Description, provide a meaningful description for the Source-file.

TS-PAIR Mapping: Configure Target-Meta:

In this section, we define more attributes of the Target-meta details.

Note

Lets take a look at the target System. In this case its a database table Employee_Master created under demo database.

Configure Target-Meta based on the target-database table above

25. Highlight the cursor on Target-Meta section.
26. In the Attribute-details section, enable the Inherit Master data check-box for Target-meta. This step will inherit the configuration that were configured in steps 11,12 and 13 above.
27. This step will display the Target-master configuration, that were inherited.
28. Click Fetch Button to get all database objects(tables / Views) based on the target system. (See the Note above)
29. In the Actual-Table-name, select the database table from the drop-down.(See the Note above)
30. In the Description, provide a meaningful description for the Target System.

Tabular Mapping

In this section, we configure one-to-one column mapping between the source-meta and target-meta that were defined/configured in the above steps.

31. To configure tabular-mapping, the control is the profile-parts tab.
32. Highlight the cursor on Tabular-Mapping section.
33. Click ADD button to configure one-to-one column mapping.

34. As seen in the above screen shot, a pop-up appears to automatically add the columns based on same column names. Click YES to confirm.
35. Configure each column name from target to source.

Note

In the Tabular mapping, the convention that is followed for column mapping is Left part of (<-) indicator is the Target and Right part is the source.

36. Details of different fields in the attribute-details section in the screen-shot above.

Field names	Explanation
Target_Field	This field is actual column name in the target database table
Source_Field	This field is actual column name in the source file
Default_Value	This field is used in many ways. Can be used to Set a fixed value as a default value Can be used to process source data by using aFunctions, for example =TRIM(<Source_field>) Can be used to generate any dynamic data using aFunctions, for example =NOW()
Target_Length	This field is used ONLY WITH FIXED WIDTH files. It specifies the width of output Column
Data_Type	This field is used to set the Target field data type. Currently there are following data-types available DT - Date IN - Integer DO - Double LO - Long FL - Float
Target_Format
Source_Format
Skip_if_Empty

Source-Selection (like where clause)

In this section, we configure selection-criterion for the source-data. In AdapLink-DX Context we call it as Source-Selection.

This concept is similar and can be compared to Simple where clause in SQL Context.

Note

Lets take an example, to understand how to configure Source-selection in AdapLink-DX.

For the scenario, lets bring only those records where city = Lakeland as shown below.

Lets configure Source-section in AdapLink-DX from the source-file above

37. Highlight the cursor on No-Source-Selection
38. Click ADD button to add Source-Selection Criterion to the DX-Profile

39. In the profile-parts tab
40. Highlight the cursor on the Source-Criterion
41. In the attributes-details section, configure the Source-Criterion as shown above

40. Click SAVE button, to save the profile.

This completes the steps for configuring AdapLink-DX profile.