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