SSAS Tabular Part 10 : Create Partitions in Table

We apply partition to table for easy management and faster access data and performance. Partitions, in tabular models, divide a table into various logical partition of objects. Each partition can then be processed independent of other partitions and because of that the performance gets increase. 

As per my view, it is very different from how partitions are implemented and utilized for deployed models.

To know more on this click here.

Scenario:

For example, a table certain 100000 rows that contain 16 years of record(2000 to 2016). In this table some of the record changes rarely (for years 2000 to 2015), but other row sets have data that changes often(for years 2015 to 2016). In these cases, there is no need to process all of the data when you really just want to process a portion of the data. Partitions enable us to divide portions of data we need to process frequently from the data that can be processed less frequently.

In this scenario we can divide it into 2 partition. First Partition contain 2000 to 2015 years data (rarely changes data) and other contain only 2016 year data (Frequently changes data).

Steps to create table Partition

For example, I am taking Fact Internet Sales table and going to create a partition on it.

Total number of Rows present in Internet Sales table : 60398

Years present in Internet Sales table: 2005, 2006, 2007 and 2008

Now we are going to create 2 partition on Internet Sales table.

•       Internet Sales 2005to2007 --> 2005, 2006 and 2007 years data (28133 Rows)
•       Internet Sales 2008             --> 2008 data (32265 Rows)

1. In the model designer, click on the Fact Internet Sales table, then click on the Table menu, and then click Partitions.
   
2. In the Partition Manager dialog box, in the partitions list, click the Fact Internet Sales partition.
3. In Partition Name, change the name to Internet Sales 2005to2007.
4. Select the Query Editor button just above the right side of the preview window.
5. In the SQL Statement field, we got full select statement. Here we have to add WHERE statement or we can remove entire query and paste the query given below. After that Click Validate.

               Select * from [dbo].[FactInternetSales] where (([OrderDate] >= N'2005-01-01 00:00:00')

               AND ([OrderDate] < N'2008-01-01 00:00:00'))

      6.  Internet Sales 2005to2007 partition has created. For 2^nd partition click on New                    button.

7. In Partition Name, change the name to Internet Sales 2008.
8. Select the Query Editor button just above the right side of the preview window.
9. In the SQL Statement field, we got full select statement. Here we have to add WHERE statement or we can remove entire query and paste the query given below. After that Click Validate.

                Select * from [dbo].[FactInternetSales] where (([OrderDate] >= N'2008-01-01 00:00:00'))

      10. In the Partition Manager dialog box, notice the asterisk (*) next to the partition            names for each of the new partitions we just created. This indicates that the partition has not been processed (refreshed). So, we have to process the created partition now. Click the Model menu, then point to Process (Refresh), and then click Process Partitions. Select 2 partitions and click on Process.

Output:

In the model designer, click on the Fact Internet Sales table, then click on the Table menu, and then click Partitions. Now you can see 2 partitions without  asterisk (*) for Fact Internet Sales.