LibraryCase StudiesScheduled Data Exchange

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.

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.

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:

• XML document — The European Central Bank (ECB), which is responsible for regulating international currency exchange, maintains a publicly available XML document that it updates daily to provide current exchange rates. This file can be viewed at: http://www.ecb.int/stats/eurofxref/eurofxref-hist.xml
• Database table — The Foreign Currency Exchange Rates & Indexes Table within our SAP system enables the storage of current data from different currencies, based on exchange rates.
• Altova XMLSpy® — XMLSpy is the industry leading XML editor and was used in this case to generate an XML Schema based on the XML instance document mentioned above.
• Microsoft Scheduler — The Scheduled Tasks feature of the Windows operating system gives users the ability to schedule recurring processes for any installed or accessible software application.
• Altova MapForce® graphical user interface (GUI) — MapForce is an application that can be used for mapping to and from a wide variety of data formats. It provides a visual data mapping interface, which allows the user to perform complex transformations using simple drag-and-drop functionality — without writing any code.
• MapForce Engine & command line interface (CLI) — The MapForce Engine can be used within custom-built data integration applications and has the ability to execute automated mappings. The MapForce CLI launches the MapForce Engine, which in this application is called by the Scheduled Task to execute an XML to database insert.

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).

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:

• XML Schema definition (XSD) — XML Schema is the W3C recommended language for describing the structure of an XML document. In this case, a schema for the ECB’s XML document was automatically generated using Altova XMLSpy.
• MapForce design (.mfd) file — A MapForce design file defines a data integration process that can be represented visually and is used to generate and/or execute complex data transformations.
• Windows Scheduled Task file (.job) — A Scheduled Task is a simple way to run any script, program, or document at a pre-determined time and/or frequency.

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

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 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.

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

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 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. Download a free trial today!