Mapping Excersizes: EDI Invoice to Open Office Tables (part One)
This is a mapping exercise that will go through the process of creating a paper map, or mapping document. We will start with an empty paper map that you can get here. And we will end with a completed paper map document that documents what data from the source goes into what fields on the target. This process will take more than one post, and I will link them together so that you can follow from one to the next. Along the way, we will discuss the things that we are doing so that you can apply this technique in your mapping using the target and source in your own mapping tasks.
The Target
Open Office tables are divided into two tables. Invoice and Invoice Details. This can be mapped in two ways; The first way is to map the data into one common format and rely on whatever ETL tool is importing the data to catch and split the data. Second, a key can be acquired or constructed in the transformation and then the the data can be divided in to matching input formats. Then when these inputs are moved into the database, they will relate to each other on this key.
The choice of how you will do this will depend on your environment. Questions like, “Will I have enough data to provide a unique key?”, or “is there a way to get a key with an API call or database query?” The answers to these questions will determine what course you will take.
If the system ultimately receiving the data is asynchronous to the transformation, and you need to send the invoice and invoice details data separately, some care needs to be taken to ensure that the data can be related after it is separated.
So what data in the invoice can be used to tie the invoice to the invoice details? The first answer might be, “The Invoice Number.” But this number is not guaranteed to be unique across multiple vendors. In EDI and cXML there are document unique identifiers. Since we are using EDI, we can use a combination of the ISA Sender, Receiver, and Control number. We will also want to use the GS control number, and ST control number in the event that more than one invoice is sent in a single envelope. And we might as well tack on the actual invoice number from the BIG_02.
Database Tables
|
Invoice Table: |
Invoice Details Table: |
 |
 |
The Paper Map
Now that we know what the target looks like, we fill out the target side of the paper map. Since we will create two “files” in our output. We are creating the Invoice and the InvoiceDetails file, but we can use one paper map for both, and will distinguish this with a bar between the two “files”. (I am saying files, but this could be a queue, or a post, or an insert over odbc, etc)
What’s Next
Today we went through the process of identifying the target, and creating a paper map with the target format identified. We talked about some of the strategy that we use in deciding what to map and how to map it. Next time we will identify the source, and begin mapping data from the source.
Subscribe to "The Integration Engineer" by Email
Find out about the tools and services available at The Integration Engineer's Consulting site.
