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

ETL Load - Dropping Indexes and Constraints

Go down

ETL Load - Dropping Indexes and Constraints Empty ETL Load - Dropping Indexes and Constraints

Post  AzeemFarooqui 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

Back to top Go down

ETL Load - Dropping Indexes and Constraints Empty Re: ETL Load - Dropping Indexes and Constraints

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

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

Back to top Go down

ETL Load - Dropping Indexes and Constraints Empty Delete only index

Post  krishna 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

Back to top Go down

ETL Load - Dropping Indexes and Constraints Empty Enforce data integrity in the ETL, not using database RI constraints...

Post  steve_waters 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

Back to top Go down

ETL Load - Dropping Indexes and Constraints Empty Re: ETL Load - Dropping Indexes and Constraints

Post  Rik Declercq 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

Back to top Go down

ETL Load - Dropping Indexes and Constraints Empty ETL Load - Dropping Indexes and Constraints

Post  tropically 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

Back to top Go down

ETL Load - Dropping Indexes and Constraints Empty Re: ETL Load - Dropping Indexes and Constraints

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