Author: Joyce Zhou
Too many records in a Writeback partition will impact both Form and Rule performance. When you have a huge Writeback partition, the first thing we recommend is trying to archive historic planning data to a MOLAP partition. If all records are still active, however, we are going to apply the following method.
We're going to store all Writeback data into a static (MOLAP) partition in order to boost performance. Whenever users enter data in the Form (which is posted to the Writeback partition), we'll create corresponding records to offset the static value. To keep the Model clean, we'll need a scheduled task, such as a nightly SQL Agent Job, to consolidate the records in these three partitions into the Static partition.
In the diagram above, we can see that on the end-users side, they won't notice any difference. We'll use this application attached below as an example. However, given the large set of data, the database will be too large to share, so first restore the attached database and then run this script to auto-generate all the data.
Once that's done, we'll need to create two partitions in the Model – Static (MOLAP) and Offset (ROLAP).
Then, we'll create a SQL Rule which will call the USR_CREATE_OFFSET stored procedure. This Rule is executed at Post.
DECLARE @ProjectID SMALLINT = (SELECT MemberId FROM @FilterScope WHERE Dimension = N'Project')
,@YearID INT = (SELECT TOP 1 MemberId FROM D_TIME DT WHERE DT.Granularity = N'Year'
AND DT.MemberLabel = (SELECT TOP 1 [Name] FROM @FilterScope WHERE [Dimension] = N'Time' AND [Hierarchy] = N'FiscalYear'));
@ChangeList = @ChangeList
,@ProjectID = @ProjectID
,@YearID = @YearID
In the stored procedure, we use the @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 the Static partition if the value exists in Writeback.
In addition, we can define an index in the Static fact table.
The index should contain all Model Dimension columns.
Setting up the partition mode and partition interval will also help with performance. In our example, we have 81 million records spreading across 9 years. On average, that's about 9 million every year. The recommended size for one partition is 20 million, maximum. Therefore, we should set the 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, which means each OLAP partition contains ~18M data (9M * 2 years), which is within the 20M range.
The last step that's not included in this example, but very important for production, is to set up a scheduled SQL Job to clean up and integrate the data. Static records should be replaced by the updated records in Writeback, and then the offset records should be removed.