ETL and Data Integration tools - Modern ETL software for everyone

Hi

I'm testing ETL Processor and trying the following:

1. have one txt-file with Companyname and address details
2. my erm has 3 tables Companies, Addresses and CompaniesAddresses (link file I call it). This structure is designed to attach multiple addresses to 1 Company and also using the addresses table to store Employee addresses etc. in.

3. I was able to split import the Companyname into Companies table and address details into Addresses table

4. Now I would like to fill the CompaniesAddresses table with the 2 new generated GUID's from table Companies and Addresses to link them together

Has anyone an idea if this is possible with ETL Processor and if there is a sample how to do that?

I have attached the ERM of the MS SQL DB.

Thank you for your help.

Beat http://www.dbsoftlab.com/media/kunena/attachments/legacy/images/companyaddress_ERM.jpeg

It is definetely possible.

How big are your tables?

Are you planning to load hundredrs of records/thousands/millions?

If I was doing it I would slightly change you database strucructure.

First of all I would chage structure of address table
1 remove Changed and ChangedDate because address never changes
(debatable that you need Created and Created Date..)
2 use MD5 or (CRC) of address as a primary key for address key

than when I load the data using advanced etl procesor i will calculate MD5 of address

so you will have the following sequence

reader => data transformer 1 =>data transformer 2 => data writer (clients table)

within data transformer 1 i would calculate md5 using joiner + md 5 data transforamation function

reader => data transformer 1 =>deduplicator => data transformer 3 => data writer (clients adresess table)

reader => data transformer 1 =>deduplicator => data transformer 4 => data writer (addresess table)

Mike

Hi Mike

thank you for quick reply. That's good news to know that it works!

Plan is to import about 100'000 records.

About primary key for address. Would it be possible to use the GUID created on address?


Now your solution sounds simple but I'm struggling already on the step

"within data transformer 1 i would calculate md5 using joiner + md 5 data transforamation function"

Would it be possible that you could create me a sample data flow Diagram? I could send you my import data as xml file.

Thank you for your help.

Beat

1. Email us your xml file and table creation script and we will create a working example for you

www.etl-tools.com/online-tutorials/advan...-generating-xml.html

This tutorial demostrates working with joiner

2. About primary key for address. Would it be possible to use the GUID created on address?

yes, it is possible. but it will have perfomace implications.
GUID is generated by sql server.
So, we need somehow get GUID for specific address.
it can be only done in two ways

a) for every record run sql select GUID from address table where post_code=;post_code and street_name=:street_name etc

Imagine If you have million of records in address table how long will take to load the data?

b) pull entire address table into memory and perfom search in the memory.
again for very large table it will be very slow plus you may run out of memory

and you would need to run it twice first time to process adresses than customers

(we support option b only a the moment)

watch this tutorial about lookups

www.etl-tools.com/online-tutorials/advan...g-lookup-object.html

md5 is very easy to calculate and it provides the best loading performance

Mike

As promised I have created a demonstration for you of how you can use lookup together with uniqueidentifier field

1 Make sure that you are using the latest version
2 Unzip everything into support folder
3 Script assumes that you have installed SQL server server locally and the database is called SUPPORT
4 Run script number 1 to populate Clients and Addresses table
5 Run script number 2 to populate ClientsAddresses table

Watch out online tutorials the will help you a lot

Mike

Hi Mike

thank you for the sample. We've finally got it working and found out that it is quite simple to change the db connection in the .ats files to connect. Eveything is in there.

I also understand now how the lookup works.

One more question...is there no way to get the id back from the writer and use this id to write directly to the ClientsAddresses table? If not could this not be an enhancement?

We will now go on and try the tool to migrate our Client data into sql-tables.

Thank you again for your help.

Regards

Beat