Using partitions in Analysis Services (tabular model)
You can partition a table into chunks, and then choose which chunks
you want to process (for example, you could choose to load just transactions for
the year 2015, knowing that data for all previous years is already in your model
and won't change).
Beware: for versions of SQL Server earlier than 2016, partitions on different
tables are processed in parallel but partitions on the same table are processed
in series. This means that if you have one table which is much bigger than
all of the others, partitioning it won't significantly speed up processing time
if you need to upload all of its data.
An example of partitioning - splitting the point-of-sale table by date
A sensible way to partition our tblPos table would be by
year, like this:
It's unlikely that data from earlier years will change, so you could put this in one or more separate partitions.
When more point-of-sales records are created, you should
now just be able to do a Process Data on the 2015+ partition followed by a
Process Recalc on the database.
As this diagram shows, you can't include calculated columns in partitions (these are processed in the
Process Recalc stage, not the Process Data one).
Managing partitions in SQL Server Data Tools (Visual Studio)
To create partitions for a table (such as for the tblPos
table as shown above), first select the table:
Select the table you want to partition in data or diagram view.
Now go into the Partitions menu:
Choose this menu to see this table's partitions.
There's always one partition set up for any table, so we'll rename this:
Change the name of the partition from
tblPos to (say) 2013 data.
Now set a filter specifying which rows should be included in this partition:
I can't find any way of doing this
apart from by using a custom filter.
Now specify the date range for the filter:
Here I'm including all point-of-sale rows for the year 2013.
It's vital that you now create other partitions, so that between them they
capture all of your rows:
Click to create a second and subsequent partition.
You should end up with 3 partitions:
Don't worry about the fact that all of the rows are in the
2013 data partition for now; this will change when you reprocess your model.
Note
that it is entirely up to you to make sure that your partitions neither overlap
nor miss out vital data. Tabular
SSAS won't tell you if processing would create anomalies.
Processing partitions in SSDT (Visual Studio)
When you want to process only certain partitions, you can select this menu
option:
First select a table, then choose this menu to process its
partitions.
Now choose the processing mode, and the partitions that you want to process:
You'll need to use Process Full the first time, to get rid of any old data and hence avoid creating duplicate values for the
PosId column (see my blog on Processing
for more details about what each processing options entails).
Reassuringly, the sum of the 3 partitions' rows is 5,586, which is the total
number of rows in the tblPos table:
There are no rows in the MAM database for any year after 2014.
Managing partitions in Management Studio
(SSMS)
As is often the case with SSAS Tabular, you can do the same thing in SSMS,
but it's harder! To manage partitions for a table, right-click on it and
choose the following menu:
Right-click on the
Pos table, for example, and choose
Partitions...
Click on this tiny icon to create a new partition:
You can add a new partition by clicking on this icon.
It's then up to you to write SQL to extract only the data you need from the
underlying SQL Server table:
No fluffy GUI tools here! This SQL
query would give you the data for the 2013 data partition, for example.
Processing partitions in Management Studio
You can use the same dialog box as the one shown above to process partitions:
First right-click on the table some or all of whose partitions you want to process.
You can now click on one of the world's smallest icons to select partitions
for this table to process:
Click on this tiny icon to process one or more partitions.
Finally, choose which partitions you want to process, and how:
Select a processing method, and tick the partitions you want to process.
SSAS loads the data into your deployed model, and updates the row statistics:
Once again, it's reassuring that the two sets really do partition (in the mathematical sense) the table of point-of-sales
data.
And that's it!
