chocobogo
asked on
Informatica ETL Staging Area Design
Hi there, I am working on a Datawarehouse implementation.
I am currently designing a ETL solution using Informatica and have come up with 3 different designs for my ETL staging area.
Overview: There are 3 company books, held across 20 countries.
And here are my options for the design:-
Option 1: 3 large tables based on different books.
Option 2: 1 table holding all the information from all countries and books.
Option 3: 20 tables, one for each country.
The main driver here is performance. It would be great if you could provide some insight into best practice and designs to improve informatica's performance, and help me decide on which option to take.
Links to white papers and examples would be great.
Much appreciated.
I am currently designing a ETL solution using Informatica and have come up with 3 different designs for my ETL staging area.
Overview: There are 3 company books, held across 20 countries.
And here are my options for the design:-
Option 1: 3 large tables based on different books.
Option 2: 1 table holding all the information from all countries and books.
Option 3: 20 tables, one for each country.
The main driver here is performance. It would be great if you could provide some insight into best practice and designs to improve informatica's performance, and help me decide on which option to take.
Links to white papers and examples would be great.
Much appreciated.
ASKER
Thanks for the quick response.
Yes, there will be queries that look at all books across all countries.
Do you happen to know the performance issues around the different options and also the informatica run times so that I can put together a business case for choosing option 2?
Also do you have a link to how to use partitioning effectively?
Yes, there will be queries that look at all books across all countries.
Do you happen to know the performance issues around the different options and also the informatica run times so that I can put together a business case for choosing option 2?
Also do you have a link to how to use partitioning effectively?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
Do you know anywhere where I can get the performance metrics for Informatica?
e.g. how long it takes to run an ETL procedure on
- 1000 rows
- 10k rows
- 100k rows?
Do you know anywhere where I can get the performance metrics for Informatica?
e.g. how long it takes to run an ETL procedure on
- 1000 rows
- 10k rows
- 100k rows?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Forced accept.
Computer101
EE Admin
Computer101
EE Admin
if latest mentioned option, one table holding all data will be better solution, but use partitioning.