BridgeGate™

Tutorial 1: General Tour

Table of Contents

Introduction

This is a tour of the BridgeGate Workbench, (graphical user interface). Everything in the tour, including the inbound and outbound templates, and the test data, is included in the BridgeGate installation; however, instructions for creating all of the Records and Fields are not provided. It is only to give you an introduction to some of the terminology and concepts that BridgeGate™ uses before starting the actual Samples themselves.

The Business Problem

You will solve the following business problem with BridgeGate™. Your company needs to send an invoice to three different suppliers. The problem is that each supplier uses a different data format than the one your company uses, and each supplier uses a different means of physical communication to receive the data that you need to send. Here is a synopsis of each:

When your company exports data out of your proprietary accounting system, the data can only be exported as a tab-delimited text file, and it looks like this:

H 1607283 1792180 02072002 drpepperphd@hotmail.com 1955757 Glynn Daniel 2140 Merritt Drive Garland TX 75041 United States 214-870-5027 2 45.00 19.90 0.00 0.00 0.00 64.90 5 4111111111111111 0205 GLYNN B DANIEL Blessy Kuriakose 40 Byron Ave Yonkers NY 10704 United States 914-843-3309 44534 A.K.A. Gourmet 0 N N

D 1792180 2158776 201529 2002 500VAL Heart of Hearts Valentine Gift Tower 1 45.00 45.00

M 1792180 Blessy Glynn If I had a star for every time you brightened my day, i'd be holding the galaxy in my hands February 14, 2002

The first supplier needs to receive your invoice as a CSV (Comma Separated Value) document, and needs it to be sent via FTP (File Transfer Protocol). This is how the data must be delivered to them via FTP:

"10","1607283","44534","","GLYNN","Daniel","","","40 Byron Ave","Yonkers","NY","10704","Unit","914-843-3309"

"15","1607283","02/07/02","120000","","2",""

"30","1607283","Blessy"," ","Kuriakose",""," ","Yonkers","NY","10704","Unit","914-843-3309"

"40","1792180","1000","500VAL","2","000045.00"

The second supplier uses HTTP (Hyper Text Transfer Protocol) as their communication method over the Web. They need to receive the invoice in XML (Extensible Markup Language) format, and looks like this:

<?xml version="1.0" ?>

<order_list>
<order>
<request_id>1607283</request_id>
<user>
<user_id />
<user_name>Glynn</user_name>
<can_spam>N</can_spam>
<can_sell>N</can_sell>
</user>
<source_code />
<credit>
<amount>64.90</amount>
<card_type>5</card_type>
<account_number>4111111111111111</account_number>
<exp_month>0205</exp_month>
<exp_year>0205</exp_year>
<bill_address>
<first_name>Glynn</first_name>
<middle_name />
<last_name>Daniel</last_name>
<company />
<address1>2140 Merritt Drive</address1>
<address2 />
<address3 />
<city>Garland</city>
<state>TX</state>
<postal_code>75041</postal_code>
<country>United States</country>
<day_phone>214-870-5027</day_phone>
<evening_phone />
<fax_phone />
<email>drpepperphd@hotmail.com</email>
</bill_address>
</credit>
<line_item_list>
<line_item>
<sku>500VAL</sku>
<quantity>1</quantity>
<unit_price>45.00</unit_price>
<spec_list />
</line_item>
<ship_to_address>
<first_name>Blessy</first_name>
<middle_name />
<last_name>Kuriakose</last_name>
<company />
<address1>40 Byron Ave</address1>
<address2 />
<address3 />
<city>Yonkers</city>
<state>NY</state>
<postal_code>10704</postal_code>
<country>United States</country>
<day_phone>914-843-3309</day_phone>
<evening_phone />
<fax_phone />
<email>drpepperphd@hotmail.com</email>
</ship_to_address>
</line_item_list>
<ship_method>2</ship_method>
<total_shipping>19.90</total_shipping>
<user_id>+</user_id>
</order>
</order_list>

The third supplier can only receive invoices through email, so they must receive the invoice as a message that looks something like this:

==========================================================

Invoice No: 1607283 Order Date: 02/07/2002
Shipping Address:

Kuriakose, Blessy
40 Byron AveYonkers, NY 10704
914-843-3309

Billing Information:

GLYNN B DANIEL
4111111111111111 Exp Date:0205
Daniel, Glynn
2140 Merritt Drive
Garland, TX 75041
214-870-5027

Item # Description Price Quantity Total Price
----------------------------------------------------------
1.0 500 Hearts Valentine $45.00 1 $45.00
----------------------------------------------------------

Sub total: $45.00
Shipping Handling: $19.90
Tax: $.00
Discount: $.00
Total: $64.90
==========================================================

The traditional approach usually used to solve this problem is to write custom programs that each extract the data from the Example1 system, then format it into the necessary formats, and finally send it to each supplier using the communication protocol they require (after writing the required protocol handlers).

BridgeGate™ solves this problem without writing any custom code or programs.

Let's get started

The Inbound Template

You will usually start by defining the Inbound Template. The Inbound Template is where you tell BridgeGate™ how the data coming in to it will look; that is, the format--such as fields, lengths, data separators (delimiters), etc.

The Inbound Template describes your company's data structure. In many cases this is in the form of an EDI specification (SEF), an XML DTD or XSD, a list of Database tables and columns, or just a set of name-value pairs. BridgeGate™ is preconfigured with many common data types. The types include Fixed Length, CSV, Name Value Pairs, Delimited, XML, Database, and X.12EDI.

Since your company's accounting system exports a tab-delimited file, you will set up a new Inbound Template in BridgeGate™ to receive this data.

Based on the inbound specification, you need to define Records and Fields. A Record signifies a grouping of fields, and a Field signifies a single piece of data, such as a Social Security Number or a Last Name. This example record is terminated by a CRLF (a Carriage Return Line Feed combination, commonly used to end logical groupings such as records). The delimiter character, however, can be any character you require.

The definition of a record is defined in the record Mapping section. BridgeGate™ has the ability to define a record using a comparator and a value. In this example, the inbound data has three records. The Order Header record is identified by the first character equal to 'H'. The Detail Record is identified by the first character equal to 'D' and so on.

The BridgeGate comparator can be simple text comparisons, such as Equals, Starts with, Ends with, or it can even be a Regular Expression (a regular expression is an advanced way to handle and manipulate text-please refer to the BridgeGate online documentation for more information).

To open our Inbound Template, Sample1TabDel, click on the Inbound button in the toolbar:

This will open up the Inbound Template screen, which is where you access all the Inbound templates for the currently selected account (in this case, Samples), organized by Workflow Group. Select the Sample1TabDel from the Inbound Templates:

Our General Tour Inbound Template is now opened in the Manage Inbound Template Screen:

After the records are defined, each of the fields needs to be defined. The field types available are Char, Date, Integer, and Float.

Char refers to alpha-numeric data, which can essentially be anything, such as the letters A-Z, the numbers 0-9, or any symbol characters, such as # or %.

Date refers to data that must be interpreted as a date value. BridgeGate™ can recognize a wide variety of Date formats, such as 01/14/03, 2003-01-14, or Jan 14, 2003 (to name just a few). In fact, you can easily tell BridgeGate™ exactly how the date format is being received so that you are not locked in to any particular pattern or formula.

Integer refers to simple numbers, such as 1, 23, -204, 1375, etc. These are all numbers without a decimal point.

Float, on the other hand, only refers to numbers that have a decimal point (or ones that might need them). So, 123.45 and 0.000112 are both Floats. The word Float refers to "floating-point number" (a number with a decimal point). The most common Field types are Char and Date.

The Outbound Template

After the Inbound Template is defined, the next step is to define the Outbound Template for the first supplier.

This process is similar to defining the Inbound Template, but with the added step of mapping the inbound fields to the corresponding outbound fields. It also offers the ability to perform data grooming from the Advanced Properties button. In some ways, the Outbound Template is easier to create, because you can drag and drop the fields from the Inbound Template side onto the Outbound Template side (which is why it makes sense to create the Inbound Template first).

In this example we will be creating three outbound templates: one for each supplier. Select Outbound from the Toolbar and pick the Outbound Template named CSVInvoice:

In the Template Records section, you define the data types, and any default behavior for the translation based on those types. For example, you might want to say that all Float numbers will always be shifted to the right, while all text fields (Char) will always be shifted to the left, or that all Date fields will be formatted as year-day-month.

Global substitution can also be defined in the template header, which allows text to be changed for the entire inbound/outbound data. For example, you can create a global substitution which will convert all occurrences of the Country Code 'US' to the ISO code '0000'.

Drag and drop mode may be toggled to enable Inbound Template fields to be associated with Outbound Template fields by either dragging the fields (or records) from the inbound to the outbound, or overlaid by checking the Link Mode checkbox.

As seen below, the field properties panel is replaced with the tree view of inbound records.

Outbound Data Mapping

As indicated above, the Mapping process may be done either by entering data directly into the panel, or dragging the inbound fields and dropping the field onto the Outbound Template. This allows the user to quickly define the outbound structure. Note that, in this example, the inbound and outbound data structures are not the same. The inbound record layout can have many records that map to a different structure in the outbound layout. Data from anywhere in the inbound side can be placed anywhere in the outbound side, regardless of its data type, format, or record location.

Once the field linking is finished, the data conversion process is started. If the inbound/outbound fields are both Char, and require no data conversion, then the mapping is finished. However, in some cases the data needs to be manipulated in some way.

BridgeGate™ has very robust data conversion abilities. If the Field Type is Char, you can adjust the case to upper, lower or proper case. You have the ability to fill fields with defined characters if an inbound field is smaller than expected. Field level data substitution is also available using Equals, Starts With, Ends With, Regular Expression, etc. This means that you can tell BridgeGate™ to change a phone number in this format: 1-800-555-1212 to this (800) 555-1212 (notice we stripped off the leading two characters in addition to adding parenthesis and removing the first hyphen). Numerous other ways of manipulating the data are also available, including taking pieces of a Field out of the Field and creating new Fields based on a number of different Fields (e.g., creating a single outbound Field from a 4 Inbound Fields for prefix, first, middle, last names).

Using the Field Portioning on the Advanced Properties screen, you can extract relative portions of a string (a string is a group of text characters). Exact portioning can be used to get an exact number of characters, i.e., the first 5 characters. Relative portioning can be used to get a substring based on a start value (a substring is a portion of a whole string, such that "Lake" is a substring of "Lakeland"). If the test data string is "John M Smith" and you want to get the last name, the start and end value would be a space, the start occurrence would be 0, the end occurrence would be 1, and the from value will be RIGHT. That tells BridgeGate™ to work from right to left and get the data between the beginning and the first space found. Click on the Advanced Properties button in the Workbench for an explanation of these advanced features.

Another Advanced Properties feature is the Alpha Mask. The Alpha Mask allows you to apply a mask to the data. An example is if you need to format a phone number (as in the case mentioned above). The Alpha Mask will be applied to the data based on the position of the X format character. All other characters will be used in the pattern.

Another Advanced Properties feature is Replacement. Replacement allows you to substitute data with some other data. An example is if you need to replace an & with the word and.

Another Advanced Properties feature is Stripping. Stripping allows you to remove data. An example is if you need to remove all & characters.

The final Advanced Properties feature is Conversion. Conversion allows you to convert data using one of the listed conversion methods. An example is if you need to URLENCODE all & characters.

BridgeGate™ has the ability to conditionally output data based on the inbound data. For example, if you wanted to only include a Purchase ID and Order Number field if the Purchase ID field coming from the inbound data had a value (wasn't blank), you would use a Conditional Field to manage this. Conditional Fields are how you tell BridgeGate™, "if this condition is true then do this."

BridgeGate™ has the ability to create variables using inbound data. The variable can be used as a counter, used for mathematical calculation, or used to concatenate strings (concatenation is combining two strings together, such as "Bridge" and "Gate" to form "BridgeGate". The example shown below is an order counter. The variable is incremented for each order. This variable can then be used in the outbound data structure.

BridgeGate™ provides the ability for a developer to implement custom logic into the translation process.

Note to programmers: the Plug-in feature takes inbound data as arguments from the translator and calls a Java class that implements the BridgeGate Plug-in interface. The Java code can perform any function the developer desires. This could be a simple calculation, an external DLL, or a native call. The end result is a list of outbound arguments that are passed back to the translator, which can then be used in the outbound structure just as any other data.

The Plug-in design permits BridgeGate™ to "talk" to outside systems while it is transforming the data. So, if the invoice data coming from the company's accounting system is not correctly calculating the amount of Sales Tax, BridgeGate™ can make a call to an outside tax system and get the correct data, then insert it into the Sales Tax field, just as if it had originally come that way.

Testing the Templates

When the outbound map is complete, BridgeGate™ provides the ability to test the translation within the template builder. This is one of the more powerful (and useful) features of BridgeGate™. It means that you can test, and examine, your data without having to run it in a typical test production environment.

Select the test button to start testing your inbound and outbound mapping.

The Test template feature allows you to paste test data into the Test Data section and select the inbound and outbound templates to be used in the test. note:(Test data is supplied with this example and can be found in the testdata folder, which is in the Sample1 folder. The file is named testdata.txt and is a normal text file.). Running the test produces a view of the translated results (as seen below). The results can then be reviewed to ensure the translation and is correct.

Setting up Workflows

Once the Inbound and Outbound Templates have been defined (and tested), we are ready to create a workflow.

A Workflow is used to emulate a business process within your organization, or to integrate BridgeGate™ with existing systems to accomplish a similar purpose. All of these actions can occur conditionally, based on any number of parameters needed to determine their execution.

A Workflow performs communication among two or more systems. These can include a synchronous response if required. Workflows use Adapters, which are the physical means BridgeGate™ uses to connect to the outside world, such as email, FTP, HTTP, etc. BridgeGate™ can connect to a wide variety of external sources using almost any physical connection protocol, but should one be required that BridgeGate™ does not automatically handle out of the box, new Adapters can easily be added.

An Adapter is an object that can be written and plugged in to BridgeGate™ in order to enable new types of communications (such as FTP, RDBMS, JMS, Sockets, HTTP, HTTPS, and so on)

A typical Workflow involves capturing data from one or more locations, translating it into other data formats, and then sending the resulting translated data to one or more destinations. Additionally, you can forward captured data to one or more destinations without it being translated. This is accomplished by using the following types of actions within a Workflow:

All of these actions can occur conditionally, based on any number of parameters needed to determine their execution.

The BridgeGate Workflow sequences the dataflow and translations by specifying which inbound and outbound templates are used to describe the data, as well as how the data will be communicated, along with any translation you have requested.

As indicated, BridgeGate™ provides industry standard communication protocols like FTP, email, and HTTP, Adapters. Each Adapter contains a list of properties specific to that protocol. For example, the FTP protocol requires an IP address, ID, and password. The FILE adapter requires a file location, such as "C:\CompanyInfo\AccountingData", and a filename mask, such as "*.fin"-that is, find only files with a .fin extension but can be named anything (the * in DOS and Windows is a wildcard character).

Get Data specifies how the data is received --which inbound template group, inbound template, and type of communications to be used.

BridgeGate™ performs data translation with the Translate function. Translate manipulates the data according to the instructions you have specified while defining the templates. Translate will perform these operations according to your Workflow sequencing.

The Send Data is used to specify how and where the data is to be sent. If for instance, your receiving destination (outbound) is an FTP site, you will specify the IP Address or hostname, FTP login information, and the filename mask you wish to use for your destination file.

Testing the Workflows

After the Workflows are created, they can be tested.

The Workflow test will perform the communications, using the Adapters, through the BridgeGate server.

Setting up a Schedule

BridgeGate™ provides the ability to schedule when each Workflow should run. This actually only applies to instances where getting the data is not being done in real-time (for example, collecting and transmitting all invoices after the close-of-business each day).

The Schedule tells BridgeGate™ when to get data, when to start waiting to receive data, or any other activity specified by the Workflow.

The scheduling mechanism allows a Workflow to be started based on the properties provided. The schedule can be run multiple times a day or once a week/month. It is very flexible on when (and how often) each Workflow should be run. It is quite capable of running multiple Workflows at the same time.

Conclusion

BridgeGate™ is a very flexible tool that allows data to be taken from one source and sent to another, transforming it along the way in almost any way conceivable.

The power of BridgeGate™ is that it allows you to describe the incoming and outgoing data, rather than setting up pre-defined holders of that data. This permits you to take in virtually any kind of data imaginable, without having to pre-agree on what it should look like, how it should arrive, etc.