| ETL stands for for extract, transform, load, three | | | | table or in fact any table? |
| database functions that are combined into one tool to | | | | Now let’s do the same using Advanced ETL |
| pull data out of one database and place it into another | | | | Processor |
| database. | | | | |
| - Extract - the process of reading data from a | | | | We would need two transformation functions Splitter |
| database. | | | | and Right |
| - Transform - the process of converting the extracted | | | | Splitter creates several fields from one using Delimiter |
| data from its previous form into the form it needs to | | | | and Text Qualifier. In our Example we have no |
| be in so that it can be placed into another database. | | | | Qualifier and delimiter is '-' |
| Transformation occurs by using rules or lookup tables | | | | |
| or by combining the data with other data. | | | | Right transformation function returns N characters of |
| - Load - the process of writing the data into the target | | | | right part of the string |
| database. | | | | In our example we set N to 2. |
| ETL is used to migrate data from one database to | | | | |
| another, to form data marts and data warehouses | | | | Next step is to connect objects |
| and also to convert databases from one format or | | | | - Source field [REGISTRATION NUMBER] to splitter |
| type to another. | | | | - Splitter to Right |
| So why use IT? | | | | - Right to target field [FIELD1] |
| Look at the MS SQL Server code below and think | | | | |
| what it will returndeclare @x varchar(10)set | | | | One of the major benefits of using Advanced ETL |
| @X='1245-XMCD-GTH'select | | | | Processor that you can see how the data will look like |
| harindex('-',@X)+1)-charindex('-',@X)-1),2) | | | | once it is loaded without actually loading it. All we we |
| Hint:There result is last two characters of a middle part | | | | need to do is to press "Magic Button" |
| -‘CD’ | | | | Result of transformation |
| Now answer those two questions | | | | |
| How long did it take you to figure out the result? | | | | As you can see Advanced ETL processor makes it |
| What is the chance of writing wrong code or getting | | | | easy to understand what is happening to the data |
| an error? | | | | during transformation. |
| Can you apply same function to a text file, Oracle | | | | |