none
Partitioning large FactSales table

    Question

  • Hi,

    Forgive a newbie question, if this already covered.

    I have a FactSales table, currently in single PRIMARY filegroup. Ultimately for use by SSAS. The table has 800M rows

    I would like to partition the table by week. Currently the table includes a DateID column (20131125 format). I can access a DimDate table to get attribs, such as YearWeek no.

    I guess (feel free to correct) that I need to create additional filegroups, one for each week? Is there a quick way for me to produce a Partition Function Script to do this.

    I am using SQL Server 2012, so there are a load of additional things which may help me in my prep for cube deployment, such as Column Indeces etc., but I believe these will need the partition scheme to already be in place?

    Obviously I would appreciate any advice on this subject. If the response may be time consuming for you(?!) perhaps you could provide a decent link to example where Fact Table partitioning by week and column indexing has been deployed?

    Many thanks for any help,

    Ken

    Tuesday, November 12, 2013 5:30 PM

Answers

  • I guess (feel free to correct) that I need to create additional filegroups, one for each week?

    Yes depending on what basis you want to partition data like weeks,months etc you need to create filegroups for each period and then associate files to them (.ndf).

    Is there a quick way for me to produce a Partition Function Script to do this.

    You can drag and drop PARTITION scripts from template explorer in SSMS

    See technet documentation for more details

    http://technet.microsoft.com/en-us/library/ms190787.aspx

    Tuesday, November 12, 2013 6:23 PM

All replies

  • I guess (feel free to correct) that I need to create additional filegroups, one for each week?

    Yes depending on what basis you want to partition data like weeks,months etc you need to create filegroups for each period and then associate files to them (.ndf).

    Is there a quick way for me to produce a Partition Function Script to do this.

    You can drag and drop PARTITION scripts from template explorer in SSMS

    See technet documentation for more details

    http://technet.microsoft.com/en-us/library/ms190787.aspx

    Tuesday, November 12, 2013 6:23 PM
  • Thanks Mike, as a BI developer, I am a little out of my comfort zone with this. I have set up the file groups, partition func and scheme, and now partitioning my fact table (looks like this will take a little while to finish...). I did not know an easy way to automate much of the above, so did a bit of manual work to create scripts.

    There seem to be a few recommendations to utilise the columnstore index, so I will try this after the partitioning finished.

    Thks,

    Ken

    Wednesday, November 13, 2013 3:26 PM