ETL Load - Dropping Indexes and Constraints

Hi,

I am currently working on an ETL solution using BODI and SQL Server 2005. Our data warehouse is very small (no more than 5mb) currently and expected growth over the next year is not going to exceed 15mb.

Based on the above volume estimates does it make sense to drop existing indexes/constraints when performing the ETL load into the fact table?

I'd appreciate other peoples comments and views on this.

Regards
Azeem

I would imagine that you could get by without dropping indexes and constraints pretty easily right now, given the small size of your warehouse. Inevitably it'll grow though, and leaving these in place during the load could cause a slowdown later on. It might be worth designing for it now to save yourself the possible work down the road. It's a judgment call really, so mine is just an opinion.

You should not delete constraints for integrity check, delete only indexes.

krishna wrote:You should not delete constraints for integrity check, delete only indexes.

Working in DEV is harder with constraints on...

krishna wrote:You should not delete constraints for integrity check, delete only indexes.

According to "The Data Warehouse ETL Toolkit (Kimball and Caserta)", referential integrity should be enforced by the ETL process, not by constraints.

For the amount of data you have, 15mb, I don't see a benefit of dropping the indexes or the constraints . Worst come to worst I'd drop and recreate the indexes. That is not a lot of data we're talking about.
You may ask,whether you should code so that even if the data is TB it will work. Yes true, but by then I'm sure your requirements will change.

When you say its an issue in dev,. what is the issue you're facing?

IF you try to enforce RI through ETL, and your code doesn't trap a scenario, you then have bad data. Yes you can test, have QA done, etc.. but underlying fact is that if something changes in your data and your code does not handle the RI ,you're in problems.

Has anyone bench marked the difference in timing by processing RI in ETL to having RI in tables. If so please could you share some numbers.It would be interesting to see the gains.