Log out?

7. Source Transformation / Data Mapping (Tutorial)

eiConsole v.19R1
Tutorial 90 mins

eiConsole Foundation

Source Transformation / Data Mapping

You will need to download sample files to do this tutorial. If you have not done so yet, please click this LINK.

Note: The Tutorials in eiConsole Foundation, 1-13, are modular and are designed to be used in the sequence presented.

Click the data mapping icon to get started in the eiConsole.

Next, the eiConsole’s Data Mapper opens. We’ll use the Data Mapper to define the logical mapping between the flat file source and the XML standard target. When you open the Data Mapper you will see 3 panels. The panel on the left will represent the logical structure of the source data. The panel on the right will represent the logical structure of the target data. The panel in the middle will be used to generate the logical mapping between the two.

To load in the Source format, choose the Open source format icon.

Select the source file format in the data mapper.

Select the Flat File and double click. This will allow you to leverage the file specification created in the File Spec Editor to populate the tree on the left-hand side of the screen.

Click Add for the Source File Format selected.

When the Select Format dialogue opens, click Add.

Save the source file format selected in the data mapper to the format folder.

Navigate to your Getting-Started-Project Working Directory.

Select the format file format for the data mapper.

Select the formats folder. Underneath that folder, you will find the FlatFileToXML folder.

Select the FlatFileToXML folder and double click to open. This contains the file specification that you created in the previous stage. Select FlatFileSpec.xml and click Open.

Select the file specification file created previously.

When the Select Format dialogue opens click Read Format.

Select the Read Format Button in the Data Mapper.

The left-hand side of the tree (the Source System) is populated with the logical structure of your inbound tab-delimited file.

To expand the tree, double click on the Record node.

To do the same with the Target format, choose the Open Target format icon on the right.

Select the file format to read into the data mapper.

This time, read the Target format from an XML schema. In the Select Format dialogue from the drop-down menu, select and double click the XSD (Schema).

Read the data target format from an XML Schema file.

When the Select format dialog opens you will be prompted to select a schema file. Click Browse.

Select the Schema file from the drop-down.

Navigate to c:\Program Files\PilotFish Technology\Samples\data\ folder or the location of the sample folder of your distribution. Select it and double click to open. Next double click the data folder.

Open the sample data file to be used in the eiConsole tutorial.

The data folder will include an XML schema that describes a standard mechanism for expressing a photo album as XML – the photo-album.xsd file. Select the file and then click Open.

Read the Schema File format into the Data Mapper.

When the Select Format dialogue opens again, click Read Format.

The tree on the right represents the logical XML structure of the output file.

The tree on the right now represents the logical structure of the XML format that you would like to output.

Double click the nodes including Picture+, Photographer and LocationTaken to fully expand the tree. You’ll need the tree fully expanded to do your mapping.

Click the nodes to expand the detail in the records.

You’ll now create the mapping between the two. To start this mapping, select the “Mapping” tab in the Data Mapper, then you’ll need to create the structural format of the output. To do this, you want to create one logical structure for each inbound Data Element, which is the root element of the XMLized flat data.

To begin, drag and drop the XCSData element (Source, left column) onto the stylesheet XSLT element (center panel). The blue XCSData element will now appear below the stylesheet element, as shown above.

Note: The red V that appears on your node, indicating a mapped element.

Note: You’ll want to drag and drop your element on top of the style sheet element so that you see the gray bar above it and then release. If you make a mistake, you can delete mapping elements by right-clicking and selecting “Delete” from the context menu.

Drag n Drop fields into the data mapper central panel.

At the root of the output (green elements in the right panel) you want the PhotoAlbum tag. Drag and drop this tag onto the XCSData element in the middle panel.

Drag n Drop fields into the data mapper central panel.

You will also want to add a Title. Drag and drop the Title element from the right column (target) onto the PhotoAlbum element in the middle panel. When done, it should look like the above mapping.

Data Mapping Route Interface eiConsole

Now you will have a number of records in the inbound file, each of which needs to be mapped to a picture element in the XML output. To handle this looping structure, use the XSLT palette (above the main mapping panel).

Select the Flow Control tab in the XSLT palette. Drag and drop the for-each element onto the PhotoAlbum element. You’ll now see the blue for-each element added to the mapping.

Note: The XSLT palette of structures and functions at the top allows you to do anything you can do by programming via the eiConsole’s drag & drop process.

Data mapper examples of drag n drop fields from the source.

Next, you will want to iterate over each inbound record. Select Record from the source and drag and drop it onto the [@select] element.

Note: Elements are placed at valid locations automatically when mapped.

Drag fields onto the center panel to set up looping structure.

For each Record, you will want to generate a Picture element. Drag and drop the green Picture+ element from the tree on the right panel onto the blue for-each element in the middle panel.

Drag fields onto the center panel to set up looping structure.

For each Picture, include a Title, FileName and Photographer element. Drag and drop Title, FileName, and Photographer from the right panel onto the Picture element.

Note: You can do this one at a time or as a group.

Data Mapping Route Interface eiConsole

Underneath Photographer, you will want to include the FirstName and LastName. You can drag and drop these elements individually or together onto Photographer.

Drag n drop fields from the panel on the right in the data mapper.

You will also include some aspects of the address of the Photographer. Drag and drop Address onto Photographer. For the next mapping, you will need to expand this Address node.

Drag n drop fields from the panel on the right in the data mapper.

Then drag and drop Address 1, City and State onto Address. When finished, all will show red Vs on the nodes. Your mapping should now look like the above screen.

Drag n drop fields from the panel on the right in the data mapper.

Next, include 2 phone numbers for the Photographer: home and work. Drag and drop PhoneNumber onto Photographer twice. Notice that they appear at the end of your mapping.

Drag n drop fields from the panel on the right in the data mapper.

Next, drag and drop the type attribute (found under PhoneNumber in the target format) on top of both PhoneNumbers in the mapping. To view the Type attribute, you will need to expand the target tree by clicking the PhoneNumber node. Your mapping should now look like the mapping above.

Drag n drop fields from the panel on the right in the data mapper.

You will also want to add information about where the photograph was taken. Drag and drop the LocationTaken element onto Picture. Notice the element is placed automatically at the valid location, in this instance under the Type element at the very bottom of the mapping.

Drag n drop fields from the panel on the right in the data mapper.

Then drop City and Country (from under LocationTaken in the target format) to LocationTaken in the mapping.

Associate source fields with target data fields in the data mapper example.


You have now created the structure for which you plan to populate from values in the source. The next step is to associate source fields with target data elements.

Click the Record node, in the Source panel to expand the tree. The first information to map falls under Picture in the mapping (center panel). You will map Photo-Name to Title. Drag and drop Photo-Name from the source format (left panel) onto Title in the mapping. You will see the blue element appear underneath the green Title and blue Photo-Name elements.

Create a similar association between File-Name in the target and File-Name in the mapping.

Associate source fields with target data fields in the data mapper example.

From the source drag and drop Photographer-First on top of FirstName (under Photographer), then drag and drop Photographer-Last on top of LastName (under Photographer) in the center panel.

Associate source fields with target data fields in the data mapper example.

From the source drag and drop Address on top of Address1 in the mapping. From the source also drag and drop City on top of City, and State on top of State.

Associate source fields with target data fields in the data mapper example.

There are 2 phone number elements to map: Home and Work. First you will need to scroll down to the bottom of the center panel to have access to the elements. Next, drag and drop the Home-Phone element from the source to the first PhoneNumber in the map and the Work-Phone element from the source to the second PhoneNumber in the map. Your mapping should now look like the above.

Associate source fields with target data fields in the data mapper example.

For LocationTaken in the mapping, map Dive-Site from the source to the City and the Dive-Country from the source to the Country. Leave Dive-Depth unmapped. (You may have to scroll to have access to these elements.) When you are done, your mapping should now look like the above.

Clarify the meaning of fields in the data mapper.

You have now mapped values from the source onto values from the target. There are several text constants that need to be added to clarify the meaning of various fields. For example, Title is unmapped. Scroll up in the center panel. Right click on Title and select Add Text Constant from the drop down.

Set identified field to a constant value in the data mapper.

When the Please set constant value dialogue panel opens, enter “My Photo Album” and click OK.

Identify the attribute type in the data mapper.

Now, scroll down to the PhoneNumber section of the mapping. You will see the type attribute, which is used to indicate the type of phone number that each PhoneNumber element represents. Double click the first PhoneNumber type attribute. In the Value text-box enter 'H' to represent Home (use caps and make sure you use single quotes) and hit enter/return.

Do the same for the Work PhoneNumber. In the character box enter 'W' and hit enter/return.

Identify the attribute type in the data mapper.

When you are done your mapping should look like the above.

Your logical data map is now complete. Hit the Save icon at the top.

Name the data mapping and save it to a named file.

When the Save File dialogue opens enter “PhotoAlbumFlatXMLToStandardXML” and click OK.

Now you are ready to move on to the next step. The Route Stage. Next, click the Return to Console icon at the very top.

    to post comments

    This is a unique website which will require a more modern browser to work! Please upgrade today!

    Thanks!

    Our editors are notified.

    Close