BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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:
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!