Key Features

The ability of the Advanced ETL Processor to work with a number of disparate systems means that it is provided with a rich set of tools and functionality, which can be used in isolation or combined in a powerful way with other toolsets, either within the processor or with other third party tools. It is in effect, an “engineering” environment for the movement of data to and from different sources.

We will now explain and define the variety of features which are provided as part of the toolset. Let us first have a look at the data extraction process.

The extraction process can handle a variety of data formats, including multiple delimited or fixed width text files. The power of this system however, is in its ability to find files to load using mask.

It can easily interpret and manipulate Microsoft Access data, from a number of different databases. Again the end user can use a mask to find the tables to load the data from. It does also apply to Excel and DBF/FoxPro files.

The Advanced ETL Processor also has other useful database features, such as the ability to connect to any Object Database Connectivity (ODBC) database. ODBC was intended to enable developers' access to any data through any application, regardless of the DBMS used for managing that data. ODBC boasts platform independence since it has been purposefully designed in a way that makes it distinct from database systems, programming languages and operating systems. The Advanced ETL Processor utilises this technology to great effect.

So what if you don’t have an Oracle or MySQL database? What about SQL Server? No problem, the Advanced ETL Processor can handle SQL Server data as efficiently as data from any other type of database.

• Multiple Delimited or Fixed width Text files
• Multiple Excel files + Multiple Excel Spread sheets
• Multiple MS Access Databases + Multiple Tables
• XML Files
• Qvx and Qvd QlikView files
• Multiple DBF Files
• Multiple Clarion DAT Files
• Any ODBC compliant database
• Any JDBC compliant database
• Full support for OLE DB
• Direct connection to Oracle, SQL Server, MySql, PostgreSQL, SQLite, SQL Server Compact and Interbase/Firebird
• Multiple Tables
• File System
• POP3 Servers
• IMAP4 Servers
• Windows Event Log
• TCP/IP Socket
• RSS
• Google Spread Sheet
• SalesForce
• BrightPearl
• Slemma
• BDE
• JSON
• Clarion

The Advanced ETL Process has a robust validation process built in. The types and nature of the validations taking place can be tweaked and configured by the user. A full range of validation functions are included. Validations can be performed on the basis of data type, lists of values, and regular expressions, which can be individually changed according to requirements.

Validation:

• 195+ Validation Functions
• String Validation
• Number Validation
• Date Validation
• Time Validation
• Validation Against list of values
• Regular Expressions: Post codes, Phone Numbers, VAT Numbers ETC

The process of “transformation” involves the manipulation of data so that it can be converted from one format or data type to another. The Advanced ETL Processor provides a useful set of “built in” transformation functions. Transformations can be performed on strings, numbers, dates, coded values and whole chunks of data. The transformation functionality allows for the sorting and grouping of data according to requirements. Data can also be duplicated as required.

In addition to the standard data transformation abilities, the processor can also perform complex translation functions. An example would be if an integer variable = “1”, then set a text variable to “yes”. Once data is translated, it is possible to join data into a completely new format or present it in a new way. The Advanced ETL Processor provides many flexible alternatives for data manipulation, and these are not difficult to take advantage of.

The Advanced ETL Processor also provides the ability to derive calculated values, join data together from multiple fields, summarise multiple rows at once, or can split or merge columns at will.

The flexibility and power of the processor means that you can customize data transformation and conversion functions according to your requirements with a click of the mouse. This saves hours and hours of coding.

Transformation:

• 200+ Transformation Functions
• String Transformation
• Number Transformation
• Date Transformation
• Sorting
• Grouping
• Deduplication
• Translating coded values (e.g., if the source system stores 1 for male and 2 for female, but the warehouse stores M for male and F for female)
• Deriving a new calculated value (e.g., sale amount = qty * unit price)
• Joining together data from multiple fields
• Summarizing multiple rows of data (e.g., total sales for each store, and for each region)
• Generating surrogate key values
• Transposing or pivoting (turning multiple columns into multiple rows or vice versa)
• Splitting a column into multiple columns (e.g., putting a comma-separated list specified as a string in one column as individual values in different columns)
• Customised Transformation
• Primary Key Generation
• Running Totals
• Powerful scripting language
• System variables

The loading capability of the Advanced ETL Processor is superior to other basic tools such as SQL Loader, because it provides the administrator with several options and ways of providing database load capability without creating any code.

Other tools allow you to load data into a single database table at a time, under a single instance. The ETL processor on the other hand allows you to specify multiple upload targets which mean you can save time loading each individual table. Another useful feature is that you can execute SQL scripts either prior to the load or after the load has completed. This is useful for tidying up the data or providing a report on the result of the load process once it completes.

It does not matter if the file to be loaded is a fixed or variable length text file, the Advanced ETL Processor can handle it. It works with Access, DBF files, Oracle, SQL Server and any ODBC compliant database.

It is one of the most powerful database loading tools in the marketplace.

Loading:

• Multiple Data Targets
• SQL scripts execution before and after loading
• Delimited or Fixed width Text files
• MS Access Database
• Microsoft Excel File
• DBF Files
• Clarion DAT Files
• Any ODBC compliant database
• Any OLE DB compliant database
• Oracle database
• Microsoft SQL Server database
• Microsoft SQL Server CE
• SQLite
• MySQL
• PostgreSQL
• Interbase/Frebird
• File System
• SMTP Servers
• XML Files
• QlikView Qvx and Qvd files
• SalesForce
• Slemma
• BDE
• JSON

Oracle

There are two ways of loading available:

• Oracle direct path loading
• Conventional path

SQL server

This software uses the same API as Microsoft DTS and SSIS service.