Schema Editing Guide

This guide outlines the steps required to export the schema information for a file to Excel for editing and to import the edited information into Kourier for further processing.

Assumptions made in this Guide

1. Microsoft Excel is installed on the user's workstation
2. Microsoft SQL Server Management Studio is installed on the user's workstation

Procedure

1. In the Kourier GUI, use the Quickstart Workbench to analyze the data file.

2. On the Quickstart Workbench page, after the analysis is complete, click the Check Out button to view the resulting Schema in Microsoft Excel

3. The following table describes the columns in the Excel spreadsheet, their meaning and how they are used by the Quick-Start tools. Many of the columns are informational only (highlighted as No in the table below, and shaded grey in the generated Schema workbook). The other columns highlighted as Yes  can be changed as noted by the "Change" column. The data in the Excel spreadsheet will be used when building the data export specifications and services when using the Generate Exports button in the Quickstart Workbench.

4. Once all edits are complete, save the Excel workbook in the ...\ODS\Schema folder, and then close Excel. (The workbench will not be able to upload the Schema if the Excel workbook is open.)

5. On the Quickstart Workbench page of the Kourier GUI, click on the Check In button. In the resulting dialog box, either enter the full path to the edited and saved Excel workbook, or use the Browse button to locate the file. Then, click on the Continue button to upload the Schema to Kourier.

Handling Multi-Line Text or Comment fields

Text and Comment fields are often set up as multiple lines of free-form text in a single Multivalue attribute, with value marks, subvalue marks or text marks separating the lines. In most cases, these fields should be brought over to SQL as a single text field, rather than as multple lines of text. This may be accomplished using the Kourier @TEXT conversion. To accomplish this, change the MVType value to S, and the DataLen to 999. In the Conversion field, enter '@TEXT (the single quote preceding the @ sign prevents Excel from treating this entry as an invalid formula).

One variation on this scenario is when Text or Comments fields are part of an associated set of multvalued attributes, and the lines of text are delimited by value marks or text marks. In these cases, you would change the MVType to MV, make sure that the Association is set to group the field with the other associated fields, and use either @SVM2SPC or @TM2SPC conversions as required.

Splitting delimited fields to multiple SQL Columns - a special case

In many Multivalue applications, data is stored in an attribute or an Item ID as multiple segments delimited by a special character (such as an asterisk *, a period . or an exclamation point !. It is often desirable to split these fields into separate SQL columns to make the data more easily accessed in SQL.

For example a common method of storing a record's last update information is as a single "stamp" attribute, in which the user ID, the date and the time are separated by asterisks. If User JOHNDOE updated a record on January 31, 2011 at 1:35:42pm, the stamp would look like this:

JOHNDOE*15737*48942

This would not be particularly useful in SQL Server. So, in the Excel workbook, the following procedure may be used to split this data up into separate SQL columns for the Update User ID and the Update DateTime:

1. Select the entire Excel row, and click the Copy button (or right-click and select Copy, or type <Ctrl>-C)
2. Right-click on the selected row, and click on Insert Copied Cells. The result is two identical copies of the row.
3. On the first of the two rows, make the following changes:
   1. Add .1 at the end of the Filename*AMC cell. In  other words, change FILENAME*0 to FILENAME*0.1, or change FILENAME*23 to FILENAME*23.1
   2. Change the Column Heading to an appropriate value (such as "LastUpdateUser" in our example).
   3. Add a G conversion to return just the 1st segment of the delimited value. In our example, the conversion would be G0*1. See the Conversion Codes page for more details.
   4. If any other conversions are required, add a pipe symbol "|" and the secondary conversion(s).
4. On the second of the two rows, make the following very similar changes:
   1. Add .2 at the end of the Filename*AMC cell. In  other words, change FILENAME*0 to FILENAME*0.2, or change FILENAME*23 to FILENAME*23.2
   2. Change the Column Heading to an appropriate value (such as "LastUpdate" in our example).
   3. Add a G conversion to return the desired delimited segments. In our example, we would take advantage of another Kourier special conversion and use the conversion code G1*2. This will skip 1 element, and then return the next two elements (i.e. "15737*48942" in our example).
   4. If any other conversions are required, again add a pipe symbol and the conversions. In our example, we would add USQLDATETIME, which converts InternalDate*InternalTime into a SQL-compliant date format. So, on our second line the conversion would be "G1*2|USQLDATETIME".

Using this technique, a delimited field may be split into as many SQL columns as necessary.

User Guide - Table of Contents