Advanced Etl Processor Professional User Manual

Advanced ETL Processor Pro&Ent User manual

Copyright © DB Software Laboratory 2008-2009 www.dbsoftlab.com

Advanced ETL Processor Professional & Enterprise User Manual

Contents 1. Introduction ........................................................................................................................... 10 2. What can Advanced ETL Processor do? ............................................................................ 12 2.1 Case Study: Automatic Processing of Financial Data ..................................................... 14 3. Requirements......................................................................................................................... 16 4. Key features ........................................................................................................................... 18 4.1 Extraction Process ........................................................................................................... 18 4.1.1 Multiple Data Formats.............................................................................................. 18 4.1.2 Multiple Databases and Table Processing................................................................ 18 4.1.3 Other Database Features........................................................................................... 18 4.2 Summary of the Extraction Process: ............................................................................... 18 4.3 Validation Process ........................................................................................................... 19 4.4 Summary of Validation Processes:.................................................................................. 19 4.5 Transformation Process ................................................................................................... 19 4.6 Powerful Data Transformation ........................................................................................ 19 4.7 Summary of Transformation Processes:.......................................................................... 20 4.8 Loading Process............................................................................................................... 21 4.9 Summary of the Loading Process:................................................................................... 21 4.10 Automation Process....................................................................................................... 22 4.11 Summary of the Automation Processes:........................................................................ 22 5. Advanced ETL Processor Architecture .............................................................................. 23 6. Demo Data............................................................................................................................. 25 7. Options .................................................................................................................................. 27 8. User Interface ........................................................................................................................ 32 8.1 System Menu ................................................................................................................... 32 8.2 Advanced ETL Processor Graphical User Interface: Main window ............................... 32 8.2.1 Objects tree description ............................................................................................ 33 8.2.2 Objects tree list ......................................................................................................... 33 8.2.3 Objects Groups ......................................................................................................... 34 8.2.4 Status Bar.................................................................................................................. 36 9. Directories and Connections.................................................................................................. 37 9.1 Directories ....................................................................................................................... 37 9.2 Database Connections ..................................................................................................... 38 9.2.1 Oracle Connection .................................................................................................... 39 9.2.2 Microsoft SQL Server Connection ........................................................................... 40 9.2.3 ODBC Connections .................................................................................................. 41 9.3 Ftp Connection ................................................................................................................ 44 9.4 SMTP Connection ........................................................................................................... 45 9.5 POP3 Connection ............................................................................................................ 46 10. Processing Data ................................................................................................................... 47 10.1 Screen Overview ........................................................................................................... 47 Main tool bar ......................................................................................................................... 48 10.2 Transformation Properties ............................................................................................. 49 10.3 Template tab .................................................................................................................. 50 10.4 Execution Log Tab ........................................................................................................ 51 10.5 Rejected Records Tab.................................................................................................... 52 10.6 Creating new transformation and working with Objects............................................... 53 10.7 Working with Reader .................................................................................................... 55 Copyright © 2009 DB Software Laboratory

Page 2 of 276

Advanced ETL Processor Professional & Enterprise User Manual 10.7.1 Universal Data Reader............................................................................................ 55 10.7.2 Data source is a Text File. ...................................................................................... 56 10.7.3 Data source is MS Access or Excel File................................................................. 61 10.7.4 Data source is a DBF File....................................................................................... 65 10.7.5 Data source is a ODBC Connection ....................................................................... 67 10.7.6 Data source is a MS SQL Server ............................................................................ 69 10.7.7 Data source is an Oracle Database ......................................................................... 70 10.8 Working with Validator................................................................................................. 71 10.8.1 Debugging Validation............................................................................................. 74 10.9 Working with Transformer............................................................................................ 77 10.9.1 Auto mapping ......................................................................................................... 79 10.10 Working with Grouper ................................................................................................ 80 10.11 Working with Sorter .................................................................................................... 81 10.12 Working with Deduplicator......................................................................................... 83 10.13 Working with UnPivot ................................................................................................ 84 10.14 Working with Pivot ..................................................................................................... 89 10.15 Working with Writer ................................................................................................... 94 10.15.1 Target type is a Text File...................................................................................... 94 10.15.2 Target type is MS Access or Excel File................................................................ 97 10.15.3 Target type is a DBF File...................................................................................... 99 10.15.4 Target type is ODBC connection........................................................................ 101 10.15.5 Target type is MS SQL Server............................................................................ 102 10.15.6 Target type is Oracle........................................................................................... 104 10.15.7 How to Update/Delete Records .......................................................................... 105 10.15.8 Running SQL Scripts.......................................................................................... 107 11. Validation Rules ................................................................................................................ 108 11.1 Strings.......................................................................................................................... 109 11.1.1 Is Null ................................................................................................................... 109 11.1.2 Is Empty String..................................................................................................... 109 11.1.3 Is Alpha ................................................................................................................ 109 11.1.4 Is Alpha Numeric.................................................................................................. 109 11.1.5 Is Hex.................................................................................................................... 110 11.1.6 Is Equal To ........................................................................................................... 110 11.1.7 Is Starts With ........................................................................................................ 111 11.1.8 Is Ends With ......................................................................................................... 111 11.1.9 Contains................................................................................................................ 111 11.1.10 In List.................................................................................................................. 111 11.2 Numbers ...................................................................................................................... 116 11.2.1 Is Number ............................................................................................................. 116 11.2.2 Is Integer ............................................................................................................... 116 11.2.3 Is Positive ............................................................................................................. 116 11.2.4 Is Negative............................................................................................................ 116 11.2.5 Is Number Between .............................................................................................. 117 11.2.6 Is Number Less Than............................................................................................ 118 11.2.7 Is Number More Than .......................................................................................... 119 11.3 Date.............................................................................................................................. 119 11.3.1 Is Date................................................................................................................... 119 11.3.2 Is Date Between.................................................................................................... 121 11.3.3 Is Date Less Than ................................................................................................. 122 Copyright © 2009 DB Software Laboratory

Page 3 of 276

Advanced ETL Processor Professional & Enterprise User Manual 11.3.4 Is Date More Than................................................................................................ 122 11.3.5 Is Monday, Is Tuesday, Is Wednesday, Is Thursday, Is Friday, Is Saturday, Is Sunday, Is Today, Is Yesterday, Is Tomorrow, Is Weekend, Is Weekday................... 123 11.3.6 Is January, Is February, Is March, Is April, Is May, Is June, Is July, Is August, Is September, Is October, Is November, Is December, Is Current Month, Is Last Month, Is Next Month...................................................................................................................... 123 11.3.7 Is 1st Quarter, Is 2nd Quarter, Is 3rd Quarter, Is 4th Quarter, Is Current Quarter, Is Last Quarter, Is Next Quarter .......................................................................................... 124 11.3.8 Is Leap Year, Is Current Year, Is Last Year, Is Next Year ................................... 124 11.3.9 Is Current Week, Is Last Week, Is Next Week..................................................... 124 11.3.10 Is Within Past Minutes, Is Within Past Hours, Is Within Past Days, Is Within Past Weeks, Is Within Past Months ........................................................................................ 125 11.4 Time............................................................................................................................. 126 11.4.1 Is Time.................................................................................................................. 126 11.4.2 Is Time Between................................................................................................... 127 11.4.3 Is Time Less Than ................................................................................................ 128 11.4.4 Is Time More Than ............................................................................................... 128 11.4.5 Is Second .............................................................................................................. 129 11.4.6 Is Minute............................................................................................................... 129 11.4.7 Is Hour 24 ............................................................................................................. 129 11.4.8 Is Hour 12 ............................................................................................................. 129 11.4.9 Is PM .................................................................................................................... 130 11.4.10 Is AM.................................................................................................................. 130 11.5 Regular Expressions .................................................................................................... 131 11.5.1 Regular Expression............................................................................................... 132 11.5.2 Is IP Address V4................................................................................................... 133 11.5.3 Is IP Address V6................................................................................................... 133 11.5.4 Is Email................................................................................................................. 133 11.5.5 Is ISBN 10 ............................................................................................................ 133 11.5.6 Is ISBN 13 ............................................................................................................ 134 11.5.7 Is Credit Card ....................................................................................................... 134 11.5.8 Is URL .................................................................................................................. 134 11.5.9 Is UNC.................................................................................................................. 134 11.5.10 Is File Name........................................................................................................ 135 11.5.11 Is UK Post Code ................................................................................................. 135 11.5.12 Is UK VAT Number ........................................................................................... 135 11.5.13 Is UK Bank Sort Code ........................................................................................ 135 11.5.14 Is UK Vehicle Registration Number .................................................................. 136 11.5.15 Is UK Driver Licence.......................................................................................... 136 11.5.16 Is UK National Insurance Number ..................................................................... 136 11.5.17 Is UK Phone Number ......................................................................................... 136 11.5.18 Is UK Mobile Number........................................................................................ 137 11.5.19 Is US Zip Code ................................................................................................... 137 11.5.20 Is US Social Security Number............................................................................ 137 11.5.21 Is US Phone Number .......................................................................................... 138 11.5.22 Is Canadian Zip Code ......................................................................................... 138 11.5.23 Is Canadian Provincial Code .............................................................................. 138 11.5.24 Is Canadian Branch-Transit/ Bank code ............................................................. 138 11.5.25 Is Canadian Social Insurance Number................................................................ 139 Copyright © 2009 DB Software Laboratory

Page 4 of 276

Advanced ETL Processor Professional & Enterprise User Manual 11.5.26 Is Australian Post Code ...................................................................................... 139 11.5.27 Is Australian Phone Number............................................................................... 139 11.5.28 Is Australian Mobile Number ............................................................................. 140 11.5.29 Is Argentinean Post Code ................................................................................... 140 11.5.30 Is Dutch Post Code ............................................................................................. 140 11.5.31 Is Dutch Phone Number ..................................................................................... 140 11.5.32 Is Dutch Car Number Plate................................................................................. 141 11.5.33 Is Belgian Post Code........................................................................................... 141 11.5.34 Is Belgian VAT Number..................................................................................... 141 11.5.35 Is French Post Code............................................................................................ 141 11.5.36 Is French VAT Number...................................................................................... 142 11.5.37 Is French Phone Number .................................................................................... 142 11.5.38 Is German Post Code .......................................................................................... 142 11.5.39 Is German VAT Number .................................................................................... 142 11.5.40 Is German Car License Plate .............................................................................. 143 11.5.41 Is German IBAN................................................................................................. 143 11.5.42 Is Italian Post Code............................................................................................. 143 11.5.43 Is Italian VAT Number....................................................................................... 143 11.5.44 Is Italian IBAN ................................................................................................... 144 11.5.45 Is Italian Phone Number ..................................................................................... 144 11.5.46 Is Italian Mobile Number ................................................................................... 144 11.5.47 Is Spanish Post Code .......................................................................................... 144 11.5.48 Is Spanish VAT Number .................................................................................... 145 11.5.49 Is Spanish IBAN................................................................................................. 145 11.5.50 Is Spanish Phone Number .................................................................................. 145 11.5.51 Is Swedish Post Code ......................................................................................... 145 11.5.52 Is Swedish VAT Number ................................................................................... 146 11.5.53 Is Swedish IBAN ................................................................................................ 146 11.5.54 Is Swedish Phone Number.................................................................................. 146 11.5.55 Is Swedish Person Number................................................................................. 146 11.5.56 Is Austrian Mobile Phone Number..................................................................... 147 11.5.57 Is Danish Post Code............................................................................................ 147 11.5.58 Is Danish VAT Number...................................................................................... 147 11.5.60 Is Danish Phone Number.................................................................................... 147 11.5.61 Is Danish Civil Registration Number ................................................................. 148 11.5.62 Is Finnish VAT Number ..................................................................................... 148 11.5.63 Is Greek VAT Number ....................................................................................... 148 11.5.64 Is Irish VAT Number.......................................................................................... 148 11.5.65 Is Luxembourg VAT Number ............................................................................ 149 11.5.66 Is Portuguese VAT Number ............................................................................... 149 11.5.67 Is Estonian Post Code......................................................................................... 149 11.5.68 Is Estonian VAT Number ................................................................................... 149 11.5.69 Is Latvian VAT Number..................................................................................... 150 11.5.70 Is Lithuanian VAT Number................................................................................ 150 11.5.71 Is Slovenian VAT Number ................................................................................. 150 11.5.72 Is Slovakian VAT Number ................................................................................. 150 I11.5.73 s Slovakian IBAN.............................................................................................. 151 11.5.74 Is Polish VAT Number....................................................................................... 151 11.5.75 Is Polish IBAN.................................................................................................... 151 Copyright © 2009 DB Software Laboratory

Page 5 of 276

Advanced ETL Processor Professional & Enterprise User Manual 11.5.76 Is Czech Republic VAT Number........................................................................ 151 11.5.77 Is Czech Republic IBAN .................................................................................... 152 11.5.78 Is Netherlands VAT Number.............................................................................. 152 11.5.79 Is Hungarian VAT Number ................................................................................ 152 11.5.80 Is Maltese VAT Number .................................................................................... 152 11.5.81 Is Algerian Post Code ......................................................................................... 153 11.5.82 Is Andorran Post Code........................................................................................ 153 11.5.83 Is Andorran IBAN .............................................................................................. 153 11.5.84 Is Azerbaijani Post Code .................................................................................... 153 11.5.85 Is Bahraini Post Code ......................................................................................... 154 11.5.86 Is Bangladeshi Post Code ................................................................................... 154 11.5.87 Is Belarusian Post Code...................................................................................... 154 11.5.88 Is Brunei Post Code ............................................................................................ 154 11.5.89 Is Chilean Post Code .......................................................................................... 155 11.5.90 Is Croatian Post Code ......................................................................................... 155 11.5.91 Is Cyprus Post Code ........................................................................................... 155 11.5.92 Is Cyprus VAT Number...................................................................................... 155 11.5.93 Is Egyptian Post Code......................................................................................... 156 11.5.94 Is Ethiopian Post Code ....................................................................................... 156 11.5.95 Is Faroe Islands Post Code.................................................................................. 156 11.5.96 Is French Polynesia Post Code ........................................................................... 156 11.5.97 Is Guadeloupe Post Code.................................................................................... 157 11.5.98 Is Guinean Post Code ......................................................................................... 157 11.5.99 Is Guinea-Bissau Post Code ............................................................................... 157 11.5.100 Is Russian Post Code ........................................................................................ 157 11.5.101 Is Russian Phone Number ................................................................................ 158 11.5.102 Is Israeli Phone Number ................................................................................... 158 11.5.103 Is Israeli Mobile Phone Number....................................................................... 158 11.5.104 Is Peru Phone Number...................................................................................... 158 11.5.105 Is Peru Cellular Number ................................................................................... 159 11.5.106 Is Indian Post Code........................................................................................... 159 11.5.107 Is Indian Phone Number ................................................................................... 159 11.5.108 Is Indian Mobile Number ................................................................................. 159 11.5.109 Is Indian Vehicle Registration Number ............................................................ 160 11.5.110 Is South African Phone Number....................................................................... 160 11.5.111 Is South African Mobile Number ..................................................................... 160 11.5.112 Is South African VAT Number ........................................................................ 160 11.5.113 Is US or CA Zip code ....................................................................................... 161 12. Transformation Functions ................................................................................................. 162 12.1 Strings.......................................................................................................................... 162 12.1.1 Upper Case ........................................................................................................... 162 12.1.2 Lower Case ........................................................................................................... 162 12.1.3 Proper Case........................................................................................................... 162 12.1.4 First Up................................................................................................................. 162 12.1.5 Delete Spaces........................................................................................................ 163 12.1.6 Trim ...................................................................................................................... 163 12.1.7 Left Trim .............................................................................................................. 163 12.1.8 Right Trim ............................................................................................................ 163 12.1.9 Sub String ............................................................................................................. 164 Copyright © 2009 DB Software Laboratory

Page 6 of 276

Advanced ETL Processor Professional & Enterprise User Manual 12.1.10 Replace ............................................................................................................... 165 12.1.11 Ensure Prefix ...................................................................................................... 166 12.1.12 Ensure Suffix ...................................................................................................... 166 12.1.13 Ensure No Prefix ................................................................................................ 166 12.1.14 Ensure No Suffix ................................................................................................ 167 12.1.15 Left Pad .............................................................................................................. 167 12.1.16 Right Pad ............................................................................................................ 167 12.1.17 Escape String ...................................................................................................... 168 12.1.18 UnEscape String ................................................................................................. 168 12.1.19 Delete.................................................................................................................. 168 12.1.20 Left ..................................................................................................................... 169 12.1.21 Right ................................................................................................................... 169 12.1.22 Left Delete .......................................................................................................... 169 12.1.23 Right Delete........................................................................................................ 170 12.2 Numbers ...................................................................................................................... 171 12.2.1 Round ................................................................................................................... 171 12.2.2 Abs........................................................................................................................ 172 12.2.3 Sign....................................................................................................................... 172 12.3 Date.............................................................................................................................. 173 12.3.1 Date Format .......................................................................................................... 173 12.3.2 Current Date ......................................................................................................... 173 12.4 Miscellaneous .............................................................................................................. 174 12.4.1 Length................................................................................................................... 174 12.4.2 Literal ................................................................................................................... 174 12.4.2 User....................................................................................................................... 175 12.4.3 Splitter .................................................................................................................. 175 12.4.4 Joiner .................................................................................................................... 176 12.4.5 Calculation............................................................................................................ 177 12.4.6 Lookup.................................................................................................................. 206 12.4.7 Previous Value...................................................................................................... 209 12.4.8 Reader Parameter.................................................................................................. 210 12.4.9 Sequence............................................................................................................... 211 12.4.10 Running Total..................................................................................................... 212 13. Date formats ...................................................................................................................... 213 14. SQL Scripts ....................................................................................................................... 214 14.1 SQL Scripts Tool Bar .................................................................................................. 216 15. Reports............................................................................................................................... 217 15.1 Report Designer........................................................................................................... 217 15.1.1 Report Title........................................................................................................... 218 15.1.2 Page Header.......................................................................................................... 218 15.1.3 Group Header ....................................................................................................... 218 15.1.4 Code...................................................................................................................... 218 15.1.5 Data....................................................................................................................... 218 15.1.6 Report tool bar...................................................................................................... 219 15.1.7 Report Properties .................................................................................................. 219 15.1.8 Save Report to the Repository.............................................................................. 219 15.1.9 Report Connection................................................................................................ 219 15.1.10 Show Only Connections for Current Project...................................................... 219 15.1.11 Manage Versions ................................................................................................ 219 Copyright © 2009 DB Software Laboratory

Page 7 of 276

Advanced ETL Processor Professional & Enterprise User Manual 15.1.12 Add Versions ...................................................................................................... 220 15.1.13 Creating A Basic Report..................................................................................... 220 15.1.14 Report in Report Designer.................................................................................. 225 16. Email Templates................................................................................................................ 227 17. Packages ............................................................................................................................ 229 17.1 Package screen overview............................................................................................. 231 17.2 Joining Actions............................................................................................................ 232 17.3 Package Tool bar ......................................................................................................... 232 17.4 Transformation Action Properties ............................................................................... 233 17.5 SQL Script Action ....................................................................................................... 234 17.6 SQL Check Action ...................................................................................................... 235 17.7 Package Action............................................................................................................ 236 17.8 FTP Action .................................................................................................................. 237 17.9 ZIP Operation .............................................................................................................. 239 17.10 Receive Email............................................................................................................ 241 17.11 Send Email Action..................................................................................................... 243 17.12 External Application Action...................................................................................... 245 17.13 Check file action........................................................................................................ 246 17.14 File Operation Action ................................................................................................ 247 17.15 Compare Files Action................................................................................................ 248 17.16 Backup Repository Action......................................................................................... 249 17.18 Working with filenames and directories.................................................................... 250 18. Scheduler ........................................................................................................................... 251 19. Execution Monitor............................................................................................................. 255 19.1 Tool bar ....................................................................................................................... 257 19.2 Stopping execution ...................................................................................................... 257 20. SQL.................................................................................................................................... 258 20.1 SQL Toolbar................................................................................................................ 258 20.2 Side toolbar.................................................................................................................. 259 21 Version Control .................................................................................................................. 260 22. Command Line .................................................................................................................. 261 23. File names functions.......................................................................................................... 263 23.1 String Functions........................................................................................................... 263 23.2 Numeric Functions ...................................................................................................... 266 23.3 Date Functions............................................................................................................. 267 23.4 Conversion Functions.................................................................................................. 270 23.5 Miscellaneous Functions ............................................................................................. 271 24. Execution Agent ................................................................................................................ 272 25. Support Procedure ............................................................................................................. 273 26. License Agreement ............................................................................................................ 274

Copyright © 2009 DB Software Laboratory

Page 8 of 276

Advanced ETL Processor Professional & Enterprise User Manual Copyright Copyright © 2009 DB Software Laboratory Limited. All rights reserved. No portion of this document may be reproduced, transmitted, transcribed, stored in a retrieval system, or translated into any language, in any form or by any means, without prior written consent of DB Software Laboratory Limited.

(ote to U.S. Government users: Documentation and programs related to restricted rights - use, duplication or disclosure is subject to restrictions set forth in: GSA FMSS Schedule Contract No. GS00K92AFS2505-PS05. License Information You have access to Advanced ETL Processor Professional and Enterprise software and documentation pursuant to the terms of a Software License Agreement granted by DB Software Laboratory Limited. As a user of this software and documentation, you are bound by the terms of the Software License Agreement. All rights, title, and interest to this software remain with DB Software Laboratory Limited. Requests for copies of this publication and for technical information about DB Software Laboratory products should be made directly to DB Software Laboratory Limited. Disclaimer All information in this manual is subject to periodic change and revision without notice. While every effort has been made to ensure that this manual is accurate, DB Software Laboratory Limited excludes its liability for errors or inaccuracies (if any) contained herein. Registered Marks Any products or services mentioned or depicted in this document are identified by the trademarks or service marks of their respective companies or organisations. Edition Information This document refers to Advanced ETL Processor Professional and Enterprise version 3.3.2.

Copyright © 2009 DB Software Laboratory

Page 9 of 276

Advanced ETL Processor Professional & Enterprise User Manual

1. Introduction The Advanced ETL Processor is a Codeless ETL Tool. The 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 for your 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 de-limited 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 sum calculations, email results a to 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 date 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 script associated with the backend process. Copyright © 2009 DB Software Laboratory

Page 10 of 276

Advanced ETL Processor Professional & Enterprise User Manual 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

Copyright © 2009 DB Software Laboratory

Page 11 of 276

Advanced ETL Processor Professional & Enterprise User Manual

2. What can Advanced ETL Processor do? 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 a 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 code 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. When planning out tasks, it is probably best to specify the requirements beforehand. This can be documented in Standard English pseudo code, or simply provided by a list of actions. Here are some examples of what our customers use this automations tool for: Automate Everyday tasks • • • • • • • • • •

Move data from one database system to another Import sales into 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

Copyright © 2009 DB Software Laboratory

Page 12 of 276

Advanced ETL Processor Professional & Enterprise User Manual Check Data • • • • •

Validate Post codes, Phone numbers, TAX Codes using regular expressions Validate Date Formats Validate Date and Time Validate Numbers Validate Strings

Transform Data • • • • • • • • •

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

Load Data • • • • •

Add Records Update Records Delete Records Add New and Update existing Records Run SQL scripts

Now let’s provide a case study and a list of actions to see how the system can work in practice:

Copyright © 2009 DB Software Laboratory

Page 13 of 276

Advanced ETL Processor Professional & Enterprise User Manual

2.1 Case Study: Automatic Processing of Financial Data We are small Accounting Company based in Melbourne. Over last year our customer base had grow twice and it is still growing. We spend a lot of time thinking how we can optimise our business processes. First thing we did created standard Excel templates for everything. For example standard expenses form will consist of customer id, employee id, expense date, category and amount. Using standard forms saved us a lot of time but once all the forms are filled in they had to be processed manually. Our people ware constantly under the pressure and we had to employ an agency staff on temporary basis.

(ot any more. During routine Google search we discovered Advanced ETL Processor Ent which quickly becomes a corner stone for automating our business processes. By using Advanced ETL Processor Ent we were able to eliminate most of our manual tasks It is like spider which sits in the middle connected to all our systems. For example, for expenses form the process is follows. A customer emails us expenses form Advanced ETL Processor Ent package connects to mail server, downloads attachment, Loads Excel file into our accounting system, Runs SQL script to validate submitted data, Emails report back to the person who submitted expenses from At the end of month summary report emailed to the company automatically. Carl Barret, Accountant

Copyright © 2009 DB Software Laboratory

Page 14 of 276

Advanced ETL Processor Professional & Enterprise User Manual The Advanced ETL Processor can be used to automate the process of transferring and transforming data from one database to another, or from several databases to several other databases. The Advanced ETL Processor is a specialised tool designed for this task. For the Advanced ETL Processor the above tasks are very simple. It can perform tasks much more complex than this, but it does provide a flavour of what the processor can achieve. Additional validation checks can be built into the process, and any problems can be reported to an error log. The process can be made to halt on error, or it can continue, so that any problem areas can be re-executed at a later time. Once you are ready to go, it is a simple matter to start the Advanced ETL Processor and allow it to perform its tasks. Stages in the process can be changed or amended for different environments and systems. The processing stages can also be changed depending on what happens during the process, so it can make intelligent decisions based on what the creator of the process would like to happen during execution. Therefore “what if” situations can be handled and alternative processing routes included.

Copyright © 2009 DB Software Laboratory

Page 15 of 276

Advanced ETL Processor Professional & Enterprise User Manual

3. Requirements Below is the list of Software that must be installed before installation of Advanced ETL Processor: Software Microsoft Windows MDAC

Version 98 or higher 2.6 or higher

MS Excel ODBC driver MS Excel 2007 ODBC driver MS Access ODBC driver

4.00.6305.00 or higher 12.00.4518.1014 or higher 4.00.6364.00 or higher

MS Access 2007 ODBC driver

12.00.4518.1014 or higher

Separate download

FoxPro ODBC driver SQL ODBC driver

6.1.8629.1 or higher 2000.81.9041.40

Separate download

Oracle Client

7.3.4 or higher

Interbase client

Jet 4.0

(otes Part of OS on W2K, XP, Vista. Part of OS on W2K, XP, Vista. Separate download Part of OS on W2K, XP, Vista.

Part of OS on W2K, XP, Vista. Provided by Oracle

GDS32.DLL

Service pack 5 or higher

Part of OS on W2K, XP, Vista.

Only to work with Excel 3.0-2003 files Only to work with Excel 2007 files Only to work with MS Access 95-2003 Databases Only to work with MS Access 2007 Databases Only to work with DBF/FoxPro Files Only to work with MS SQL Server 7/2008 Only to work with Oracle Databases/Connect to Oracle repository Only to work with Interbase or Firebird Databases To connect to MS Access repository

Separate Downloads: FoxPro ODBC driver http://msdn.microsoft.com/en-us/vfoxpro/bb190233.aspx Office 2007 Data Access Components http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72EF94E038C891&displaylang=en

Copyright © 2009 DB Software Laboratory

Page 16 of 276

Advanced ETL Processor Professional & Enterprise User Manual Working with Oracle: Oracle client 8.1.7 and Oracle Ole DB Provider for Oracle to use Oracle repository on Oracle 8-9 and load data into/from Oracle Or Oracle client 9 and Oracle Ole DB Provider for Oracle to use Oracle repository on Oracle 8-9 and load data into/from Oracle Or Oracle client 10 and Oracle Ole DB Provider for Oracle to use Oracle repository on Oracle 910g and load data into/from Oracle. Or Oracle client 11 and Oracle Ole DB Provider for Oracle to use Oracle repository on Oracle 10-11g and load data into/from Oracle.

(ote: Depending on the Requirements you may or may not need to have all components installed There is no need to install clients for MySql and PostgreSQL they are integrated into the software itself.

Copyright © 2009 DB Software Laboratory

Page 17 of 276

Advanced ETL Processor Professional & Enterprise User Manual

4. 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. 4.1 Extraction Process 4.1.1 Multiple Data Formats 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. 4.1.2 Multiple Databases and Table Processing 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. 4.1.3 Other Database Features 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. 4.2 Summary of the Extraction Process: • • • • • • •

Multiple Delimited or Fixed width Text files Multiple Excel files + Multiple Excel Spreadsheets Multiple MS Access Databases + Multiple Tables Multiple DBF Files Any ODBC compliant database SQL Server databases Multiple Tables

Copyright © 2009 DB Software Laboratory

Page 18 of 276

Advanced ETL Processor Professional & Enterprise User Manual 4.3 Validation Process 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. 4.4 Summary of Validation Processes: Validation: • • • • • • •

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

4.5 Transformation Process 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. 4.6 Powerful Data Transformation 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.

Copyright © 2009 DB Software Laboratory

Page 19 of 276

Advanced ETL Processor Professional & Enterprise User Manual

4.7 Summary of Transformation Processes: Transformation: • • • • • • • • • • • • • • •

36 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

Copyright © 2009 DB Software Laboratory

Page 20 of 276

Advanced ETL Processor Professional & Enterprise User Manual

4.8 Loading Process 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. 4.9 Summary of the Loading Process: Loading: • • • • • • • • •

Multiple Data Targets SQL scripts execution before and after loading Delimited or Fixed width Text files MS Access Database Microsoft Excel File DBF File Any ODBC compliant database Oracle database Microsoft SQL Server database

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.

Copyright © 2009 DB Software Laboratory

Page 21 of 276

Advanced ETL Processor Professional & Enterprise User Manual

4.10 Automation Process The automation process of the Advanced ETL Processor is very powerful and generic. If there is a process requiring automation there is a very good chance that the ETL Processor can handle it. The ETL processor can automate a myriad of different technologies. In fact, "automation ability" sits at the core of the Advanced ETL Processor. This is a valid claim as the system caters for a number of database technologies and protocols. The coding to perform this is built in; therefore there is no need for further development by any programmers. The only actions required are that the definitions of the data and the functionality are specified, and the system does the rest. As previously stated, the automation functions allow you to set up a "conveyor belt" of actions. The administrator simply sets the process in motion. For instance, you may wish to run an SQL script, produce a report, copy a file, send a file over to a server via FTP, backup the resulting data, and then inform the project manager of the end result via email. The Advanced ETL Processor is an excellent automation system, without the need for coding. 4.11 Summary of the Automation Processes: Automation: • • • • • • • • • • • • • • •

SQL Scripts Email Templates Report Designer Integrated Scheduler Logging Email Notifications PO3 Email Receiver File Operations Check Files Compare Files Full range of FTP operations ZIP Applications SQL Data Check Automatic Backups

Copyright © 2009 DB Software Laboratory

Page 22 of 276

Advanced ETL Processor Professional & Enterprise User Manual

5. Advanced ETL Processor Architecture The following graphical depiction of a typical ETL installation shows how the software interacts with other components and interfaces belonging to various databases. The processor sits in the middle of the various databases and carries out its tasks, such as converting, transforming and validating data from various sources. As you can see that the Advanced ETL Processor uses native low level API's for specific databases, such as the Oracle Call Interface (OCI), or, in order to handle the Microsoft SQL Server database the BCP API. Other API's can also be "plugged in" such as the ODBC API which allows MS Access, DBF or plain flat files to be processed. (ote: One of major benefit of using native low level API' is a great performance boost it gives to Advanced ETL Processor.

Copyright © 2009 DB Software Laboratory

Page 23 of 276

Advanced ETL Processor Professional & Enterprise User Manual

Oracle

OCI API

ETL Processor

SQL ODBC Excel MS Access

Oracle SQL

BCP API Data Reader Validator Transformer Groupper Sorter Deduplicator Pivot Unpivot Data Writer

ODBC Excel ODBC API

MS Access DBF Files

DBF Files Integrated Clients

MySQL PostgreSQL

Automation

MySQL PostgreSQL Interbase

Interbase Flat Files

Extraction

Connections Scheduler Packages SQL Scripts Emails Reports FTP Files Check File

Flat Files

Oracle, MS SQL Server, MS Access, MySQL, PostgreSQL, Interbase Copyright © 2009 DB Software Laboratory

Page 24 of 276

Advanced ETL Processor Professional & Enterprise User Manual

6. Demo Data In order to provide a flavour of the types of data the Advanced ETL Processor can handle, a number of different file types have been provided for demonstration purposes. A range of different types of data files have been provided, in addition to SQL scripts to create database tables in Oracle, and MS SQL Server. Text files Examples provided are using text files in “C:\Program Files\DB Software Laboratory\Demo\Buffer” and “C:\Program Files\DB Software Laboratory\Demo\Text Files” Access Databases “C:\Program Files\DB Software Laboratory\Demo\MS Access” DBF Files “C:\Program Files\DB Software Laboratory\Demo\DBF” Excel Files “C:\Program Files\DB Software Laboratory\Demo\Excel” (ote: If you want to use other locations please amend Directories properties. Demo tables Use SQL Scripts provided to create demo tables for Oracle and MS SQL Server. Please adjust connection details before executing these scripts. Most of the SQL Server Imports use DEMO database. You have to create demo tables within this database first before executing import scripts. ODBC connections During the setup process, the following ODBC DSN is created during the installation: • • • • • •

ODBC_FOXPRO ODBC_ACCESS_SOURCE ODBC_ACCESS_TARGET ODBC_MS ODBC_ORACLE ODBC_EXCEL

Copyright © 2009 DB Software Laboratory

Page 25 of 276

Advanced ETL Processor Professional & Enterprise User Manual These ODBC connections provide the basis for the ETL Processor to perform its tasks and process different types of data, while ensuring that the automation of these processes is completely transparent.

Copyright © 2009 DB Software Laboratory

Page 26 of 276

Advanced ETL Processor Professional & Enterprise User Manual

7. Options The Advanced ETL Processor allows you to tweak key settings. Using this provision you can change the repository type, control the execution of packages and where the results will be logged, set interface actions such as the prevention of accidental deletion and set email options to report the outcome of the automation to individuals or groups of people. To change Advanced ETL Processor settings click System menu-> File-> Options. The Dialog box will appear.

Copyright © 2009 DB Software Laboratory

Page 27 of 276

Advanced ETL Processor Professional & Enterprise User Manual

The Repository tab defines the repository connection. Once the logon parameters have been verified via the “Test Connection” dialogue this information is saved so it is not necessary to provide this information again. The Repository type can be: • • • • • •

MS Access MS SQL Server Oracle Interbase (Firebird) MySql PostgreSQL

(ote: Provided Default Repository [MS Access] contains all the SQL scripts required to create Oracle, MS SQL server, Interbase, MySql and PostgreSQL repositories. Execution tab defines settings related to the logging and Packages execution.

Copyright © 2009 DB Software Laboratory

Page 28 of 276

Advanced ETL Processor Professional & Enterprise User Manual

Copyright © 2009 DB Software Laboratory

Page 29 of 276

Advanced ETL Processor Professional & Enterprise User Manual The Interface tab defines key settings. These operate globally during the activities of the Advanced ETL Processor. For instance, in order to save time when creating packages to automate tasks, the editor can be set so that it automatically opens. In order to ensure that any changes made to scripts and packages upon exit, you can safeguard this by checking the “Save all scripts and packages on exit” box. Accidental deletion is prevented by requesting that the user is asked before deleting any objects. Another aspect of the interface actions is the updating of the log. This can be set so that once a package has completed its actions the log is updated with all the actions which took place during execution. Finally, if you wish to automatically refresh table list when creating/opening transformation you simply check the “Update Tables List” to ensure that you this is accurate, and up to date.

Copyright © 2009 DB Software Laboratory

Page 30 of 276

Advanced ETL Processor Professional & Enterprise User Manual The Email tab defines settings required to send Emails. As the Advanced ETL Processor is designed to work automatically and in batch mode, it will be necessary to specify where emails will be sent from. Emails messages are used to convey when processes and activities have been completed. It is also possible to specify the default recipient. The default recipient can be set to a group email address, or it can be left blank.

Copyright © 2009 DB Software Laboratory

Page 31 of 276

Advanced ETL Processor Professional & Enterprise User Manual

8. User Interface Upon starting the Advanced ETL Processor software, you will be presented with the main window. This is where the main tasks are controlled such as processing cycles, and other processing tasks taking place in the processor. 8.1 System Menu The system menu allows you access to the essential setting and tools you will need to use the processor. 8.2 Advanced ETL Processor Graphical User Interface: Main window System Menu

Objects tree

Copyright © 2009 DB Software Laboratory

Status bar

Page 32 of 276

Advanced ETL Processor Professional & Enterprise User Manual

8.2.1 Objects tree description The "Objects" tree lists the main categories for the provision of objects. For instance, the "Directories" category contains icons representing the location of files and related information belonging to an individual item. For example, by clicking on the "Excel Files" icon you will access the directory area where all the Excel files are stored for processing. You may also find icons representing individual database connection services. For instance, you can see connections available for both MS SQL Server and Oracle. Other object types include the ODBC connections. You can see from the illustration that the DEMO system has been configured, and as a result the ODBC connections created are listed in the objects tree. The Advanced ETL Processor provides the Objects tree so that users have easy access to the main objects. The full list of objects is described below: 8.2.2 Objects tree list Below is a list of possible objects with the short descriptions: Projects

Provides a way to group objects together

Directories

Defines path to flat files, Excel files, MS Access databases or DBF files SQL Server Connection Defines MS SQL Server connection properties ODBC Connection

Defines ODBC connection properties

Oracle Connection

Defines Oracle connection properties

FTP Connection

Defines FTP Server connection properties

SMTP Connection

Defines SMTP connection properties

POP3 Connection

Defines POP3 server connection properties

Transformations and Transformations Groups Reports and Reports Groups SQL Scripts and Scripts Groups Email templates and groups Packages and packages groups

Provides a way of transferring data from one database/file into another together with complex transformation, validation, sorting and grouping operations Reports design Defines SQL statements to perform against target Databases Creates general email templates. Combines complex Actions together like Ftp downloads File operations, emails, Check files, SQL scripts and Transformations.

Copyright © 2009 DB Software Laboratory

Page 33 of 276

Advanced ETL Processor Professional & Enterprise User Manual 8.2.3 Objects Groups The Advanced ETL Processor also has the ability to group objects together into “groups”. The purpose of doing this is so that objects of a specific type can be viewed as a branch of the object tree. Whenever a new object is added it will be placed under the appropriate branch for its type.

Groups of Objects

Copyright © 2009 DB Software Laboratory

Page 34 of 276

Advanced ETL Processor Professional & Enterprise User Manual To create a new object group select appropriate object type for example transformation and click “new”.

Depending on the object type, the appropriate dialogue will be presented. In this example the Transformation Group has been selected. The dialogue in this case requests a description and comment about the object to be created. Some of the fields in a dialogue of this type will be mandatory and will therefore have to be entered. However, comments will be optional, and just provide the user with the ability to specify more detail about an object and what it does.

Copyright © 2009 DB Software Laboratory

Page 35 of 276

Advanced ETL Processor Professional & Enterprise User Manual

8.2.4 Status Bar The Status Bar indicates various activities during processing. It provides an indication of what the system is doing at any moment, including indicating when the processor is connected to a repository and if it is processing or waiting for various actions to take place.

Copyright © 2009 DB Software Laboratory

Page 36 of 276

Advanced ETL Processor Professional & Enterprise User Manual

9. Directories and Connections 9.1 Directories If a new directory is required, selecting “New” on the new directory icon brings up the following dialogue:

The following steps are needed to allow creation of a directory, as follows: • • •

In the Name Text Box type in a new name for the directory you are about to create Fill in Directory path you wish to load data from Click OK to close the directory Properties Window

(ote: The user may change connection or directory properties at any time by double clicking on it. It is also possible to use sub directory in existing directory \Subdirectory. For example <Buffer>\tmp will expand to: C:\Program Files\DB Software Laboratory\Demo\Buffer\tmp

Copyright © 2009 DB Software Laboratory

Page 37 of 276

Advanced ETL Processor Professional & Enterprise User Manual

9.2 Database Connections The database connection functionality provides a way of connecting to database servers. Once a connection is set up, it is always available until deleted or amended. In order to set up a new connection it will be necessary to have database credentials to hand. When setting up database connections, you should ensure that the account you wish to use has access to all the available functionality you may wish to use. For instance, it is not much use, setting up a connection to an Oracle account to use certain stored procedures, if the underlying Oracle permissions are not set for this facility. The account used in the Advanced ETL Processor for the database should allow the user to perform underlying database actions, which may not be under control of the processor directly. To create a new connection select appropriate connection type and click “New”.

Once this step has been completed, you will be provided with an appropriate dialogue which will request information about the new connection you wish to create, and the type of connection you are creating.

Copyright © 2009 DB Software Laboratory

Page 38 of 276

Advanced ETL Processor Professional & Enterprise User Manual

9.2.1 Oracle Connection For an Oracle connection you will need to specify the TNS name required, and provide the user name and password for the connection. (ote: It is also possible to use Oracle instant client.

The procedure to set up an Oracle Connection is as follows: • • •

In the Name Text Box type in a new name for the connection you are about to create Select a TNS Name from the Server Drop Down List Fill in Username/Password for the database you wish to connect to a. If you are unsure of these parameters, please contact your Database Administrator for the correct settings.

• •

Click Test to ensure the details you have provided are correct Click OK to close the connection properties window

Copyright © 2009 DB Software Laboratory

Page 39 of 276

Advanced ETL Processor Professional & Enterprise User Manual

9.2.2 Microsoft SQL Server Connection The procedure for many database connections is very similar. In the case of MS SQL Server it is necessary to specify the server, database name and user name/password combination.

An SQL Server connection setup requires the following steps: • • • •

In the Name Text Box type in a new name for the connection you are about to create Select a Server Name from Server Drop Down List Select a Database Name form the Drop Down List Fill in Username/Password for the database you wish to connect to a. If you are unsure of these parameters, please contact your Database Administrator for the correct settings.

• •

Click Test to ensure the details you have provided are correct Click OK to close the connection properties window

(ote: Leave user name and password blank to use Windows Authentication.

Copyright © 2009 DB Software Laboratory

Page 40 of 276

Advanced ETL Processor Professional & Enterprise User Manual

9.2.3 ODBC Connections ODBC history ODBC, standing for Open Data Base Connectivity, represents a standard database access method working as SQL API software. It was developed back in 1992 with the sole purpose of facilitating the work with database management systems (DBMS). It was created by the Microsoft corporation and is since then included in every copy of Microsoft Windows. In 1995 the ODBC was included in the SQL Standard, which played a great role in making ODBC more popular. Today, there are versions for almost any operating system currently used. How ODBC works 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. Facilitating the data access from an application to a database management system through ODBC is done through a specific mechanism. A common ODBC implementation contains one or more applications, a core ODBC 'Driver Manager' library, and one or more database drivers. The Driver Manager's role is to interpret the data queries coming from an application by using the DBMS-specific details contained in database drivers. The latter represent a middle layer inserted between an application and the DBMS in use. This way, the application's data queries are translated into commands that can easily be read by the DBMS. A basic requirement for an ODBC implementation to be run is that both the application and the DBMS be ODBC-compliant. In other words, the application must be able to issue ODBC commands and the DBMS must be capable of responding to them. Thanks to its modular nature ODBC gives developers a great freedom in creating the separate components of an ODBC implementation. Thus, a programmer can write applications that use standard features without needing to worry about the type of DBMS used for administering the database that the application tries to access. Likewise, the only thing the database driver programmers need to keep in mind during the development process is how to attach their database driver to the 'Driver Manager' library. The result is that currently there are hundreds of ODBC drivers created for a large variety of data sources. Thanks to the long period of existence and the fruitful efforts of its team of developers, ODBC now offers access to a much wider range of data sources than any other database access method available today. It has turned into a universal data access standard, working with a great variety of operating systems and providing access to even non-relational data, including text and XML files.

Copyright © 2009 DB Software Laboratory

Page 41 of 276

Advanced ETL Processor Professional & Enterprise User Manual The ODBC Connection properties dialogue allows the administrator to create appropriate connections to various databases. It is be necessary to create connections to databases, whenever they are to be processed.

In order to create an ODBC connection, the following steps should be followed: • • •

In the Name Text Box type in a new name for the connection you are about to create Select a ODBC DSN Name from Server Drop Down List Fill in Username/Password for the database you wish to connect to a. You may use ODBC admin to create/modify ODBC data sources.

• •

Click Test to ensure the details you have provided are correct Click OK to close the connection properties window

Copyright © 2009 DB Software Laboratory

Page 42 of 276

Advanced ETL Processor Professional & Enterprise User Manual

9.2.4 ODBC Connection Strings It also possible to use ODBC connection strings for both Reader and Writer connections. For example for MS SQL Server connection string is: Driver={SQL Native Client}; Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword; One of the major benefits of using connection strings that it makes it no longer necessary to create ODBC Dsn’s manually on every single computer where Advanced ETL Processor is installed. It also gives a greater control over the connection parameters. (ote: Leave user name and password blank and provide it within connection string More information about connection strings can be found at: http://www.connectionstrings.com The simplest way to create ODBC connection string is to use ODBC Connection builder dialog. Double click on ODBC driver name to create a connection string

Copyright © 2009 DB Software Laboratory

Page 43 of 276

Advanced ETL Processor Professional & Enterprise User Manual

9.3 Ftp Connection FTP stands for File Transfer Protocol, the protocol for exchanging files over the Internet. FTP works in the same way as HTTP for transferring Web pages from a server to a user's browser and SMTP for transferring electronic mail across the Internet in that, like these technologies, FTP uses the Internet's TCP/IP protocols to enable data transfer. FTP is most commonly used to download a file from a server using the Internet or to upload a file to a server (e.g., uploading a Web page file to a server). Advanced ETL Processor Pro supports full range of FTP operations.

To create an FTP connection, follow these steps: • • • • • •

In the Name Text Box type in a new name for the FTP Connection you are about to create Fill in the host name Select TCP/IP port (default is 21) Type the user name and password Test connection Click OK to close the FTP connection properties window

Copyright © 2009 DB Software Laboratory

Page 44 of 276

Advanced ETL Processor Professional & Enterprise User Manual

9.4 SMTP Connection SMTP stands for Simple Mail Transfer Protocol, a protocol for sending e-mail messages between servers. Most e-mail systems that send mail over the Internet use SMTP to send messages from one server to another; the messages can then be retrieved with an e-mail client using either POP or IMAP. In addition, SMTP is generally used to send messages from a mail client to a mail server.

To create an SMTP connection, follow these steps: • • • • •

In the Name Text Box type in a new name for the SMTP connection you are about to create Fill in host name Select TCP/IP port (default is 25) Type in user name and password Click OK to close the SMTP connection properties window

Copyright © 2009 DB Software Laboratory

Page 45 of 276

Advanced ETL Processor Professional & Enterprise User Manual

9.5 POP3 Connection POP3 (Post Office Protocol 3) is the most recent version of a standard protocol for receiving e-mail. POP3 is a client/server protocol in which e-mail is received and held for you by your Internet server. Periodically, you (or your client e-mail receiver) check your mail-box on the server and download any mail, probably using POP3. This standard protocol is built into most popular e-mail products, such as Eudora and Outlook Express. It's also built into the Netscape and Microsoft Internet Explorer browsers. POP3 is designed to delete mail on the server as soon as the user has downloaded it. However, some implementations allow users or an administrator to specify that mail be saved for some period of time. POP can be thought of as a "store-and-forward" service.

To setup email access using POP as the protocol follow these steps: • • • • •

In the Name Text Box type in a new name for the POP3 connection you are about to create Fill in host name Select TCP/IP port (default is 110) Type in user name and password Click OK to close the POP3 connection properties window

Copyright © 2009 DB Software Laboratory

Page 46 of 276

Advanced ETL Processor Professional & Enterprise User Manual

10. Processing Data In order to load data from the data source into the data target you must define data mapping between data source and data target. Typical transformation consists of reader, transformer and writer. 10.1 Screen Overview The data processing screen provides a number of settings, for the three stages of reading, transformation and writing. This is facilitated by the main tool bar, and a list of available objects. The main reader toolbar provides the user with all the necessary functionality to setup the reading process, according to the settings provided. Main Tool bar

Reader Toolbar

Available Objects

Field Width

Field No Reader Fields

Copyright © 2009 DB Software Laboratory

Reader data

Source file/Table Page 47 of 276

Advanced ETL Processor Professional & Enterprise User Manual Main tool bar

1 2 3

4 5

6 7

8 9 10 11 12 13 14 15 16 17 18

The main tool bar provides a number of icons which allow the user to create the steps required to carry out the automatic functions required. 1. Transformation Properties 2. New Transformation 3. Load Transformation From the file 4. Saves Transformation to the file 5. Saves Transformation under the new name 6. Saves Transformation to the Repository 7. Cut 8. Copy 9. Paste 10. Delete 11. Arrange Horizontally 12. Arrange Vertically 13. Prints Transformation 14. Print Preview Transformation 15. Manage Versions 16. Add Version 17. Revert to the previous version 18. Process Data

Copyright © 2009 DB Software Laboratory

Page 48 of 276

Advanced ETL Processor Professional & Enterprise User Manual

10.2 Transformation Properties to change Transformation properties, please note that Default Date format applies to Click all templates. Default Date format only used for new Date related validation and transformation functions for example Is Date, Is Date between ETC

Copyright © 2009 DB Software Laboratory

Page 49 of 276

Advanced ETL Processor Professional & Enterprise User Manual

10.3 Template tab The template shows the actual transformation script source.

Copyright © 2009 DB Software Laboratory

Page 50 of 276

Advanced ETL Processor Professional & Enterprise User Manual

10.4 Execution Log Tab The execution log provides information about the Advanced ETL Processor and the actions it took during its operations. This is useful when you wish to analyse the activities of individual processes during their execution.

Copyright © 2009 DB Software Laboratory

Page 51 of 276

Advanced ETL Processor Professional & Enterprise User Manual

10.5 Rejected Records Tab Occasionally, records will be rejected by the processor. This may be due to things like corrupt records which have been read in a format not expected by the processor. The rejected records tab allows the user to see a list of all the rejected records, and other information about the nature of the rejection and where this occurred in the process.

Copyright © 2009 DB Software Laboratory

Page 52 of 276

Advanced ETL Processor Professional & Enterprise User Manual

10.6 Creating new transformation and working with Objects . New transformation consists of Reader, Validator, To create new transformation click Transformer and Writer. You may add new object at any time by dragging it from the Objects panel.

Objects Panel

To join two objects together, first click on the source Output button then drag it on to the Input button Output Buttons

Drag and Drop to Join

Input Button

To remove the Join, repeat the same procedure again. To delete an object right click on the object and click delete.

Copyright © 2009 DB Software Laboratory

Page 53 of 276

Advanced ETL Processor Professional & Enterprise User Manual To change object properties double click on it

Deletes All Links

Deletes Links for Selected objects

Copyright © 2009 DB Software Laboratory

Page 54 of 276

Advanced ETL Processor Professional & Enterprise User Manual

10.7 Working with Reader The Reader extracts data from various data sources. Description

Records/ Lines Read

Records Rejected

Every Transformation created must have one reader object. The Reader connects to the Data source and extracts data from it. Depending on the Data source type some options may not be or double click on the Reader object. available. To change the Reader properties click 10.7.1 Universal Data Reader One of the benefits of using Advanced ETL Processor is a concept of Universal Data Reader Consider following scenario: You have several extracts from your accounting system • Text files • Excel Files • Access Databases All files have same format such as: • Same field’s order • Same field’s names • Same date and number formats Provided that you are using Advanced ETL Processor all you need to do is to change connection type no mapping will be lost. Other tools use different connector for different databases and some of then even sell separate licences for them. That mean that user have to recreate mapping for new files/ databases.

Copyright © 2009 DB Software Laboratory

Page 55 of 276

Advanced ETL Processor Professional & Enterprise User Manual

10.7.2 Data source is a Text File. The Reader is capable of extracting data from delimited or fixed width files. All parameters are user definable. It can also skip a number of Header and Footer lines

Copyright © 2009 DB Software Laboratory

Page 56 of 276

Advanced ETL Processor Professional & Enterprise User Manual

Copyright © 2009 DB Software Laboratory

Page 57 of 276

Advanced ETL Processor Professional & Enterprise User Manual The Rejected records file can have a pre-defined format. The following dialogue allows the user to set this up.

Copyright © 2009 DB Software Laboratory

Page 58 of 276

Advanced ETL Processor Professional & Enterprise User Manual Data View One of the useful features of the Advanced ETL Processor is the ability to view the resultant data prior, or subsequently processed. The data view looks like a spreadsheet view as follows:

Number of records to show

Source file

Data View Toolbar The data view toolbar allows you to change various aspects of the data view, such as refreshing data as it changes and setting properties for how the data will look. You can also switch between viewing of the data and checking to see how the data is defined i.e. the data dictionary, via the “Switch to Data Definition View”.

1

2 1. 2. 3. 4. 5. 6. 7.

3

4 5

6 7

Reader Properties Refresh Data Edit file in external editor Add a column Delete last column Switch to Data View Switch to Data Definition View

Copyright © 2009 DB Software Laboratory

Page 59 of 276

Advanced ETL Processor Professional & Enterprise User Manual Data Definition View (ote: You may rename fields and change field’s width here. (Works only for text files)

Data Definition View Toolbar Within the Data Definition view you can perform a number of actions. These allow you to change how you want data to be represented in the data view screens. The navigation also allows switching between views.

1

2

3

4

5 6

7

8

9 10

1. Reader Properties 2. Refresh Data 3. Print Data Definition 4. Print Preview Data Definition 5. Find 6. Edit file in external editor 7. Add a column 8. Delete last column 9. Switch to Data View 10. Switch to Data Definition View

Copyright © 2009 DB Software Laboratory

Page 60 of 276

Advanced ETL Processor Professional & Enterprise User Manual

10.7.3 Data source is MS Access or Excel File

Copyright © 2009 DB Software Laboratory

Page 61 of 276

Advanced ETL Processor Professional & Enterprise User Manual

Query Builder

(ote: It is also possible to use Query builder to design queries

Copyright © 2009 DB Software Laboratory

Page 62 of 276

Advanced ETL Processor Professional & Enterprise User Manual

Data View

Copyright © 2009 DB Software Laboratory

Page 63 of 276

Advanced ETL Processor Professional & Enterprise User Manual Data View Toolbar

1

2 1. 2. 3. 4. 5.

3

4 5

Reader Properties Refresh Data Edit file in external editor Switch to Data View Switch to Data Definition View

Data Definition View

Data Definition View Toolbar

1

2 1. 2. 3. 4. 5. 6. 7. 8.

3 4

5 6

7 8

Reader Properties Refresh Data Print Data Definition Print Preview Data Definition Find Edit file in external editor Switch to Data View Switch to Data Definition View

Copyright © 2009 DB Software Laboratory

Page 64 of 276

Advanced ETL Processor Professional & Enterprise User Manual

10.7.4 Data source is a DBF File

Source Directory

Copyright © 2009 DB Software Laboratory

Page 65 of 276

Advanced ETL Processor Professional & Enterprise User Manual Data View Source Directory

Data View Toolbar

1

2 1. 2. 3. 4.

3 4

Reader Properties Refresh Data Switch to Data View Switch to Data Definition View

Data Definition View

Data Definition View Toolbar

1

2 1. 2. 3. 4. 5. 6. 7.

3 4

5

6 7

Reader Properties Refresh Data Print Data Definition Print Preview Data Definition Find Switch to Data View Switch to Data Definition View

Copyright © 2009 DB Software Laboratory

Page 66 of 276

Advanced ETL Processor Professional & Enterprise User Manual 10.7.5 Data source is a ODBC Connection

Data View

Source Table

Copyright © 2009 DB Software Laboratory

Page 67 of 276

Advanced ETL Processor Professional & Enterprise User Manual Data View Toolbar

1

2 1. 2. 3. 4.

3 4

Reader Properties Refresh Data Switch to Data View Switch to Data Definition View

Data Definition View

Data Definition View Toolbar

1

2 1. 2. 3. 4. 5. 6. 7.

3 4

5

6 7

Reader Properties Refresh Data Print Data Definition Print Preview Data Definition Find Switch to Data View Switch to Data Definition View

Copyright © 2009 DB Software Laboratory

Page 68 of 276

Advanced ETL Processor Professional & Enterprise User Manual

10.7.6 Data source is a MS SQL Server

(ote: All properties are very similar to ODBC connection

Copyright © 2009 DB Software Laboratory

Page 69 of 276

Advanced ETL Processor Professional & Enterprise User Manual

10.7.7 Data source is an Oracle Database

(ote: All properties are very similar to ODBC connection

Copyright © 2009 DB Software Laboratory

Page 70 of 276

Advanced ETL Processor Professional & Enterprise User Manual

10.8 Working with Validator Advanced ETL Processor Data Validator guarantees to your application database that every data value is correct and accurate. There are several types of data validation. • • • • •

Data type validation Range checking Code checking Complex validation Pattern checking

One of the simplest forms of data validation is verifying the data type. Data type validation answers such simple questions as "Is the string alphabetic?" and "Is the number valid?" As an extension of simple type validation, range checking ensures that the provided value is within allowable minimums and maximums. For example, a character data type service code may only allow the alphabetic letters A through Z. All other characters would not be valid. Code checking is a bit more complicated, typically requiring a lookup table. For example, maybe your application calculates sales tax for only certain state codes. You would need to create a lookup object to hold the authorized, taxable state codes. Pattern checking when you checking structure of the data field for example social security number format or car registration number. Regular expressions used quite often for pattern checks. It does not matter which business you are in sooner or later you will discover that there is something wrong with the data ant it has to be validated. Here when Advanced ETL Processor Validation can help. Records Rejected by validation Rules

Processed Records Discarded Records (ote: • Records can also be rejected by the Server. • If you have several validation rules and one of them rejects record and another discards it, record will be discarded

Copyright © 2009 DB Software Laboratory

Page 71 of 276

Advanced ETL Processor Professional & Enterprise User Manual To change Validator properties double click on it Validator Toolbar

Inputs

Validation Rules

Objects Panel

Data Sample

Copyright © 2009 DB Software Laboratory

Page 72 of 276

Advanced ETL Processor Professional & Enterprise User Manual Validator Toolbar

1 2 3 4

5 6

7 8

9 10 11 12 13 14 15 16

1. Cut 2. Copy 3. Paste 4. Delete 5. Arrange Horizontally 6. Arrange Vertically 7. Print 8. Print Preview 9. Delete All objects 10. Delete All Links 11. Process Data 12. First Record 13. Previous Record 14. Next Record 15. Last Record 16. Show Data

Copyright © 2009 DB Software Laboratory

Page 73 of 276

Advanced ETL Processor Professional & Enterprise User Manual

10.8.1 Debugging Validation To start debugging validation press Process Data button grid. Pass

Warning

. To test data edit it in the Data

Failed

To test different value edit data in the Data grid To Change Validation Rule properties double click on it

Copyright © 2009 DB Software Laboratory

Page 74 of 276

Advanced ETL Processor Professional & Enterprise User Manual

(ote: Use to include actual value into default value To add new Validation rule drag and drop it from the Validation rules panel

Copyright © 2009 DB Software Laboratory

Page 75 of 276

Advanced ETL Processor Professional & Enterprise User Manual There more than 190 Validation Functions at the moment. They are grouped in five different categories 1. 2. 3. 4. 5.

String Number Date Time Regular Expressions

To choose appropriate category click on Category toolbar

Category Toolbar

It is also possible to apply several validation rules to Input field by joining them

Data is considered as validated when all validation rules are succeeded.

Validated Data

Failed Data (ote: If you have several validation rules and one of them rejects record and another discards it, record will be discarded Copyright © 2009 DB Software Laboratory

Page 76 of 276

Advanced ETL Processor Professional & Enterprise User Manual 10.9 Working with Transformer

Records Transformed (ote: To change Transformer properties double click on it

Transformer Toolbar

Inputs

Data Sample

Copyright © 2009 DB Software Laboratory

Outputs

Objects Panel

Transformations

Page 77 of 276

Advanced ETL Processor Professional & Enterprise User Manual Transformer Toolbar 1 2 3 4

5 6

7 8

9 10 11 12 13 14 15 16 17 18

1. Cut 2. Copy 3. Paste 4. Delete 5. Arrange Horizontally 6. Arrange Vertically 7. Print 8. Print Preview 9. Auto Map 10. Delete All objects 11. Delete All Links 12. Process Data 13. First Record 14. Previous Record 15. Next Record 16. Last Record 17. Copies Inputs to Outputs (Only visible if transformer is connected to any object other than writer) 18. Show Data (ote: If transformer is connected to any object other than writer it is possible to modify list of Outputs. When transformer is connected to writer list of Outputs is taken from Writer. Examples: Example below splits date field into Day, Month and Year using ‘/’ as a delimiter

Another Example Converts Customer Name into More readable format:

Copyright © 2009 DB Software Laboratory

Page 78 of 276

Advanced ETL Processor Professional & Enterprise User Manual

10.9.1 Auto mapping If the Inputs and Outputs have got the same names you may use Auto map feature. Click

, Fill in all necessary data and click map.

Copyright © 2009 DB Software Laboratory

Page 79 of 276

Advanced ETL Processor Professional & Enterprise User Manual 10.10 Working with Grouper

Records In

Records Out

To change grouper properties double click on the object. Choose appropriate grouping order and grouping type. Fields with grouping type Ignore are not passed to the next object. Grouper does not perform sorting.

Copyright © 2009 DB Software Laboratory

Page 80 of 276

Advanced ETL Processor Professional & Enterprise User Manual 10.11 Working with Sorter

Records In

Records Out

About Sorting In computer science and mathematics, a sorting algorithm is an algorithm that puts elements of a list in a certain order. All sorting algorithms are very well documented and it is hard to invent something new or much faster that existing algorithm. To achieve the best performance is important to load all the data into the memory which is not possible for large amount of data. Advanced ETL Processor uses combination of Quicksort and Merge sort. Portion of Data is loaded in the memory and sorted using Quicksort algorithm than data is saved into temporary file. Once all portions are sorted temporary files merged together using Merge sort algorithm. Quicksort Quicksort is a divide and conquer algorithm which relies on a partition operation: to partition an array, we choose an element, called a pivot, move all smaller elements before the pivot, and move all greater elements after it. This can be done efficiently in linear time and in-place. We then recursively sort the lesser and greater sublists. Efficient implementations of quicksort (with in-place partitioning) are typically unstable sorts and somewhat complex, but are among the fastest sorting algorithms in practice. Together with its modest O(log n) space usage, this makes quicksort one of the most popular sorting algorithms, available in many standard libraries. The most complex issue in quicksort is choosing a good pivot element; consistently poor choices of pivots can result in drastically slower (O(n²)) performance, but if at each step we choose the median as the pivot then it works in O(n log n). Merge sort Merge sort takes advantage of the ease of merging already sorted lists into a new sorted list. It starts by comparing every two elements (i.e., 1 with 2, then 3 with 4...) and swapping them if the first should come after the second. It then merges each of the resulting lists of two into lists of four, then merges those lists of four, and so on; until at last two lists are merged into the final sorted list. Of the algorithms described here, this is the first that scales well to very large lists, because its worst-case running time is O(n log n).

Copyright © 2009 DB Software Laboratory

Page 81 of 276

Advanced ETL Processor Professional & Enterprise User Manual To change Sorter properties double click on the object. Tick sort, select field order and sort order. Field order must be unique for sort field. Data is loaded in the memory first than sorted and passed to the next object.

Copyright © 2009 DB Software Laboratory

Page 82 of 276

Advanced ETL Processor Professional & Enterprise User Manual

10.12 Working with Deduplicator

Records In

Records Out

To change Deduplicator properties double click on the object. Tick Deduplicate for the fields you wish to depuplicate, only ticked fields are passed to the next object. (ote: There is no need to sort data before deduplication

Copyright © 2009 DB Software Laboratory

Page 83 of 276

Advanced ETL Processor Professional & Enterprise User Manual

10.13 Working with UnPivot

Records In

Records In

Example of Pivoted Data

Group 1

Group 2

Group 3

Group 4

To change UnPivot properties double click on the object. Fill in Description, Group field name, create all necessary Groups and Outputs. Once it is done Map input fields to outputs and Groups

UnPivoted Data Group field

Copyright © 2009 DB Software Laboratory

Page 84 of 276

Advanced ETL Processor Professional & Enterprise User Manual UnPivot Properties

Copyright © 2009 DB Software Laboratory

Page 85 of 276

Advanced ETL Processor Professional & Enterprise User Manual

Copyright © 2009 DB Software Laboratory

Page 86 of 276

Advanced ETL Processor Professional & Enterprise User Manual

Copyright © 2009 DB Software Laboratory

Page 87 of 276

Advanced ETL Processor Professional & Enterprise User Manual

Copyright © 2009 DB Software Laboratory

Page 88 of 276

Advanced ETL Processor Professional & Enterprise User Manual

10.14 Working with Pivot

Records In

UnPivoted Data

Records Out

Fields to Pivot

Pivot Key

Set Key

Pivoted Data

Copyright © 2009 DB Software Laboratory

Page 89 of 276

Advanced ETL Processor Professional & Enterprise User Manual Since there are more than one field to pivot let’s join Profit and Sales fields together first using tab a delimiter than pivot the data and split it again

Joining Data

Copyright © 2009 DB Software Laboratory

Page 90 of 276

Advanced ETL Processor Professional & Enterprise User Manual

Copyright © 2009 DB Software Laboratory

Page 91 of 276

Advanced ETL Processor Professional & Enterprise User Manual

Copyright © 2009 DB Software Laboratory

Page 92 of 276

Advanced ETL Processor Professional & Enterprise User Manual Data after Pivoting

Splitting Pivoted Data

Copyright © 2009 DB Software Laboratory

Page 93 of 276

Advanced ETL Processor Professional & Enterprise User Manual

10.15 Working with Writer

Inserted Records

Deleted Records

Updated Records

Errors

Every Transformation created must have at least one writer object. Writer connects to Target database and loads data into it. Depending of the target type some options may not be available. To change the Writer properties click or double click on the Writer object. 10.15.1 Target type is a Text File Writer is capable of saving data into delimited or fixed width files. All parameters are user definable.

Copyright © 2009 DB Software Laboratory

Page 94 of 276

Advanced ETL Processor Professional & Enterprise User Manual

Data View

Number of records to show

Copyright © 2009 DB Software Laboratory

Target File

Page 95 of 276

Advanced ETL Processor Professional & Enterprise User Manual Data View Toolbar

1

2 1. 2. 3. 4. 5. 6. 7.

3

4 5

6 7

Reader Properties Refresh Data Edit file in external editor Add new column Delete last column Switch to Data View Switch to Data Definition View

Data Definition View (ote: You may rename fields and change field’s width here. (Works only for text files)

Data Definition View Toolbar

1

2

3

4

5 6

7

8

9 10

1. Reader Properties 2. Refresh Data 3. Print Data Definition 4. Print Preview Data Definition 5. Find 6. Edit file in external editor 7. Add new column 8. Delete last column 9. Switch to Data View 10. Switch to Data Definition View

Copyright © 2009 DB Software Laboratory

Page 96 of 276

Advanced ETL Processor Professional & Enterprise User Manual 10.15.2 Target type is MS Access or Excel File

Data View

Copyright © 2009 DB Software Laboratory

Page 97 of 276

Advanced ETL Processor Professional & Enterprise User Manual Data View Toolbar

1

2 1. 2. 3. 4. 5. 6. 7. 8.

3 4

5 6 7 8

Reader Properties Refresh Data Print Print Preview Find Edit file in external editor Switch to Data View Switch to Data Definition View

Data Definition View

Data Definition View Toolbar

1

2 1. 2. 3. 4. 5. 6. 7. 8.

3

4

5 6

7 8

Reader Properties Refresh Data Print Data Definition Print Preview Data Definition Find Edit file in external editor Switch to Data View Switch to Data Definition View

Copyright © 2009 DB Software Laboratory

Page 98 of 276

Advanced ETL Processor Professional & Enterprise User Manual 10.15.3 Target type is a DBF File

Data View

Copyright © 2009 DB Software Laboratory

Page 99 of 276

Advanced ETL Processor Professional & Enterprise User Manual Data View Toolbar

1

2 1. 2. 3. 4. 5. 6. 7.

3 4

5 6 7

Reader Properties Refresh Data Print Print Preview Find Switch to Data View Switch to Data Definition View

Data Definition View

Copyright © 2009 DB Software Laboratory

Page 100 of 276

Advanced ETL Processor Professional & Enterprise User Manual 10.15.4 Target type is ODBC connection

Copyright © 2009 DB Software Laboratory

Page 101 of 276

Advanced ETL Processor Professional & Enterprise User Manual

10.15.5 Target type is MS SQL Server

Check constraints Ensure that any constraints on the destination table are checked during the bulk copy operation. By default, constraints are ignored. Keep identity Specify that there are values in the data file for an identity column. Keep (ULLS Specify that any columns containing a null value should be retained as null values, even if a default value was specified for that column in the destination table. Batch size Specify the number of rows in a batch. The default is the entire data file. The following values for the Batch size property have these effects: If you set Batch size to zero, the data is loaded in a single batch. The first row that fails will cause the entire load to be cancelled, and the step fails.

Copyright © 2009 DB Software Laboratory

Page 102 of 276

Advanced ETL Processor Professional & Enterprise User Manual If you set Batch size to one, the data is loaded a row at a time. Each row that fails is counted as one row failure. Previously loaded rows are committed. If you set Batch size to a value greater than one, the data is loaded one batch at a time. Any row that fails in a batch fails that entire batch; loading stops and the step fails. Rows in previously loaded batches are either committed or, if the step has joined the package transaction, provisionally retained in the transaction, subject to later commitment or rollback.

Copyright © 2009 DB Software Laboratory

Page 103 of 276

Advanced ETL Processor Professional & Enterprise User Manual

10.15.6 Target type is Oracle

(ote: Option ‘Commit every Array’ works only for Oracle conventional path loading

Copyright © 2009 DB Software Laboratory

Page 104 of 276

Advanced ETL Processor Professional & Enterprise User Manual

10.15.7 How to Update/Delete Records In order to Update/Delete records you must specify update key.

Update Key For the example provided below, Advanced ETL Processor will execute the following SQL (Update key is CustomerId, OrderNo).

Copyright © 2009 DB Software Laboratory

Page 105 of 276

Advanced ETL Processor Professional & Enterprise User Manual Add (ew And Update Old Records Select count(*) from [DEMO].[dbo].[orders] where CustomerId=? And OrderNo=? If any records found Advanced ETL will update them by executing Update [DEMO].[dbo].[orders] set orderdate=?, amount=? where customerid=? And OrderNo=? If no records found Advanced ETL will add new records Update Records Update [DEMO].[dbo].[orders] set OrderDate=?, Amount=? where CustomerId=? And OrderNo=? Delete Records Delete from [DEMO].[dbo].[orders] Where CustomerId=? And OrderNo=? (ote: “Add ew And Update Old Records” is not supported for SQL Server Connection use ODBC connection instead.

Copyright © 2009 DB Software Laboratory

Page 106 of 276

Advanced ETL Processor Professional & Enterprise User Manual 10.15.8 Running SQL Scripts Advanced ETL Processor is capable of running SQL Scripts before and after the transformation.

SQL Editor Toolbar 1 2

3 4

5

6 7 8 9 10 11 12 13 14 15 16

1. Loads Script from a file 2. Saves Script to a file 3. Print 4. Print Preview 5. Clear Script 6. Cut 7. Copy 8. Paste 9. Undo 10. Find 11. Find Next 12. Find Previous 13. Replace 14. Run 15. Stop on error 16. Show/Hide Log Copyright © 2009 DB Software Laboratory

Page 107 of 276

Advanced ETL Processor Professional & Enterprise User Manual

11. Validation Rules This chapter represents list of validation rules together with short description grouped by category. Every Validation Rule/function could have some specific parameters and common parameters to control data flow of Advanced ETL. Most if it is self explanatory see the example below:

(ote: To change validation rule properties double click on it

Copyright © 2009 DB Software Laboratory

Page 108 of 276

Advanced ETL Processor Professional & Enterprise User Manual

11.1 Strings

11.1.1 Is Null

Category: Validation Function Description: Checks if the data is equal to null. Properties: Default 11.1.2 Is Empty String

Category: Validation Function Description: Checks if the data is Empty String. (Length of the string is zero). Properties: Default 11.1.3 Is Alpha

Category: Validation Function Description: Checks if the data contains only Alpha characters. Properties: Default Pass: FCD|||mmm|||ABC Fail: adc1|||ad b||12 D 11.1.4 Is Alpha Numeric

Category: Validation Function Description: Checks if the data contains only Alpha Numeric characters. Properties: Default Pass: FCD12|||mm11m|||1ABC Fail: adc-1|||ad_b||12 D

Copyright © 2009 DB Software Laboratory

Page 109 of 276

Advanced ETL Processor Professional & Enterprise User Manual

11.1.5 Is Hex

Category: Validation Function Description: Checks if the data contains valid HEX string Properties: Default Pass: 3D|||FF|||CE Fail: DK|||KK||12D 11.1.6 Is Equal To

Category: Validation Function Description: Checks if the data is equal to supplied value Properties:

Copyright © 2009 DB Software Laboratory

Page 110 of 276

Advanced ETL Processor Professional & Enterprise User Manual 11.1.7 Is Starts With

Category: Validation Function Description: Checks if the data starts with supplied value Properties: Same as ‘Is Equal To’ 11.1.8 Is Ends With

Category: Validation Function Description: Checks if the data ends with supplied value Properties: Same as ‘Is Equal To’ 11.1.9 Contains

Category: Validation Function Description: Checks if the data contains supplied value Properties: Same as ‘Is Equal To’ 11.1.10 In List

Category: Validation Function Description: Checks if the data exists within supplied list values Properties: List of values can be entered by the user or extracted for the file(s) or database. When source data for the “List of values” consists of more than 1 field only first one is used.

Copyright © 2009 DB Software Laboratory

Page 111 of 276

Advanced ETL Processor Professional & Enterprise User Manual

Copyright © 2009 DB Software Laboratory

Page 112 of 276

Advanced ETL Processor Professional & Enterprise User Manual

Copyright © 2009 DB Software Laboratory

Page 113 of 276

Advanced ETL Processor Professional & Enterprise User Manual

Copyright © 2009 DB Software Laboratory

Page 114 of 276

Advanced ETL Processor Professional & Enterprise User Manual

Copyright © 2009 DB Software Laboratory

Page 115 of 276

Advanced ETL Processor Professional & Enterprise User Manual

11.2 Numbers 11.2.1 Is Number

Category: Validation Function Description: Checks if the data is a Number Properties: Default Pass: 12|||123.6|||1 Fail: adc1|||ad b||11,112.D 11.2.2 Is Integer

Category: Validation Function Description: Checks if the data is an Integer Properties: Default Pass: 12|||123|||1 Fail: 12.5|||ad b||11,112.8 11.2.3 Is Positive

Category: Validation Function Description: Checks if the data is a Positive Number Properties: Default 11.2.4 Is Negative

Category: Validation Function Description: Checks if the data is a Negative Number Properties: Default

Copyright © 2009 DB Software Laboratory

Page 116 of 276

Advanced ETL Processor Professional & Enterprise User Manual

11.2.5 Is Number Between

Category: Validation Function Description: Checks if the data is a number between two values Properties:

Copyright © 2009 DB Software Laboratory

Page 117 of 276

Advanced ETL Processor Professional & Enterprise User Manual

11.2.6 Is Number Less Than

Category: Validation Function Description: Checks if the data is a number less than value Properties:

Copyright © 2009 DB Software Laboratory

Page 118 of 276

Advanced ETL Processor Professional & Enterprise User Manual

11.2.7 Is Number More Than

Category: Validation Function Description: Checks if the data is a number more than value Properties:

11.3 Date 11.3.1 Is Date

Category: Validation Function Description: Checks if the data is a Date Properties:

Copyright © 2009 DB Software Laboratory

Page 119 of 276

Advanced ETL Processor Professional & Enterprise User Manual

Copyright © 2009 DB Software Laboratory

Page 120 of 276

Advanced ETL Processor Professional & Enterprise User Manual

11.3.2 Is Date Between

Category: Validation Function Description: Checks if the data is a Date between two values Properties:

Copyright © 2009 DB Software Laboratory

Page 121 of 276

Advanced ETL Processor Professional & Enterprise User Manual

11.3.3 Is Date Less Than

Category: Validation Function Description: Checks if the data is a Date less than value Properties:

11.3.4 Is Date More Than

Category: Validation Function Description: Checks if the data is a Date more than value Properties: Same as ‘Is Date Less Than’

Copyright © 2009 DB Software Laboratory

Page 122 of 276

Advanced ETL Processor Professional & Enterprise User Manual

11.3.5 Is Monday, Is Tuesday, Is Wednesday, Is Thursday, Is Friday, Is Saturday, Is Sunday, Is Today, Is Yesterday, Is Tomorrow, Is Weekend, Is Weekday

Category: Validation Function Description: Checks if the data is a Date value which belongs to a specific day of week Properties: Same as ‘Is Date’ 11.3.6 Is January, Is February, Is March, Is April, Is May, Is June, Is July, Is August, Is September, Is October, Is November, Is December, Is Current Month, Is Last Month, Is Next Month

Category: Validation Function Description: Checks if the data is a Date value which belongs to a specific month of a year Properties: Same as ‘Is Date’

Copyright © 2009 DB Software Laboratory

Page 123 of 276

Advanced ETL Processor Professional & Enterprise User Manual 11.3.7 Is 1st Quarter, Is 2nd Quarter, Is 3rd Quarter, Is 4th Quarter, Is Current Quarter, Is Last Quarter, Is Next Quarter

Category: Validation Function Description: Checks if the data is a Date value which belongs to a specific quarter of a year Properties: Same as ‘Is Date’ 11.3.8 Is Leap Year, Is Current Year, Is Last Year, Is Next Year

Category: Validation Function Description: Checks if the data is a Date value which belongs to a specific year Properties: Same as ‘Is Date’ 11.3.9 Is Current Week, Is Last Week, Is Next Week

Category: Validation Function Description: Checks if the data is a Date value which belongs to a specific week Properties: Same as ‘Is Date’

Copyright © 2009 DB Software Laboratory

Page 124 of 276

Advanced ETL Processor Professional & Enterprise User Manual 11.3.10 Is Within Past Minutes, Is Within Past Hours, Is Within Past Days, Is Within Past Weeks, Is Within Past Months

Category: Validation Function Description: Checks if the data is a Date value within in past minutes etc. Properties:

Copyright © 2009 DB Software Laboratory

Page 125 of 276

Advanced ETL Processor Professional & Enterprise User Manual

11.4 Time

11.4.1 Is Time

Category: Validation Function Description: Checks if the data is a time value Properties:

Copyright © 2009 DB Software Laboratory

Page 126 of 276

Advanced ETL Processor Professional & Enterprise User Manual 11.4.2 Is Time Between

Category: Validation Function Description: Checks if the data is a time between two values Properties:

Copyright © 2009 DB Software Laboratory

Page 127 of 276

Advanced ETL Processor Professional & Enterprise User Manual

11.4.3 Is Time Less Than

Category: Validation Function Description: Checks if the data is a time less than value Properties:

11.4.4 Is Time More Than

Category: Validation Function Description: Checks if the data is a time more than value Properties: Same as ‘Is Time Less Than’

Copyright © 2009 DB Software Laboratory

Page 128 of 276

Advanced ETL Processor Professional & Enterprise User Manual 11.4.5 Is Second

Category: Validation Function Description: Checks if data is a number between 0 and 59 Properties: Default 11.4.6 Is Minute

Category: Validation Function Description: Checks if data is a number between 0 and 59 Properties: Default 11.4.7 Is Hour 24

Category: Validation Function Description: Checks if data is a number between 0 and 23 Properties: Default 11.4.8 Is Hour 12

Category: Validation Function Description: Checks if data is a number between 0 and 12 Properties: Default

Copyright © 2009 DB Software Laboratory

Page 129 of 276

Advanced ETL Processor Professional & Enterprise User Manual

11.4.9 Is PM

Category: Validation Function Description: Checks if the data is a PM Date Properties: Same as ‘Is Date’ 11.4.10 Is AM

Category: Validation Function Description: Checks if the data is a AM Date Properties: Same as ‘Is Date’

Copyright © 2009 DB Software Laboratory

Page 130 of 276

Advanced ETL Processor Professional & Enterprise User Manual

11.5 Regular Expressions Regular expression is a string that is used to describe or match a set of strings, according to certain syntax rules. Regular expressions are used by many text editors, utilities, and programming languages to search and manipulate text based on patterns. For example, Perl and Tcl have a powerful regular expression engine built directly into their syntax. Several utilities provided by Unix distributions—including the editor ed and the filter grep—were the first to popularize the concept of regular expressions. "Regular expression" is often shortened to regex or regexp (singular), or regexes, regexps, or regexen (plural). Some authors distinguish between regular expression and abbreviated forms such as regex, restricting the former to true regular expressions, which describe regular languages, while using the latter for any regular expression-like pattern, including those that describe languages that are not regular. As only some authors observe this distinction, it is not safe to rely upon it. As an example of the syntax, the regular expression \bex can be used to search for all instances of the string "ex" that occur at word boundaries (signified by the \b). Thus in the string, "Texts for experts," \bex matches the "ex" in "experts," but not in "Texts" (because the "ex" occurs inside the word there and not immediately after a word boundary). Source: Wikipedia. For more information on regular expressions please visit: www.regularexpression.info Most of regular expressions were taken from www.regexlib.com. We are not able to include full list of contributors because it is too big. We did our best to test and modify if necessary pattern strings however as time goes by standards changes and some of them could became no longer valid. Therefore Regular Expression Validation Rules should be used with caution. If you have any useful Regular expressions please let us know we would be more than happy to include it into the next release of Advanced ETL Processor.

Copyright © 2009 DB Software Laboratory

Page 131 of 276

Advanced ETL Processor Professional & Enterprise User Manual

11.5.1 Regular Expression

Category: Validation Function Description: Validates data against regular expression Properties:

Copyright © 2009 DB Software Laboratory

Page 132 of 276

Advanced ETL Processor Professional & Enterprise User Manual

11.5.2 Is IP Address V4

Category: Validation Function Description: Validates IP addresses in the dotted quad form Properties: Default Pass: 192.168.1.1|||10.2.234.1|||66.129.71.122 Fail: 192.168.1|||10.2.1234.1|||66 129 71 122 11.5.3 Is IP Address V6

Category: Validation Function Description: Validates all IPv6 text representations as defined within RFC 2373 Properties: Default Pass: ::0:0:0:FFFF:129.144.52.38|||FEDC:BA98::3210:FEDC:BA98:7654:3210|||::13.1.68.3 Fail: FEDC:BA98:7654:3210:FEDC:BA98:7654:3210:1234|||3210:FEDC:BA98:7654:3210:1234|||: FEDC:BA98:7654:3210: 11.5.4 Is Email

Category: Validation Function Description: Email Address validation that allows both IP addresses and regular domains. In the case of an IP address it makes sure that it is no more than 255 for each part. Properties: Default Pass: [email protected]|||[email protected]|||[email protected] Fail: john@doe|||[email protected]|||@john.doe.com 11.5.5 Is ISBN 10

Category: Validation Function Description: Validation of 10 digits ISBN. The ISBN number must be preceded by the text "ISBN:" or "ISBN-10:", the colon is optional. Properties: Default Pass: ISBN-10: 0-93028-923-4 Fail: ISBN-13: 978-0-5960-0289-3 Copyright © 2009 DB Software Laboratory

Page 133 of 276

Advanced ETL Processor Professional & Enterprise User Manual 11.5.6 Is ISBN 13

Category: Validation Function Description: Validation of new 13 digits ISBN. The ISBN number must be preceded by the text "ISBN:" or "ISBN-13:", the colon is optional. Properties: Default Pass: ISBN-13: 978-0-5960-0289-3 Fail: ISBN-10: 0596002890 11.5.7 Is Credit Card

Category: Validation Function Description: Validation of all major credit cards formats Properties: Default Pass: 341-1111-1111-1111|||5431-1111-1111-1111|||30569309025904 Fail: 30-5693-0902-5904|||5631-1111-1111-1111|||31169309025904 11.5.8 Is URL

Category: Validation Function Description: Validation of URL Properties: Default Pass: http://www.download.com Fail: http://999.999.999.999/dfd 11.5.9 Is UNC

Category: Validation Function Description: Validation of UNC Properties: Default Pass: \\server\c$ Fail: //server

Copyright © 2009 DB Software Laboratory

Page 134 of 276

Advanced ETL Processor Professional & Enterprise User Manual

11.5.10 Is File Name

Category: Validation Function Description: Validation of file name format, file name must have a drive letter and an extension Properties: Default Pass: c:\1.txt Fail: ftc 11.5.11 Is UK Post Code

Category: Validation Function Description: Validates United Kingdom Post Code Properties: Default Pass: SW1W 8EU Fail: SW1WK8EU 11.5.12 Is UK VAT Number

Category: Validation Function Description: United Kingdom VAT Numbers format verification with support for optional member state definition. Properties: Default Pass: GB123123412|||GBGD123|||123123412123 Fail: GB1231234124|||GBGD1234|||023 1234 12 123 11.5.13 Is UK Bank Sort Code

Category: Validation Function Description: Validation of United Kingdom Bank Sort code Properties: Default Pass: 09-01-29|||05-06-25 Fail: 90125

Copyright © 2009 DB Software Laboratory

Page 135 of 276

Advanced ETL Processor Professional & Enterprise User Manual 11.5.14 Is UK Vehicle Registration Number

Category: Validation Function Description: Validation of United Kingdom Vehicle registration Number Properties: Default Pass: ABC 123 A|||A 123 ABC|||AB 12 ABC Fail: AB 123 C|||A 123 AB|||AB 12 AB 11.5.15 Is UK Driver Licence

Category: Validation Function Description: Validates the United Kingdom Drivers License format as described by the DVLA. See http://www.govtalk.gov.uk/gdsc/html/frames/default.htm. Matches: 1. must be 16 characters 2. First 5 characters are alphanumeric. 3. Next 6 characters must be numeric 4. Next 3 characters are alphanumeric 5. Last 2 characters are alpha 6. Second character of numeric section can only be 0, 1, 5 or 6. 7. Fourth and fifth characters of numeric section must be in the range 01 to 31 Properties: Default Pass: JOHNS711215GG9SY Fail: JOHNS731215GG9SY 11.5.16 Is UK National Insurance Number

Category: Validation Function Description: Validation of United Kingdom National Insurance Number Properties: Default Pass: SP939393H|||PX123456D|||SW355667G Fail: 12SP9393H|||S3P93930D|||11223344SP00ddSS 11.5.17 Is UK Phone Number

Category: Validation Function Description: Validation of United Kingdom phone number, also allows +44 national code in addition to extension numbers of 3 or 4 digits Properties: Default Pass: +447222555555|||+44 7222 555 555|||(0722) 5555555 #2222 Copyright © 2009 DB Software Laboratory

Page 136 of 276

Advanced ETL Processor Professional & Enterprise User Manual Fail: (+447222)555555|||+44(7222)555555|||(0722) 5555555 #22 11.5.18 Is UK Mobile Number

Category: Validation Function Description: United Kingdom mobile phone number validation with optional +44 national code, also allows optional brackets and spaces at appropriate positions Properties: Default Pass: 07222 555555|||(07222) 555555|||+44 7222 555 555 Fail: 7222 555555|||+44 07222 555555|||(+447222) 555555 11.5.19 Is US Zip Code

Category: Validation Function Description: Validates US Zip Code Properties: Default Pass: 48222|||48222-1746 Fail: 4632|||Blake|||37333-32 11.5.20 Is US State

Category: Validation Function Description: Validates US State 2 letter abbreviations Properties: Default Pass: CO|||GA|||TX Fail: A|||ZZ|||Florida 11.5.20 Is US Social Security Number

Category: Validation Function Description: Validates US Social security number Properties: Default Pass: 123-45-6789|||123 45 6789|||123456789 Fail: 12345-67-890123|||1234-56-7890|||123-45-78901

Copyright © 2009 DB Software Laboratory

Page 137 of 276

Advanced ETL Processor Professional & Enterprise User Manual 11.5.21 Is US Phone Number

Category: Validation Function Description: US Phone Number Validation Properties: Default Pass: 5305551212|||(530) 555-1212|||530-555-1212 Fail: 0010011212|||1991991212|||123) not-good 11.5.22 Is Canadian Zip Code

Category: Validation Function Description: Canadian postal code format verification. The format of a Canadian postal code is LDL DLD where L is alpha characters and D are numeric digits. But there are some exceptions. The letters D, F, I, O, Q and U never appear in a postal code because of their Advanced similarity to 0, E, 1, 0, 0, and V respectively. In addition to avoiding the six "forbidden" letters W and Z also do not appear as the first letter of a postal code (at least not at present). Properties: Default Pass: M1R 4B0|||L0R 1B1|||L0R1B9 Fail: W1R 4B0|||L0R 1D1|||LOR1B9 11.5.23 Is Canadian Provincial Code

Category: Validation Function Description: Validates Canadian provincial codes Properties: Default Pass: ON|||PE|||NB Fail: OB|||NM 11.5.24 Is Canadian Branch-Transit/ Bank code

Category: Validation Function Description: Validates Canadian Branch-Transit number. The branch number must be 3 or 4 digits then '-' then five digits Properties: Default Pass: 654-45654|||5544-65486 Fail: 455645564|||44-55545|||55455-54565 Copyright © 2009 DB Software Laboratory Page 138 of 276

Advanced ETL Processor Professional & Enterprise User Manual 11.5.25 Is Canadian Social Insurance Number

Category: Validation Function Description: Validates Canadian Social Insurance Number Properties: Default Pass: 046 454 286|||123-456-789 Fail: 046FF 454 286|||123GG-456-789 11.5.26 Is Australian Post Code

Category: Validation Function Description: Australian postal code verification. Australia has 4-digit numeric postal codes with the following state based specific ranges. ACT: 0200-0299 and 2600-2639. NSW: 10001999, 2000-2599 and 2640-2914. NT: 0900-0999 and 0800-0899. QLD: 9000-9999 and 40004999. SA: 5000-5999. TAS: 7800-7999 and 7000-7499. VIC: 8000-8999 and 3000-3999. WA: 6800-6999 and 6000-6799 Properties: Default Pass: 0200|||7312|||2415 Fail: 0300|||7612|||2915 11.5.27 Is Australian Phone Number

Category: Validation Function Description: Validation of Australian phone number. Accepts all forms of Australian phone numbers in different formats (area code in brackets, no area code, spaces between 2-3 and 67th digits, +61 international dialling code). Also Checks those area codes are valid (if provided). Properties: Pass: 0403111111|||(03) 1111 1111|||+61403111111|||02 9111 1111|||0403 111 111|||91111111 Fail: 9111 11111|||99 1111 1111

Copyright © 2009 DB Software Laboratory

Page 139 of 276

Advanced ETL Processor Professional & Enterprise User Manual

11.5.28 Is Australian Mobile Number

Category: Validation Function Description: Validates Australian Mobile Number Properties: Default Pass: 0412345678 Fail: 0299999999 11.5.29 Is Argentinean Post Code

Category: Validation Function Description: Validates Argentinean Post Code Properties: Default Pass: C1406HHA|||A4126AAB|||c1406hha Fail: c1406HHA|||4126|||C1406hha 11.5.30 Is Dutch Post Code

Category: Validation Function Description: Validates Dutch Postal Code Properties: Default Pass: 1234AB|||1234 AB|||1001 AB Fail: 0123AB|||1234A B|||0123 AB 11.5.31 Is Dutch Phone Number

Category: Validation Function Description: Validates Dutch Phone Number Properties: Default Pass: +31235256677|||+31(0)235256677|||023-5256677 Fail: +3123525667788999|||3123525667788|||232-2566778

Copyright © 2009 DB Software Laboratory

Page 140 of 276

Advanced ETL Processor Professional & Enterprise User Manual

11.5.32 Is Dutch Car Number Plate

Category: Validation Function Description: Validates Dutch Car Number Plate Properties: Default Pass: ABC123|||12-AB-AB|||12-ABC-1 Fail: 12ABAB|||12ABC1|||1ABC12 11.5.33 Is Belgian Post Code

Category: Validation Function Description: Validates Belgian Post Code Properties: Default Pass: 1234 Fail: 123|||123A 11.5.34 Is Belgian VAT Number

Category: Validation Function Description: Belgian VAT Numbers format verification with support for optional member state definition. Properties: Default Pass: BE123456789|||0123456789 Fail: BE12345678|||O123456789 11.5.35 Is French Post Code

Category: Validation Function Description: French Post Code Validation Properties: Default Pass: 12345|||F-12345|||F-2B100 Fail: F12345|||F-123456|||123456

Copyright © 2009 DB Software Laboratory

Page 141 of 276

Advanced ETL Processor Professional & Enterprise User Manual

11.5.36 Is French VAT Number

Category: Validation Function Description: French VAT Numbers format verification with support for optional member state definition. Properties: Default Pass: FRAB 123456789|||L7 123456789 Fail: FRAB123456789|||L7 L23456789 11.5.37 Is French Phone Number

Category: Validation Function Description: French Phone Number Validation Properties: Default Pass: 01 46 70 89 12|||01-46-70-89-12|||0146708912 Fail: 01-46708912|||01 46708912|||+33235256677 11.5.38 Is German Post Code

Category: Validation Function Description: Validates German Post Code format Properties: Default Pass: 12556|||01550|||80796 Fail: 05234|||8973|||62980 11.5.39 Is German VAT Number

Category: Validation Function Description: German VAT Numbers format verification with support for optional member state definition. Properties: Default Pass: DE123456789|||224466880 Fail: DE12345678|||22446688B

Copyright © 2009 DB Software Laboratory

Page 142 of 276

Advanced ETL Processor Professional & Enterprise User Manual 11.5.40 Is German Car License Plate

Category: Validation Function Description: German Car License Plate Validation Properties: Default Pass: F-RM373|||WÜ-Z1|||K- 07333|||HN-WK11H Fail: MOS-AS 11.5.41 Is German IBAN

Category: Validation Function Description: German International Bank Account Number Validation Properties: Default Pass: DE89 3704 0044 0532 0130 00|||DE89370400440532013000 Fail: DE89-3704-0044-0532-0130-00 11.5.42 Is Italian Post Code

Category: Validation Function Description: Postcode check for Italy (including possible Vatican/Italy indications) Properties: Default Pass: 1234|||V-1234 Fail: 12345 11.5.43 Is Italian VAT Number

Category: Validation Function Description: Italian VAT Numbers format verification with support for optional member state definition. Properties: Default Pass: IT12345678901|||12345678901 Fail: IT1234567890|||1234567890I

Copyright © 2009 DB Software Laboratory

Page 143 of 276

Advanced ETL Processor Professional & Enterprise User Manual

11.5.44 Is Italian IBAN

Category: Validation Function Description: Italian International Bank Account Number Validation Properties: Default Pass: IT28 W800 0000 2921 0064 5211 151|||IT28W8000000292100645211151 Fail: IT28-W800-0000-2921-0064-5211-151 11.5.45 Is Italian Phone Number

Category: Validation Function Description: Validates Italian phone number Properties: Default Pass: 02-343536|||02/343536|||02 343536 Fail: 02a343536|||02+343536 11.5.46 Is Italian Mobile Number

Category: Validation Function Description: Validates Italian mobile phone number with (or without) the International code Properties: Default Pass: +393471234567|||3381234567 Fail: +39 3401234567|||347 1234567|||338-1234567 11.5.47 Is Spanish Post Code

Category: Validation Function Description: Spanish Post Code Validation Properties: Default Pass: 12345|||10234|||01234 Fail: 00123

Copyright © 2009 DB Software Laboratory

Page 144 of 276

Advanced ETL Processor Professional & Enterprise User Manual

11.5.48 Is Spanish VAT Number

Category: Validation Function Description: Spanish VAT Numbers format verification with support for optional member state definition. Properties: Default Pass: ES01234567A|||A12345678 Fail: ES012345678|||AB2345678 11.5.49 Is Spanish IBAN

Category: Validation Function Description: Spanish IBAN Validation Properties: Default Pass: ES91 2100 0418 4502 0005 1332|||ES9121000418450200051332 Fail: ES91-2100-0418-4502-0005-1332 11.5.50 Is Spanish Phone Number

Category: Validation Function Description: Spanish Phone Number Validation Properties: Default Pass: 972367087|||97-3770866|||972-377086 Fail: 9988-989898|||989898988989|||9 9898989898 11.5.51 Is Swedish Post Code

Category: Validation Function Description: Validates Swedish Post Code Format Properties: Default Pass: 12345|||932 68|||S-621 46 Fail: 5367|||425611|||31 545

Copyright © 2009 DB Software Laboratory

Page 145 of 276

Advanced ETL Processor Professional & Enterprise User Manual

11.5.52 Is Swedish VAT Number

Category: Validation Function Description: Swedish VAT Numbers format verification with support for optional member state definition. Properties: Default Pass: SE123456789012|||123456789012 Fail: SE12345678901|||123456789O12 11.5.53 Is Swedish IBAN

Category: Validation Function Description: Swedish International Bank Account Number IBAN Properties: Default Pass: SE35 5000 0000 0549 1000 0003|||SE3550000000054910000003 Fail: SE35-5000-0000-0549-1000-0003 11.5.54 Is Swedish Phone Number

Category: Validation Function Description: Validates Swedish Phone Number Properties: Default Pass: +46 8 123 456 78|||08-123 456 78|||0123-456 78 Fail: +46 08-123 456 78|||08 123 456 78|||0123 456 78 11.5.55 Is Swedish Person Number

Category: Validation Function Description: Validates Swedish Person Number Properties: Default Pass: 19740609-7845|||19811116-7845|||20010913-7598 Fail: 21003612-9999|||18790505-4545|||19740641-5559

Copyright © 2009 DB Software Laboratory

Page 146 of 276

Advanced ETL Processor Professional & Enterprise User Manual

11.5.56 Is Austrian Mobile Phone Number

Category: Validation Function Description: Validates Austrian Mobile Phone Number Properties: Default Pass: 0664-1234567|||06641234567|||0699-11234567 Fail: 06991-1234567|||+43650-1234567|||07661234567 11.5.57 Is Danish Post Code

Category: Validation Function Description: Validates Danish Post Code Properties: Default Pass: DK-1234|||dk 1234|||Dk-1234 Fail: DK 12345 11.5.58 Is Danish VAT Number

Category: Validation Function Description: Validates Danish VAT Numbers format with support for optional member state definition. Properties: Default Pass: DK11 22 33 44 Fail: DK11223344 11.5.60 Is Danish Phone Number

Category: Validation Function Description: Validates Danish Phone Number Properties: Default Pass: (+45) 35 35 35 35 ||| +45 35 35 35 35 ||| 35 35 35 35 ||| 35353535 Fail: (45)35353535 ||| 4535353535

Copyright © 2009 DB Software Laboratory

Page 147 of 276

Advanced ETL Processor Professional & Enterprise User Manual

11.5.61 Is Danish Civil Registration Number

Category: Validation Function Description: Validates Danish civil registration number, which consists of 10 numeric characters and one special character '-'. The format is DDMMYY-XXXX Properties: Default Pass: 010380-2343 || 120454-5467 || 390178-3434 Fail: 1223876776 || 4023100923 11.5.62 Is Finnish VAT Number

Category: Validation Function Description: Finnish VAT Numbers format verification with support for optional member state definition. Properties: Default Pass: FI12345678|||22446688 Fail: FI1234567|||224466BB 11.5.63 Is Greek VAT Number

Category: Validation Function Description: Greek VAT Numbers format verification with support for optional member state definition. Properties: Default Pass: EL123456789|||224466880 Fail: EL12345678|||22446688B 11.5.64 Is Irish VAT Number

Category: Validation Function Description: Irish VAT Numbers format verification with support for optional member state definition. Properties: Default Pass: IE4*12345Z|||0012345K Fail: IE4-12345Z|||00123456 Copyright © 2009 DB Software Laboratory

Page 148 of 276

Advanced ETL Processor Professional & Enterprise User Manual 11.5.65 Is Luxembourg VAT Number

Category: Validation Function Description: Luxemburg VAT Numbers format verification with support for optional member state definition Properties: Default Pass: LU12345678|||22446688 Fail: LU1234567|||224466BB 11.5.66 Is Portuguese VAT Number

Category: Validation Function Description: Portuguese VAT Numbers format verification with support for optional member state definition. Properties: Default Pass: PT123456789|||224466880 Fail: PT12345678|||22446688B 11.5.67 Is Estonian Post Code

Category: Validation Function Description: Validates Estonian Post Codes Properties: Default Pass: 00000|||99999 Fail: 00 000 11.5.68 Is Estonian VAT Number

Category: Validation Function Description: Estonian VAT Numbers format verification with support for optional member state definition. Properties: Default Pass: EE123456789|||224466880 Fail: EE12345678|||22446688B

Copyright © 2009 DB Software Laboratory

Page 149 of 276

Advanced ETL Processor Professional & Enterprise User Manual 11.5.69 Is Latvian VAT Number

Category: Validation Function Description: Validates Latvian VAT Number Properties: Default Pass: LV12345678901|||12345678901 Fail: IT1234567890|||1234567890I 11.5.70 Is Lithuanian VAT Number

Category: Validation Function Description: Lithuanian VAT Numbers format verification with support for optional member state definition. Properties: Default Pass: LT123456789|||123456789012 Fail: LX123456789|||123466789O12 11.5.71 Is Slovenian VAT Number

Category: Validation Function Description: Slovenian VAT Numbers format verification with support for optional member state definition Properties: Default Pass: SI12345678|||22446688 Fail: SI1234567|||224466BB 11.5.72 Is Slovakian VAT Number

Category: Validation Function Description: Slovakian VAT Numbers format verification with support for optional member state definition Properties: Default Pass: SK1234567890|||1234567890 Fail: SK1234567890|||1234567890

Copyright © 2009 DB Software Laboratory

Page 150 of 276

Advanced ETL Processor Professional & Enterprise User Manual I11.5.73 s Slovakian IBAN

Category: Validation Function Description: Validates Slovakian International Bank Account Number Properties: Default Pass: SK31 1200 0000 1987 4263 7541|||SK3112000000198742637541 Fail: SK31-1200-0000-1987-4263-7541 11.5.74 Is Polish VAT Number

Category: Validation Function Description: Polish VAT Numbers format verification with support for optional member state definition Properties: Default Pass: PL1234567890|||1234567890 Fail: PL123456789|||123456789O 11.5.75 Is Polish IBAN

Category: Validation Function Description: Validates Polish International Bank Account Number Properties: Default Pass: PL27 1140 2004 0000 3002 0135 5387|||PL27114020040000300201355387 Fail: PL27-1140-2004-0000-3002-0135-5387 11.5.76 Is Czech Republic VAT Number

Category: Validation Function Description: Czech Republic VAT Numbers format verification with support for optional member state definition. Properties: Default Pass: CZ12345678|||1234567890 Fail: CZ1234567|||12345678901

Copyright © 2009 DB Software Laboratory

Page 151 of 276

Advanced ETL Processor Professional & Enterprise User Manual

11.5.77 Is Czech Republic IBAN

Category: Validation Function Description: Validates Czech International Bank Account Number Properties: Default Pass: CZ65 0800 0000 1920 0014 5399|||CZ6508000000192000145399 Fail: CZ65-0800-0000-1920-0014-5399 11.5.78 Is Netherlands VAT Number

Category: Validation Function Description: The Netherlands VAT Numbers format verification with support for optional member state definition. Properties: Default Pass: NL123456789B12 Fail: NL123456789012 11.5.79 Is Hungarian VAT Number

Category: Validation Function Description: Hungarian VAT Numbers format verification with support for optional member state definition Properties: Default Pass: HU12345678|||22446688 Fail: HU1234567|||224466BB 11.5.80 Is Maltese VAT Number

Category: Validation Function Description: Maltese VAT Numbers format verification with support for optional member state definition Properties: Default Pass: MT12345678|||22446688 Fail: MT1234567|||224466BB

Copyright © 2009 DB Software Laboratory

Page 152 of 276

Advanced ETL Processor Professional & Enterprise User Manual 11.5.81 Is Algerian Post Code

Category: Validation Function Description: Validates Algerian Post Code Format Properties: Default Pass: 12345|||99999|||00000 Fail: 1|||1234|||999999 11.5.82 Is Andorran Post Code

Category: Validation Function Description: Validates Andorran Post Code format Properties: Default Pass: AB 123|||AB123 Fail: 12 abc|||12345 11.5.83 Is Andorran IBAN

Category: Validation Function Description: Validates Andorran International Bank Account Number Properties: Default Pass: AD12 0001 2030 2003 5910 0100|||AD1200012030200359100100 Fail: AD12-0001-2030-2003-5910-0100 11.5.84 Is Azerbaijani Post Code

Category: Validation Function Description: Validates Azerbaijani Post Code format Properties: Default Pass: AZ 1234|||AZ1234 Fail: 12 abcd|||AB 1234

Copyright © 2009 DB Software Laboratory

Page 153 of 276

Advanced ETL Processor Professional & Enterprise User Manual

11.5.85 Is Bahraini Post Code

Category: Validation Function Description: Validates Bahraini Post Code format Properties: Default Pass: 123|||1299|||199 Fail: 1300|||000|||99 11.5.86 Is Bangladeshi Post Code

Category: Validation Function Description: Validates Bangladeshi Post Code format Properties: Default Pass: 1000|||9999|||1234 Fail: 123456|||0123|||99 11.5.87 Is Belarusian Post Code

Category: Validation Function Description: Validates Belarusian Post Code format Properties: Default Pass: 123456|||000000|||999999 Fail: 0|||9|||1234 11.5.88 Is Brunei Post Code

Category: Validation Function Description: Validates Brunei Post Code format Properties: Default Pass: BT2328|||KA1234 Fail: AB1234|||AB 1234

Copyright © 2009 DB Software Laboratory

Page 154 of 276

Advanced ETL Processor Professional & Enterprise User Manual

11.5.89 Is Chilean Post Code

Category: Validation Function Description: Validates Chilean Post Code format Properties: Default Pass: 872-0019|||000-0000|||999-9999 Fail: 000 0000|||000000 11.5.90 Is Croatian Post Code

Category: Validation Function Description: Validates Croatian Post Code format Properties: Default Pass: HR-00000|||HR-99999 Fail: HR 00000|||00000 11.5.91 Is Cyprus Post Code

Category: Validation Function Description: Validates Cyprus Post Code format Properties: Default Pass: 1000|||9999|||0000 Fail: 1|||99999|||99 000 11.5.92 Is Cyprus VAT Number

Category: Validation Function Description: Cyprus VAT Numbers format verification with support for optional member state definition Properties: Default Pass: CY12345678A|||12345678A Fail: CY1234567A|||123456789

Copyright © 2009 DB Software Laboratory

Page 155 of 276

Advanced ETL Processor Professional & Enterprise User Manual 11.5.93 Is Egyptian Post Code

Category: Validation Function Description: Validates Egyptian Post Code format Properties: Default Pass: 00000|||99999 Fail: 0 0 0 00|||00 11.5.94 Is Ethiopian Post Code

Category: Validation Function Description: Validates Ethiopian Post Code format Properties: Default Pass: 1000|||9999 Fail: 1 999 11.5.95 Is Faroe Islands Post Code

Category: Validation Function Description: Validates Faroe Islands Post Code format Properties: Default Pass: FO100|||FO000|||FO 000 Fail: AB 123|||AB123 11.5.96 Is French Polynesia Post Code

Category: Validation Function Description: Validates French Polynesia Post Code format Properties: Default Pass: 00000|||99999 Fail: 00 000|||99 9 9 9

Copyright © 2009 DB Software Laboratory

Page 156 of 276

Advanced ETL Processor Professional & Enterprise User Manual

11.5.97 Is Guadeloupe Post Code

Category: Validation Function Description: Validates Guadeloupe Post Code format Properties: Default Pass: 97100|||98099 Fail: 12345|||97 100 11.5.98 Is Guinean Post Code

Category: Validation Function Description: Validates Guinean Post Code format Properties: Default Pass: 001 BP 123|||499 BP 999 Fail: 001BP999|||999 BP 000 11.5.99 Is Guinea-Bissau Post Code

Category: Validation Function Description: Validates Guinea-Bissau Post Code format Properties: Default Pass: 1000|||1999|||9999 Fail: 0000|||0123 11.5.100 Is Russian Post Code

Category: Validation Function Description: Validates Russian Post Code Properties: Default Pass: 150016 Fail: wert12

Copyright © 2009 DB Software Laboratory

Page 157 of 276

Advanced ETL Processor Professional & Enterprise User Manual

11.5.101 Is Russian Phone Number

Category: Validation Function Description: Validates Russian Phone Number Properties: Default Pass: +7(916)9985670|||8-912-268-5440|||8905148-3339|||8(913)448-51-90|||903-345-34-34||| 903-34-334-34|||903-34-33434 Fail: +33903-34-33434 11.5.102 Is Israeli Phone Number

Category: Validation Function Description: Validates Israeli Phone Number Format Properties: Default Pass: +972-4-8253055|||972-4-8615466|||972-04-8564521 Fail: 04-5487665|||05-455665545|||456884624 11.5.103 Is Israeli Mobile Phone Number

Category: Validation Function Description: Validates Israeli Mobile Phone Number Properties: Default Pass: 050-1234567, 0501234567, 501234567 Fail: 0601234567, 5412345678 11.5.104 Is Peru Phone Number

Category: Validation Function Description: Validates Peru Phone Number format Properties: Default Pass: 263-8854|||5660578 1235|||622-4588 21 Fail: 1227585|||4224586 50124|||0554499

Copyright © 2009 DB Software Laboratory

Page 158 of 276

Advanced ETL Processor Professional & Enterprise User Manual

11.5.105 Is Peru Cellular Number

Category: Validation Function Description: Validates Peru Cellular Number format Properties: Default Pass: 8874-2544|||99106800|||84509955 Fail: 6540-9985|||77329390|||725-2763 11.5.106 Is Indian Post Code

Category: Validation Function Description: Validates Indian Post Code Properties: Default Pass: 400 099|||400099|||400050 Fail: 2345678|||12345|||asdf 11.5.107 Is Indian Phone Number

Category: Validation Function Description: Validates Indian Phone Number Properties: Default Pass: 0493 - 3227341|||0493 3227341|||493 3227341 Fail: 93 0227341|||493 322734111|||493 -- 3227341 11.5.108 Is Indian Mobile Number

Category: Validation Function Description: Validates Indian Mobile Phone Number Properties: Default Pass: +919847444225|||+91-98-44111112|||98 44111116 Fail: +919800444225|||+947444225|||44111116

Copyright © 2009 DB Software Laboratory

Page 159 of 276

Advanced ETL Processor Professional & Enterprise User Manual

11.5.109 Is Indian Vehicle Registration Number

Category: Validation Function Description: Validates Indian Vehicle Registration Number Properties: Default Pass: mh 12 bj 1780|||mmx 1234 Fail: mmm 123 1234|||mm 12 bj 345|||mm 12345 11.5.110 Is South African Phone Number

Category: Validation Function Description: Validates South African Phone Number Properties: Default Pass: 0333872119|||033 386162|||074 101 2850 Fail: 033386 2612 11.5.111 Is South African Mobile Number

Category: Validation Function Description: only allows valid cell phones from South African service providers Properties: Default Pass: 0848298477|||084 829 8477|||084-829 8477 Fail: 084829 8477 11.5.112 Is South African VAT Number

Category: Validation Function Description: Validates South African VAT Number Properties: Default Pass: 2001/067995/23|||1989/064445/73 Fail: LX123456789|||123466789O12

Copyright © 2009 DB Software Laboratory

Page 160 of 276

Advanced ETL Processor Professional & Enterprise User Manual

11.5.113 Is US or CA Zip code

Category: Validation Function Description: Validates US or CA Zip code format Properties: Default Pass: 00501|||84118-3423|||n3a 3B7 Fail: 501-342|||123324|||Q4B 5C5

Copyright © 2009 DB Software Laboratory

Page 161 of 276

Advanced ETL Processor Professional & Enterprise User Manual

12. Transformation Functions (ote: To change Transformation function properties double click on it 12.1 Strings 12.1.1 Upper Case

Category: Transformation Function Description: Converts all characters of a string to Upper Case My String => MYSTRING Properties: None 12.1.2 Lower Case

Category: Transformation Function Description: Converts all characters of a string to Lower Case My String => my string Properties: None 12.1.3 Proper Case

Category: Transformation Function Description: Converts first character of all words within a string to Upper Case and the rest to the lower case my strinG => My String Properties: None 12.1.4 First Up

Category: Transformation Function Description: Converts first character a string to Upper Case and the rest to the lower case my strinG => My string Properties: None Copyright © 2009 DB Software Laboratory

Page 162 of 276

Advanced ETL Processor Professional & Enterprise User Manual 12.1.5 Delete Spaces

Category: Transformation Function Description: Makes sure that there is only one space between words My string => My string Properties: None 12.1.6 Trim

Category: Transformation Function Description: Removes Leading and Trailing Spaces from a string Properties: None 12.1.7 Left Trim

Category: Transformation Function Description: Removes Leading Spaces from a string Properties: None 12.1.8 Right Trim

Category: Transformation Function Description: Removes Trailing Spaces from a string Properties: None

Copyright © 2009 DB Software Laboratory

Page 163 of 276

Advanced ETL Processor Professional & Enterprise User Manual

12.1.9 Sub String

Category: Transformation Function Description: Returns part of a string. Properties:

Copyright © 2009 DB Software Laboratory

Page 164 of 276

Advanced ETL Processor Professional & Enterprise User Manual

12.1.10 Replace

Category: Transformation Function Description: Replaces a one or more characters from the data with zero or more new characters. Properties:

Copyright © 2009 DB Software Laboratory

Page 165 of 276

Advanced ETL Processor Professional & Enterprise User Manual

12.1.11 Ensure Prefix

Category: Transformation Function Description: Check string for prefix and if it is missing adds it Properties:

12.1.12 Ensure Suffix

Category: Transformation Function Description: Check string for suffix and if it is missing adds it Properties: Same as ‘Ensure Prefix’ 12.1.13 Ensure No Prefix

Category: Transformation Function Description: Check string for prefix and if it is present removes it Properties: Same as ‘Ensure Prefix’

Copyright © 2009 DB Software Laboratory

Page 166 of 276

Advanced ETL Processor Professional & Enterprise User Manual 12.1.14 Ensure No Suffix

Category: Transformation Function Description: Check string for suffix and if it is present removes it Properties: Same as ‘Ensure Prefix’ 12.1.15 Left Pad

Category: Transformation Function Description: Returns the string left-padded to the length with spaces or specified character. Properties:

12.1.16 Right Pad

Category: Transformation Function Description: Returns the string right-padded to the length with spaces or specified character. Properties: Same as ‘Right Pad’

Copyright © 2009 DB Software Laboratory

Page 167 of 276

Advanced ETL Processor Professional & Enterprise User Manual 12.1.17 Escape String

Category: Transformation Function Description: Replaces special characters such as TAB, CR, LF,NULL,BEL,BS,ESC,HT,FF,VT with there C-Style escape equivalents. Properties: None 12.1.18 UnEscape String

Category: Transformation Function Description: Opposite to Escape String, Replaces C-Style escape characters with special characters such as TAB, CR, LF,NULL,BEL,BS,ESC,HT,FF,VT. Properties: None 12.1.19 Delete

Category: Transformation Function Description: Deletes part of a string. Properties:

Copyright © 2009 DB Software Laboratory

Page 168 of 276

Advanced ETL Processor Professional & Enterprise User Manual 12.1.20 Left

Category: Transformation Function Description: Returns left part of a string. Properties:

12.1.21 Right

Category: Transformation Function Description: Returns right part of a string Properties: Same as ‘Left’ 12.1.22 Left Delete

Category: Transformation Function Description: Deletes left part of a string Properties: Same as ‘Left’

Copyright © 2009 DB Software Laboratory

Page 169 of 276

Advanced ETL Processor Professional & Enterprise User Manual 12.1.23 Right Delete

Category: Transformation Function Description: Deletes right part of a string Properties: Same as ‘Left’

Copyright © 2009 DB Software Laboratory

Page 170 of 276

Advanced ETL Processor Professional & Enterprise User Manual

12.2 Numbers

12.2.1 Round

Category: Transformation Function Description: Rounding is the process of reducing the number of significant digits in a number. The result of rounding is a "shorter" number having fewer non-zero digits yet similar in magnitude. The result is less precise but easier to use. For example: 73 rounded to the nearest ten is 70, because 73 is closer to 70 than to 80. Properties:

Copyright © 2009 DB Software Laboratory

Page 171 of 276

Advanced ETL Processor Professional & Enterprise User Manual

12.2.2 Abs

Category: Transformation Function Description: Returns absolute value of a number. Absolute value is always positive. For example -2 will give 2 Properties:

12.2.3 Sign

Category: Transformation Function Description: Returns Sign of numeric value -1 for negative values, 1 for positive values and 0 for 0 Properties: Same as ‘Abs’

Copyright © 2009 DB Software Laboratory

Page 172 of 276

Advanced ETL Processor Professional & Enterprise User Manual

12.3 Date 12.3.1 Date Format

Category: Transformation Function Description: Returns Date in ‘yyyy-mm-dd hh:nn:ss.zzz' format. In order to load Date fields into target database they should be converted into ‘yyyy-mm-dd hh:nn:ss.zzz' format Properties:

12.3.2 Current Date

Category: Transformation Function Description: Returns Current Date in ‘yyyy-mm-dd hh:nn:ss.zzz' format Properties: None

Copyright © 2009 DB Software Laboratory

Page 173 of 276

Advanced ETL Processor Professional & Enterprise User Manual

12.4 Miscellaneous

12.4.1 Length

Category: Transformation Function Description: Returns Length of a String Properties: None 12.4.2 Literal

Category: Transformation Function Description: Populates field with a constant value Properties:

Copyright © 2009 DB Software Laboratory

Page 174 of 276

Advanced ETL Processor Professional & Enterprise User Manual

12.4.2 User

Category: Transformation Function Description: Returns Current Windows User Name Properties: None 12.4.3 Splitter

Category: Transformation Function Description: Splits data into number of values Properties:

Copyright © 2009 DB Software Laboratory

Page 175 of 276

Advanced ETL Processor Professional & Enterprise User Manual

12.4.4 Joiner

Category: Transformation Function Description: Joins several values into one value delimited Delimiter and surrounded by a Qualifier if specified Properties:

Copyright © 2009 DB Software Laboratory

Page 176 of 276

Advanced ETL Processor Professional & Enterprise User Manual

12.4.5 Calculation

Category: Transformation Function Description: Allows end user to write scripts to transform data. Input values represented as [F00N] Where N is a value number. For example calculation you can see above has got two input values [F001] and [F002]. Programming language used is very close to Pascal. The Structure of a Calculation Below is the basic structure that every Calculation transformation must follow: Var VariableName : VariableType; VariableName : VariableType; ... // Some single line Comment if necessary { Multi line Comment } Procedure ProcedureName; variables here if necessary Begin Some Code; End; Function FunctionName(variableList): VariableType; variables here if necessary Begin Some Code if necessary; Result := some expression More Code if necessary; End; ... some more functions and procedures if necessary ... Begin the main program block. Result:= some calculation End.

Copyright © 2009 DB Software Laboratory

Page 177 of 276

Advanced ETL Processor Professional & Enterprise User Manual (ote: The functions and procedures can appear in any order. The only requirement is that if one procedure or a function uses another one, that latter one must have been defined already. Declaring Variables var // This starts a section of variables LineTotal : Integer; // This defines an Integer variable called LineTotal First,Second : String; // This defines two variables to hold strings of text Assigning Values to Variables Variables are simply a name for a block of memory cells in main memory. If a value is assigned to a variable, that value must be of the same type as the variable, and will be stored in the memory address designated by the variable name. The assignment statement is the semicolon-equal :=. • • •

Variables must be declared at the beginning of the program, a procedure, or a function Variables must be initialized before they can be used. Variables can be reused as often as necessary. Their old value is simply overwritten by a new assignment.

Example: Var i : Integer: Begin i := 10; End.

{ variable name is i, type is integer) { valid integer number assigned to variable i }

Copyright © 2009 DB Software Laboratory

Page 178 of 276

Advanced ETL Processor Professional & Enterprise User Manual

Variable Types (umeric Data Types Type Byte ShortInt Word SmallInt LongWord Cardinal LongInt Integer Int64 Single Currency Double Extended

Storage size 1 1 2 2 4 4* 4 4* 8 4 8 8 10

Range 0 to 255 -127 to 127 0 to 65,535 -32,768 to 32,767 0 to 4,294,967,295 0 to 4,294,967,295 -2,147,483,648 to 2,147,483,647 -2,147,483,648 to 2,147,483,647 -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 7 significant digits, exponent -38 to +38 50+ significant digits, fixed 4 decimal places 15 significant digits, exponent -308 to +308 19 significant digits, exponent -4932 to +4932

Assigning to and from number variables Number variables can be assigned from other numeric variables, and expressions: var Age : Byte; // Smallest positive integer type Books : SmallInt; // Bigger signed integer Salary : Currency; // Decimal used to hold financial amounts Expenses : Currency; TakeHome : Currency; begin Expenses := 12345.67; // Assign from a literal constant TakeHome := Salary; // Assign from another variable TakeHome := TakeHome - Expenses; // Assign from an expression end; (umerical operators Number calculations, or expressions, have a number of primitive operators available: + * / div mod

Add one number to another Subtract one number from another Multiply two numbers Divide one decimal number by another Divide one integer number by another Remainder from dividing one integer by another

Copyright © 2009 DB Software Laboratory

Page 179 of 276

Advanced ETL Processor Professional & Enterprise User Manual When using these multiple operators in one expression, you should use round brackets to wrap around sub-expressions to ensure that the result is obtained. This is illustrated in the examples below: var myInt : Integer; // Define integer and decimal variables myDec : Single; begin myInt := 20; // myInt is now 20 myInt := myInt + 10; // myInt is now 30 myInt := myInt - 5; // myInt is now 25 myInt := myInt * 4; // myInt is now 100 myInt := 14 div 3; // myInt is now 4 (14 / 3 = 4 remainder 2) myInt := 14 mod 3; // myInt is now 2 (14 / 3 = 4 remainder 2) myInt := 12 * 3 - 4; // myInt is now 32 (* comes before -) myInt := 12 * (3 - 4); // myInt is now -12 (brackets come before *) myDec := 2.222 / 2.0; // myDec is now 1.111 end; Character Types var Str1 : Char; Str2 : WideChar; Str3 : AnsiChar; Str4 : ShortString; Str5 : String; Str6 : AnsiString; Str7 : WideString;

// Holds a single character, small alphabet // Holds a single character, International alphabet // Holds a single character, small alphabet // Holds a string of up to 255 Char's // Holds strings of Char's of any size desired // Holds strings of AnsiChar's any size desired // Holds strings of WideChar's of any size desired

Some simple text variable usage examples are given below: + = < <= > >= <>

Concatenates two strings together Compares for string equality Is one string lower in sequence than another Is one string lower or equal in sequence with another Is one string greater in sequence than another Is one string greater or equal in sequence with another Compares for string inequality

Copyright © 2009 DB Software Laboratory

Page 180 of 276

Advanced ETL Processor Professional & Enterprise User Manual Variants The Variant data type provides a flexible general purpose data type. It can hold anything but structured data and pointers. Variants are useful in very specific circumstances, where data types and their content are determined at run time rather than at compile time. Example: var myVar : Variant; Date Variables TDateTime Description The TDateTime type holds a date and time value. It is stored as a Double variable, with the date as the integral part, and time as fractional part. The date is stored as the number of days since 30 Dec 1899. Quite why it is not 31 Dec is not clear. 01 Jan 1900 has a days value of 2. Because TDateTime is actually a double, you can perform calculations on it as if it were a number. This is useful for calculations such as the difference between two dates. (ote: ,o local time information is held with TDateTime - just the day and time values. Example: Finding the difference between two dates var day1, day2 : TDateTime; diff : Double; begin day1 := StrToDate('12/06/2002'); day2 := StrToDate('12/07/2002'); diff := day2 - day1; Result:='day2 - day1 = '+FloatToStr(diff)+' days'; end; day2 - day1 = 30 days

Copyright © 2009 DB Software Laboratory

Page 181 of 276

Advanced ETL Processor Professional & Enterprise User Manual Logical data types These are used in conjunction with programming logic. They are very simple: Var Log1 : Boolean;

// Can be 'True' or 'False'

Boolean variables are a form of enumerated type. This means that they can hold one of a fixed number of values, designated by name. Here, the values can be True or False. Logical Operation Simple if then else Here is an example of how the if statement works: var number : Integer; text : String; begin number := Sqr(17); // Calculate the square of 17 if number > 400 then text := '17 squared > 400' // Action when if condition is true else text := '17 squared <= 400'; // Action when if condition is false result:=text; end; text is set to : '17 squared <= 400' There are a number of things to note about the if statement. First that it spans a few lines remember that statements can span lines - this is why it insists on a terminating ; Second, that the then statement does not have a terminating ; -this is because it is part of the if statement, which is finished at the end of the else clause. Third, that we have set the value of a text string when the If condition is successful - the Then clause - and when unsuccessful - the Else clause. We could have just done a then assignment: if number > 400 then text := '17 squared > 400'; Note that here, the then condition is not executed (because 17 squared is not > 400), but there is no else clause. This means that the if statement simply finishes without doing anything. Note also that the then clause now has a terminating ; to signify the end of the if statement. Compound if conditions, and multiple statements Copyright © 2009 DB Software Laboratory

Page 182 of 276

Advanced ETL Processor Professional & Enterprise User Manual We can have multiple conditions for the if condition. And we can have more than one statement for the then and else clauses. Here are some examples: If (condition1) And (condition2) // Both conditions must be satisfied then begin statement1; statement2; ... end // Notice no terminating ';' - still part of 'if' else begin statement3; statement4; ... end; We used And to join the if conditions together - both must be satisfied for the then clause to execute. Otherwise, the else clause will execute. We could have used a number of different logical primitives, of which And is one, covered under logical primitives below.

(ested if statements: There is nothing to stop you using if statements as the statement of an if statement. Nesting can be useful, and is often used like this: if condition1 then statement1 else if condition2 then statement2 else statement3;

However, too many nested if statements can make the code confusing. The Case statement, discussed below, can be used to overcome a lot of these problems. Logical primitives Before we introduce these, it is appropriate to introduce the Boolean data type. It is an enumerated type that can have one of only two values: True or False. We will use it in place of a condition in the if clauses below to clarify how they work: begin if false And false then Result:='false and false = true'; if true And false then Result:= 'true and false = true'; Copyright © 2009 DB Software Laboratory

Page 183 of 276

Advanced ETL Processor Professional & Enterprise User Manual

if false And true then Result:= 'false and true = true'; if true And true then Result:= 'true and true = true'; if false Or false then Result:= 'false or false = true'; if true Or false then Result:= 'true or false = true'; if false Or true then Result:= 'false or true = true'; if true Or true then Result:= 'true or true = true'; if false Xor false then Result:= 'false xor false = true'; if true Xor false then Result:= 'true xor false = true'; if false Xor true then Result:= 'false xor true = true'; if true Xor true then Result:= 'true xor true = true'; if Not false then Result:= 'not false = true'; if Not true then Result:= 'not true = true'; end; true and true = true false or true = true true or false = true true or true = true false xor true = true true xor false = true not false = true Note that the Xor primitive returns true when one, but not both of the conditions are true.

Copyright © 2009 DB Software Laboratory

Page 184 of 276

Advanced ETL Processor Professional & Enterprise User Manual Case statements The If statement is useful when you have a simple two way decision. Ether you go one way or another way. Case statements are used when you have a set of 3 or more alternatives. A simple numerical case statement: var i : Integer; begin i := [F1]; Case i of 15 : Resut := ('Number was fifteen'); 16 : Resut := ('Number was sixteen'); 17 : Resut := ('Number was seventeen'); 18 : Resut := ('Number was eighteen'); 19 : Resut := ('Number was nineteen'); 20 : Resut := ('Number was twenty'); end; end; Number was fifteen The case statement above routes the processing to just one of the statements. OK, the code is a bit silly, but it is used to illustrate the point.

Using the otherwise clause Supposing we were not entirely sure what value our case statement was processing? Or we wanted to cover a known set of values in one fell swoop? The Else clause allows us to do that: var i : Integer; begin i := [F1]; Case i of 15 : Resut := ‘Number was fifteen'; 16 : Resut := 'Number was sixteen'; 17 : Resut := 'Number was seventeen'; 18 : Resut := 'Number was eighteen'; 19 : Resut := 'Number was nineteen'; 20 : Resut := 'Nuumber was twenty'; else Resut := 'Unexpected number‘; end; end; Unexpected number : 10 Copyright © 2009 DB Software Laboratory

Page 185 of 276

Advanced ETL Processor Professional & Enterprise User Manual Repeating sets of commands Why loops are used in programming One of the main reasons for using computers is to save the tedium of many repetitive tasks. One of the main uses of loops in programs is to carry out such repetitive tasks. A loop will execute one or more lines of code (statements) as many times as you want. Your choice of loop type depends on how you want to control and terminate the looping. The For loop This is the most common loop type. For loops are executed a fixed number of times, determined by a count. They terminate when the count is exhausted. The count (loop) is held in a variable that can be used in the loop. The count can proceed upwards or downwards, but always does so by a value of 1 unit. This count variable can be a number or even an enumeration. Counting up Here is a simple example counting up using numeric values: var count : Integer; begin For count := 1 to 5 do Result:= 'Count is now '+IntToStr(count); end; Counting down Here is a simple example counting up using numeric values: var count : Integer; begin For count := 5 downto 1 do Result:= 'Count is now '+IntToStr(count); end; The For statements in the examples above have all executed one statement. If you want to execute more than one, you must enclose these in a Begin and End pair.

Copyright © 2009 DB Software Laboratory

Page 186 of 276

Advanced ETL Processor Professional & Enterprise User Manual The Repeat loop The Repeat loop type is used for loops where we do not know in advance how many times we will execute. For example, when we keep asking a user for a value until one is provided, or the user aborts. Here, we are more concerned with the loop termination condition. Repeat loops always execute at least once. At the end, the Until condition is checked, and the loop aborts of condition works out as true. A simple example var stop : Boolean; // Our exit condition flag i : Integer; begin i := 1; exit := False; // do not exit until we are ready repeat i := i+1; // Increment a count if Sqr(i) > 99 then stop:= true; // Exit if the square of our number exceeds 99 until stop; // Shorthand for 'until exit := true' result:=I; end; Upon exit, i will be 10 (since Sqr(10) > 99) Here we exit the repeat loop when a Boolean variable is true. Notice that we use shorthand just specifying the variable as the condition is sufficient since the variable value is either true or false. Using a compound condition var i : Integer; begin i := 1; repeat i := i+1; // Increment a count until (Sqr(i) > 99) or (Sqrt(i) > 2.5); result:=i; end; Upon exit, i will be 7 (since Sqrt(7) > 2.5) Notice that compound statements require separating brackets. Notice also that Repeat statements can accommodate multiple statements without the need for a begin/end pair. The repeat and until clauses form a natural pairing. Copyright © 2009 DB Software Laboratory

Page 187 of 276

Advanced ETL Processor Professional & Enterprise User Manual While loops While loops are very similar to Repeat loops except that they have the exit condition at the start. This means that we use them when we wish to avoid loop execution altogether if the condition for exit is satisfied at the start. Var i : Integer; begin i := 1; while (Sqr(i) <= 99) and (Sqrt(i) <= 2.5) do i := i+1; // Increment a count result:=i; end; Upon exit, i will be 7 (since Sqrt(7) > 2.5) Notice that our original Repeat Until condition used Or as the compound condition joiner we continued until either condition was met. With our While condition, we use And as the joiner - we continue whilst neither condition is met. Have a closer look to see why we do this. The difference is that we repeat an action until something or something else happens. Whereas we keep doing an action while neither something nor something else have happened. Functions Functions provide a flexible method to apply one formula many times to possibly different values. They are comparable to procedures but • • •

functions are of always of a certain type functions usually have one or more input variable(s) the function name must appear at least once inside the definition

The general form of the function statement looks like this: Function Function(ame(Variable(ame: VariableType): VariableType; Begin some code, if necessary; Result := some computation; more code if necessary; End;

Copyright © 2009 DB Software Laboratory

Page 188 of 276

Advanced ETL Processor Professional & Enterprise User Manual

Basic Calculation Examples 1. Adding one number to another Begin Result:= [F001] + [F002]; End; 2. Strings Concatenation Begin Result:= ‘[F001]’ + ‘[F002]’; End; Note single quotes around field names for string values. 2. If Statement Begin If '[F002]'='' then Result:= 0 else If [F002]=0 then Result:= 0 Else Result:= [F001] mod [F002]; End; 3. Variables var MyVariable : integer; Begin MyVariable:=10; Result :=[F001] mod MyVariable; end;

Copyright © 2009 DB Software Laboratory

Page 189 of 276

Advanced ETL Processor Professional & Enterprise User Manual Properties:

Copyright © 2009 DB Software Laboratory

Page 190 of 276

Advanced ETL Processor Professional & Enterprise User Manual

Copyright © 2009 DB Software Laboratory

Page 191 of 276

Advanced ETL Processor Professional & Enterprise User Manual

Calculation Function List LowerCase(S) LowerCase returns a string with the same text as the string passed in S, but with all letters converted to lowercase. The conversion affects only 7-bit ASCII characters between 'A' and 'Z'. To convert 8-bit international characters, use AnsiLowerCase. AnsiUpperCase(S) AnsiUpperCase returns a string that is a copy of S, converted to upper case. AnsiLowerCase(S) AnsiLowerCase returns a string that is a copy of the given string converted to lower case. AnsiCompareStr(S1,S2) AnsiCompareStr compares S1 to S2, with case sensitivity. The return value is: Conditio n

Return Value

S1 > S2

>0

S1 < S2

<0

S1 = S2

=0

AnsiCompareText(S1,S2) AnsiCompareText compares S1 to S2, without case sensitivity. AnsiCompareText returns a value less than 0 if S1 < S2, a value greater than 0 if S1 > S2, and returns 0 if S1 = S2. AnsiStrLIComp (S1,S2,MaxLen) AnsiStrLIComp compares S1 to S2, without case sensitivity. If S1 or S2 is longer than MaxLen characters, AnsiStrLIComp only compares up to the first MaxLen characters. The return value is: Condition

Return Value

S1 > S2

>0

S1 < S2

<0

S1 = S2 (up to MaxLen characters) = 0

Copyright © 2009 DB Software Laboratory

Page 192 of 276

Advanced ETL Processor Professional & Enterprise User Manual AnsiLastChar(S) Call AnsiLastChar to obtain the last character in a string. Trim(S) Trim removes leading and trailing spaces and control characters from the given string S. TrimLeft(S) TrimLeft returns a copy of the string S with leading spaces and control characters removed. TrimRight(S) TrimRight returns a copy of the string S with trailing spaces and control characters removed. QuotedStr(S) Use QuotedStr to convert the string S to a quoted string. A single quote character (') is inserted at the beginning and end of S, and each single quote character in the string is repeated. AnsiQuotedStr(S,Quote) Use AnsiQuotedStr to convert a string (S) to a quoted string, using the provided Quote character. A Quote character is inserted at the beginning and end of S, and each Quote character in the string is doubled. AnsiExtractQuotedStr(S,Quote) AnsiExtractQuotedStr removes the quote characters from the beginning and end of a quoted string, and reduces pairs of quote characters within the string to a single quote character. The Quote parameter defines what character to use as a quote character. If the first character in S is not the value of the Quote parameter, AnsiExtractQuotedStr returns an empty string. The function copies characters from S to the result string until the second solitary quote character or the first null character in S. The S parameter is updated to point to the first character following the quoted string. If S does not contain a matching end quote character, the S parameter is updated to point to the terminating null character. IntToStr(S) IntToStr converts an integer into a string containing the decimal representation of that number. IntToHex(I,Digits) IntToHex converts a number into a string containing the number's hexadecimal (base 16) representation. Value is the number to convert. Digits indicate the minimum number of hexadecimal digits to return. Copyright © 2009 DB Software Laboratory

Page 193 of 276

Advanced ETL Processor Professional & Enterprise User Manual

StrToInt(S) StrToInt converts the string S, which represents an integer-type number in either decimal or hexadecimal notation, into a number. If S does not represent a valid number, StrToInt raises an exception. StrToIntDef(S,Default) StrToIntDef converts the string S, which represents an integer-type number in either decimal or hexadecimal notation, into a number. If S does not represent a valid number, StrToIntDef returns Default. FileAge(File(ame) Call FileAge to obtain the OS timestamp of the file specified by FileName. The return value can be converted to a TDateTime object using the FileDateToDateTime function. The return value is -1 if the file does not exist. FileExists(File(ame) FileExists returns true if the file specified by FileName exists. If the file does not exist, FileExists returns false. DeleteFile(File(ame) DeleteFile deletes the file named by FileName from the disk. If the file cannot be deleted or does not exist, the function returns false. RenameFile(OldFile,(ewFile) RenameFile attempts to change the name of the file specified by OldFile to NewFile. If the operation succeeds, RenameFile returns true. If RenameFile cannot rename the file (for example, if the application does not have permission to modify the file), it returns false. ChangeFileExt(File(ame,EXT) ChangeFileExt takes the file name passed in FileName and changes the extension of the file name to the extension passed in Extension. Extension specifies the new extension, including the initial dot character. ChangeFileExt does not rename the actual file, it just creates a new file name string. ExtractFilePath(File(ame) The resulting string is the leftmost characters of FileName, up to and including the colon or backslash that separates the path information from the name and extension. The resulting string is empty if FileName contains no drive and directory parts. Copyright © 2009 DB Software Laboratory

Page 194 of 276

Advanced ETL Processor Professional & Enterprise User Manual ExtractFileDir(File(ame) Extracts Directory part from the File Name provided ExtractFileDrive(File(ame) ExtractFileDrive returns a string containing the drive portion of a fully qualified path name for the file passed in the FileName. For file names with drive letters, the result is in the form "drive". For file names with a UNC path the result is in the form "\servername\sharename". If the given path contains neither style of path prefix, the result is an empty string. ExtractFile(ame(File(ame) The resulting string is the rightmost characters of FileName, starting with the first character after the colon or backslash that separates the path information from the name and extension. The resulting string is equal to FileName if FileName contains no drive and directory parts. ExtractFileExt(File(ame) Use ExtractFileExt to obtain the extension from a file name. ExpandFile(ame(File(ame) ExpandFileName converts the relative file name into a fully qualified path name. ExpandFileName does not verify that the resulting fully qualified path name refers to an existing file, or even that the resulting path exists. ExpandU(CFile(ame(File(ame) ExpandUNCFileName returns the fully-qualified file name for a specified file name. ExtractRelativePath(File(ame) Call ExtractRelativePath to convert a fully qualified path name into a relative path name. The DestName parameter specifies file name (including path) to be converted. BaseName is the fully qualified name of the base directory to which the returned path name should be relative. BaseName may or may not include a file name, but it must include the final path delimiter. DiskFree(Drive) DiskFree returns the number of free bytes on the specified drive, where 0 = Current, 1 = A, 2 = B, and so on. DiskSize(Drive) DiskSize returns the size in bytes of the specified drive, where 0 = Current, 1 = A, 2 = B, etc. DiskSize returns -1 if the drive number is invalid. GetCurrentDir(Directory) Copyright © 2009 DB Software Laboratory

Page 195 of 276

Advanced ETL Processor Professional & Enterprise User Manual

GetCurrentDir returns the fully qualified name of the current directory. SetCurrentDir(Directory) The SetCurrentDir function sets the current directory. The return value is true if the current directory was successfully changed, or false if an error occurred. CreateDir(Directory) CreateDir creates a new directory. The return value is true if a new directory was successfully created, or false if an error occurred. RemoveDir(Directory) Call RemoveDir to remove the directory specified by the Dir parameter. The return value is true if a new directory was successfully deleted, false if an error occurred. The directory must be empty before it can be successfully deleted. FloatToStr(F) FloatToStr converts the floating-point value given by Value to its string representation. The conversion uses general number format with 15 significant digits. StrToFloat(S) Use StrToFloat to convert a string, S, to a floating-point value. S must consist of an optional sign (+ or -), a string of digits with an optional decimal point, and an optional mantissa. The mantissa consists of 'E' or 'e' followed by an optional sign (+ or -) and a whole number. Leading and trailing blanks are ignored. EncodeDate(Year,Month,Day) EncodeDate returns a TDateTime value from the values specified as the Year, Month, and Day parameters. The year must be between 1 and 9999. Valid Month values are 1 through 12. Valid Day values are 1 through 28, 29, 30, or 31, depending on the Month value. For example, the possible Day values for month 2 (February) are 1 through 28 or 1 through 29, depending on whether or not the Year value specifies a leap year. EncodeTime(Hour,Min,Sec,MSec) EncodeTime encodes the given hour, minute, second, and millisecond into a TDateTime value. Valid Hour values are 0 through 23. Valid Min and Sec values are 0 through 59. Valid MSec values are 0 through 999. If the specified values are not within range, EncodeTime raises an EConvertError exception. The resulting value is a number between 0 and 1 (inclusive) that indicates the fractional part of a day given by the specified time or (if 1.0) midnight on the following day. The value 0 corresponds to midnight, 0.5 corresponds to noon, 0.75 corresponds to 6:00 pm, and so on. Copyright © 2009 DB Software Laboratory

Page 196 of 276

Advanced ETL Processor Professional & Enterprise User Manual DayOfWeek(D) DayOfWeek returns the day of the week of the specified date as an integer between 1 and 7, where Sunday is the first day of the week and Saturday is the seventh. Date Use Date to obtain the current local date as a TDateTime value. The time portion of the value is 0 (midnight). Time Use Time to return the current time as a TDateTime value. The two functions are completely equivalent. (ow Returns the current date and time. IncMonth(D) IncMonth returns the value of the Date parameter, incremented by NumberOfMonths months. NumberOfMonths can be negative, to return a date N months previous. If the input day of month is greater than the last day of the resulting month, the day is set to the last day of the resulting month. The time of day specified by the Date parameter is copied to the result. IsLeapYear(D) Call IsLeapYear to determine whether the year specified by the Year parameter is a leap year. Year specifies the calendar year. Use YearOf to obtain the value of Year for IsLeapYear from a TDateTime value. DateToStr(D) Use DateToStr to obtain a string representation of a date value that can be used for display purposes. TimeToStr(D) TimeToStr converts the Time parameter, a TDateTime value, to a string. DateTimeToStr(D) Converts a TDateTime value to a string. StrToDate(S) Call StrToDate to parse a string that specifies a date. If S does not contain a valid date, StrToDate raises an exception. Copyright © 2009 DB Software Laboratory

Page 197 of 276

Advanced ETL Processor Professional & Enterprise User Manual

StrToTime(S) Call StrToTime to parse a string that specifies a time value. If S does not contain a valid time, StrToTime raises an exception. StrToDateTime(S) Call StrToDateTime to parse a string that specifies a date and time value. If S does not contain a valid date, StrToDateTime raises an exception. FormatDateTime(Format,DateTime) FormatDateTime formats the TDateTime value given by DateTime using the format given by Format. See the table below for information about the supported format strings. Abort Use Abort to escape from an execution path without reporting an error. Beep Beep generates a conventional message beep. AnsiPos(Substr,S) Call AnsiPos to obtain the byte offset of the Substr parameter, as it appears in the string S. For example, if Substr is the string "AB", and S is the string "ABCDE", AnsiPos returns 1. If Substr does not appear in S, AnsiPos returns 0. Chr(X) Returns the character for a specified ASCII value. Abs(X) Returns an absolute value. Length(X) Returns the number of characters in a string or elements in an array. Copy(S,Index,Count) S is an expression of a string or dynamic-array type. Index and Count are integer-type expressions. Copy returns a substring or sub array containing Count characters or elements starting at S[Index]. The substring or sub array is a unique copy (that is, it does not share memory with S, although if the elements of the array are pointers or objects, these are not copied as well.) If Index is larger than the length of S, Copy returns an empty string or array. If Copyright © 2009 DB Software Laboratory

Page 198 of 276

Advanced ETL Processor Professional & Enterprise User Manual Count specifies more characters or array elements than are available, only the characters or elements from S[Index] to the end of S are returned. Round(X) Returns the value of X rounded to the nearest whole number. Trunc(X) Truncates a real number to an integer. Pos(Substr,Str) Pos searches for Substr within S and returns an integer value that is the index of the first character of Substr within S. Pos is case-sensitive. If Substr is not found, Pos returns zero Delete(S,Index,Count) Delete removes a substring of Count characters from string S starting with S[Index]. S is a string-type variable. Index and Count are integer-type expressions. If index is larger than the length of the string or less than 1, no characters are deleted. If count specifies more characters than remain starting at the index, Delete removes the rest of the string. If count is less than or equal to 0, no characters are deleted Insert(Substr,Dest,Index) Insert merges Source into S at the position S[index]. Source is a string-type expression. S is a string-type variable of any length. Index is an integer-type expression. It is a character index and not a byte index. If Index is less than 1, it is mapped to a 1. If it is past the end of the string, it is set to the length of the string, turning the operation into an append. If the Source parameter is an empty string, Insert does nothing Sqr(X) The Sqr function returns the square of the argument. X is a floating-point expression. The result, of the same type as X, is the square of X, or X*X. Sqrt(X) The result is the square root of X. Exp(X) Exp returns the value of e raised to the power of X, where e is the base of the natural logarithms Ln(X) Ln returns the natural logarithm (Ln(e) = 1) of the real-type expression X. Copyright © 2009 DB Software Laboratory

Page 199 of 276

Advanced ETL Processor Professional & Enterprise User Manual

Sin(X) Sin returns the sine of the angle X in radians. Cos(X) Cos returns the cosine of the angle X. X expression that represents an angle in radians Tan(X) Tan returns the tangent of X. Tan(X) = Sin(X) / Cos(X). ArcTan(X) ArcTan returns the arctangent of X. X is a real-type expression that gives an angle in radians SetLength(S,Length) Set Length of dynamic array or string High(X) Call High to obtain the upper limit of an Array Low(X) Call Low to obtain the lowest value or first element of an Array. PI Represents the mathematical value pi, the ratio of a circle's circumference to its diameter. Pi is approximated as 3.1415926535897932385. ArcCos(X) ArcCos returns the inverse cosine of X. X must be between -1 and 1. The return value is in the range [0..Pi], in radians. ArcCosh(X) ArcCosh returns the inverse hyperbolic cosine of X. The value of X must be greater than or equal to 1. ArcCot(X) ArcCot returns the inverse cotangent of X. ArcCotH(X) Copyright © 2009 DB Software Laboratory

Page 200 of 276

Advanced ETL Processor Professional & Enterprise User Manual

ArcCot returns the inverse hyperbolic cotangent of X. ArcCsc(X) ArcCsc returns the inverse cosecant of X. ArcCscH(X) ArcCsc returns the inverse hyperbolic cosecant of X. ArcSec(X) ArcSec returns the inverse secant of X. ArcSecH(X) ArcSec returns the inverse hyperbolic secant of X. ArcSin(X) ArcSin returns the inverse sine of X. X must be between -1 and 1. The return value will be in the range [-Pi/2..Pi/2], in radians. ArcSinh(X) ArcSinh returns the inverse hyperbolic sine of X. ArcTan(X) ArcTan returns the arctangent of X. X is a real-type expression that gives an angle in radians. ArcTanh(X) ArcTanh returns the inverse hyperbolic tangent of X. The value of X must be between -1 and 1 (inclusive). Ceil(X) Call Ceil to obtain the lowest integer greater than or equal to X. The absolute value of X must be less than MaxInt. For example: Ceil(-2.8) = -2 Ceil(2.8) = 3 Ceil(-1.0) = -1 Cosecant(X) Use the Cosecant to calculate the cosecant of X, where X is an angle in radians. The cosecant is calculated as 1/ Sin(X).

Copyright © 2009 DB Software Laboratory

Page 201 of 276

Advanced ETL Processor Professional & Enterprise User Manual Cosh(X) Use the Cosh to calculate the hyperbolic cosine of X. Cot(X) Call Cot to obtain the cotangent of X. The cotangent is calculated using the formula 1 / Tan (X). Cotan(X) Call Cotan to obtain the cotangent of X. The cotangent is calculated using the formula 1 / Tan (X) Do not call Cotan with X = 0 CotH(X) Call CotH to obtain the hyperbolic cotangent of X, where X is an angle in Radians. Csc(X) Use the Csc to calculate the cosecant of X, where X is an angle in radians. CscH(X) Use the CscH to calculate the hyperbolic cosecant of X, where X is an angle in radians. CycleToDeg(X) CycleToDeg converts angles measured in cycles into degrees, where degrees = cycles * 360. CycleToGrad(X) CycleToGrad converts angles measured in cycles into grads. CycleToRad(X) CycleToRad converts angles measured in cycles into radians, where radians = 2pi * cycles. DegToCycle(X) Use DegToCycle to convert angles expressed in degrees to the corresponding value in cycles. DegToGrad(X) Use DegToGrad to convert angles expressed in degrees to the corresponding value in grads.

Copyright © 2009 DB Software Laboratory

Page 202 of 276

Advanced ETL Processor Professional & Enterprise User Manual DegToRad(X) Use DegToRad to convert angles expressed in degrees to the corresponding value in radians, where radians = degrees(pi/180). Floor(X) Call Floor to obtain the highest integer less than or equal to X. For example: Floor(-2.8) = -3 Floor(2.8) = 2 Floor(-1.0) = -1 GradToCycle(X) GradToCycle converts angles measured in grads into cycles. GradToDeg(X) GradToDeg converts angles measured in grads into degrees. GradToRad(X) GradToRad converts angles measured in grads into radians, where radians = grads(pi/200). Hypot(X,Y) Hypot returns the length of the hypotenuse of a right triangle. Specify the lengths of the sides adjacent to the right angle in X and Y. Hypot uses the formula Sqrt(X**2 + Y**2) IntPower(Base,Exponent) IntPower raises Base to the power specified by Exponent. Ldexp(X) Ldexp returns X times (2 to the power of P). LnXP1(X) LnXP1 returns the natural logarithm of (X+1). Use LnXP1 when X is a value near 0. Log10(X) Log10 returns the log base 10 of X. Log2(X) Log2 returns the log base 2 of X.

Copyright © 2009 DB Software Laboratory

Page 203 of 276

Advanced ETL Processor Professional & Enterprise User Manual Log((Base,X) LogN returns the log base Base of X. Max(A,B) Call Max to compare two numeric values. Max returns the greater value of the two. Min(A,B) Call Min to compare two numeric values in Delphi. Min returns the smaller value of the two. Power(Base,Exponent) Power raises Base to any power. For fractional exponents or exponents greater than MaxInt, Base must be greater than 0. RadToCycle(X) Use RadToCycle to convert angles measured in radians into cycles, where cycles = radians/(2pi). RadToDeg(X) Use RadToDeg to convert angles measured in radians to degrees, where degrees = radians(180/pi). RadToGrad(X) Use RadToGrad to convert angles measured in radians to grads, where grads = radians(200/pi). RandG(Mean,StdDev) RandG produces random numbers with Gaussian distribution about the Mean. This is useful for simulating data with sampling errors and expected deviations from the Mean. RandomRange(AFrom,ATo) RandomRange returns a random integer from the range that extends between AFrom and ATo (non-inclusive). RandomRange can handle negative ranges (where AFrom is greater than ATo). To initialize the random number generator, add a single call Randomize or assign a value to the RandSeed variable before making any calls to RandomRange. Sec(X) Call Sec to obtain the secant of X, where X is an angle in radians. The secant is calculated using the formula 1 / Cos(X). Copyright © 2009 DB Software Laboratory

Page 204 of 276

Advanced ETL Processor Professional & Enterprise User Manual SecH(X) Call SecH to obtain the hyperbolic secant of X, where X is an angle in Radians. Sinh(X) Sinh calculates the hyperbolic sine of X. Tan(X) Tan returns the tangent of X. Tan(X) = Sin(X) / Cos(X). Tanh(X) Tanh calculates the hyperbolic tangent of X.

Copyright © 2009 DB Software Laboratory

Page 205 of 276

Advanced ETL Processor Professional & Enterprise User Manual

12.4.6 Lookup

Category: Transformation Function Description: This function substitutes values using mapping table. Mapping table can be populated manually or loaded from file/database. Properties:

Copyright © 2009 DB Software Laboratory

Page 206 of 276

Advanced ETL Processor Professional & Enterprise User Manual

Copyright © 2009 DB Software Laboratory

Page 207 of 276

Advanced ETL Processor Professional & Enterprise User Manual

Copyright © 2009 DB Software Laboratory

Page 208 of 276

Advanced ETL Processor Professional & Enterprise User Manual

12.4.7 Previous Value

Category: Transformation Function Description: This function returns previous value of field. For the first record it returns null. Properties:

Copyright © 2009 DB Software Laboratory

Page 209 of 276

Advanced ETL Processor Professional & Enterprise User Manual

12.4.8 Reader Parameter

Category: Transformation Function Description: This function returns selected metadata information from the Reader object Properties:

Copyright © 2009 DB Software Laboratory

Page 210 of 276

Advanced ETL Processor Professional & Enterprise User Manual

12.4.9 Sequence

Category: Transformation Function Description: This function returns a number incremented by one starting from “Start With” value Properties:

Copyright © 2009 DB Software Laboratory

Page 211 of 276

Advanced ETL Processor Professional & Enterprise User Manual

12.4.10 Running Total

Category: Transformation Function Description: The running total represents an instantaneous total of all the "quantities" of stuff that can be reviewed at any time during the recording period to determine what the total was at that time of review. Here's a sample of a running total of that store's receipts: Sale 1: $6.49 -- Running total: $6.49 Sale 2: $2.81 -- Running total: $9.30 Sale 3: $1.37 -- Running total: $10.67 Sale 4: Refund: $0.89 -- Running total: $9.78 Sale 5: $5.26 -- Running total: $15.04 Properties:

Copyright © 2009 DB Software Laboratory

Page 212 of 276

Advanced ETL Processor Professional & Enterprise User Manual

13. Date formats Date/Time format strings control the conversion of strings into date time type. Date/Time format strings are composed from specifiers which describe values to be converted into the date time value. In the following table, specifiers are given in lower cases. Case is ignored in formats, except for the "am/pm" and "a/p" specifiers. Specifier

Description

d dd

Day as a number without a leading zero (1-31). Day as a number with a leading zero (01-31).

m mm mmm mmmm

Month as a number without a leading zero (1-12). Month as a number with a leading zero (01-12). Month as an abbreviation (Jan-Dec). Month as a full name (January-December).

yy yyyy

Year as a two-digit number (00-99). Year as a four-digit number (0000-9999).

h hh

Hour without a leading zero (0-23). Hour with a leading zero (00-23).

n nn

Minute without a leading zero (0-59). Minute with a leading zero (00-59).

s ss

Second without a leading zero (0-59). Second with a leading zero (00-59).

fff

Fraction of Second with a leading zero (000-999). (Works only for oracle time stamp fileds)

tt

Uses the 12-hour clock for the preceding h or hh specifier, 'am' for any hour before noon, and 'pm' for any hour after noon.

Important thing is to understand that this format has nothing to do with your target database. This is the format of the source data. It is there to help to covert string into date time type inside of the software, so it can be loaded later into date or timestamp field So if source data is: 16/08/2009 than the format is DD/MM/YYYY 1/31/2009 than the format is M/D/YYYY 2006-05-23 22:34:42.096 than the format is YYYY-MM-DD HH:NN:SS.FFF 1992/mar/12 00:00 than the format is YYYY/MMM/DD HH:NN Copyright © 2009 DB Software Laboratory

Page 213 of 276

Advanced ETL Processor Professional & Enterprise User Manual

14. SQL Scripts Once data is loaded into the database user may want to perform various tasks like transformations, validations or calculations. User may perform any SQL command allowed by the target database. SQL commands must be separated by a separator. SQL Scripts Screen is designed to provide such functionality for the end user. • • • • •

To create a new SQL Script select SQL scripts group from objects tree and click New Dialog box will appear Fill in Description edit box with the name of SQL Script you are about to create Fill in comment if required Click OK to finish creation of SQL Script

To edit script double click on any previously created SQL Scripts.

Copyright © 2009 DB Software Laboratory

Page 214 of 276

Advanced ETL Processor Professional & Enterprise User Manual

Copyright © 2009 DB Software Laboratory

Page 215 of 276

Advanced ETL Processor Professional & Enterprise User Manual

14.1 SQL Scripts Tool Bar

1

2 3 4

17 18

5 6

7 8 9 10 11 12 13 14 15 16

19

20 21 22 23

1. SQL Script Properties 2. Open the SQL script from the disk 3. Save the SQL script to the disk 4. Save the SQL Script to the repository 5. Print the SQL script 6. Preview 7. Cut 8. Copy 9. Paste 10. Undo 11. Search 12. Repeat Search 13. Find Previous 14. Replace 15. Execute SQL Script 16. Stop execution when any error happens 17. Show/hide log 18. SQL script separator 19. Connection 20. Show connections only for current project 21. Manage Versions 22. Add Version 23. Revert to previous version

Copyright © 2009 DB Software Laboratory

Page 216 of 276

Advanced ETL Processor Professional & Enterprise User Manual

15. Reports This chapter illustrates the process involved in creating Advanced ETL Processor reports. A report provides the end user with the ability to view data as a neatly formatted presentation of data. A typical report will be specific to a set of parameters or business concepts, such as a "Latest Orders" list, or in the case of accounts, an “Overdue Payments" report. The type of report created will be dependant on the nature of the tasks and reporting procedures required. Advanced ETL Processor reports can be designed specifically, for individual business cases or can be generated from the system. The following "Report Designer" section describes the process in more detail: 15.1 Report Designer Upon opening Report Designer you will be presented with a screen similar to the following:

The units of measurement in the report can be determined from the horizontal and vertical rulers shown in the display. The units of measurement are usually based on centimetres, but this can be changed if required. This enables the user to design the exact layout of the data and titles on the report on paper before the report is designed. Therefore, it is possible to sue “X” and “Y” measurements. Copyright © 2009 DB Software Laboratory

Page 217 of 276

Advanced ETL Processor Professional & Enterprise User Manual Reports have three main aspects to them, as you can see on this illustration. These are described as follows: 15.1.1 Report Title As the name suggests, this is the name the report will be identified by, such as "Overdue Orders Report". This title is global to the whole report and will not change. 15.1.2 Page Header This header will appear on every page of the report. The headings for each page should be relevant for the data you intend to print on the report. For instance, the heading on this report for each page will contain the "Product Name", "Quantity Per Unit" etc. 15.1.3 Group Header Each report can be grouped by a specific category e.g. "group all prices by product type". The groupings usually take place in the SQL driving the report. Grouping can also take place at a number of levels. Hence, in this example there are two levels of grouping. The "CategoryName" and the "CompanyName" reflect the order of the groupings, i.e. group the data by "Category Name", then by "Company Name". The order of grouping can change, depending on the SQL or the groupings physically defined in the report. You will also note the other two tabs in the report. These are described as follows: 15.1.4 Code This reflects the actual SQL code used in the report. Therefore, if you wish to change the data in the report, or the way it is grouped, for example, you can amend the SQL to reflect the changes you wish to implement in the report. 15.1.5 Data This feature allows you to see a list of all the data used in the compilation of the report. The information presented here will probably change as a result of any changes made to the "code" section.

Copyright © 2009 DB Software Laboratory

Page 218 of 276

Advanced ETL Processor Professional & Enterprise User Manual

15.1.6 Report tool bar

1

2

3 1. 2. 3. 4. 5. 6. 7.

4

5 6 7

Report Properties Saves Report to the Repository Report Connection Show only connections for current project Manage Versions Add Version Revert to previous version

The above aspects are all described below: 15.1.7 Report Properties The Report Properties hold the basic information about the report, such as the report description and any comments describing the report in more detail. 15.1.8 Save Report to the Repository All reports are stored internally in the Advanced ETL Processor. This facility allows provides the report writer with an easy way to save the report for later retrieval or amendment. 15.1.9 Report Connection In order to retrieve information for the report, it is necessary to connect to a relevant database. The report connection tab shows which database the user is connected to at time of generating the report. 15.1.10 Show Only Connections for Current Project It is possible to have a number of different projects stored on the ETL configuration. If you wish only to show only connections for the current project you would click this icon. 15.1.11 Manage Versions Every report can have a number of versions associated with it. For instance, one version may show all the prices for a list of products, but a different version of the same report may only show a subset of these prices. Hence, there may be a requirement for different versions of the same report. The "Manage Versions" utility allows you to organize different versions of the report according to your preferences. These may include subsets of your report which can be Copyright © 2009 DB Software Laboratory Page 219 of 276

Advanced ETL Processor Professional & Enterprise User Manual "branched" e.g. you can have a version 1.0 or a version 1.1. Version 1.1 may have features which are slightly different from version 1.0. 15.1.12 Add Versions This allows you to add a new version of the original report. 15.1.13 Creating A Basic Report In order to demonstrate how the report process works, let’s create very simple report which returns a list of customers. First, the user should select an appropriate report group from the objects tree and click “new”.

This will bring up the Report Properties tab as shown. Fill in description and comment edit box if necessary:

Copyright © 2009 DB Software Laboratory

Page 220 of 276

Advanced ETL Processor Professional & Enterprise User Manual

Provide a report description, and any comments in the Comment box. Once you are happy with this, click “OK”. The report Wizard Form will then appear. Once the report wizard commences, the user can enter the details necessary for production of the report. The first dialogue box requests the data “source”. The data source can be based on an SQL statement or it can be based on data contained within a table. The example below will create a report based on a query. Therefore, click “next” and “new query”. At this point we have a choice. We can type in SQL directly or we can use the query builder.

Copyright © 2009 DB Software Laboratory

Page 221 of 276

Advanced ETL Processor Professional & Enterprise User Manual

If you are not very confident of your SQL skills, we recommend that the query builder it used in the process. However, in this example we will generate the report from an SQL query:

Copyright © 2009 DB Software Laboratory

Page 222 of 276

Advanced ETL Processor Professional & Enterprise User Manual Query Builder

The example illustrated shows that we have chosen the "Customers" table and chosen from the list of fields within that table for the report. Once you have the data you wish to include in the report the next step is to choose the Copyright © 2009 DB Software Laboratory

Page 223 of 276

Advanced ETL Processor Professional & Enterprise User Manual report layout. The process of choosing the fields from the table does not actually mean they will be displayed in the report. The objective here is to include appropriate data, perhaps to include in the query. However, not all fields will be needed in the report. This takes us on to the next step, i.e. deciding from the sub-set of fields which of these will be printed on the final report:

The "Fields" dialogue allows you to select the fields you would like to be printed in the report, from the query you have just created. In the field’s dialogue shown, you can see that all fields in the query have been chosen for the report in this case. We do not wish to group by anything in this report, therefore leave the "Groups" section blank as shown:

Here you can define the preferred layout of the report, including the orientation, and if you prefer a tabular (the standard report format), or if you prefer you can have the information printed in a columnar format. In this case we have chosen a portrait layout, a tabular format and so that the data in the report fits neatly on to the page, we have clicked the "Fit fields to page width" checkbox.

Copyright © 2009 DB Software Laboratory

Page 224 of 276

Advanced ETL Processor Professional & Enterprise User Manual The other information about the report is shown, such as the company information and the address. Next, we come the to "Style" dialogue. This allows us to specify how the report will "look and feel", by the use of colours and fonts. In our example there are four report styles to choose from but it is possible to create your own styles. For our report we chose "Corporate".

15.1.14 Report in Report Designer Finally, we click "Finish" to create the new report. Once we have done this we see the original Report Designer screen. From here we can choose the "Preview" button as shown:

Copyright © 2009 DB Software Laboratory

Page 225 of 276

Advanced ETL Processor Professional & Enterprise User Manual

Preview Button

We are then presented with the report as it will look when it is printed like so:

Using the preview button, the report can be exported to PDF, Html or into an Excel file as a CSV. Reports can also be emailed, either by saving them into one of the formats stated or directly from the preview facility.

Copyright © 2009 DB Software Laboratory

Page 226 of 276

Advanced ETL Processor Professional & Enterprise User Manual

16. Email Templates Email templates is a library of the most often used email texts. These can be used when automating processes such as when a set of transformations have taken place. Individual emails can then be set for various departments or management. For example most of the packages will have notification email to administrator. For instance, if a package fails it will be necessary to inform about the fact. An email template allows specific variables and information to be provided relating to the package failure. In addition email templates can be used to attach the actual execution log relating to the specific problem occurrence. For instance, a typical message about this sent in an email might be: “Package have failed please find attached execution log.” One of the advantages of the approach of using email templates is that they save a lot of time designing and sending emails individually. Another factor is that the email system reports incidents as they occur, and are not sent on after the event has finished.

In the example email template below you can see that certain variables have been defined. These variables are replaced by “actual” values at run time, or when the incident occurs.

Copyright © 2009 DB Software Laboratory

Page 227 of 276

Advanced ETL Processor Professional & Enterprise User Manual

Email templates can be used to indicate incidents, end of processes, commencement of processes, the results of processes and a variety of other business uses. They fit in nicely with the Advanced ETL Processor’s overall functionality.

Copyright © 2009 DB Software Laboratory

Page 228 of 276

Advanced ETL Processor Professional & Enterprise User Manual

17. Packages This chapter describes Packages - a great way of combining different Actions together. Most of the actions are self-explanatory therefore only screenshots provided. Below is list of actions supported by Advanced ETL Processor: • • • • • • • • • • • • • • •

Transformation SQL Script SQL Data check Package FTP Operation ZIP Operation Application File Check File operation Compare Files Backup Repository Pause Check Date Report Note

We are constantly working on improving our software if some of the functionality is missing please let us know and we will be more than happy to add it. Creating (ew Package • • • • •

To create a new Package select a Package group from objects tree and click New Dialog box will appear Fill in Description edit box with the name of the Package you are about to create Fill in comment if required Click OK to finish creation of the Package

Copyright © 2009 DB Software Laboratory

Page 229 of 276

Advanced ETL Processor Professional & Enterprise User Manual

Copyright © 2009 DB Software Laboratory

Page 230 of 276

Advanced ETL Processor Professional & Enterprise User Manual

17.1 Package screen overview The package screen overview provides the user with the ability to create objects in the ETL Advanced Processor. The objects are numerous and can be combined with any number of other objects. For instance, you can combine an SQL script to output information to an email and vice versa, to store information from an email into a database. There is a great deal of flexibility in the system. Double click to edit an Action

Objects Tree Drag and Drop Actions from the left to the right

Available Actions

What to do if error happened

Copyright © 2009 DB Software Laboratory

What to do next after success

Drag and Drop Actions from the list

Page 231 of 276

Advanced ETL Processor Professional & Enterprise User Manual Every package must have a starting point (Action in blue box on the picture).

17.2 Joining Actions Objects can be joined to other objects as shown here. This is achieved by simple “drag” and “drop” of the items from one to another. It is a very simple process.

Drag and Drop

(ote: To remove join repeat same procedure again. 17.3 Package Tool bar The package tool bar provides all the tools you need in order to create and manipulate objects. It is also possible to change how objects are viewed in the interface.

1 1. 2. 3. 4. 5. 6. 7. 8. 9.

2 3 4 5 6 7 8 9 10 11 12 13 14 15 Packages Properties Save to the Repository Print Print Preview Cut Copy Paste Delete selected Action(s) Clear all

Copyright © 2009 DB Software Laboratory

Page 232 of 276

Advanced ETL Processor Professional & Enterprise User Manual 10. Execute 11. Align Vertical 12. Align Horizontal 13. Manage Versions 14. Add Version 15. Revert to previous version 16. Execute Package 17.4 Transformation Action Properties To create a new Transformation Action, Drag and Drop it from the Object tree. You will then be presented with the following properties dialogue for transformations:

Copyright © 2009 DB Software Laboratory

Page 233 of 276

Advanced ETL Processor Professional & Enterprise User Manual

17.5 SQL Script Action The SQL Script Action object allows the user to define and identify any SQL scripts to be run (in this case Drop Demo Objects), the connection to be used:

Copyright © 2009 DB Software Laboratory

Page 234 of 276

Advanced ETL Processor Professional & Enterprise User Manual

17.6 SQL Check Action The SQL Check Action object can be used to check data queries in the database, to ensure that data integrity and ensure that results are returned as expected.

(ote: SQL Data Check uses SQL scripts to check data in the database. For example user may write following SQL: Select count(*) from table Execution will be successful if the value of first field of lookup query is more than 0 Execution will fail if the value of first field of lookup query is equals to 0 or less than 0 or field type is not numeric. Copyright © 2009 DB Software Laboratory

Page 235 of 276

Advanced ETL Processor Professional & Enterprise User Manual

17.7 Package Action A “Package Action” object allows the user to specify “what if” conditions based on the results of package executions. For instance, in the example below an email notification will be sent when the package completes it’s processing. (ote: Recursion is not allowed

Copyright © 2009 DB Software Laboratory

Page 236 of 276

Advanced ETL Processor Professional & Enterprise User Manual

17.8 FTP Action The Ftp Action performs various operations on the ftp server. For example, using the Advanced ETL Processor you will be able to download data directly from the FTP server, as in the following example, where the user is downloading Customer Sales figures:

Copyright © 2009 DB Software Laboratory

Page 237 of 276

Advanced ETL Processor Professional & Enterprise User Manual

Copyright © 2009 DB Software Laboratory

Page 238 of 276

Advanced ETL Processor Professional & Enterprise User Manual

17.9 ZIP Operation The “Zip” object allows an automatic compression of data in the processor. This can be used to backup log files, as in the following example, although compression can be used when sending attachments via email, when saving space on servers, and so on.

With the compression facility you can compress items and uncompress them as required. The location of the zip file and the files to compress are specified, as illustrated below:

Copyright © 2009 DB Software Laboratory

Page 239 of 276

Advanced ETL Processor Professional & Enterprise User Manual

Copyright © 2009 DB Software Laboratory

Page 240 of 276

Advanced ETL Processor Professional & Enterprise User Manual

17.10 Receive Email The “Receive” email object sets up the configuration you need in order to receive emails. The setting on here will be based on your particular server. A variety of connections are available including POP, IMAP, HTTP, Exchange and others.

The dialogue below allows the user to specify a directory where attachments will be downloaded to. This is useful in the automation process as attachments can be gathered in batch mode and saved to a central area.

Copyright © 2009 DB Software Laboratory

Page 241 of 276

Advanced ETL Processor Professional & Enterprise User Manual

Copyright © 2009 DB Software Laboratory

Page 242 of 276

Advanced ETL Processor Professional & Enterprise User Manual

17.11 Send Email Action The “Send” email action object allows you to configure the settings required for sending email, and is the converse objects to “Receive” email. The workflow settings allow the user to specify email templates to use for sending emails. The configuration can be set to the users preferred settings. In this case, the Simple Network Management Protocol (SMTP) has been defined by the user as the preferred method of transmitting messages.

Copyright © 2009 DB Software Laboratory

Page 243 of 276

Advanced ETL Processor Professional & Enterprise User Manual

(ote: Type in ‘TO’ box to reply to last sender

Copyright © 2009 DB Software Laboratory

Page 244 of 276

Advanced ETL Processor Professional & Enterprise User Manual

17.12 External Application Action Another useful feature is the ability in the Advanced ETL Processor to call and use external scripts and programs. This enhances the existing functionality already built into the system, and provides an even greater degree of flexibility.

Copyright © 2009 DB Software Laboratory

Page 245 of 276

Advanced ETL Processor Professional & Enterprise User Manual

17.13 Check file action The Check File Action object is provided to check on the status of files, for things like their existence, and attributes. The radio box dialogue below allows the user to specify which option is required to check for the file specified in the “Files To Check” text box.

Copyright © 2009 DB Software Laboratory

Page 246 of 276

Advanced ETL Processor Professional & Enterprise User Manual

17.14 File Operation Action In addition to being provided to with the ability to check the properties of files, it is also possible to decide the actions to take in relation to files and folders. For instance, the following dialogue is set up to create a directory, in “c:\temp” as specified in the filename text box:

Copyright © 2009 DB Software Laboratory

Page 247 of 276

Advanced ETL Processor Professional & Enterprise User Manual

17.15 Compare Files Action This “Compare” files object allows the user to perform a validation check against two sets of files by comparing their properties, via a “check sum”. The comparison can be done via the creation date as the comparison factor, or it can check to ensure that the MD5 checksums match. The files are successfully compared when the comparisons of both files are the same. The files to be compared are specified in the dialogue boxes as shown:

Copyright © 2009 DB Software Laboratory

Page 248 of 276

Advanced ETL Processor Professional & Enterprise User Manual

17.16 Backup Repository Action From time to time, it will be necessary to perform backup procedures on the repository. This object allows the user to set up a backup scheme. This returns the results of the backup to the user, in terms of success or fail status.

Copyright © 2009 DB Software Laboratory

Page 249 of 276

Advanced ETL Processor Professional & Enterprise User Manual

17.18 Working with filenames and directories Consider the following scenarios. • •

Every day we export data from our database and would like to save it into different file using current date as a part of the file. We would like to load yesterday’s data from the folder which has date part in the name

We can use functions in filenames to do it as follows

(ote: Only one {} pair is allowed. GETSYSTEMVARIABLE('SYSTEM_DATE') will return current date in ‘YYYYMMDDHHNNSS’ format If we want only part of date we can use LeftString(String,Count):String for example LeftString(GETSYSTEMVARIABLE('SYSTEM_DATE'),8) will return only date part. Now more complicated example for loading yesterday’s data; LeftString(DecDateS(GETSYSTEMVARIABLE('SYSTEM_DATE'),'YYYYMMDDHH NNSS', 'DAY',1),8) (ote: For complete reference of available functions consult chapter 23.

Copyright © 2009 DB Software Laboratory

Page 250 of 276

Advanced ETL Processor Professional & Enterprise User Manual

18. Scheduler Once a Package is created the Scheduler allows user to execute it on a regular basis. A Package may be executed once, daily, weekly, or monthly. The user may also specify a day of the week or month when to execute the Package. .

Copyright © 2009 DB Software Laboratory

Page 251 of 276

Advanced ETL Processor Professional & Enterprise User Manual

Advanced Schedule Options allows you to define execution boundaries.

Copyright © 2009 DB Software Laboratory

Page 252 of 276

Advanced ETL Processor Professional & Enterprise User Manual

The user may specify day of the week when to execute the batch. User must specify at least one day of the week.

Copyright © 2009 DB Software Laboratory

Page 253 of 276

Advanced ETL Processor Professional & Enterprise User Manual

The user may specify month when to execute the batch. User must specify at least one month.

Copyright © 2009 DB Software Laboratory

Page 254 of 276

Advanced ETL Processor Professional & Enterprise User Manual

19. Execution Monitor Once a Package is running or completed the Execution Monitor screen allows checking status or troubleshooting if any error happens. Package may have four different statuses: • Executing • Submitted • Failed • Finished The login screen consists of two panels. The Top panel shows overall status of the Package execution.

Double click on the top panel to see the Package log.

Copyright © 2009 DB Software Laboratory

Page 255 of 276

Advanced ETL Processor Professional & Enterprise User Manual

The Bottom panel shows status of individual items within the Package. Double click on Bottom panel to check item log.

Copyright © 2009 DB Software Laboratory

Page 256 of 276

Advanced ETL Processor Professional & Enterprise User Manual 19.1 Tool bar

1

2 3

4 5

6

7

8

9 10 11 12 13 14

1. Shows Log dialog 2. Refreshes the screen 3. Deletes the record 4. Prints 5. Previews Grid 6. Find Record 7. Export Grid 8. Show/Hide Fields 9. Delete all records from the log 10. Stops Execution 11. Show/Hides bottom panel 12. Refresh log every minute 13. Open directory containing log files 14. Open Package 19.2 Stopping execution The user can stop execution at any time by pressing

.

(ote for stopping SQL scripts SQL script stops once execution of current SQL statement is finished. It could take some time to do.

Copyright © 2009 DB Software Laboratory

Page 257 of 276

Advanced ETL Processor Professional & Enterprise User Manual

20. SQL The SQL Screen is a tool to run a free hand SQL or SQL scripts against the target databases.

20.1 SQL Toolbar 1 2 3 4 1. 2. 3. 4.

Show/hide SQL Statement Show/hide connection panel Show/hide log panel Show System Tables

Copyright © 2009 DB Software Laboratory

Page 258 of 276

Advanced ETL Processor Professional & Enterprise User Manual

20.2 Side toolbar

1. Previous SQL Statement 2. Next SQL Statement 3. Select SQL Statement to execute

Copyright © 2009 DB Software Laboratory

Page 259 of 276

Advanced ETL Processor Professional & Enterprise User Manual

21 Version Control Of the major benefits of using Advanced ETL Processor is integrated version control system. User can have multiple versions of transformations, SQL scripts, Reports, Email templates and Packages. Version control toolbar

1 2 3 1. Manage versions 2. Add version 3. Revert to the old version

Copyright © 2009 DB Software Laboratory

Page 260 of 276

Advanced ETL Processor Professional & Enterprise User Manual

22. Command Line To run a package from the command line type aetlcl.exe package id.

Package ID

Copyright © 2009 DB Software Laboratory

Page 261 of 276

Advanced ETL Processor Professional & Enterprise User Manual Parallel Execution Another benefit of using aetlcl.exe that the combination of Application Action and aetlcl makes it possible to execute several packages in parallel. See the pictures below.

Copyright © 2009 DB Software Laboratory

Page 262 of 276

Advanced ETL Processor Professional & Enterprise User Manual

23. File names functions This chapter provides list of functions which can be used in file names or directories Examples: C:\Data{GetSystemDate('YYYYMMDD')}.vis {GetSystemDate('YYYYMMDD')}.vis (otes: Only one pair of {} is allowed is replaced with directory path 23.1 String Functions

Trim Trim(String):string Trims leading and trailing spaces from a string.

TrimLeft TrimLeft(String):string Trims leading spaces from a string.

TrimRight TrimRight(String):string Trims trailing spaces from a string.

UpperCase UpperCase(String):string UpperCase returns a string with the same text as the string passed in, but with all letters converted to Uppercase

LowerCase LowerCase(String):string LowerCase returns a string with the same text as the string passed in, but with all letters converted to LowerCase

Replace Replace(String,OldPattern,NewPattern):string Copyright © 2009 DB Software Laboratory

Page 263 of 276

Advanced ETL Processor Professional & Enterprise User Manual Replace replaces all occurrences of the OldPattern by NewPattern within the String

SubString SubString(String,Index,Count):string SubString returns a substring containing Count characters or elements starting from Index.

RightString RightString(String,Count):string RightString returns the trailing characters of String up to a length of Count characters

LeftString LeftString(String,Count):string LeftString returns the leading characters of String up to a length of Count characters

AddCharLeft AddCharLeft(Char,String,Count):string AddCharLeft returns a string left-padded to Length with characters Char

AddCharRight AddCharRight(Char,String,Count):string AddCharRight returns a string right-padded to Length with characters Char

MakeString MakeString(Char,Count):string MakeString returns a string of Count filled with character Char.

DelSpaces DelSpaces(String):string DelSpaces returns string with all spaces deleted except one. "two spaces"->"two spaces

Delete Delete(String,Index,Count):String DelSpaces returns string with count characters deleted starting from index.

Copyright © 2009 DB Software Laboratory

Page 264 of 276

Advanced ETL Processor Professional & Enterprise User Manual

Insert Insert(Source,S,Index):String Insert returns string with s string inserted in index.

ProperCase ProperCase(String):string ProperCase returns string, with the first letter of each word in uppercase and all other letters in lowercase "proper case"->"Proper Case"

Copyright © 2009 DB Software Laboratory

Page 265 of 276

Advanced ETL Processor Professional & Enterprise User Manual

23.2 Numeric Functions

Abs Abs(Integer):Integer Abs returns the absolute value of the argument

Round Round(Float,Integer):Float Use Round to round Value to a specified power of ten. The following examples illustrate the use of Round: Expression

Value

Round(1234567, 3) Round(1.234, -2) Round(1.235, -2) Round(1.245, -2)

1234000 1.23 1.24 1.24

Sign Sign(Integer):Integer Use Sign to test the sign of a numeric value. Sign returns 0 if AValue is zero. 1 if AValue is greater than zero. -1 if AValue is less than zero.

Copyright © 2009 DB Software Laboratory

Page 266 of 276

Advanced ETL Processor Professional & Enterprise User Manual

23.3 Date Functions

Day Day(Date,Format):Integer. Use Day to get the day part of a date value. Day('01012003','DDMMYYYY')

Hour Hour(Date,Format):Integer. Use Hour to get the hour part of a date value. Hour('01012003','DDMMYYYY')

Minute Minute(Date,Format):Integer. Use Minute to get the minute part of a date value. Minute('01012003','DDMMYYYY')

Month Month(Date,Format):Integer. Use Month to get the month part of a date value. Month('01012003','DDMMYYYY')

Second Second(Date,Format):Integer. Use Second to get the second part of a date value. Second('01012003','DDMMYYYY')

Year Year(Date,Format):Integer. Use Year to get the year part of a date value. Year('01012003','DDMMYYYY')

Copyright © 2009 DB Software Laboratory

Page 267 of 276

Advanced ETL Processor Professional & Enterprise User Manual

DayS DayS(Date,Format):String. Use DayS to get the day part of a date value as string. DayS('01012003','DDMMYYYY')

HourS HourS(Date,Format):String. Use HourS to get the hour part of a date value as string. HourS('01012003','DDMMYYYY')

MinuteS MinuteS(Date,Format):String. Use MinuteS to get the minute part of a date value as string. MinuteS('01012003','DDMMYYYY')

MonthS MonthS(Date,Format):String. Use MonthS to get the month part of a date value as string. MonthS('01012003','DDMMYYYY')

SecondS SecondS(Date,Format):String. Use SecondS to get the second part of a date value as string. SecondS('01012003','DDMMYYYY')

YearS YearS(Date,Format):String. Use YearS to get the year part of a date value as string. YearS('01012003','DDMMYYYY')

Copyright © 2009 DB Software Laboratory

Page 268 of 276

Advanced ETL Processor Professional & Enterprise User Manual

IncDateS IncDateS(Date,Format,ChangeType,Increment):String. ChangeType: YEAR,MONTH,WEEK,DAY,HOUR,MINUTE,SECOND Use IncDateS to Increase ChangeType part of a date value by an Increment. IncDateS ('01012003','DDMMYYYY', 'YEAR',1)

DecDateS DecDateS(Date,Format,ChangeType,Decrement):String. ChangeType: YEAR,MONTH,WEEK,DAY,HOUR,MINUTE,SECOND Use DecDateS to Decrease ChangeType part of a date value by an Decrement. DecDateS ('01012003','DDMMYYYY', 'YEAR',1)

Copyright © 2009 DB Software Laboratory

Page 269 of 276

Advanced ETL Processor Professional & Enterprise User Manual

23.4 Conversion Functions

IntegerToString IntegerToString(Integer):String IntegerToString converts integer value to string value.

NumberToString NumberToString(Float):String NumberToString converts float value to string value.

StringToInteger StringToInteger(String):Integer StringToInteger converts string value to integer value.

StringToNumber StringToNumber(String):Float StringToNumber converts string value to float value.

Copyright © 2009 DB Software Laboratory

Page 270 of 276

Advanced ETL Processor Professional & Enterprise User Manual

23.5 Miscellaneous Functions

Iif Iif(expr1==expr2;expr3;expr4) Iif function returns expr3 or expr4 depending on expr1==expr2

GetSystemVariable GetSystemVariable('VARIABLENAME'):string GetSystemVariable returns value of 'VARIABLENAME'. Possible values for 'VARIABLENAME' are: COMPUTERNAME, OSUSERNAME, DBUSERNAME, BLOCKNUMBER, LINENUMBER, RECORDNUMBER, SYSTEM_DATE

GetSystemDate GetSystemDate(Format):String Returns Current system date/time in format specified GetSystemDate('MMDDYYYY')

Pos Pos(Substr,String): Integer Pos searches for Substr within String and returns an integer value that is the index of the first character of Substr within String. Pos is case-sensitive. If Substr is not found, Pos returns zero.

GetFileHeaderLine GetFileHeaderLine(LineNumber): String GetFileHeaderLine function returns header line LineNumber from the source text file

Copyright © 2009 DB Software Laboratory

Page 271 of 276

Advanced ETL Processor Professional & Enterprise User Manual

24. Execution Agent The Execution Agent is Microsoft Windows service which allows user to schedule and execute Advanced ETL Processor packages. (ote: Execution Agent is only available with Advanced ETL Processor Enterprise Version. To install the Advanced ETL Processor Enterprise Execution Agent as a service run following command aetlagent.exe /INSTALL To uninstall the Advanced ETL Processor Enterprise Execution Agent as a Windows service you must run the Advanced ETL Processor Enterprise Execution Agent with the /UNINSTALL switch as follows aetlagent.exe /UNINSTALL

Copyright © 2009 DB Software Laboratory

Page 272 of 276

Advanced ETL Processor Professional & Enterprise User Manual

25. Support Procedure We welcome your suggestions and comments to make our software better. Please do contact us through email or visit our website For General Sales and License Queries Email to: [email protected] For Technical Help Support Forum: http://www.dbsoftlab.com//Support-Forum/ Email To: [email protected] Online Contact Form: http://www.dbsoftlab.com/Support/Contact-DB-SoftwareLaboratory.html While submitting a bug or problem please include the following to make it easier to solve the problem as soon as possible: • • • • • • • • •

All the symptoms of a Problem Exact steps required to reproduce it Version number Operation system version Database version Log files Script SQL script to create tables Screenshots

We intend to resolve 90 percent of the reported problems with in 48 hours. Most difficult issues or functionality extension are resolved with in one week

Copyright © 2009 DB Software Laboratory

Page 273 of 276

Advanced ETL Processor Professional & Enterprise User Manual

26. License Agreement Advanced ETL Processor Professional by DB Software Laboratory www.dbsoftlab.com [email protected] E(D-USER LICE(SE AGREEME(T FOR THIS SOFTWARE IMPORTA(T - READ CAREFULLY: This End-User License Agreement ("EULA") is a legal agreement between you (either an individual or a single entity) and DB Software Laboratory for the SOFTWARE PRODUCT identified above, which includes computer software and may include associated media, printed materials, and "online" or electronic documentation. By installing, copying, or otherwise using the SOFTWARE PRODUCT, you agree to be bound by the terms of this EULA. If you do not agree to the terms of this EULA, you may be subject to civil liability if you install and use this SOFTWARE PRODUCT. SOFTWARE PRODUCT LICE(SE The SOFTWARE PRODUCT is protected by copyright laws and international copyright treaties, as well as other intellectual property laws and treaties. The SOFTWARE PRODUCT is licensed, not sold. I(STALLATIO( A(D USAGE Once SOFTWARE PRODUCT is installed you may use it for 30 days. After evaluation period ends, you have to purchase a license or stop using the SOFTWARE PRODUCT. If this is an EVALUATION VERSION of the SOFTWARE PRODUCT, you may copy and distribute an unlimited number of copies of the SOFTWARE PRODUCT; provided that each copy shall be a true and complete copy, including all copyright and trademark notices, and shall be accompanied by a copy of this EULA. If this is a REGISTERED VERSION of the SOFTWARE PRODUCT, you may install and use it for your personal use only. You may not reproduce or distribute the SOFTWARE PRODUCT for use by others.

Copyright © 2009 DB Software Laboratory

Page 274 of 276

Advanced ETL Processor Professional & Enterprise User Manual LICE(SI(G There are two types of licenses available 1. A single computer usage license. The user purchases one license to use the SOFTWARE PRODUCT on one computer.

2. A SITE usage license. The user purchases a single usage license, authorising the use of SOFTWARE PRODUCT, by the purchaser, the purchaser's employees or accredited agents, on an unlimited number of computers at the same physical site location. This site location would normally be defined as a single building, but could be considered to be a number of buildings within the same, general, geographical location, such as an industrial estate or small town. OTHER RIGHTS A(D LIMITATIO(S You may not reverse engineer, decompile, or disassemble the SOFTWARE PRODUCT, except and only to the extent that such activity is expressly permitted by applicable law notwithstanding this limitation. Any such unauthorized use shall result in immediate and automatic termination of this license and may result in criminal and/or civil prosecution. All rights not expressly granted here are reserved by DB Software Laboratory. The SOFTWARE PRODUCT is licensed as a single product. Its component parts may not be separated for use on more than one computer. You may permanently transfer all of your rights under this EULA, provided the recipient agrees to the terms of this EULA. SEVERABILITY In the event of invalidity of any provision of this license, the parties agree that such invalidity shall not affect the validity of the remaining portions of this license. COPYRIGHT The SOFTWARE PRODUCT is protected by copyright laws and international treaty provisions. All title and copyrights related to the SOFTWARE PRODUCT (including but not limited to any images, photographs, animations, video, audio, music, text, and "applets" incorporated into the SOFTWARE PRODUCT), the accompanying printed materials, and any copies of the SOFTWARE PRODUCT are owned by DB Software Laboratory. MISCELLA(EOUS Should you have any questions concerning this EULA, or if you desire to contact the author of this Software for any reason, please contact DB Software Laboratory (see contact information at the top of this EULA). LIMITED WARRA(TY Copyright © 2009 DB Software Laboratory

Page 275 of 276

Advanced ETL Processor Professional & Enterprise User Manual DB Software Laboratory expressly disclaims any warranty for the SOFTWARE PRODUCT. The SOFTWARE PRODUCT and any related documentation is provided "as is" without warranty of any kind, either express or implied, including, without limitation, the implied warranties or merchantability, fitness for a particular purpose, or no infringement. The entire risk arising out of use or performance of the SOFTWARE PRODUCT remains with you. In no event shall DB Software Laboratory be liable for any damages whatsoever or refund any money (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or any other pecuniary loss) arising out of the use of or inability to use this product, even if DB Software Laboratory has been advised of the possibility of such damages. Because some states/jurisdictions do not allow the exclusion or limitation of liability for consequential or incidental damages, the above limitation may not apply to you. SUPPORT A(D MAI(TE(ACE The annual maintenance fee is 20 percent of initial software license cost. Users with a fully paid annual maintenance fee get the following benefits: Priority Support Free software enhancements, updates and upgrades during the maintenance period Advanced and exclusive notification of software promotions "Maintenance Owner ONLY" product promotions E(TIRE AGREEME(T This is the entire agreement between you and DB Software Laboratory which supersedes any prior agreement or understanding, whether written or oral, relating to the subject matter of this license. Thank you for using the Advanced ETL Processor. DB Software Laboratory

Copyright © 2009 DB Software Laboratory

Page 276 of 276