Implementing the sliding window partitioning strategy for Microsoft Analysis Service Tabular Model

The sliding window partitioning concept is a well-known data base concept and it has been heavily used by DBAs, especially in Data warehousing. The concept behind this is to maintain fix number of partitions in a table and with the data volume grow, a new partition is added to the database table while merging the oldest partition in to a partition with a historical data. In most of the data warehousing solutions, data is loaded incrementally daily basis and a date field from the source system is used to identify the changed data to load in to the warehouse. However, there can be instance where old data is updated within certain period. For example, think of a scenario where daily sales transactions are loaded in to the data warehouse and SSAS tabular model is build using this sales transaction table. However, business user also says that there can be instances business users might update transactions up to 7 days old. That mean whenever we process Sales table, we need to process 7 days back dated data.  The ideal way for achieve these is to maintain 7 partitions to past 7 days and one partition for all the historical information. With this approach, in each time, there can only be maximum of 8 partitions in your database (SSAS cube or model).

To explain this scenario, let’s take the “Fact.Sale” table in the free sample database called “WideWorldImportersDW” database. If you do not have the database with you, click here to download the all the sample files of WideWorldImporters database solution. In the Sales table, there are more than 200,000 sales record invoiced from 1st of January 2013 to 31st of May 2016. For the demo purpose of this post, I created a Tabular model and exported “Fact.Sales” table and “Dimension.Date “table from WideWorldImportersDW database and this is how my tabular model looks when view in management studio.

clip_image001

Let’s see how many partitions available by default for a table in tabular model. For that, right click on sales table and click partitions menu option.

clip_image002

It will open up a window with all the available partitions for that table, and as you can see, by default there will only be a single partition for a particular table.

clip_image003

My next task is to create 8 partitions; 7 partition for sales information from 2015-May-25 to 2016-May -31 and one partition for all the sales which were invoiced after 25th of May 2016.  For that I will use Tabular Model Scripting Language Commands(TMSL). If you are familiar with XMLA for SSAS multidimensional modeling, just like we use XMLA to execute command to multidimensional models, TMSL is used to execute command against tabular model databases. If you like to learn more about TMSL, click here for Microsoft learning documents. 

Here is the TMSL script I used to create the 8 partitions. It is not necessary to use a TMSL script for this purpose, You can use SSMS or SSDT to create partitions using a UI window.However, if you are using this script, When running this script, you have to select section by section before run because TMSL does not have something similar to GO statement in T-SQL.

{
“create”: {
“parentObject”: {
“database”: “Demo”,
“table”: “Sale”
},
“partition”: {
“name”: “Sales After 7 days”,
“source”: {
“query”: [
“SELECT [Fact].[Sale].* FROM [Fact].[Sale] “,
“where [Invoice Date Key] < ‘2016-05-25′”
],
“dataSource”: “SqlServer localhost WideWorldImportersDW”
}
}
}
}

{
“create”: {
“parentObject”: {
“database”: “Demo”,
“table”: “Sale”
},
“partition”: {
“name”: “7 Days Old”,
“source”: {
“query”: [
“SELECT [Fact].[Sale].* FROM [Fact].[Sale] “,
“where [Invoice Date Key] =’2016-05-25′”
],
“dataSource”: “SqlServer localhost WideWorldImportersDW”
}
}
}
}

{
“create”: {
“parentObject”: {
“database”: “Demo”,
“table”: “Sale”
},
“partition”: {
“name”: “6 Days Old”,
“source”: {
“query”: [
“SELECT [Fact].[Sale].* FROM [Fact].[Sale] “,
“where [Invoice Date Key] =’2016-05-26′”
],
“dataSource”: “SqlServer localhost WideWorldImportersDW”
}
}
}
}

{
“create”: {
“parentObject”: {
“database”: “Demo”,
“table”: “Sale”
},
“partition”: {
“name”: “5 Days Old”,
“source”: {
“query”: [
“SELECT [Fact].[Sale].* FROM [Fact].[Sale] “,
“where [Invoice Date Key] =’2016-05-27′”
],
“dataSource”: “SqlServer localhost WideWorldImportersDW”
}
}
}
}

{
“create”: {
“parentObject”: {
“database”: “Demo”,
“table”: “Sale”
},
“partition”: {
“name”: “4 Days Old”,
“source”: {
“query”: [
“SELECT [Fact].[Sale].* FROM [Fact].[Sale] “,
“where [Invoice Date Key] =’2016-05-28′”
],
“dataSource”: “SqlServer localhost WideWorldImportersDW”
}
}
}
}

{
“create”: {
“parentObject”: {
“database”: “Demo”,
“table”: “Sale”
},
“partition”: {
“name”: “3 Days Old”,
“source”: {
“query”: [
“SELECT [Fact].[Sale].* FROM [Fact].[Sale] “,
“where [Invoice Date Key] =’2016-05-29′”
],
“dataSource”: “SqlServer localhost WideWorldImportersDW”
}
}
}
}

{
“create”: {
“parentObject”: {
“database”: “Demo”,
“table”: “Sale”
},
“partition”: {
“name”: “2 Days Old”,
“source”: {
“query”: [
“SELECT [Fact].[Sale].* FROM [Fact].[Sale] “,
“where [Invoice Date Key] =’2016-05-30′”
],
“dataSource”: “SqlServer localhost WideWorldImportersDW”
}
}
}
}

{
“create”: {
“parentObject”: {
“database”: “Demo”,
“table”: “Sale”
},
“partition”: {
“name”: “1 Days Old”,
“source”: {
“query”: [
“SELECT [Fact].[Sale].* FROM [Fact].[Sale] “,
“where [Invoice Date Key] =’2016-05-31′”
],
“dataSource”: “SqlServer localhost WideWorldImportersDW”
}
}
}
}

Run each of the block one by one to create 8 partitions and then process all the partitions using SSMS. If all worked without any issues, the sale table partitions should look like this.

clip_image004

Now what should happen when we load data for 1st of June 2016? Ideally, a new partition must be created to hold the data for the sales which took place in 1st of June 2016 ,and “7 Days Old” partition, the partition which contains data for 25th May 2016, should merge with “Sales After 7 days” partition. How can we automate this process? For that, we can use SSIS. This is the SSIS package I created to automate the process.

image

The initial task is an Analysis Service Execute DDL Task, and I have configured the following TMSL script to run using this task against my tabular database.

The Script: 

{

“mergePartitions”: {

“target”: {

“database”: “Demo”,

“table”: “Sale”,

“partition”: “Sales After 7 days”

},

“sources”: [

“7 Days Old”

]

}

}

This script merges the “7 days Old” partition with “Sales After 7 days” partition. The next task is to loop through other 6 partitions and change the names of that partitions. Because when we add new partition with one day old data, all other partitions become older by one day. Therefore, I added for loop container which loop from 6 to 1 and generate a dynamic TMSL which alter the existing partition name. Once I generated the script, I ran that script using an Analysis Service Execute DDL Task. Here is how I generated the dynamic TMSL inside the “Change Partition Name” script task.

image

Here I have used the loop variable to change the name, so “6 Days Old” become “7 Days Old” and “5 Days Old” become “6 Days Old” and so forth. After renaming 6 partitions, I created a partition to hold sales transaction for 1st of June 2016. Here is TMSL script I used for that. Although in here it is hardcoded as 1st of June 2016, in practical scenarios, this should be current date –1 days. Therefore, I kept the provision for that and used a script component to make dynamic TMSL script. Here is how I created it.

image

In your case, instead of hardcoding “2016-06-01”, use “DateTime.Now.AddDays(-1)” in order to create a partition to hold yesterday’s transactions. Once I generated the TMLS Script, I used another Analysis Service Execute DDL Task to run that script against my Tabular database. At the end, I full processed all my 7 partitions apart from the archive partitions, which hold lots of data. For that I used the following TMSL script and ran it using another Analysis Service Execute DDL Task.

The Process Script:

{
   “refresh”: {
     “type”: “full”,
     “objects”: [
     
       {
         “database”: “Demo”,
         “table”: “Sale”,
         “partition”: “7 Days Old”
       },
       {
         “database”: “Demo”,
         “table”: “Sale”,
         “partition”: “6 Days Old”
       },
       {
         “database”: “Demo”,
         “table”: “Sale”,
         “partition”: “5 Days Old”
       },
       {
         “database”: “Demo”,
         “table”: “Sale”,
         “partition”: “4 Days Old”
       },
       {
         “database”: “Demo”,
         “table”: “Sale”,
         “partition”: “3 Days Old”
       },
       {
         “database”: “Demo”,
         “table”: “Sale”,
         “partition”: “2 Days Old”
       },
       {
         “database”: “Demo”,
         “table”: “Sale”,
         “partition”: “1 Days Old”
       }
     ]
   }

}

Once I ran this package, this is how the partitions of the Sales table looked like. Since there are no sales data for 1st of June 2016 in “WideWorldImportersDW” database, “1 Days Old” partition did not have any records .

image

I hope this post helps you to implement sliding window partitioning in your tabular model database. Since the post is bit lengthy, If you find any difficulty in understanding any details, please put a comment. I am more than happy to clarify any unclear areas.

2 thoughts on “Implementing the sliding window partitioning strategy for Microsoft Analysis Service Tabular Model

  1. Robert Pitman March 6, 2020 / 5:09 am

    Hi Asanka, this is exactly what I need but I’m very new to SSIS so could you give me some details as to how you created each of the tasks. I have the TMSL script but don’t know how to add it to the tasks. Your help would be very much appreciated.
    Regards
    Robert

    Like

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s