Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

What are some Pros and Cons of ETL from Excel into my SQL Server DW?

3 posters

Go down

What are some Pros and Cons of ETL from Excel into my SQL Server DW? Empty What are some Pros and Cons of ETL from Excel into my SQL Server DW?

Post  BI Consultant Thu Aug 11, 2011 1:04 pm

I have a data model for my DW and the data source for this DW is Excel, i.e., there's no operational data source, all the data is in several Excel files, updated weekly. What are some things to keep in mind if I have to ETL using SSIS packages into my data warehouse?

BI Consultant

Posts : 18
Join date : 2011-08-09

Back to top Go down

What are some Pros and Cons of ETL from Excel into my SQL Server DW? Empty Re: What are some Pros and Cons of ETL from Excel into my SQL Server DW?

Post  ngalemmo Thu Aug 11, 2011 1:34 pm

Your biggest problem will be locking down the format of the sheets. Getting a lot of garbage data would be second on my list.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

What are some Pros and Cons of ETL from Excel into my SQL Server DW? Empty Re: What are some Pros and Cons of ETL from Excel into my SQL Server DW?

Post  Mike Honey Thu Aug 11, 2011 9:34 pm

I agree with ngalemmo's first two.
Third on my list would be accessing files (typically on a secured file share) from the ETL process (typically running under a service account on a different server).
Fourth on my list would be file locking. If someone leaves an Excel window open (or has an Excel crash) you usually find your SSIS process fails.

Overall in my experience it can be done, but you need to put serious thought into the design and build of your control flow and data staging to make it as resilient as possible.

Good luck!
Mike Honey
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

http://www.mangasolutions.com

Back to top Go down

What are some Pros and Cons of ETL from Excel into my SQL Server DW? Empty Re: What are some Pros and Cons of ETL from Excel into my SQL Server DW?

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum