Возможности обратной записи (Write Back) в кубах MS SQL Server Analysis Service

Сегодня все большую популярность завоевывают In-Memory BI решения. Кубы уже не в моде, их структура морально устарела, и хотя они довольно прилично масштабируются, требования к скорости работы современных BI систем значительно возросли. Тем не менее, многие компании до сих пор успешно используют аналитику, построенную на одном из OLAP-серверов (Microsoft, Oracle, Cognos, и др.). Мне, например, очень нравится Microsoft SQL Server Analysis Service, и я хотел бы рассказать, как в нем можно использовать немного необычную для аналитики функцию – обратную запись данных в источник (Write Back).

Постановка задачи.

Необходимо вносить план продаж на год в разрезе месяца, товара и региона. При этом, необходимо видеть фактические данные текущего года и посчитанный тренд на следующий год, который менеджер и будет менять. Инструмент ввода данных должен быть удобным, он должен позволять накладывать фильтры и смотреть итоги по разным группировкам (в целом по регионам, товарам и периоду) и еще не требовать от пользователя много времени на свое изучение.

Решение.

Будем использовать MS SQL Server 2008 R2 (Database Engine и Analysis Service) + Excel 2010. Создаем базу данных, несколько таблиц и заполняем их данными.

CREATE DATABASE [MyDB] ON  PRIMARY 
( NAME = N'MyDB', FILENAME = N'C:MyDB.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'MyDB_log', FILENAME = N'C:MyDB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
USE MyDB
GO
CREATE TABLE dbo.Dates (DayDate DATETIME, Month_Num SMALLINT, Month_Name VARCHAR(50), Year_Num SMALLINT, PRIMARY KEY (DayDate))
GO
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)
GO
CREATE TABLE dbo.Regions (Region_Id INT, Region_Name VARCHAR(250), PRIMARY KEY (Region_Id))
GO
INSERT INTO dbo.Regions (Region_Id, Region_Name)
VALUES (1, 'Центральный'), (2, 'Северный'), (3, 'Южный'), (4, 'Западный'), (5, 'Восточный')
GO
CREATE TABLE dbo.SKU (SKU_Id INT, SKU_Name VARCHAR(250), PRIMARY KEY (SKU_Id))
GO
INSERT INTO dbo.SKU (SKU_Id, SKU_Name)
VALUES (1, 'Вино'), (2, 'Водка'), (3, 'Пиво'), (4, 'Коньяк'), (5, 'Виски'), 
 (6, 'Ликер'), (7, 'Ром'), (8, 'Шампанское'), (9, 'Текила'), (10, 'Абсент')
GO
CREATE TABLE dbo.Plan_Sale (DayDate DATETIME, Region_Id INT, SKU_Id INT, Sum_Sale MONEY, PRIMARY KEY (DayDate, Region_Id, SKU_Id))
GO

Создаем куб.

Активируем для партиции Write Back, указываем источник данных и таблицу. Обрабатываем куб.

Вот так таблица будет выглядеть в БД:

Ничего не меняйте в ее структуре – может поломаться обратная запись. В эту таблицу будут попадать изменения, корректирующие начальное значение. Для получения реального значения, данные из этой таблицы нужно сложить с данными исходной таблицы.

В качестве клиента используем Excel. До Excel версии 14.0 приходилось записывать данные с помощью макроса, или в версии 13.0 — надстройки (AddIn). Начиная с Excel 2010 возможность редактировать данные стала частью функционала. Поддерживается два режима записи данных: временная обратная запись (temporary writeback), также известная как Анализ Что Если (What If Analysis), и постоянная обратная запись (permanent writeback).

1. Подключите источник данных. В сводной таблице включите эти параметры
   
2. Находясь внутри сводной таблицы, которая подключена к кубу, нажмите вкладку Работа со сводными таблицами
   
3. Найдите группу Анализ «что если»
   
4. В этой группе есть элемент меню Включить анализ «что если». При его включении появится возможность вносить данные в область данных сводной таблицы.
5. Вносимые Вами данные будут выделяться маркером в нижнем правом углу ячейки. Эти данные пока никуда не попадают и не влияют на остальные показатели в кубе.
   
6. 6. После внесения порции данных, в меню группы Анализ «что если» выберите Учет изменений при расчете сводной таблицы – все поля, которые зависят от вносимых Вами значений, обновятся (можно включить Автоматически учесть изменения при расчете сводной таблицы, тогда после каждого вносимого значения будет происходить обновление данных). Этот пункт можно пропускать и сразу публиковать изменения (п.6).
   
7. После того, как Вы учли изменения, нужно нажать Опубликовать изменения, иначе данные не попадут в таблицы постоянного хранения и будут потеряны. После публикации изменения, маркер в нижнем правом углу ячейки исчезнет, данные будут сохранены в базе данных.

Посмотрим, что находится в нашей Write Back таблице в БД:

Заключение

Распределение данных может быть более сложным, например: пользователь вводит данные на верхнем уровне иерархии, и данные распределяются на листовые элементы, используя коэффициенты, установленные в листовых элементах. Тут все будет зависеть от Вашей фантазии и поставленных задач.
Вот такой простой и главное привычный для простого пользователя инструмент бюджетирования. Легко использовать и быстро настраивать. Отвечу на все вопросы – пишите!
Ссылка на используемые файлы.

Автор: AntLogist

Источник