One feature that makes SSAS Tabular a good choice for handling the large datasets is table partitioning.Table partitioning allows a big table be split into multiple partitions based on values of one or multiple columns and the main benefit is reducing cube processing time by processing just the required partitions instead of the whole table. By the time writing table partitioning is still not supported by Power BI Desktop.
In this post I show how to create partitions manually in SSDT and automatically by using PowerShell script and M query.
How to create partitions
When developing SSAS project in SSDT, the partitions need to be created one by one by specifying the name of the partition and define the data boundary in the partition’s query. For example if we have table named Sales with a date column OrderDate and want to have monthly partitions, the partitions can follow the below pattern :
Partition name: Sales_201901
Select * from Sales where Order_Date >= ‘2019-01-01’ and Order_Date < ’2019-02-01’Partition name: Sales_201902
Select * from Sales where Order_Date >= ‘2019-02-01’ and Order_Date < ’2019-03-01’From SQL Server 2017, Power Query has been included to SSDT to transform the source data into SSAS table and due to this change the partitions must be created by Power Query or M scripts.To show how it works, let’s quickly create some partitions by using Power Query.
First select the table in your model and from Table menu select Partitions:
In Partition Manager window you will see a default partition for the table, by clicking on Design button Power Query would be open:
Click on Filter button on the date column, then select Number Filters and Greater Than Or Equal To:
And the month boundary will be defined here:
After entering the filter values, the records will be filtered out and we should select Close & Update or Close & Update Without Processing to get back to Partition Manager window:
Now you can see a new line has been added to the M query for filtering the rows of the first dataset. We need to give a proper name to the partition and click on Ok to save the works:
Fortunately creating the next partitions is much easier than the first one because now it has the template and will use it for creating the new partitions. We need to go to Partition Manager and click on New button then provide the month boundary in the script:
Now that you have some partition created, can process the partitions instead of table and avoid importing a lot of rows in your model:
Dynamic Partitioning
We learned how to create partitions manually based on a date period such as month in SSDT. When the model is deployed to Sql Server and time passing more partitions need to be created for the new coming months and this has to be done in one of the following ways:
- Partitions to be crated manually for all historic and some future months for example 5 years.
- Partitions to be crated manually for historic months and create a partition for the coming months on or before the first day of each new month..
- Automate creating the new partitions by using script and job scheduling.
It is clearly understandable that options 1 and 2 need a lot of time specially if there are many partitioned tables in the model. The third option is dynamic partitioning that I am going to show how to implement it by PowerShell script here.
First in PowerShell I create a function with SSAS table, M query expression for the partition and partition name as the input parameters:
Function CreatePartition {
param( [Microsoft.AnalysisServices.Tabular.Table] $table,
[string]$Expression,
[string]$Name )
$newpartition = New-Object -TypeName Microsoft.AnalysisServices.Tabular.Partition;
$newpartition.Source = New-Object -TypeName Microsoft.AnalysisServices.Tabular.MPartitionSource;
$newpartition.Mode = [Microsoft.AnalysisServices.Tabular.ModeType]::Import;
$newpartition.Source.Expression = $Expression
$newpartition.Name = $Name;
$table.Partitions.Add($newpartition);
$result = $model.SaveChanges()
if (-Not $result.Impact.IsEmpty)
{
write-output("Partition " + $Name + " been created successfully")
}
}
Now we can start writing the main part. The below script creates a connection to the SSAS db and list the existing partitions of Sales table:
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.Tabular")
$server = New-Object Microsoft.AnalysisServices.Tabular.Server
$server.Connect("SSASServer1")
$db = $server.Databases["RetailDB"]
$model = $db.Model
$SalesTable = $model.Tables["Sales"]
$pnames = $SalesTable.Partitions.Name
foreach ($pname in $pnames|sort) {
Write-Output($pname)
}
The next step is finding the partitions which don’t exist in the table and call CreatePartition function for them.The logic is simple, As expected to have one partition for each month, we can loop through the months starting from the current month back to the first month of data (201701 in this example) and check if the partition name exists in of Sales partition collection.
In case the partition doesn’t exist, the M query will be generated based on our query pattern and by using the month boundaries and then will be passed to CreatePartition function:
$ProcessDate= (Get-Date)
$i=0
$PartitionToBeChecked =""
while($PartitionToBeChecked -ne "Sales_201701")
{
$PartitionToBeChecked="Sales_" + $ProcessDate.AddMonths($i).Year + $ProcessDate.AddMonths($i).Month.ToString("00")
if ($pnames -contains $PartitionToBeChecked -eq $False)
{
$ThisMonth=$ProcessDate.AddMonths($i).Year.ToString() + $ProcessDate.AddMonths($i).Month.ToString("00")+"01"
$NextMonth=$ProcessDate.AddMonths($i+1).Year.ToString() + $ProcessDate.AddMonths($i+1).Month.ToString("00")+"01"
$Expression="let " +
"Source = #""SQL Datawarehouse"", "+
"dbo_vw_Sales = Source{[Schema=""dbo"",Item=""vw_Sales""]}[Data], "+
"#""Filtered Rows"" = Table.SelectRows(dbo_vw_Sales, each true), "+
"#""Filtered Rows1"" = Table.SelectRows(#""Filtered Rows"", each [Order_Date_Key] >=" + $ThisMonth +" and [Order_Date_Key] < " + $NextMonth +") "+
"in "+
"#""Filtered Rows1"" "
CreatePartition $SalesTable $Expression $PartitionToBeChecked
}
$i--
}
As you can see the new partitions have been added to Sales table and this script lists the all partitions in Sales now:
Summary
In SSAS Tabular the large tables can be split into the smaller partitions to increase the queries performance and saving time on processing the cube. In Sql Server 2017 and Sql Azure, SSAS tables and partitions are maintained by M query similar to Power BI and Power Query. The partitions can be created manually in SSDT or Sql Server but they can be automated by using PowerShell script.