skaggs.andrew (5/14/2014)

---

Hi All,

I have a Fact table that has been in use for 2 years now and I am getting to the point where the table is beginning to get big. It is currently sitting at roughly 40 mill records. I have BO reports that are built of this table and I have an ETL nightly scheduler that repopulates this table every night. (Along with other staging, dim, etc.)

What I am looking to do is improve the performance of recreating this fact table. The fact table has about 6 years worth of data already in it (education data). What I want to do (because I THINK this would help in improving performance) is only delete the current records and then load in the current records each night leaving all historical data untouched in this fact table. (If it's not changing, why delete and reload every night?)

When testing this process, I found that trying to delete 5 mill records from this table was taking me an hour to do so. After researching a little, I felt like maybe building partitions on this fact table would greatly help me achieve what I want to do. The fact table is already kind of broken into chunks with State Tests, Course Grades, Attendance, Incidents, etc.

Based on this, can I create partitions on this already existing FACT table or would I have to rebuild an all new Fact table with partitions, then transfer over the data?

Please let me know your opinions.

Thanks for the help.