Introduction

Advanced ETL Processor is a Codeless ETL Tool. Advanced ETL Processor is designed to automate all of your business processes. It saves you time and money so you can concentrate on what is important: running the business.

The Advanced ETL Processor is an end to end database extraction and importing tool. The beauty of the system is that it saves the drudgery and manual tasks normally required for tasks of this type, such as the writing of code, and all the transformations, validations and general checks normally performed. Using traditional methods, operations of this type can only be performed in stages, and not as one smooth operation!

For example, the traditional method of importing data from one system to another is to write specific code to extract data from the source database, e.g. an Oracle database, by creating a CSV comma delimited file, and then writing code in the new language or system, for instance, Microsoft Access, and then performing the import. However, the operation does not end there. Any data imported has to be sorted, duplicated and loaded into the database using appropriate primary and foreign key constraints. This is only possible by creating code designed to achieve this process. You then need to manually send an e-mail to the administrator when the process is complete. In other words, each stage cannot be left to run in an automated fashion and has to be completed before proceeding to the next.

As you can see, the process is not straightforward. The Advanced ETL Processor automates all these processes in a simple and transparent fashion, and all without writing any code whatsoever.

As stated, the tool to handle any kind of database, including Oracle, Microsoft Access, SQL Server, DB2, MySQL, PostgreSQL, Interbase, Excel spreadsheets, and a wide range of others. It is an excellent tool for those organisations who work with data warehouses, and where this involves working with a number of disparate databases.

Existing users find that the tool provides several benefits over existing tools such as Oracle SQL Loader, BCP, DTS or SSIS, such as the ability to update records automatically via utilisation of the primary key.

For example, typical usage would be to download an Excel file from the FTP server, load into a database run some calculations, email results to a manager and log to the administrator.

The Advanced ETL Processor would automatically carry out all the following steps for this task using the following process:

1. Download Excel file from the FTP server.
2. Extract required data using any appropriate filters (these can easily be configured in the tool beforehand).
3. Validate data formats to ensure they are consistent with the target database.
4. Sort the data appropriately.
5. Migrate data into relevant tables, including any duplication where required.
6. Run any stored procedures or scripts associated with the backend process.
7. Notify the administrator when the process is complete.

The idea is to present the tool with a “roadmap” of what to do and a set of tools and procedures to execute the end to end process in one swoop. Once you are ready to go, you simply start the automation process and it completes all the necessary tasks required without any further intervention by the user or developer. Unlike standard migrations, it may be possible to leave the tool to do its job overnight, saving staff resources and daytime business hours. If something went wrong Advanced ETL Processor writes detailed messages into the log.

Since Advanced ETL Processor is a Codeless ETL Tool most of the tasks performed without writing a single line of code

The Advanced ETL Processor provides a mechanism by which a number of different processes, protocols and systems can interact to provide the technical machinery to process a number of tasks from start to finish.

Enterprise environments benefit from this type of system, such as Fortune 100 companies and large corporations. However, it is equally useful for the small business environment.

Advanced ETL Processor can handle a number of different IT functions and services. For example, it can process databases, files, email protocols such as SMTP, pop3 and IMAP, FTP, Microsoft Office documents, and numerous others.

The system works by working to a schedule and set of tasks created and engineered by the end user. Some of these tasks may involve the running of a shell script; dos batch file or an SQL script/stored procedure. The system is set up like a “conveyor belt” of tasks, each one to be completed before the next one can take place. However, it is also possible to set up tasks to run concurrently, and indeed this would increase the efficiency of some of the tasks.

Although the system itself is “codeless” i.e. the end-user does not need to enter any code, it does not prevent the execution of code or having the code is written to perform certain complex actions. However, any code written is not part of the Advanced ETL Processor and can be run independently if required. This provides even more flexibility.

Here are some examples of what our customers use this automation tool for:

• Move data from one database system to another
• Import sales into a marketing system
• Validate and correct key data
• Remove duplicated data
• Avoid re-keying data
• Design, generate and email reports
• Download files
• Automatically respond to emails
• Populate Data warehouses
• Deal With Dirty Data
• Extract Data from Emails
• Send emails to customers

• Validate Postcodes, Phone numbers, TAX Codes using regular expressions
• Validate Date Formats
• Validate Date and Time
• Validate Numbers
• Validate Strings

• Sort Data
• Group Data
• Summarize Data
• Pivot and UnPivot Data
• Translate coded values.
• Create surrogate keys
• Split, merge Fields
• Split data and load it into different tables
• Add, replace, and delete strings

• Add Records
• Update Records
• Delete Records
• Add New and Update existing Records
• Run SQL scripts
• Generate insert script
• Parse XML