Going in Production - Expert Cube Development with SSAS Multidimensional Models - page 288

In this section, we'll demonstrate how you can use SQL Server Integration Services
( SSIS ) plus some .NET code to create partitions dynamically. Here's a breakdown of
what we'll do:

• We'll create a template partition within our measure group that we will use

as the basis for all of the new partitions.

• We'll create an Integration Services package that loads data from a

configuration database, uses this data to decide if any new partitions need
to be created, and finally creates them by copying the template partition and
modifying its properties.
There will be two types of partition slice: data for the most recent 12 months
will be stored in 12 monthly partitions, while older data will be stored in
yearly partitions. This will give us the opportunity to show how to handle
more advanced problems like merging partitions.
The measure group will contain different aggregation designs for the
different types of partition: monthly and yearly. Our package will apply the
right aggregation design to any new partitions it creates.

Once again, all of the code for this chapter is available as part of the sample projects
and databases for the topic; we're only going to highlight the most interesting parts
of that code here.

Relational versus Analysis Services
partitioning
The fact tables upon which we build our Analysis Services measure groups are
normally partitioned. Partitioning in the relational database follows rules and needs
dictated by the relational data structure. A common relational partitioning model
is by month, so each partition holds a month of data. When data gets too old, it is
deleted or moved somewhere else, to leave space for new data.