4.7. Data Loader Utilities

The Data Loader Utilities folder gives you access to the icons that let you to access the batch booking and exchange rate loaderutilities.

Exchange Rate Loader #

The SICS Currency Exchange Rate loader allows you to load Day Rates from an external file into the SICS Database, thus ensuring the most recent rates available.

The loader is based on the same principles as the SICS Migration Tool Kit.

Enable/Disable Message Logging #

When you load data into SICS using any of the other data loader facilities, for example, Exchange Rate Loader and Batch Booking Loader, a message file is created.

The message file can contain three types of messages:

• Error messages
• Warning messages
• Informational messages

An error message reflects the fact that a record in the input file was rejected. The rejected record will be found in the “rejected records” file. The message explains why the record was rejected.

A warning message usually means that an input record contained data that could not be loaded. However, the problem was not serious enough for the record to be rejected. The “warning” severity indicates that it might be useful to investigate the nature of the problem. A typical example is that an input record field contained a reference to a non-existing Country or State.

An informational message usually means that an input record contained some data that could not be loaded. The “information” severity means that the data item that was not loaded does not represent loss of information. A typical example is that a value was found in a record-field that is not in use.

In the Options tab, you can enable and disable messaging. If, for example, you do not want the system to log informational messages, clear the Informational Messages check box (By default, this check box is selected).

Note! The options shown on the Options tab only tell the SICS to write information to the message file or not. The system will still perform all types of validations on the input records, and reject records if necessary.

Incremental Commit Threshold #

This field shows how many records the Loader will process between each database commit. The default value is 2,000.

In certain cases, the threshold level may have an impact on the performance of the Loader. However, it is not trivial to explain how the threshold impacts performance. It may vary de_pending_ on the type of records that are loaded.

In general, the threshold should never be changed, unless a SICS technical consultant recommends it.

Refer to the Migration Tool Kit Loader document for more information about “Write to Loader Files” and “Write to Database” options.

Accounting Options #

The Performance Optimisation settings enable you to increase the speed of loading data. You can set the system to Enable Pre-Caching of Business and Claims Data and enter the number of records after which data will be cached.

Selecting the User Performance Enhanced Accounting Migration box triggers an MTK function that runs the data at an enhanced performance level.

If you migrate Accounting Classifications using the Look for existing Accounting Classification option, the MTK ensures that duplicate Accounting Classifications are not created. This is useful if, for example, you have already created Accounting Classifications through migrating bookings.

Note! The migration process slows down if this option is used. If no Accounting Classifications have yet been created or if you are certain that no duplicates will be created, you should run the migration without setting this option.

Input File Format, Record Structures #

EDI Day Rates #

The Input File must have the following format:

Example File

RECORD ID, Primary CCY, Foreign CCY, Category, FIP, Factor, Rate, Date
"%EDI DAY RATE","USD","NOK","DR","Y", 100, 13.22, "19951201"

EDI Period Rate #

Example File #

"%EDI PERIOD
RATE","USD","NOK","E","Y",100.00,13.10,1998,1,"P","M"

EDI Year Rates #

Example File #

"%EDI YEAR RATE",

"USD","NOK","E","Y",100.00,13.10,1998,"P"

Record Structure Details #

Error Handling #

Any record not having the correct record identifier (%EDI DAY RATE) will be rejected.

Any record missing one or more of the fields (except the Category field - which will default to “DR”) will be rejected.

If a record contains a Day Rate that already exists in the database, the record will be rejected.

The Edit Rejections function may be executed after running a Data Load that resulted in one or more rejected records.

With this edit function, you can correct the error(s), save the corrected records to a new file and rerun the Data Load using the new file as input.

The Exchange Rate Loader vs. the Migration Tool Kit #

Loading Currency Exchange Rates using the Exchange Rate Loader is almost identical to loading exchange rates using the Migration Tool Kit - with the following exceptions:

• The Currency Exchange Rate Loader assumes standard ISO codes for Currency (i.e. the ISO code Mapper is not used).
• The Currency Exchange Rate Loader verifies that each day rate is not already loaded into the database.
• Security can be defined specifically for the Currency Exchange Rate Loader.
• The Migration Tool Kit is intended for loading data from old systems (for example, SICS 7.1).
• The Exchange Rate Loader is intended for loading data from official sources (for example, Reuters).

The January 1^st 1900 Day Rate #

SICS assumes that if a Day Rate with Category C for Primary Currency P and Foreign Currency F exists in the database, there must also exist a Day rate for January 1^st 1900, for the same combination of C, P and F.

This means that if Day Rates are loaded for a new combination of C, P and F, you must make sure that the January 1^st1900 rate is also created (either by creating it manually, or by adding a record to the Input File).

Failing to do this may cause unpredictable results when exchanging amounts in Reporting and in the on-line system.

Batch Bookings #

The Batch Bookings utility allows you to load worksheets, balances, detailed bookings and remittances to the system from an external input file. Technical Bookings, including ledger records, worksheets and balances, must be loaded separately from Remittances.

It uses the same comma separated file (csv-) formats as the Migration Tool Kit uses for loading accounting worksheet s, balances and details.

For more information about the file formats that the Batch Booking utility accepts, refer to the MTK Loader document.

The Batch Booking utility accepts these types of records in the input file:

• %ACCOUNTING WORKSHEET
• %ACCOUNTING BALANCE
• %ACCOUNTING DETAIL
• %REMITTANCE

For details of these record types, refer to the MTK Mapper document.

Batch Booking Utility #

You can find the Batch Booking Utility icon in the Data Loader Utility folder on the SICS System Administration Utility desktop.

To open the Batch Booking Utility:

1. Double-click the Batch Booking Utility icon.
2. You see the SICS Batch Booking dialog box.

Here you can specify the input file to be processed and the file names and directories of the output files (rejected records file, message file and the directory to use for Loader Files).

Write to Loader Files #

If you select the Write to Loader Files check box, you send the output of the import process to “Loader Files.” These files can later be bulk-loaded into the SICS database using the loader utility that comes with your Database Management System (DBMS).

This option allows you to do test imports of data. If you use this option for the real import, you must also execute the Update Accounting Data function (shown in the Batch Booking window) after the loader files have been loaded into the database. For further information, refer to the MTK Loader document.

Direct Import #

If you select the Write to Database check box, data processed by the utility (except rejected records) will be written directly into the target SICS database.

This option is recommended if:

1. The volume of the data to be loaded is relatively low (for example, less than one hundred thousand records) and only if no, or very few, users are using the system when the load is running.
2. You are certain that the quality of the data to be loaded is good.

If you are uncertain of the quality the input data, DXC highly recommends that you to do a test run, using the Write to Loader Files option. After the test run, you can go through the Message file to see that no warnings or error messages were produced.

Loader Options #

For information on the Options tab in the Batch Booking Utility window, refer to Exchange Rate Loader.

Batch Booking Options #

The options on the Batch Booking Options tab are only useful for Technical Bookings, and can be ignored when you load a csv-file that includes Remittances.

Enable Pre-Caching of Business and Claims Data #

DXC recommends that you do not select the Enable Pre-Caching of Business and Claims Data check box for Batch Booking import.

Look for Existing Accounting Classifications Names #

DXC recommends that you select the Look for Existing Accounting Classification Names check box before executing the Batch Booking Utility.

This option should only be selected if you can guarantee that the bookings in the import file only are related to businesses that do not yet have any bookings. If this is the case, you can achieve better performance by switching the option on.

If this option is disabled, the system will not check whether the Accounting Classifications specified for the detailed bookings already exist in the database. The system will instead create a new set (duplicate) Accounting Classifications.

The parameter Look for Existing Accounting Classification Names is not in function if the Look for Accounting Classification Reference Item Values is selected as well.

Look for Accounting Classification Reference Item Values #

The Look for Accounting Classification Reference Item Values check box is enabled when you have selected the Look for existing Accounting Classification Names check box.

You should only select this option when you want the system to check the match between all reference item values in the Business Accounting Classification and the values in the batch file. If a match is found, the Batch Booking Utility will accept the record. If the system does not find a match, the record will be rejected. This is independent of the Accounting Classification Name (see above parameter).

If you do not select this check box, the system will not validate the individual Accounting Classifications Reference item values, but only rely on the Accounting Classification Name (see above parameter).

Accounting Year, Insured Period Start Year #

You should only select this option when you want the system to validate the Accounting Year against the start year of the actual Insured Period. The system will reject the record if the Accounting Year is less than the start year of the Insured Period. If the Accounting Year is greater than or equal the start year of the Insured Period, the loading will continue.

Accounting Year, Booking Year #

You should only select this option when you want the system to validate the Accounting Year against the Booking Year in the record. The system will reject the record if the Accounting Year is greater than the Booking Year. If the Accounting Year is less than or equal the Booking Year, the loading will continue.

Date of Booking #

You should only select this option when you want the system to validate the Date of Booking in the record. The system will validate the Date of Booking, and if it is present the loading will continue. If the Date of Booking is missing in the record, the field will be set equal the current system date and the loading will continue.

Occurrence Year #

You should only select this option when you want the system to validate the Occurrence Year against the Underwriting Year in the record. The system will reject the record if the Occurrence Year is not equal the Underwriting Year, but only if the Cession Basis on the Administration Conditions is Occurrence Year. If the Occurrence Year is equal the Underwriting Year, the loading will continue.

Note! Independent of the above parameters, the system will refuse to load a record if:

• Accounting Period Start Date is less than the Insured Period Start Date.
• Accounting Period Start Date is greater than the Accounting Period End Date.
• The Entry Code in the detail record is Estimate but the Entry Code is not allowed as Estimate.
• The Entry Code in the detail record is Actual but the Entry Code must be an Estimate. Note! If the system refuses to load a record because of the above validations, open the Message File. All information why the system does not want to load is registered in this file.

Update Accounting Data after successful Loading #

Normally, you don’t need to execute this function for Batch Booking processes. You should only execute this function if you have sent the your data to a “Loader File” (used the Write to Loader Files option on the first Batch Bookings tab).

Rejected Records #

The Batch Booking utility will reject records that are not syntactically valid, or records that for other reasons cannot be loaded (for example, if a referenced business does not exist).

Error and warning messages are written to the file that you specify when starting the Load process.

Rejected records are written to the Rejected Records File. This file can later be used as an input file to the Batch Booking Utility (provided the reason for the rejections have been corrected).

Refer to Enable/Disable Message Logging for more information on Rejected Records File.

Edit Rejected Records #

From the Edit Rejections tab you can start a tool for examining the rejected records along with the error and warning messages explaining why record were rejected.

Click the Execute button to start the tool.

The tool also allows you to edit the fields in the rejected records.

Changes can be saved, and the corrected file can then be used as input to another Batch Booking process.

Note! For each record that you change, you must click the Save Changes button for the changes to take effect.

For information, refer to the MTK Loader document.