|
Using the EDI Data Object
** Note: The EDI data object requires a separate license, please contact us at: vis@relationalsolutions.com for more information.
Quick links:
The EDI File data object is a generic object for reading EDI files based on a predefined EDI schema definition. EDI schema definitions for specific EDI formats can be either be developed by the user (with the EDI Builder license), or can be obtained directly from Relational Solutions.
The EDI File data object is a generic EDI file reader
You cannot write EDI files with the EDI File data object, it is read-only.
How does the EDI data object work?
The EDI data object contains a powerful parsing engine which is capable of reading any predefined EDI file document type. Using a specially designed EDI Schema definition, which can be built with the EDI Builder, the EDI file is read in, parsed, and written to a readable flat file which can then be transformed and mapped using the standard BlueSky Integration Studio Join object.
Below is a graphical picture of what occurs.
How to use the EDI Data Object
Using the standard BlueSky Integration Studio designer, select the EDI object from the toolbar and draw out your design. Remember the EDI data object is a read-only object and can only be used to read EDI files for the purposes of transformation and loading.
Select the EDI data object by clicking on it, then press the F4 key to get the properties window. Here you will set all of the necessary properties to define your EDI file format, location and final converted output definitions.
See the EDI File object information for specific property definitions.
Select the EDISchema by clicking on the ellipse button (...). The predefined EDI schema definitions are located on the server. Then select your EDI file path and Text output path (where you want the converted edi text file to go). Next, set the parameters which define how the parser will read your EDI file. You can use the EDIWizard property for this as well as test a local copy of your EDI conversion. This way you can see what the final output will be before actually running your job.
Controlling the output - EDI Wizard
When you click on the ellipse button (...) next to the EDIWizard property, you will get the following window. This window allows you to define and control the final output of your converted EDI file.
XML EDI Schema Definition Tab
The EDI Schema definition tab show 3 panes:
Left pane - this is the tree that represents the definition, or layout, of the selected EDI document type that you have selected in the EDISchema property. By clicking on each of the segments, you will get a list of the elements available in that segment in the center pane.
Center pane - this is the list of elements available for the selected EDI segment. Here you can double click on an element and tell the EDI parser where you want that specific element to be mapped in the final output text file. Also, you can tell the EDI parser when to write the current line. This is necessary so that you have total control at the element level to instruct the EDI parser to write out a line.
Right pane - The right pane shows the mapping of where each element will be written to in the final output text file. You can use the View mappings as feature at the top of the window to see different mapping views. This helps to understand and quickly see where your EDI element data will end up.
You will do all of your mapping work in the center pane, at the element level. When you double-click on an element you can control the mapping where the element data will be written to and instruct the parser when you want it to write a line. You can also control the read order of the elements. Let's look at a typical 852 document's SDQ segment line:
Ex: SDQ*EA*92*0141*.01*0149*.01*0208*.01*0527*.01*0882*.01*0918*.01*0930*.01*0943*.01*0984*.01*1201*1~
In this case, the EDI segment line has multiple store information contained in a single EDI segment line. Each "Identification Code" and "Quantity" group represent a store number and amount. What we really need is for the parser to write those stores and amounts to separate lines in the final output, like this:
040207 040213 COMPANY NAME 123456789000 QS EA 92 0141 0.01 |
040207 040213 COMPANY NAME 123456789000 QS EA 92 0149 0.01 |
040207 040213 COMPANY NAME 123456789000 QS EA 92 0208 0.01 |
040207 040213 COMPANY NAME 123456789000 QS EA 92 0527 0.01 |
040207 040213 COMPANY NAME 123456789000 QS EA 92 0882 0.01 |
040207 040213 COMPANY NAME 123456789000 QS EA 92 0918 0.01 |
040207 040213 COMPANY NAME 123456789000 QS EA 92 0930 0.01 |
040207 040213 COMPANY NAME 123456789000 QS EA 92 0943 0.01 |
040207 040213 COMPANY NAME 123456789000 QS EA 92 0984 0.01 |
040207 040213 COMPANY NAME 123456789000 QS EA 92 1201 1 |
To accomplish this, you can instruct the compiler to [Write] after each store/amount grouping.
Here is the dialog box that comes up when you double-click on an element:
|
|
** Note: It is important, before continuing on to testing your output, that you click the 'Apply Schema Changes' button if you made any changes, that way the changes are saved to the EDI schema definition file and available to the parser engine.
The remaining tabs in the EDIWizard are used to test your EDI mapping with local test data. This requires that you have a local copy of your raw EDI data file on your PC so that you can make sure the final output is what you are expecting.
Here you will simply select a local copy of your raw EDI file, and where you want the test output written to, then click the Execute Local EDI Parsing button.
To view your data files (EDI and Output file), click on the "Show Local EDI File" and/or Show Local Output File tabs. These are simply viewers which will allow you to see and verify your data.
Viewing the EDI data file:
Viewing the Local output file:
Job design - mapping EDI in the Join Object
Now that we have successfully mapped and verified the EDI output, we can continue on with our Job design. From this point on, everything is exactly the same with any typical BlueSky Integration Studio job, we want to connect our EDI data object to a Join Object and output that information to anywhere we need it - in this case a flat text file.
There may be some additional transformation logic that you want to include, for instance putting element values into different columns based on other elements. In the following example, we are checking the Activity code from the EDI Document and mapping the elements values accordingly.
EDI files can be quite complex and many times require additional work to be done such as grouping and aggregating the data. It is best from a performance, as well as a debugging standpoint if you design your transformations in multiple steps. We have found it best to follow this process:
1. Build a job that transforms the basic EDI document into a flat database table. Here you can make sure the EDI document is transformed properly and make any quick changes to the basic conversion logic if the Sender of the EDI document decides to make changes. You can think of this as the Staging process - you are loading the EDI document into a staging area for further scrubbing and validation before loading it into your production systems. There are many times when the Sender could make changes without telling you! By keeping the basic transformation separate, you can debug issues and make changes very fast and keep everything down the line (the true business logic) the same.
2. Build a job that reads, transforms, validates and aggregates the staging database table into its final form. Here you can:
Make sure the data is valid by using hash lookup tables to ensure correct information, and output to an exception file if necessary.
Transform the data if necessary, for instance adding leading zero's to a UPC code or trimming extraneous spaces from text.
Aggregate the data. It is always best to use the database for this functionality. We can easily write a data aggregation object into BlueSky Integration Studio, but we have found that we will never do it as good as the database - they are built to perform this type of functionality. From a performance standpoint, keep all of your sorting and aggregation logic in the database.
3. Build a job and employs the Batch object to tie the two jobs together. Here you can tell the Batch object to run the EDI Transformation job first, then run the job that transforms and scrubs the data.
© 2003 - 2007 Relational Solutions, Inc. - All rights reserved