- PVSM.RU - https://www.pvsm.ru -
Сегодня все большую популярность завоевывают In-Memory BI [1] решения. Кубы уже не в моде, их структура морально устарела, и хотя они довольно прилично масштабируются, требования к скорости работы современных BI систем значительно возросли. Тем не менее, многие компании до сих пор успешно используют аналитику, построенную на одном из OLAP-серверов [2] (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).
Посмотрим, что находится в нашей Write Back таблице в БД:
Распределение данных может быть более сложным, например: пользователь вводит данные на верхнем уровне иерархии, и данные распределяются на листовые элементы, используя коэффициенты, установленные в листовых элементах. Тут все будет зависеть от Вашей фантазии и поставленных задач.
Вот такой простой и главное привычный для простого пользователя инструмент бюджетирования. Легко использовать и быстро настраивать. Отвечу на все вопросы – пишите!
Ссылка на используемые файлы [3].
Автор: AntLogist
Источник [4]
Сайт-источник PVSM.RU: https://www.pvsm.ru
Путь до страницы источника: https://www.pvsm.ru/sql/46030
Ссылки в тексте:
[1] In-Memory BI: http://en.wikipedia.org/wiki/In-Memory_Processing
[2] OLAP-серверов: http://ru.wikipedia.org/wiki/%D0%A1%D1%80%D0%B0%D0%B2%D0%BD%D0%B5%D0%BD%D0%B8%D0%B5_OLAP-%D1%81%D0%B5%D1%80%D0%B2%D0%B5%D1%80%D0%BE%D0%B2
[3] Ссылка на используемые файлы: https://www.dropbox.com/sh/m14fu7joosq2u01/s7ywGALsDe
[4] Источник: http://habrahabr.ru/post/198098/
Нажмите здесь для печати.