ETL Load - Dropping Indexes and Constraints

View previous topic View next topic Go down

ETL Load - Dropping Indexes and Constraints

Post  AzeemFarooqui on Tue Mar 03, 2009 8:32 am

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

AzeemFarooqui

Posts : 6
Join date : 2009-02-23

View user profile

Back to top Go down

Re: ETL Load - Dropping Indexes and Constraints

Post  BrianJarrett on Tue Mar 03, 2009 9:44 am

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.
avatar
BrianJarrett

Posts : 61
Join date : 2009-02-03
Age : 43
Location : St. Louis, MO

View user profile

Back to top Go down

Delete only index

Post  krishna on Tue Apr 14, 2009 3:18 am

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

krishna

Posts : 1
Join date : 2009-04-14

View user profile

Back to top Go down

Enforce data integrity in the ETL, not using database RI constraints...

Post  steve_waters on Wed Apr 29, 2009 9:00 pm

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

Working in DEV is harder with constraints on...

steve_waters

Posts : 4
Join date : 2009-04-23

View user profile

Back to top Go down

Re: ETL Load - Dropping Indexes and Constraints

Post  Rik Declercq on Thu Apr 30, 2009 4:41 am

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.

Rik Declercq

Posts : 10
Join date : 2009-02-03

View user profile

Back to top Go down

ETL Load - Dropping Indexes and Constraints

Post  tropically on Wed May 13, 2009 6:28 pm

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.

tropically

Posts : 13
Join date : 2009-05-12

View user profile

Back to top Go down

Re: ETL Load - Dropping Indexes and Constraints

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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