Performance Tuning for Huge Writeback Partition
By Joyce Zhou - Apr 25, 2017
Too many records in writeback partition will impact both form and rule performance. When you have a huge writeback partition, the first thing we recommend is try to archive historic planning data to a MOLAP partition. If all records are active, we are going to apply the following method.
We are going to store all writeback data into a static (MOLAP) partition in order to boost performance. Whenever user enters data in the form (which is posted to Writeback partition), we create corresponding records to offset the static value. To keep the model clean, we need a scheduled task, such as a nightly processed SQL agent job, to consolidate records in these three partitions into the Static partition.
From the illustrator above, we can see on the end users side, they will not notice any difference. We will use this application as an example. However, with large data set, the database will be too large to share. Therefore, the extracted out the data. Please first restore the database, and then run this script in the database and all data will be auto-generated. Now, let’s walk through the Kepion configuration that realizes this functionality.
First, we need to create two partitions in the model – Static (MOLAP) and Offset (ROLAP).
Then, we create a SQL rule which will call the USR_CREATE_OFFSET stored procedure. This rule is executed at Post.
In the stored procedure, we use @ChangeList parameter to pass in only the cells that have been changed. For details of @ChangeList, please refer to this article. The stored procedure creates an offset value (highlighted in the illustrator below) to zero out the value in Static partition if there is a value exists in Writeback. Please refer to the application for the details of the stored procedure.
In addition, we can define an index on the Static fact table.
The index should contain all model dimension columns.
Setting up the partition mode and partition interval will also help in performance tuning. In our example, we have 81M records spreading across 9 years. On average, it is about 9M every year. The recommended size for one partition is 20M maximum. Therefore, we set partition mode to By Years and partition interval to 2. In this case, when generating the cube, SSAS will generate multiple partitions by splitting the data by every two years. Therefore, each OLAP partition contains ~18M data (9M * 2 years), which is within the 20M range.
The last step that is not included in this example but very important for production is to set up a scheduled SQL job to clean up and integrate data. Static records should be replaced by the updated records in Writeback , and then the offset records should be removed.