OLAP-куб продаж на SSAS 2005: Excel вместо выгрузок

Дистрибьюторская компания с несколькими десятками менеджеров по продажам. У каждого - свой регион, своя категория товаров, свои планы. Каждый понедельник финансист вручную собирает отчёты из 1С, сводит их в Excel и рассылает по почте. К среде данные уже неактуальные, а к пятнице появляется следующий запрос с новым срезом.

Нас попросили это починить.

Что именно болело

Задержка данных. Актуальная картина была только у финансиста в голове и в его файлах - и то вчерашняя. Менеджеры работали вслепую или теребили его звонками.

Запросы на нестандартные срезы. «А покажи мне топ-10 позиций по маржинальности в Поволжье за последние три месяца, но только по активным клиентам» - это ещё несколько часов ручной работы.

Размножение версий. Три разных Excel-файла с тремя разными цифрами за один и тот же месяц - классика, которую все узнают.

Решение напрашивалось: один источник правды, самообслуживание для менеджеров. Мы предложили OLAP-куб на SQL Server 2005 Analysis Services.

Архитектура в двух словах

Данные уже лежали в SQL Server - мы подключились к тем же базам, что обслуживала 1С. Вытаскивать прямо из транзакционной базы в куб - плохая идея: нагрузка непредсказуемая, и структура 1С-таблиц не предназначена для аналитических запросов. Поэтому сначала - промежуточный слой.

Ночью по расписанию SSIS-пакет (SQL Server Integration Services) вытягивает данные, чистит и кладёт в плоскую аналитическую схему - отдельная БД, звёздочка: факты продаж, измерения регионов, менеджеров, категорий товаров, клиентов, времени. Про инкрементальную загрузку мы уже писали раньше - здесь применили тот же подход с дельта-ключом по дате проводки.

Поверх этой схемы - куб SSAS. Три основных измерения:

• Регион - иерархия «округ - регион - город»
• Менеджер - плоский список с привязкой к региону
• Категория товаров - иерархия «группа - категория - SKU»

Меры: выручка, себестоимость, маржа, количество документов, количество уникальных клиентов. Всё считается агрегатами при обработке куба, поэтому запросы на уровне «итого по кварталу» отвечают мгновенно.

Что получили менеджеры

Excel 2007 умеет подключаться к Analysis Services напрямую через Data Connection Wizard. Один раз настроили шаблон с Pivot Table, указали строку подключения к SSAS, сохранили файл. Дальше менеджер открывает его, обновляет - и видит вчерашние данные (куб пересчитывается ночью).

Дальше - сам: тащит измерения в строки/столбцы, фильтрует по региону, проваливается в детали, делает срез по кварталу. Никаких запросов в ИТ, никаких ожиданий. Первые два часа у некоторых ушли на то, чтобы осознать, что они могут это делать сами, и убедиться, что цифры сходятся с тем, что раньше присылал финансист.

Финансист, к слову, выдохнул.

Что пошло не по плану

Качество данных в 1С. Это отдельная история. Часть документов задним числом, часть с некорректными кодами регионов, часть без привязки к менеджеру. Пришлось добавить слой очистки в ETL и согласовать с клиентом правила: «если менеджер не указан - считать как неатрибутированная продажа». Без этого соглашения цифры куба не сходились бы с цифрами 1С, и доверие к системе умерло бы на старте.

Время обработки куба. С первым вариантом разбиения агрегатов куб пересчитывался около двух часов. Для ночного окна - терпимо, но на нервах. Поправили настройки агрегаций в SSAS и партиционирование по месяцам - ужалось в несколько раз.

Пользователи хотят большего. Примерно через две недели после запуска пошли запросы: «а можно добавить план vs факт?», «а можно дебиторку?», «а можно прогноз?». Это хороший знак - значит, инструментом пользуются. Но и сигнал, что нужно управлять scope: всё это требует новых источников, новой ETL-логики и отдельного разговора о сроках.

Наблюдение напоследок

SSAS - не самый простой продукт в настройке. MDX-запросы, которые SSAS генерирует под капотом Pivot Table, выглядят пугающе, если открыть SQL Profiler. Но именно это скрыто от менеджера - он просто перетаскивает поля мышкой.

Ключевое условие, чтобы это работало: хорошая аналитическая схема под кубом. Если данные в источнике грязные или структура промежуточного слоя была спроектирована наспех - куб это честно отразит. Мусор на входе, мусор на выходе, только теперь в красивом Pivot Table.

Для компаний, которые уже сидят на SQL Server, SSAS - логичное расширение: лицензия входит в Enterprise Edition, отдельно покупать ничего не нужно. SQL Server 2005 SP2 закрыл ряд проблем стабильности SSAS, которые были в RTM - обновляться стоит до старта проекта, не после.