Fact table partioining and indexing
2 posters
Page 1 of 1
Fact table partioining and indexing
Hi,
I am looking for some help and advise on my fact table.
I have a large fact table on sql2012. I have partioned this by a date. I also have indexes on this table aswell.
Currently my nightly extract inserts my data into staging tables and then drops the indexes and inserts my data into my fact table and then recreates the indexes.
I am new to table partioining and was wanting some advise on my nightly extract and inserting data into a partioned table?.There are different types of data scenarios which also add to the complexity.
The first is we receive fresh data i.e. data for the current year. Would this require a change to my script to ensure data is inserted into the correct partion?, if so how can this be done?.
The second scenario is data that is older than the current year may get updated. How would i update this type of data in a partioned table?.
I am also finding that although i have indexed my table. When i create and drop the indexes it takes hours to rebuild. I was looking to find out what is the best way to deal with indexes when inserting data into a fact table?.
Thank You
JTigger
I am looking for some help and advise on my fact table.
I have a large fact table on sql2012. I have partioned this by a date. I also have indexes on this table aswell.
Currently my nightly extract inserts my data into staging tables and then drops the indexes and inserts my data into my fact table and then recreates the indexes.
I am new to table partioining and was wanting some advise on my nightly extract and inserting data into a partioned table?.There are different types of data scenarios which also add to the complexity.
The first is we receive fresh data i.e. data for the current year. Would this require a change to my script to ensure data is inserted into the correct partion?, if so how can this be done?.
The second scenario is data that is older than the current year may get updated. How would i update this type of data in a partioned table?.
I am also finding that although i have indexed my table. When i create and drop the indexes it takes hours to rebuild. I was looking to find out what is the best way to deal with indexes when inserting data into a fact table?.
Thank You
JTigger
jtigger- Posts : 2
Join date : 2012-04-27
Re: Fact table partioining and indexing
Your indexes should be partitioned as well. You should only build indexes on the new partition. The old ones do not need to change. Also look at the partition swap functionality to manage your partitions.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Similar topics
» Fact Table Indexing Strategy
» Loading fact table and dimension indexing.
» Dimension Table Indexing Strategy
» Fact Indexing -SQL Server 2008
» 'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'
» Loading fact table and dimension indexing.
» Dimension Table Indexing Strategy
» Fact Indexing -SQL Server 2008
» 'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum