Help needed with Partitioning

We have a data warehouse which is updated monthly. We need to partition the
tables of the data warehouse in to filegroups for 1 month each.
These are the details of the schema:
Table: MonthDim
Columns: MonthId, MonthName, Year
Table: Fact1
Columns: Fact1Id, MonthId, Value
Table: Fact2
Columns: Fact2Id, Fact1Id, Value
Relation between Fact1 and Fact2 is 1-many.
We need to implement partitioning by creating filegroups which contain
monthly data only, i.e. Rows of that month from Fact1 and rows of those
Fact1Ids from Fact2.
Is this possible? As I see we need to specify the range for partitioning
while creating partitioned tables, whereas what we need is increasing the
partitioned files as data comes in for each month.
Also, we need to remove data after 2 years, that is the filegroup older than
24 months has to be removed, so the no of filegroups always remains at 24
max.
Thanks in advance.
Sumit PilankarTake a look at the ALTER PARTITION SCHEME command since this allows you to
add and remove filegroups. You'll also want the NEXT USED option to
designate which FG is used by the next partition that is created.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Sumit Pilankar" <sumit.pilankar@.gmail.com> wrote in message
news:eeZRkLBdGHA.3556@.TK2MSFTNGP02.phx.gbl...
> We have a data warehouse which is updated monthly. We need to partition
> the tables of the data warehouse in to filegroups for 1 month each.
>
> These are the details of the schema:
>
> Table: MonthDim
> Columns: MonthId, MonthName, Year
>
> Table: Fact1
> Columns: Fact1Id, MonthId, Value
>
> Table: Fact2
> Columns: Fact2Id, Fact1Id, Value
>
> Relation between Fact1 and Fact2 is 1-many.
>
> We need to implement partitioning by creating filegroups which contain
> monthly data only, i.e. Rows of that month from Fact1 and rows of those
> Fact1Ids from Fact2.
>
> Is this possible? As I see we need to specify the range for partitioning
> while creating partitioned tables, whereas what we need is increasing the
> partitioned files as data comes in for each month.
>
> Also, we need to remove data after 2 years, that is the filegroup older
> than 24 months has to be removed, so the no of filegroups always remains
> at 24 max.
>
> Thanks in advance.
> Sumit Pilankar
>
>