Continuing my tabular kick that I’ve been on lately, tabular databases allow partitioning of tables to break down larger fact tables into smaller pieces for the engine to manage. This can be very beneficial for us, especially if we have large groups of data that don’t change much from day to day. It is very wasteful to reprocess something if it’s never going to change, and creates a lot of unneeded pressure on the server to reprocess something. We can alter the table to only process current data when needed. To access the partitioning dialogue box, there are 2 ways that it can be accessed. The first is through the menu bar, by going to Table->Partitions, but an easier way (one less mouse click) is to just click on the partitions button from the main design screen, located towards the top left corner (I’ve circled it in this diagram)
After we click it, we get an options box that looks like the below:
At the top we have a table selector, that we can use to select which table we want to partition. For this, I’m going to use the classic FactInternetSales table in Adventure Works 2012 DW. Directly underneath that is a list of the current partitions that are associated with that table. We are going to split the 1 existing partition on this table into 2, 1 for orders before 2006 and then another one for everything after. To start, we’re going to modify the existing partition by changing it from being table bound (as it is by default) to being query-bound. To do this, on the right hand side about half-way down you’ll see the toggle, as indicated by the icons in red below:
Click on the SQL icon, and you will get the query window. Assuming you are not importing every column from that table into your model (which you shouldn’t be anyway), you will have a query that lists out all of the columns, ending with the FROM clause. Here, we’re just hard-coding to before a certain date, basing it on the order date, so our where clause would be WHERE OrderDateKey < 20051231. Give the partition a new name, and that will do it for the first one.
After we’ve modified the query for the original table, click on New at the top underneath the partitions list. Click New to add a new partition to the list, and click down underneath in the SQL view. Change your less than sign to a greater than sign, and then give it a new name. Clicking on either partition allows to toggle between them and check your work. You’re final partitions list should look like the below:
Click Ok, do a complete Deploy and Process, and you’re ready to go.