В этой статье я хотел бы описать основные этапы построения системы аналитической отчетности с использованием MS SQL Server 2008 R2 в организации, использующей ОЛТП системы учета на платформе 1С .
В статье описан мой первый опыт построения решений Business Intelligence. 
Общие входные данные
Компания, в которой я работаю, занимается оптовой торговлей и состоит примерно из 30 офисов, расположенных в регионах России.В каждом офисе имеется информационная база данных 1С, в которой фиксируются данные о продажах.
Организация использует два типа конфигурации Базы данных 1С.
Одна конфигурация используется в центральном офисе в Москве, вторая – в филиалах (в регионах России).
В качестве СУБД, обеспечивающей работу систем 1С, используется Microsoft SQL Server 2008 R2 (SP2) Standard Edition (64-бит).
Единой общей нормативно-справочной информации (ОГРИ) не существует. Справочник «Продукция» и некоторые другие справочники, являющиеся классификаторами продукции и контрагентов, синхронизируются по коду или другому идентификатору, которые хранятся в системах 1С.
Одним из основных отчетов, используемых в организации, является отчет о продажах.
Существующий отчет о продажах позволяет извлекать данные только из той системы, в которой он сформирован.
Сформированные отчеты выгружаются в MS Excel, где они подвергаются дальнейшей обработке.
В связи с ростом компании и появлением новых офисов руководство поставило перед ИТ-отделом задачу разработать сводный отчет, который позволил бы автоматически получать информацию о продажах по всем офисам организации.
Бизнес-требования
Основным бизнес-требованием было автоматическое формирование отчета о продажах для всех офисов компании.Кроме того, отчет должен содержать данные о количестве и сумме продаж в следующих аналитических разделах:
- Период (год, квартал, месяц, день).
- Продукты (включая атрибуты, классифицирующие продукты).
- Контрагенты (включая признаки, классифицирующие контрагентов).
В качестве фильтра можно указать произвольное количество значений.
Отчет должен формироваться не дольше минуты.
Формирование отчета не должно существенно влиять на работу учетных систем 1С.
Внедрение и дальнейшее сопровождение отчета должно быть минимально затратным.
Предварительная оценка и выбор решения
На основании имеющихся исходных данных и требований заказчику было предложено следующее решение:- Развивать хранилище данных , который включает всю информацию, необходимую для создания консолидированного отчета о продажах.
- Развертывание хранилища данных на экземпляре SQL-сервера Ядро базы данных SQL Server в центральном офисе.
- Разработайте многомерную модель данных, содержащую показатели и измерения, необходимые для создания отчета о продажах.
- Развертывание многомерной базы данных, содержащей многомерную модель, на экземпляре SQL-сервера.
СССАС в центральном офисе.
- Развивать ЭТЛ -пакеты ССИС , с помощью которого будут обновляться данные в хранилище данных и в многомерной базе данных.
- Разверните пакеты SSIS на экземпляре SQL-сервера SSIS в центральном офисе.
- Обеспечьте автоматическое выполнение пакетов SSIS с уведомлением по электронной почте специалистов технической поддержки о статусе выполнения пакетов.
- Предоставить сотрудникам компании доступ к многомерной базе данных для формирования сводного отчета о продажах с использованием объекта Сводная таблица Отчет в MS Excel.
- Проводить обучение сотрудников, участвующих в составлении отчетов о продажах.
Реализация решения
Тап №1. Сбор информации об источниках данных в системах 1С.
Создание представлений для доступа к необходимым данным Прежде чем приступить к проектированию хранилища, я создал Представления в базах данных SQL, поддерживающие работу систем 1С.
В итоге у меня получилось два набора представлений: набор для базы данных в центральном офисе (см.
рис.
1) и набор для баз данных в филиалах (рис.
2).
Напомню, что структура баз данных в филиалах организации одинакова, но отличается от структуры базы данных в центральном офисе.
Рис.
1. Представления в базе данных SQL центрального офиса.
Рис.
2. Представления в отраслевых базах данных SQL Состав справок в центральном офисе и филиалах оказался разным, поскольку часть основных данных является общей и полностью хранится в базе данных центрального офиса.
В частности, речь идет о представлениях:
- dbo.ChainStores (Клиентские торговые сети).
- dbo.Countries (Классификатор стран мира).
- dbo.Продукты (Продукты).
- dbo.ProductAnalogs (Аналоги продукта).
- dbo.ProductTypes (Классификатор типов продукции).
- dbo.Projects (Классификатор типов клиентов).
- dbo.ProjectsForProductMatrix(Классификатор типов продукции).
- dbo.CrossProductsAndProjectsForProductMatrix (представление, обеспечивающее связи «многие ко многим» между представлениями dbo.Products и dbo.ProjectsForProductMatrix).
Например, при изменении структуры таблиц в базах данных 1С нам не придется вносить изменения в ETL-пакеты; достаточно будет переделать виды.
Экран №2. Разработка структуры хранилища данных.
Развертывание хранилища данных Выполнив первый этап, мы легко можем получить информацию о составе и типах данных, хранящихся в источниках данных, и спроектировать структуру хранения.
Для этого просто посмотрите на типы столбцов представления.
Например, представление dbo.Clients выглядит так.
Рис.
3. Просмотр dbo.Clients Обратите внимание, что в представлении dbo.Clients есть поле ParentId. Используя это поле, мы позже сможем построить иерархию «родитель-потомок» в многомерной модели данных для измерения «Клиенты».
Аналогичное поле присутствует в представлениях dbo.Products и dbo.Managers. Прежде чем приступить к проектированию хранилища данных, необходимо определиться с его схемой.
Есть две схемы хранения данных – это звезда И снежинка .
Обе схемы имеют свои плюсы и минусы, и их сравнение выходит за рамки данной статьи.
Я выбрал схему «снежинка», основываясь на том факте, что при переходе на SQL Server 2012 и использовании самообслуживания BI в будущем пользователям, вероятно, будет удобнее использовать более нормализованные данные из хранилища данных при разработке собственных моделей данных в PowerPivot для Excel .
Структура разработанного мной хранилища данных показана на следующем рисунке.
Рис.
4. Структура хранилища данных Таблицы dim.DimDates(даты), dim.DimOffices(офисы), dim.DimRegions(регионы России) заполняются один раз и не требуют автоматического обновления.
Таблица dim.DimOffices содержит названия офисов компании.
Таблица dim.DimDates содержит информацию о дате для соответствующего измерения в многомерной модели данных.
Таблицы размеров содержат суррогатный ключ , выступающий в качестве первичного ключа.
Это связано с тем, что ключи записей в разных источниках данных могут перекрываться.
Кран №3. Разработка многомерной модели данных.
Развертывание многомерной базы данных При создании многомерной модели данных было создано представление Просмотр источника данных , в который вошли все таблицы из хранилища данных, кроме таблицы stage.FactSales. Эта таблица будет использоваться только для временного хранения данных о продажах перед загрузкой в таблица фактов факт.FactSales. Куб «Продажи» реализует две группы мер (см.
рис.
5).
Рис.
5. Меры Группа мер «Перекрестные продукты и проекты для матрицы продуктов» обеспечивает связь «многие ко многим» между измерениями «Продукты» и «Каналы продаж» для матрицы продуктов.
Список измерений показан на рисунке 6. 
Рис.
6. Измерения Для измерений Продукты, Клиенты, Менеджеры реализована иерархия Родитель-потомок.
Рис.
7. Измерение продукта Для управления доступом к многомерной базе данных создана роль «Аналитики», которой предоставлены права «Чтение» и «Детализация» куба «Продажи».
Права на детализацию позволяют пользователям получать расшифровку информации о том, как были рассчитаны значения ячеек в отчете.
Рис.
8. Роль аналитиков Для развертывания многомерной базы данных на сервере укажите в свойствах проекта имя экземпляра SSAS SQL-сервера, имя базы данных на сервере и в меню.
СТАВКИ нажмите «Развернуть».
Подключитесь к экземпляру SSAS, используя SMS и мы видим, что создана многомерная база данных.
Рис.
9. Многомерная база данных OLAP по продажам.
Тап №4. Разработка ETL-пакетов.
Развертывание ETL-пакетов.
Настройка автоматического выполнения ETL-пакетов Самым трудоемким этапом проектирования решений Business Intelligence является разработка ETL-пакетов.
Это связано с тем, что источники данных, как правило, имеют разную структуру, а хранящиеся в них данные содержат ошибки и имеют другой формат. Например, пол сотрудника в разных базах данных может быть представлен буквами М и Ж или цифрами 0 и 1, и перед загрузкой этих данных в хранилище их необходимо очистить и привести к общему виду.
Кроме того, в хранилище данных необходимо обновлять только те данные, которые были введены или изменены с момента последней загрузки.
Это лишь основные трудности; на самом деле их гораздо больше.
Однако благодаря инструментам SSIS большинство этих проблем можно решить.
В моей реализации данные в таблицах измерений обновляются полностью, т.е.
добавляются новые записи, а существующие записи перезаписываются.
Таблица фактов очищается и заполняется повторно в течение периода по умолчанию, равного трем месяцам.
Глубина обновления таблицы фактов в месяцах хранится в конфигурации пакета SSIS, которая представляет собой отдельную таблицу в хранилище данных.
Рис.
10. Пакеты SSIS На рисунке 10 показаны 4 пакета SSIS, назначение которых следующее:
- Обновление хранилища данных и процессов продаж OLAP.dtsx — это главный пакет, который реализует общую логику процесса ETL и запускает все остальные пакеты.
- Импорт измерений и фактов из Москвы.
dtsx - пакет для загрузки данных в таблицы измерений и фактов из базы данных центрального офиса в хранилище данных.
- Импорт измерений и фактов из Branches.dtsx — пакет для загрузки данных в таблицы измерений и фактов из баз данных филиалов в хранилище данных.
- Process Sales OLAP.dtsx — это пакет, выполняющий обновление (обработку) данных в многомерной базе данных.
рис.
11).
Рис.
11. Обновление пакета OLAP DW и Process Sales. Давайте рассмотрим каждый элемент этой схемы:
- Установка значений переменных пакета (задача «Выполнение SQL») выполняется первой.
Цель этого элемента — получить значения из конфигурации пакета и записать их в переменные пакета.
В конфигурации пакета также хранится информация о глубине обновления таблицы фактов в месяцах.
Конфигурация пакета хранится в отдельной таблице в хранилище базы данных и может быть изменена ИТ-специалистами.
- Далее «Вставка значений по умолчанию в измерения» (задача «Выполнение SQL») проверяет и заполняет хранилище данных пустыми элементами.
Например, в таблице dim.DimProducts после выполнения этой задачи должен появиться элемент с идентификатором (Id), равным нулю.
Записи с нулевыми идентификаторами будут созданы во всех таблицах измерений для обеспечения логической целостности данных, поскольку все поля таблицы фактов определены как NOT NULL и имеют значение по умолчанию, равное нулю.
Наличие NULL в таблице фактов приводит к ошибкам при обработке многомерной базы данных.
- Получить список исходных OLTP-баз данных в Москве (Execute SQL Task) — получает список баз данных центрального офиса (в моем случае такая база данных только одна, но для большей универсальности решения я предположил, что их может быть несколько).
Список баз данных хранится в таблице dim.DimOffices. В этой таблице также хранятся строки подключения к базам данных.
Полученный образец записывается в переменную пакета.
- Для всех баз данных OLTP в Москве (контейнер циклов по каждому элементу) проходит выборку, полученную на предыдущем шаге, и для каждой строки выборки (т. е.
для каждой базы данных) выполняет пакет «Импорт измерений и фактов из Москвы.
dtsx».
Параметры из главного пакета передаются вызываемому пакету путем установки значений конфигурации пакета, что выполняется задачей «Задание конфигураций пакета (выполнение задачи SQL)».
- Следующие два шага — «Получить список исходных баз данных OLTP в филиалах» (задача «Выполнение пакета») и «Для всех баз данных OLTP в филиалах» (контейнер цикла по каждому элементу) — аналогичны двум предыдущим, только они выполняются для баз данных филиалов.
- Последний этап OLAP процесса продаж (задача «Выполнение пакета») запускает пакет обновления данных в многомерной базе данных.
рис.
12).
Рис.
12. Задание SQL для запуска пакета SSIS Для контроля выполнения ETL-процесса в задаче настроено уведомление специалистов службы поддержки по электронной почте о завершении задачи (см.
рис.
13).
Рис.
13 Настройка уведомления о завершении задачи по электронной почте
Этап №5. Предоставление доступа к многомерной базе данных
Доступ к многомерной базе данных предоставляется сотрудникам организации путем добавления их доменных учетных записей к роли «Аналитики» многомерной базы данных с помощью SMS (см.рис.
14).
Рис.
14. Членство в качестве аналитиков
Экран №6. Обучение сотрудников организации
Для обучения пользователей был записан 15-минутный видеоролик, в котором были продемонстрированы возможности MS Excel, позволяющие подключиться к многомерной базе данных и построить отчет с помощью объекта «Отчет сводной таблицы».
Один из возможных вариантов отчета показан на рисунке 15. 
Рис.
15. Пример отчета сводной таблицы в Excel
выводы
Требования заказчика были полностью выполнены.Бета-тестирование проводили ключевые пользователи компании, которые ежедневно формируют отчеты о продажах.
В своем отчете ключевые пользователи описали созданное решение как очень удобное, быстрое и достаточное для проведения комплексного анализа продаж.
Для оценки решения приведем несколько цифр:
- На реализацию данного решения было потрачено 40 человеко-часов.
Все описанное делал один человек, то есть я.
Ранее я посещал курсы и успешно сдал экзамены Microsoft, получив сертификат Microsoft Certified Solutions Expert в области бизнес-аналитики.
- Таблица фактов в рабочей базе данных содержит около 40 миллионов строк.
- Процесс ETL занимает около 20 минут.
- Отчеты формируются в течение нескольких секунд.
-
Бром
19 Oct, 24 -
«Частное Решение Проблем Человечества»
19 Oct, 24 -
Собственный Динамический Dns
19 Oct, 24 -
Андроид 2.2, Он Же Фройо. Первые Впечатления
19 Oct, 24 -
Pptp Против L2Tp Через Маршрутизатор
19 Oct, 24 -
Странное Поведение Stmicroelectronics
19 Oct, 24
