Write Back Features in MS SQL Server Analysis Service Cubes

Today, In-Memory BI solutions are gaining in popularity . 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 still successfully use analytics built on one of the OLAP servers (Microsoft, Oracle, Cognos, etc.). For example, I really like the Microsoft SQL Server Analysis Service, and I would like to tell you how it can use a slightly unusual function for analytics - writing data back to the source (Write Back).

Formulation of the problem.

You need 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 apply filters and view the results for different groups (in general, by region, product and period) and not yet require a lot of time for the user to study.


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

Script to create a database and populate tables
( NAME = N'MyDB', FILENAME = N'C:\MyDB.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
( NAME = N'MyDB_log', FILENAME = N'C:\MyDB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
CREATE TABLE dbo.Dates (DayDate DATETIME, Month_Num SMALLINT, Month_Name VARCHAR(50), Year_Num SMALLINT, PRIMARY KEY (DayDate))
INSERT INTO dbo.Dates (DayDate, Month_Num, Month_Name, Year_Num)
VALUES ('20130101', 1, 'Январь', 2013), ('20130201', 2, 'Февраль', 2013), ('20130301', 3, 'Март', 2013),
 ('20130401', 4, 'Апрель', 2013), ('20130501', 5, 'Май', 2013), ('20130601', 6, 'Июнь', 2013),
 ('20130701', 7, 'Июль', 2013), ('20130801', 8, 'Август', 2013), ('20130901', 9, 'Сентябрь', 2013),
 ('20131001', 10, 'Октябрь', 2013), ('20131101', 11, 'Ноябрь', 2013), ('20131201', 12, 'Декабрь', 2013)
CREATE TABLE dbo.Regions (Region_Id INT, Region_Name VARCHAR(250), PRIMARY KEY (Region_Id))
INSERT INTO dbo.Regions (Region_Id, Region_Name)
VALUES (1, 'Центральный'), (2, 'Северный'), (3, 'Южный'), (4, 'Западный'), (5, 'Восточный')
VALUES (1, 'Вино'), (2, 'Водка'), (3, 'Пиво'), (4, 'Коньяк'), (5, 'Виски'), 
 (6, 'Ликер'), (7, 'Ром'), (8, 'Шампанское'), (9, 'Текила'), (10, 'Абсент')
CREATE TABLE dbo.Plan_Sale (DayDate DATETIME, Region_Id INT, SKU_Id INT, Sum_Sale MONEY, PRIMARY KEY (DayDate, Region_Id, SKU_Id))

Create a cube.

We activate Write Back for the partition, specify the data source and the table. We process a cube.

This is how the table will look in the database:

Do not change anything in its structure - the reverse record may break. 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 of the original table.

We use Excel as a client. Prior to Excel version 14.0, you had to write data using a macro, or in version 13.0, add-ons. Starting with Excel 2010, the ability to edit data has become part of the functionality. Two data recording 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 “what if” analysis menu item . When it is turned on, 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 and do not affect other indicators in the cube.

  6. 6. After making a portion of the data, in the “what if” analysis menu , select Accounting for changes when calculating the pivot table - all fields that depend on the values ​​you make will be updated (you can enable Automatically take into account the changes when calculating the pivot table , then after each value data will be updated). You can skip this item and immediately publish the changes (item 6).

  7. After you take into account the changes, you need to click Post 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 will disappear, the data will be saved in the database.

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


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 to the sheet elements using the coefficients set in the sheet elements. Everything will depend on your imagination and tasks.
Here is such a simple and most important budgeting tool familiar to a simple user. Easy to use and quick to set up. I will answer all questions - write!
Link to the files used .

Also popular now: