Discussion about dynamic creation of partitions in AS2005

I have searched around and found very little about dynamically creating (and processesing) partitions in AS2005.

In AS2000 it is actually quite easy to manage partitions dynamically using a metadata table and DSO, but i had hoped that they came a little further in AS2005. Basically it's all about dynamically creating a partition for each month of a year or just partitioning over years. Does anyone have some good input about how to do it in AS2005 ?

Furthermore does someone have a comment on which prefferable option to choose (or maybe a completely other way) concerning partitions.

Option 1:

Partition "FACT_SALES_2005" points to a view called "v_FACT_SALES_2005" that is a "Select col1,col2 from FACT_SALES where period_year = 2005". The FACT_SALES table has a clustered index on period_year.

This way it's easy to manage views using a stored_procedure (create new view when we enter a new month etc.) and all the data resides in one single table which eases the ETL (in my opinion)

Option 2:

Partition "FACT_SALES_2005" points to a table called "FACT_SALES_2005".

This way it connects directly to the table, which someway is ok when it's partitioned in years, but what if you want to partition in months or weeks (52 weeks a year for 5 years of data). Then it becomes hard to manage.

Option 3:

Use partitioned tables and somehow point to a specific partition in the partitioned table from AS2005.

This i havent tried. But how will this perform. If you have a partitioned table that is partitioned in months, how do you reffer to let's say April 2006 in that partitioned table (FACT_SALES) from AS2005. And how about setting the Slice value of that partition....

Hi there,

i've had the same Problem and written a Assembly in .Net. This Assembly do all the things you can do from the GUI.

So i can process, create, ... Partitions from a stored Procedure in SQL. It works very nice. If you have detail Questions, aske me

Kind Regards

Andy L?wen

|||

You might take a look at:
http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=Partition

That function isn't in the latest release, so you'll have to download the source code and compile it yourself from:
http://www.codeplex.com/ASStoredProcedures/SourceControl/ListDownloadableCommits.aspx

|||

It is definitely worth having a look at the code that furmangg points to. You should be able to use this "out of the box" or if not it will give you a good starting point.

There is one variation that is probably the easiest to manage which is similar to option 1. And that is to have all your data in a single table and then use a query as the data source for the partition to only select the data for a specific period. (essentially storing the view definition in the partition rather than creating a separate view object in the relational database)

And just a quick note on option 3. When you have a partitioned table it appears to all the other applications as if it is a single table, the data is partitioned based on specific data values, so selecting data from a particular partition is simply a matter of using a where clause that matches the partitioning scheme.

|||

Nice, i'll have a look at it.

I just think it's funny the Microsoft hasn't put any more attention into partitioning setup/handling since it's the most obvious thing to use when working with large cubes, which again is what SQL Server 2005 is profiling itself to handle very well.

sql