Find how to use table partitions in SSAS Tabular to process data more quickly
You can partition a table in SSAS Tabular, which involves processing it in chunks rather than in one fell swoop. This blog explains how partitions work, and how to set them up and use them.

This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

Posted by Andy Brown on 19 February 2016

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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:

Pos table partitions

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:

The tblPos table

Select the table you want to partition in data or diagram view.

 

Now go into the Partitions menu:

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:

Renaming a partition

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:

Custom date filter

I can't find any way of doing this apart from by using a custom filter.

Now specify the date range for the filter:

Dates for 2013

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:

Creating a new partition

Click to create a second and subsequent partition.

 

You should end up with 3 partitions:

The 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:

Process partitions menu

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:

Process partitions options

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:

Processing results

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:

Managing partitions in SSMS

Right-click on the Pos table, for example, and choose Partitions...

 

Click on this tiny icon to create a new partition:

Creating 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:

SQL for partitiion

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:

Partitions menu again

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:

Process icon

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

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:

The updated 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! 

This blog has 0 threads Add post