Partitions in Tabular

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)

 

image

 

After we click it, we get an options box that looks like the below:

image

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:

 

image

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:

 

image 

Click Ok, do a complete Deploy and Process, and you’re ready to go.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s