What are some Pros and Cons of ETL from Excel into my SQL Server DW?
3 posters
Page 1 of 1
What are some Pros and Cons of ETL from Excel into my SQL Server DW?
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
Re: What are some Pros and Cons of ETL from Excel into my SQL Server DW?
Your biggest problem will be locking down the format of the sheets. Getting a lot of garbage data would be second on my list.
Re: What are some Pros and Cons of ETL from Excel into my SQL Server DW?
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!
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!
Similar topics
» Fedrated DW pros and cons and what to watch out for
» Wide fact tables
» Too many bridge tables dimensional modeling - pros/cons
» What are some Best Practices to ETL from Excel to SQL Server 2008?
» Pros and cons of consolidated dimension table Vs. many dimension table ?
» Wide fact tables
» Too many bridge tables dimensional modeling - pros/cons
» What are some Best Practices to ETL from Excel to SQL Server 2008?
» Pros and cons of consolidated dimension table Vs. many dimension table ?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum