SSAS – Writebacks, an Efficient Planning Model

Hi,

In this post I would like to highlight the techniques usually involved in SSAS (multi-dimensional model) for planning purposes, the challenges faced and some tricks to optimize the write-back model.

The motivation for me in this post is to efficiently apply the multiple features of cube at work and to derive an optimal planning model.

Because in most of the post I have come across so far (though very helpful) mainly exhibits the functionality of achieving the write-back functionality, via partition. But beyond it, the efficient utilization of the same in completing the model isn’t yet available.

Hence this is my attempt to present the complete picture of the same.

(In the entire post I will assume when ever I mention the SSAS, I will be referring to the multi-dimensional model)

Basics:

SSAS, offers write-back’s at both dimensional and cell level. Thus, I wont be digging much deeper into the fundamentals of the same as it has been highlighted across multiple post as follows:

Dimensional Writeback:

http://ram-msbi.blogspot.com.au/2012/03/learn-msbi-dimension-writeback-and-cell_15.html

https://msdn.microsoft.com/en-us/library/ms174540.aspx

As there are not many articles describing it completely are available on the web, I will highlight the feature with few screen shots for better covering the topic:

To enable a write back on any dimension, ensure that its built on plain vanilla table, because based on views, or named calculations (in the dsv) wouldn’t allow the write-back and you will keep pulling your hairs with the errors.

So to enable write-back:

In the SSAS designer (BIDS) you will see the following icon, which will enable you to add members to the dimension , but beware the dimensional write-back is not supported from excel so unfortunately if you were planning for the same.

You would have to take a different development approach for the same as nothing out of the box would support to my knowledge so far.

And there you go, we have established our selves a write-back dimension !!!

Cell Writeback:

https://sstoltze.wordpress.com/2011/05/04/enabling-writeback-to-ssas-in-excel-2010/

The cell write-back in SSAS is more popular among’st bloggers so you would be easily be able to find many articles on the same. But for dimensional write back the number of articles are less but it ai’nt that complex either.

Planning Model

So now after completing the ground work for write-back, in this article we will try to build a planning model for the following challenge.

Objective:

Build a simple HR planning model, where employee can plan only monthly level salaries (only , not daily), but for reporting those planned monthly salaries should be spread across daily (equally weighted) for reporting purposes.

Solution:

To achieve the solution, once of the challenge for me to address was that of single dimension linking at multiple granularity level to different fact tables (one containing the daily fact data for the employees, and one used for planing purposes)

Leading me to adopt the following structure for my dsv

Now in the above the the model comprises of only 4 tables out of which (Employee and Date) are dimensional tables and (FactSalary and FactPlanSalary) are the tables representing factual data.

Now coming back to model of the cube, the dimensional usage of the date dimension is where all the trick lies of utilizing the dimension related to fact at different granularity level.

Now one of the most important setting to set in the measure group representing the FactPlanSalary data (also remember to remove the count measure from this group as this will become the write back area for users and any thing apart from sum is not liked by the SSAS for write back partition to be created). And create the write back partition for the FactPlanSalary data.

This setting will avoid the replicated measure values repeating for date levels carried over from monthly levels. A common problem across many cubes caused by incorrect relationships.

And once all the above is done, time to get hands dirty by firing up the MDX studio to prepare the computed calculation for the cube which will accomplish all the magic.

Now the above caluation will pick the monthly value from the planned measure group and with the count of days in each month will divide the value to get the daily rate and spread out the data in the reporting area.

Results

Now to analyse the fruits of the efforts:

In the above screen shot we can see that the measure belonging to the FactPlanSalary is editable under the what-if scenario in excel.

And the major power of calcuations is relaized in the following screen shot where the value in the plan measure is picked and distributed across dates via the calculated measure.

Advantages

The major advantage of building such a planning model is that for the SSAS the writeback is achived via data insertion in the associated writeback table (delta).

And if the number of rows that are to be inserted are less in number for each write-back the faster and quicker the user experience will be with the model.

Now with the write back only enabled for monthly data the amount of rows being pushed in the database to achieve the writeback affects are substantially reduced hence providing a great user experience while planning.

So indeed write back can be little complex to achieve effectively (if granularity/grain of planning and reporting are different) for different business and in different scenarios, but when planned comprehensively utilizing all the features of SSAS at play. One can build really powerful planning models using SSAS.

Hope it helps !!!

Downloads

Note:
This article has been long and complex, and my write-up couldn’t have covered every single details of implementation. So in case of any issues please leave a comment or drop me a mail for clarification.