How to manage partitions dynamically in SSAS Tabular.
Partitioning your fact tables is a very good way of decreasing processing time when you load data into these tables. Often there is no need for processing the entire fact table every day, likely only the most recent data has changed in the underlaying table.
Let’s say we have a fact table called FactSales which contains a column called DateKey
If we assume that only the most recent sales data is inserted or updated into the table, then we can partition the table in SSAS using the DateKey column. We can choose to partition the table yearly, monthly, weekly, or even daily, but that will depend on how large the table would be. In this example the table will be partitioned by month.
Here we have three partitions for October, November and December, but what about when we hit January?
If we don’t want to create a new partition manually at every beginning of a new month, we can set up a script that does that for us. Objects in SSAS Tabular can be manipulated by a variety of techniques such as C#, PowerShell, TMSL or Analysis Services DDL Tasks in SSIS. We are going to demonstrate how you can manage your partitions using Script tasks in SSIS containing .NET code using the Analysis Management Object (AMO) client library.
First off, we need somewhere to store the definition of the partition and some other data, such as the scope of the partition. We can do that easily in a config table in our SQL Server environment like this:
The trick here is to use {0} and {1} in the WHERE clause so that we can set the filter dynamically in the C# script based on what month we are currently in.
Now we can create an SSIS-package where we use a Script Task component to execute a piece of C# code and thereby connect to and manipulate our SSAS Tabular Model. When we have created an SSIS-package and a Script Task within that, we need to download and install a couple of assemblies in the form of dll files to utilize the AMO client library. Here you can find detailed information about this.
After configuration you need to add these dll files to References in your Script Task, then it should look something like this:
In the Script Task itself, we will use C# to connect to our Model and work with our partitions. Here we can process existing partitions based on certain criteria or we can create new partitions when we hit a new month. In the following C# code, we are querying our config table and using the result of that query to generate the name of the previous and the current partition, based on the current year and month.
The variables currentYear / currentMonth / lastMonthsYear / previousMonth are defined in an earlier step, but that you can easily create by yourself. After that we are connecting to our SSAS Tabular Model and processing the previous partition, if it exists:
Here comes the cool part. Now we are checking the current month partition, and if that exist we process it. However, let’s say that this script is being executed on Jan 1st. Then there wouldn’t exist a partition called FactSales_2019_1 yet. As you can see in the “else“ statement, if the currPartitionName does not exist in our Model, we can create that partition, with the query we retrieved from the config table. All we need to do is use the string.Format method and swap out {0} and {1} with currMonthStart and currMonthEnd, which are variables with the value of the first and last day of the month that we are in:
Conclusion
Clearly there is a need for handling SSAS partitions in a dynamic way to reduce recurring manual configuration. Using C# and the AMO client library is one way of doing this and we at BizOne thinks it’s the easiest way of doing so. Except knowledge about SQL Server of course, some .NET knowledge is also required to accomplish this, but you don’t need to be an experienced programmer for this.
One drawback with this method is that if you have specified certain columns in the PartitionQuery instead of “SELECT *”, you will have to manually add any new columns to all existing partitions. However, in the latest SSAS Tabular Compatibility mode 1400, there is a new feature called Partition Functions that lets you query data with the M query language. This makes it possible to set up the query definition once with the filter values parameterized, which means that we only need to add new columns in the Partition Function definition once and not in all existing partitions. But more on this in another blog post…