Scheduled Data Exchange Case Study

Exchange Rate Case Study

Altova, a global corporation with operating units in the United States and Europe needed a reliable and consistent way of updating currency exchange rates to keep its price lists and accounting systems current.

Overview

The accounting and IT teams at Altova designed and developed a system that performs automated currency exchange rate updates to the company’s internal business management application. This system works behind the scenes, ensuring that all pricing information remains current for both the Euro and the US Dollar.

Altova software tools are designed to solve real-world business challenges, and therefore, projects such as this are generally done in-house, without requiring external services.

This project was assigned to an IT manager who worked with Altova tools and XML technology, as well as with built-in Microsoft® Windows functionality and the Foreign Currency Exchange Rates & Indexes Table embedded within the company’s SAP Business One enterprise resource planning (ERP) system.

The Challenge

Altova needed to build and deploy a simple, lightweight application whereby its back-end, SAP system would be automatically updated daily with the most current exchange rate information for US Dollars (USD) and Euros (EURO). The application was to be created and implemented by an Altova IT manager using Altova software tools. This way we would not only avoid troubling our own software developers (who were busy working on enhancements for our line of application development and data management tools) and save the cost of using third-party services, but would also demonstrate the versatility and ease of use of our tools to power users.

This project required the use of several different components, some pre-existing, and some that needed to be created by the IT manager:

Existing Components:

Using these available components, the IT manager set about developing a straightforward system whereby internal pricing information would be automatically updated by referencing an external resource (the ECB XML document).

The Solution

To complete this task, the IT manager devised a method for a variety of different components to interact remotely on a regularly scheduled basis.

Created Components:

In order to update the SAP database table with the constantly changing information from the ECB XML document, a simple mapping was created using the MapForce GUI. The mapping would take the data from the XML file and simply transform it to the database format for use by the SAP system.

Altova MapForce utilizes an XSD to create a structural tree diagram of an XML instance for mapping purposes. The XSD is essentially a bare bones representation of the XML document structure without the associated content, and MapForce uses it as a sort of stub file or table of contents to map the elements of the XML file. The ECB XML file is not supported by an XSD, and, in order to reap the benefits that MapForce offers, one needed to be created so that its components could be mapped to the database. XMLSpy offers the capability to infer and automatically generate an XML Schema from an XML instance document, and this was an ideal feature for this project because it only took a couple of seconds to use.

By choosing Generate DTD/Schema from the DTD/Schema menu in XMLSpy and choosing the settings below, the IT manager was able to instantly generate the relevant valid XSD from the ECB XML document.

The Generate DTD/Schema window in XMLSpy

The Generate DTD/Schema window in XMLSpy

In this case, XMLSpy actually generated two XSD files, covering each of the two namespaces utilized in the XML instance document: one for the message wrapper, and one for the exchange message information. A namespace is an identifier which provides a context for elements within a document – this way an element can be used more than one time, but with a different meaning in each namespace. In this case, the ECB used namespace prefixes gesmes and n1 (see below). For the simple mapping required in this project, the IT manager only needed to use the XSD for the exchange message information.

XMLSpy Schema View

XMLSpy Schema View of generated XSD file governing the structure of message information

Among its many data integration capabilities, Altova MapForce can be used to convert data from XML structures to database formats according to user created mapping designs. Using the XSD as the source component and the database table as the target structure, the IT manager laid the groundwork for the mapping file. In the screenshot below, the relevant elements from the XSD appear on the left, while corresponding column headings from the SAP database table appear on the right.





Initial mapping structures displayed in MapForce’s graphical interface

The XML source file contained a lot of ancillary data that the IT manager did not need to send to the database (mostly information about currencies other than the Euro and US Dollar). Using MapForce’s graphical interface and visual function builder, the IT manager was able to create a constant (USD), a function (equal), and a filter (<n1:Cube>), thus limiting the database table updates to just the required currency rates.



The MapForce data mapping design including relevant data processing functions

MapForce provides a full library of data processing functions, which also allowed the IT manager to apply the set-null function (to populate a required field the corresponding column in the SAP Foreign Currency Exchange Rates & Indexes Table with a null value) and the now function (to populate the corresponding column with the current date – every time the task runs). Once this was done, the all that remained was to connect the final required elements from the XSD on the left to the relevant database table column names on the right, then supply the URL of the source data so it can be instantly converted from its original XML format to the SAP database table format each time the task runs.

MapForce mapping

The completed MapForce mapping

The Scheduled Tasks wizard is usually located in the Windows Control Panel. Using this function, the IT manager was able to schedule a task consisting of three parts: path to mapforce.exe (the MapForce Engine), command parameters, and path to mapping. This task was designed to access the MapForce Engine via the CLI and trigger the mapping process to run daily, executing the required updates against the database table.

The Scheduled Task configured to trigger the mapping process.

The Scheduled Task configured to trigger the mapping process

The Results

Every day at 2:58pm Central European Time, the scheduled task is initiated on the Altova server automatically launching the MapForce Engine and accessing the exchange rate data from the ECB. The data in the ECB XML file is instantly converted to the required SAP database format according to the mapping that the IT manager designed with the MapForce GUI. Then the MapForce Engine generates and executes the necessary SQL commands to insert the new exchange rate data into the Foreign Currency Exchange Rates & Indexes Table within the embedded SAP database, and Altova’s price list and accounting systems are automatically updated.

Altova UModel diagram of the finished product.

Altova UModel diagram of the finished product

Altova now has an automated system where the Euro to US Dollar exchange rate is updated daily in its SAP Business One system. This allows this progressive, international company to stay abreast of currency issues which directly effect its internal and external operations.

Find out how MapForce can help with your data conversion challenge.