In order for the system to read and manage data from the incoming data source, the columns that constitute this source need to be identified. It is done by creating a Source layout where for each field of the source corresponding column is entered and its attributes – a unique column name, column description, column title, data type, etc. specified. These source column attributes are listed in the Header of the Source Layout tab’s grid (Fig.).
Note: Setting up the Layout is a crucial step. The system will not allow you to Save unless you set up at least a basic layout (e.g. one column with mandatory attributes).
Before you start Source Layout setup, you must obtain the data structure from the provider i.e. how many columns there are in the source and their order, columns names, and sizes, method of delineation, etc. Also, the data provider should give you reference data, which columns will serve as keys, where to insert lookups, what default values to use and any other information required to build an adequate source layout.
The provider can deliver this information in several ways:
1. In a hard copy format. In this case, you would have to build the layout by manually entering column names in the order of their appearance in the source.
2. As an Excel or any other spreadsheet table. In this case, you will copy and paste the layout using the Layout tab’s functions.
The columns must be named without spaces and special characters (underscores are allowed) and must be in lowercase.
Existing layouts may also be imported into the new data source setup (see below).
Setting Up Layout for a New Source
Here we will set up the layout for our tutorial source. Before you start, please take a look at the source in Excel format (enclosed). As you open the Layout tab the working area appears empty (Fig.). At this point you can proceed in two ways:
1. Import the layout from an existing source or external table and modify it if necessary. This method should be used in case of massive transactional sources that may have hundreds of columns.
2. Manually insert column names following the guideline from the data provider and filling in necessary parameters. This method is usually used when creating small in-house control data sources that often have only a few columns.
Importing Layout from the Existing Source
The easiest way to copy the layout from the existing source is as follows:
1. Open the source with the layout you want to copy
2. Open the Layout Tab
3. Copy all rows
4. Click on theCopy button to transfer selected rows to the clipboard.
5. Open the new source’s layout tab
6. Pressbutton to paste copied rows. The system transfers the entire source’s layout including all column settings into the new source setup.
Importing Layout from the DB Source Table
If copy and paste are not possible, you can import layout table from the DB Source, where layouts for existing sources are stored as external tables in conjunction with sources to which they belong. Also, you may receive a stand-alone layout table only.
You can import several layouts into one. The system will remove redundant columns from the added multiple layouts.
button provides access to the lookups listing all external tables residing on the DB Source including existing sources’ layouts. For our purposes we would like to import the layout for the source table
1. Press button the Lookup listing available tables will open (Fig.).
2. Locate the table you need and press OK to close the lookup. The imported layout appears in the Layout tab’s data grid (Fig.).
Note: When importing the existing layout only the basic setup will be copied. You will have to manually add Lookup sources and enter other parameters.
Importing Layout from Excel File
The layout can be imported from a table created in Excel or another spreadsheet program by using Copy and Paste function. For example, if the data provider supplied you with an Excel file containing headers of the columns that constitute the source, you can:
1. Use Excel’s functionality to Paste Special/Transpose to convert the table into the vertical format
2. Copy the cells
3. Paste them into the tab’s data grid using the grid’s button.
Manual Source Layout Setup
There are times however when you are forced to create source layout manually. Should this be the case, the following preliminary steps must be taken:
1. Obtain the list and order of columns in the actual source table
2. Set up Control sources (tables) to provide lookups wherever necessary (see Control Sources setup section below).
After completion of the above steps, you can start building the layout for your data source.
The first step is creating columns for the source data to go to. It is important to notice, that it is not necessary to create the entire replica of the original source file. In fact, you can only set up as many columns as you know you are going to need for your further work.
Note: In the latter case, however, while the data upload will result in populating these columns, the remaining data will be lost.
To start setting up the Source layout click on the Insert Column button of the Grid’s Tool Bar. This will add a row in the data grid of the Layout tab (Fig.).
Insert as many rows as needed usingand/or buttons and then start filling in the parameters. Alternatively, you can set up one column at a time.
Setting up Columns Parameters
Some of the characteristics of the column are mandatory, while some are arbitrary. The system will not allow you to proceed with the setup without entering the mandatory specifications for the source columns.
We will start with the very first column of the tutorial source. This column is called Axiom Index and will be filled by the system providing a key field containing a unique value for each record.
Note: when setting up columns try to keep columns in order of the original source file.
INTEGER Data Field Setup
To set up the Axiom Index column:
1. In the Column Name field enter axiom_index as the column name using low case and underscores. This name will be used by the system for column locating and referencing.
2. In the Description field enter axiom_index. The column description you enter here will appear in lookup windows.
3. In the Title field enter Axiom Index. This entry will appear at the head of the column in the reports or GUI screens.
4. In the Data Type field enter INTEGER using the arrow button’s list menu (Fig.).
5. In Data Display select Integer format from the arrow button menu that conforms to your organization practices #0, for example.
VARCHAR Data Field Setup
Next column to set up will be Branch Code:
1. Repeat steps 1, 2, 3 and 4 entering branch_code, branch_code and Branch Code and VARCHAR respectively.
2. In the Size, field enters the number of characters you know the entry would not exceed. In the case of the Branch Code, this number will be 3.
3. In the case of the Branch Code column Allow Nulls will be left blank.
4. In the case of the Branch, Code column Allow Defaults will be left blank.
5. In the case of the Branch Code column Default Value will be left blank.
Note: By leaving the above three parameters blank we are preventing the system from accepting the data without any branch codes. Please refer to the Source Editor chapter for details about the purpose and relationship between these three parameters.
6. We will skip Synth. Content parameter for the Branch Code column.
7. In the case of the Branch Code column, we would like to attach a lookup (control) source to provide a lookup. To do this, use a lookup button to find a lookup source containing branch codes (cntr_branch_codes in our example).
Note: Prior to setting up the Main Data Source, all necessary Lookup sources should be agreed upon, set up, and created or imported. Please refer to the next chapter for detailed instructions on how to build lookup sources.
8. The lookup source for Branch Code consists of two columns. One column contains branch codes proper, while the other contains descriptions. Use the arrow button and insert the branch_codes (Branch Codes) column name.
9. Use the arrow button and insert the branch_desc (Branch Description) into the Lookup Description Column input field.
This will conclude the Branch Code column setup. The next seven columns setup is essentially the same. The only difference is the presence of the control sources that may be attached to them or lack thereof and allowed NULLS in columns where the anticipated data may not contain any data. You should be able to set them up using the steps described above. If necessary consult the attached Excel file with the tutorial source layout for reference.
Date Field setup
Start Date column, however, is different from the ones we described above because it contains a date that should be specified as the Data Format in step 4. This includes an extra parameter to be set:
1. Display Format The arrow button opens the menu listing Date (Fig.) formats supported by the system.
2. Select the dd-mmm-yyyy-HH-mm-ss format from the lookup to confirm the date format to the Axiom SL standard.
FLOAT Data Field Setup
Following the Start date field in our source are two consecutive data fields that you should not have any problem setting up. After the date fields sequence however comes amount_in_local_currency. This field had FLOAT as the data type.
To set up FLOAT Data Field:
1. Repeat the steps described above, setting the data type as FLOAT, leaving all other parameters blank with the exception of the Display Format.
2. For the Display Format select numerical data format from the menu (Fig.) following the convention established at your organization.
Continue to build the Tutorial Source Layout using steps described above and consulting the Excel table with the original source structure.
After you finished with setting up the columns of the source (whether via import or manually, the grid of the Source Layout tab should look as shown on the illustrations below (Fig.).
Note: Setting up the Source Layout will enable you to save and to define other parameters of the new source.
Key Field Setup
In our source, we will designate the axiom_index column as a key field as containing a unique value for each record in the table. To set axiom_index as the key field:
- Open Key Fields Tab;
- Use button and select axiom_index from the lookup;
- Check Yes on the Create Index On Key Fields;
- Leave Index of Key Fields Clustered on No;