Write Back in MS SQL Server Analysis Service cubes

Today, In-Memory BI solutions are becoming increasingly popular. Cubes are no longer in vogue, their structure is morally obsolete, and although they scale pretty well, the requirements for the speed of modern BI systems have increased significantly. Nevertheless, many companies are still successfully using analytics built on one of the OLAP servers (Microsoft, Oracle, Cognos, and others). For example, I really like the Microsoft SQL Server Analysis Service, and I would like to tell you how you can use a function that is a bit unusual for analytics in it - write back to the source (Write Back).

Formulation of the problem.

It is necessary to make a sales plan for the year by month, product and region. At the same time, it is necessary to see the actual data of the current year and the calculated trend for the next year, which the manager will change. The data entry tool should be convenient, it should allow you to overlay filters and look at the results for different groupings (in general, by region, product and period) and still not require a lot of time from the user to study.

Decision.

We will use MS SQL Server 2008 R2 (Database Engine and Analysis Service) + Excel 2010. Create a database, several tables and fill them with data.
')




Create a cube.

---

Activate Write Back for the partition, specify the data source and the table. Process the cube.




This is how the table will look in the database:


Do not change anything in its structure - the writeback may break. The changes correcting the initial value will fall into this table. To get the real value, the data from this table must be added to the data from the original table.

As a client, we use Excel. Prior to Excel version 14.0, you had to write data using a macro, or in version 13.0, add-in (AddIn). Starting with Excel 2010, the ability to edit data has become part of the functionality. Two data writing modes are supported: temporary writeback, also known as What If Analysis, and permanent writeback.

1. Connect the data source. In the pivot table, enable these options.
   
   
2. From inside the pivot table that is connected to the cube, click the Work with Pivot Tables tab.
   
   
3. Find the what-if analysis group .
   
   
4. This group has an Enable what-if analysis menu item. When enabled, it will be possible to enter data into the data area of ​​the pivot table.
   
5. The data you enter will be highlighted with a marker in the lower right corner of the cell. These data do not get anywhere yet and do not affect the other indicators in the cube.
   
   
6. 6. After making a portion of the data, in the “what if” analysis group menu , select Accounting changes when calculating the pivot table — all fields that depend on the values ​​you enter will be updated (you can turn on the changes automatically when calculating the pivot table , then after each input value data will be updated). This item can be skipped and immediately publish changes (p.6).
   
   
7. After you have taken into account the changes, you must click Publish changes , otherwise the data will not fall into the permanent storage tables and will be lost . After the publication of the change, the marker in the lower right corner of the cell disappears, the data will be saved in the database.
   

Let's see what is in our Write Back table in the database:

Conclusion

The distribution of data can be more complex, for example: the user enters data at the top level of the hierarchy, and the data is distributed into leaf elements using the coefficients set in the leaf elements. It will all depend on your imagination and tasks.
This is such a simple and most importantly common budgeting tool for a simple user. Easy to use and quick to set up. I will answer all questions - write!
Link to used files .