Tutorials

Objectives of the tutorial

The objective of this tutorial is to get to know the functionalities of Stambia regarding handling, reading and writing XML files.
We will study the following points:

• Creating and reversing XML metadata
• Creating Mappings with XML files as a source
• Creating Mappings that write XML files

The following technologies will be used:

• Databases
• XSD and XML files

Prerequisites

This tutorial has been designed to be done after the Tutorial: fundamentals of Stambia. The notions learned in this first tutorial are supposed to be known and the environment we will use is the same. Please pay attention to the following points:

• The three Metadata files of the Tutorial: fundamentals of Stambia must have been properly reversed and be grouped in the project Tutorial – Common Metadata
• The templates must have been imported into the global project

Environment initialization

Create the Tutorial - Working with XML Files Project, which will serve to store all the elements of this tutorial.

Inside this project, create a Mappings folder which will serve to store the various Mappings that you will be creating.

Finally, start the demonstration environment:

1. Go to the Runtime view
2. Click on Environment
3. Click on Start local Runtime and Start demo Database
4. Click on OK to close this window
5. In the Runtime view, check the box Connect and verify that Refresh Diagram is also checked

Tip:
If you stop the demo environment or if you stop your machine, do not forget to restart the demo environment before you resume work on the tutorial at the place where you stopped.

Important:
Be careful to stop properly the demo databases when closing the environment, with the use of the Stop demo Databases button.
Closing them violently may lead to the instability of the demo databases.
If you are facing instabilities with it, if the connection is running indefinitely, or if you dropped or remove by mistake tables or data, you can at any time re-initialize the demo environment.
For this close the Designer and Runtime, and then delete the <Designer Installation>/stambiaRuntime/samples folder.
At the next Designer restart, it will be re-created with default settings and data.

Loading a table from an XML file

Reversing the geography file

In Stambia, the same principles guide the handling of objects, whether XML files, flat files or databases. Metadata are used to declare their structure, then the details of their structure are fetched by a Reverse engineering.

Start by creating the Metadata file. This creation will be based on an XSD file (XML Schema Definition) which contains the definition of the XML file’s structure:

1. Right-click on the Tutorial – Common Metadata Project
2. Select New then Metadata
3. In the list of technologies, select Xml Schema then click on Next
4. Enter the name of the Metadata file: Geography
5. Click on Finish

The XML files Reverse engineering assistant automatically opens:

1. In the Name field, enter the value: geographySchema
2. Click on the Browse button which can be found next to Default Xml Path, then browse till the directory where Stambia was installed. Continue to navigate until the directory stambiaRuntime/samples/xml
3. Select the file geography.xml
4. Then click on the Browse button which can be found next to Xsd Path, and browse till the directory where Stambia was installed. Continue to navigate until the directory stambiaRuntime/samples/xml
5. Select the file geography.xsd
6. Click on Refresh
7. Select the Root Element geography
8. You can now click on Reverse then on Finish

Warning: The Generate button allows you to generate an XSD file from an XML file. This functionality is useful if your XML file has no pertaining XSD file, but, if you click on this button when your XSD file is already defined, it will be overwritten. So be careful not to click on this Generate button during this tutorial.

Stambia then does the Reverse engineering based on the structure defined in the geography.xsd file. This structure will be represented as a tree in the Metadata file.

Save this Metadata file, then consult the content of the XML file:

1. Right-click on the geography element
2. Select Actions then Open Xml Editor
3. Click on Format Xml File in order to automatically format the content
4. Finally, check the Syntax Coloring option to use the syntax coloring specific to XML files

You can now see that the content of the XML file is similar to the hierarchy reversed by Stambia. The geography element contains the list of all the states (state elements), which contain the list of each of their cities (city elements).

Loading the DIM_GEOGRAPHY table

Creating the Mapping

Create a new Mapping: Load DIM_GEOGRAPHY in the Mappings folder you created earlier. Add the target datastore DIM_GEOGRAPHY.

Reminder: The DIM_GEOGRAPHY datastore was reversed in the Tutorial: fundamentals of Stambia. It can be found in the Tutorial – Common Metadata Project and in the Datamart Metadata file.

Now you can add the root element of the XML file to be used as a source for the Mapping:

• Drag and Drop the geography element on the Mapping background

You can notice that by default the nodes of the source datastore are collapsed. You can expand the state node in order to see the other elements.

You can also expand or collapse all the nodes:

1. Select the Datastore
2. Click on the or icons that appear to the left of the Datastore.

Create the following transformation rules:

You can notice that you don’t need to create a join because Stambia uses the parent-child relationship of the XML structure to naturally link the elements.

Warning: The business rule that loads the GEO_KEY_ID column must only be executed for insertions. Be careful to deactivate it for Updates.

You can now save and execute the Mapping.

Analyzing the result of the execution

Go to the Statistic view to check the statistics of the execution:

Information: You can notice that new statistics have appeared: XML_NB_ATTRIBUTES indicates the number of XML attributes that were read, XML_NB_ELEMENTS indicates the number of XML elements that were read during the Session execution.

Warning: if the Tutorial: fundamentals of Stambia was not executed previously, the 41 693 Updates will be replaced by 41 693 Inserts.

Loading several tables from an XML file

Reversing the marketing campaigns file

Create a Metadata file with the following properties:

Information: You can notice that the XSD file contains three root elements. Stambia is able to manage XSD files that contain several root elements. Also, the root node Hotel Management will be used later on in this tutorial.

Since the hotelManagement.xsd file will be used for several root elements, we will declare the XML file at the root node level instead of in the schema itself:

1. Click on the marketingCampaign node
2. Open the Properties view
3. In the Xml Path field, enter the path to the XML file called <Setup folder>/stambiaRuntime/samples/xml/marketingCampaign.xml.

Loading several tables from the marketing campaigns file

First load of the T_CUSTOMER table

Create a Mapping with the following properties:

Start by creating the transformation business rules for T_CUSTOMER using the customer element found in phoningCampaign:

Adding a Stage to reuse the loading business rules

In order to make a union between the customer data from the phoning campaigns and the customer data from the mailing campaigns, create a Stage between marketingCampaign and T_CUSTOMER by dragging-and-dropping the HOTEL_MANAGEMENT schema onto the link between these two Datastores.

A wizard opens automatically. Verify that Initialize from Target is selected, then click Ok.

This creates a new Stage between the two Datastores that reuses the target’s columns and transfers the target’s business rules to the Stage. This is an easy way to initialize a Stage in an existing Mapping.

Give a name to this Stage:

1. Click on the newly created Stage
2. In the Properties view, enter customers in the Alias field

Add a new set to this Stage. Rename the former set phoneCampaign and the new set mailCampaign.

Here is the business rule to unite the data:

[phoneCampaign] union [mailCampaign]

Tip: If you have difficulties creating a new set, you can refer to the chapter Mutualizing transformations in Tutorial: Stambia Fundamentals.

Fill in the transformation business rules for the mailCampaign set using the customer element found in mailingCampaign:

Loading the T_TITLE table and defining the integration sequence

In order to reuse the same Stage to load T_TITLE you need to add a new field to it:

1. Select the customers Stage
2. Click on the button that appears to the left of the Stage
3. Select this new field
4. Enter TIT_NAME in the Alias field in the Properties view

Load this field TIT_NAME with the following business rules:

Create the T_TITLE transformation business rules:

In order to avoid duplicated data during the integration of T_TITLE check the Use Distinct option on the Integration Template of the T_TITLE Datastore.

1. Select the T_TITLE Datastore
2. In the Properties view select the Advanced tab
3. Enter 1 in the Integration Sequence field
4. Select the T_CUSTOMER Datastore
5. In the Properties view, select the Advanced tab
6. Enter 2 in the Integration Sequence tab

By doing this, in addition to the natural dependence between the source and target Datastores materialized by the Mapping’s arrows, you have defined a functional dependence between T_TITLE and T_CUSTOMER materialized by this Integration Sequence: T_TITLE will indeed be loaded before T_CUSTOMER.

Loading the T_PHONE_TYPE table

Create the transformation business rules for T_PHONE_TYPE:

Here again, the same phoneType may be used for several phone numbers. So the data must be deduplicated: check the Use Distinct parameter in the Integration Template of T_PHONE_TYPE.

Loading the T_PHONE table

Create the transformation business rules for T_PHONE:

Loading the T_EMAIL table

Create the transformation business rules for T_EMAIL:

Setting a sequence for all integrations

In order to respect the foreign keys between the various tables, specify the following Integration Sequence:

You may save and execute this Mapping.

Analyzing the result of the execution

Go to the Statistic view to check the statistics of the execution:

Loading a simple XML structure

Reversing the simplified customer file

The hotel’s XSD file contains several root elements. You have already reversed the marketingCampaign element that contained a description of the marketing campaigns files. You shall now reverse the customerSummary element which is a simplified description structure of the hotel’s customers:

1. Open the Metadata file Hotel XML Files
2. Right-click on the Hotel Management schema
3. Select Actions then Launch Schema wizard
4. Click on Refresh
5. In the Root Element dropdown list, select the customerSummary element
6. Click on Reverse

Your Metadata file has now been enriched with a new root element and its hierarchy. Define the path to the associated XML file:

1. Click on the customerSummary element
2. Open the Properties view
3. In the Xml Path field, enter the path to the XML file called customerSummary.xml.

Information: You can store this file in the temporary folder of your computer, or in a folder of your desktop. For example, under Linux, you could give the following path: /tmp/customerSummary.xml

Loading the customerSummary.xml file

Creating the Mapping

Create a Mapping with the following properties:

The transformation business rules are:

Important: when loading XML files, functional keys indicate an expression enabling iteration on an element. For example in this Mapping, the functional key on the customer element is CUS_ID. This indicates that there will be one customer element created for each different value of CUS_ID.

The join business rules are:

You can now save and execute this Mapping.

Analyzing the result of the execution

Go to the Statistic view to check the statistics of the execution:

Information: The size of the output file is in bytes.

You can also see the content of the generated file:

1. Come back to the Load customerSummary Mapping
2. Right-click on the customerSummary Datastore
3. Select Actions then Open Xml Editor

An XML editor will then open and allow you to see the content of the file:

1. Click on Format Xml File in order to automatically format the content
2. Click on Validate Xml to check if the XML content agrees with the structure declared in the XSD file
3. Check the Syntax Coloring option to use the syntax coloring specific to XML files

Loading an XML hierarchy

Reversing the hotel’s data exchange file

The hotel’s XSD file has got a last root element still to be reversed: hotelManagement. Reverse this element in to the Hotel XML Files file.

Now define the path to the associated XML file:

1. Click on the hotelManagement element
2. In the Properties view, enter a path to a file: hotelManagement.xml (for example: /tmp/hotelManagement.xml)

Loading a complete customer file

Creating the Mapping

Create a Mapping with the following properties:

Tip: To save time you can reuse the Mapping Load customerSummary created during the previous exercise.
* Right click on the Mapping Load customerSummary and select Copy
* Right click on the Folder Mappings and select Paste
* Enter the name of the new file: Load customerDetails.map
* Open this new Mapping
* Drag and drop hotelManagement from the Project Explorer on the datastore customerSummary located in the Mapping. Stambia replaces the target datastore and preserves the existing business rules.
* Rename the root node: right click on the field customerSummary and select Rename field
* You can now complete the definition of the Mapping with the new sources and business rules.

The transformation business rules are:

Information: loading of the bill element will be the subject of an optional exercise further on. For the moment, this element can be left empty.

The join business rules are:

Important: Notice that each loaded element has a functional key which indicates to Stambia on which expression it must rely to determine the different occurrences of the element. For example, each different value of T_CUSTOMER.CUS_ID will result in a new customer element. In the same way, inside each occurrence of customer, there will be:
* one occurrence of address for each T_ADDRESS.ADR_ID
* one occurrence of phone for each T_PHONE.PHO_ID
* one occurrence of email for each T_EMAIL.EML_ID.

Information: The hotelManagement element has a default functional key, but the field is not mapped here. So Stambia will not iterate for this element. We will see further on that it is possible to map this element and use it as a functional key.

You can now save and execute this Mapping.

Analyzing the result of the execution

Go to the Statistic view to check the statistics of the execution:

Now open the hotelManagement.xml file from the location you specified earlier and check that the content is correct:

<?xml version="1.0" encoding="UTF-8"?>
<mgt:hotelManagement xmlns:com="http://stambia.org/samples/common" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:mgt="http://stambia.org/samples/management">
    <customer lastName="GIBBS" titleCode="Mr" title="Mister" customerId="1" company="" birthDate="1975-02-15T00:00:00+01:00" firstName="Jason">
        <address line4="" zipCode="02115" stateCode="MA" line1="610 Beacon St" line3="" addressId="1" line2="" city="BOSTON"></address>
        <phone phoneNumber="(413)454-5663" phoneType="Home" phoningAllowed="true" phoneId="1" phoneTypeCode="HOM"></phone>
        <phone phoneNumber="(413)442-5252" phoneType="VoIP" phoningAllowed="true" phoneId="2" phoneTypeCode="SIP"></phone>
        <phone phoneNumber="(413)442-5250" phoneType="Office" phoningAllowed="true" phoneId="3" phoneTypeCode="OFF"></phone>
        <email mailingAllowed="true" emailType="home" emailId="1" emailAddress="[email protected]"></email>
    </customer>
    <customer lastName="O'NEAL" titleCode="Mr" title="Mister" customerId="2" company="Springfield Ltd" birthDate="1967-03-03T00:00:00+01:00" firstName="Michael">
        <address line4="" zipCode="02114" stateCode="MA" line1="225 Friend St" line3="" addressId="7" line2="" city="BOSTON"></address>
        ...

Writing in a specific file

The Mapping as it is written for the moment uses the default options of the Template. Particularly the generated XML file takes the name defined in the Metadata file. However, it is sometimes necessary to take control on the file name to generate a specific name.

Change the Out File Name option in the integration Template:

1. Click on the Integration step of the hotelManagement element
2. In the Properties view, click on the Out File Name link to activate the option
3. Enter the path to a file named customerDetails.xml (for example, under Linux: /tmp/customerDetails.xml)

Execute the Mapping once again and check that the generated file is indeed customerDetails.xml.

Optional exercise: Adding the billing information

Creating the Mapping

In order to save time on this exercise, copy (CTRL+C or right-click Copy) the Load customerDetails Mapping then paste it (CTRL+V or right-click Paste) in the Mappings folder. Give it its new name: Load customerDetailsWithBillings.

Load the bill element of this new Mapping in the following way:

New source Datastores:

• T_BILLING
• T_BILLING_LINES
• T_PAYMENT_TYPE

The new join business rules are:

Finally, change the Out File Name option in order to load a file called customerDetailsWithBillings.xml.

Analyzing the result of the execution

Go to the Statistic view to check the statistics of the execution:

Tip: If there is an error, make sure you did the aggregation when loading the totalAmount attribute.

Loading several XML files

Creating the Mapping

In this exercise, you will generate a complete customer file for each city. The Out File Mask option of the XML integration Template works just like the Out File Name option, but, instead of a fixed name, you will specify a mask. This mask will be used to generate a dynamic file name that will be different for each target file created by the Mapping. This mask is defined thanks to predefined elements or source data.

Create a new Mapping by copying (CTRL+C or right-click Copy) the Load customerDetails Mapping and by pasting it (CTRL+V or right-click Paste) in the Mappings folder. Give it its new name: Load customerDetailsByCity.

The first thing to do is to tell Stambia on what expression it must rely to iterate on the root elements since the XML norm dictates that there must be only one root element per XML file:

1. Check that the hotelManagement element is activated as functional key
2. Enter the following transformation business rule: T_ADDRESS.ADR_CITY

The Template allows the value of Out File Mask to be parameterized with various predefined values:

• [FILE_NAME]: Complete name of the file as specified in the Metadata
• [FILE_SHORT_NAME]: Path and name of the file without the extension as specified in the Metadata
• [FILE_EXTENSION]: File extension extracted from the file name specified in the Metadata
• [POSITION]: Number that increments with each newly generated file
• [TIMESTAMP]: Number of milliseconds since January 1st, 1970

However, the Template also allows the user to define the values. These values are defined with Tags in the Mapping. Tags allow you to tag an expression or a column which will then be used by the Template(s). In the INTEGRATION Rdbms to Xml Template, Tags are used to parameter the name of the target file:

1. Click on the city target attribute
2. In the Properties view, enter the value CITY_NAME in the Tags field
3. Click on +

Notice that an icon with the letter T appears to say that a Tag was added to this target column.

You can then use this tag to generate a file name that takes the city name into account:

1. Click on the Integration step of the hotelManagement element
2. In the Properties view, select and cut the path specified in the Out File Name option
3. Click on the Out File Name link to deactivate this option
4. Click on the Out File Mask link to activate this option
5. Paste in Out File Mask the path that was previously in the Out File Name option
6. Finally, change the file name customerDetails.xml into customerDetailsForCity_[CITY_NAME].xml

You can now execute the Mapping.

Analyzing the result of the execution

Go to the Statistic view to check the statistics of the execution:

Go into the folder where you generate the files and look at the created files:

Optional exercise: grouping the files by state

Creating the Mapping

Make a copy of the Load customerDetailsByCity Mapping and call it: Load customerDetailsByStateAndCity

Change this Mapping so that it generates an XML file for each city, but so that these files get stored into one folder per state (i.e. for each value of T_ADDRESS.STATE_CODE).

Analyzing the result of the execution

Go to the Statistic view to check the statistics of the execution:

Moreover, you must have 27 XML files allocated to 23 folders. For example, the TX folder contains 3 files.

Loading independent hierarchies

Until now, the loading of XML files concerned files where the root element contained a hierarchy that could be loaded from a same set of joined tables. However, some XML files have hierarchies that have no direct link with one another. This is what we will see in this chapter.

Creating the Mapping

Create a new Mapping by copying (CTRL+C or right-click Copy) the Load customerDetails Mapping then by pasting it (CTRL+V or right-click Paste) in the Mappings folder. Give it its new name: Load hotelManagement.

Load the bedroom element in this new Mapping in the following way:

New source Datastores:

• T_BEDROOM
• T_BDR_PRICE
• T_BDR_PLN_CUS

The new join business rules are:

Finally, deactivate the Out File Name option in order to load the file defined in the Metadata.

You will notice there are two distinct groups of Datastores. The first one loads the hierarchy of the customer element and the second one loads the hierarchy of the bedroom element.

You can now execute the Mapping.

Analyzing the result of the execution

Go to the Statistic view to check the statistics of the execution: