- PVSM.RU - https://www.pvsm.ru -
В этой статье я бы хотел описать основные этапы построение системы аналитической отчетности средствами MS SQL Server 2008 R2 в организации, использующей OLTP [1] системы учета на платформе 1С [2]. В статье описан мой первый опыт построения решений Business Intelligence.
Компания, в которой я работаю, занимается оптовой торговлей и состоит приблизительно из 30 офисов, расположенных в регионах России. В каждом офисе существует информационная база данных 1С, в которой регистрируются данные о продажах. В организации используется два вида конфигураций [3] баз данных 1С. Одна конфигурация используется в центральном офисе в Москве, вторая — в филиалах (в регионах России). В качестве СУБД, обеспечивающей работу систем 1С, используется Microsoft SQL Server 2008 R2 (SP2) Standard Edition (64-bit). Единая общая нормативно-справочная информация (НСИ) отсутствует. Справочник «Продукция» и некоторые другие справочники, являющиеся классификаторами продукции и контрагентов, синхронизируются по коду или другому идентификатору, которые хранятся в системах 1С. Одним из основных отчетов, используемых в организации, является отчет о продажах. Существующий отчет о продажах позволяет извлекать данные только из той системы, в которой он формируется. Сформированные отчеты выгружаются в MS Excel, где происходит их дальнейшая обработка. В связи с ростом компании и появлением новых офисов руководство поставило перед IT-подразделением задачу о разработке консолидированного отчета, позволяющего автоматически получать информацию о продажах в разрезе всех офисов организации.
Основным требованием бизнеса было автоматическое формирование отчета о продажах по всем офисам компании. Кроме этого, в отчете должны быть данные о количестве и сумме продаж в следующих аналитических разрезах:
Отчет должен позволять накладывать фильтры на выборку по любому из аналитических разрезов. В качестве фильтра может быть задано произвольное количество значений. Отчет должен формироваться не дольше минуты. Формирование отчета не должно существенно влиять на производительность учетных систем 1С. Реализация и дальнейшее сопровождение отчета должны быть минимально затратными.
На основании имеющихся вводных данных и требований заказчику было предложено следующее решение:
Перед началом проектирования хранилища я создал представления (View) в базах данных SQL, обеспечивающих работу систем 1С. У меня получилось два набора представлений: набор для базы данных в центральном офисе (см. рис. 1) и набор для баз данных в филиалах (рис. 2). Напомню, что структура баз данных в филиалах организации одинаковая, но отличается от структуры базы данных в центральном офисе.
Рис. 1. Представления в SQL-базе данных центрального офиса
Рис. 2. Представления в SQL-базах данных филиалов
Состав представлений в центральном офисе и филиалах получился разный, так как часть НСИ является общей и хранится в полном объеме в базе данных в центральном офисе. В частности речь идет о представлениях:
Создание представлений в SQL-базах данных позволяет сделать решение более универсальным. Например, при изменении структуры таблиц в базах данных 1С нам не придется вносить изменения в ETL-пакеты, достаточно будет переделать представления.
Завершив первый этап, мы можем с легкостью получить информацию о составе и типах данных, хранящихся в источниках данных, и спроектировать структуру хранилища. Для этого достаточно взглянуть на типы колонок представлений. Например, представление dbo.Clients выглядит следующим образом.
Рис. 3. Представление dbo.Clients
Обратите внимание, что в представлении dbo.Clients существует поле ParentId. С помощью этого поля в последствии мы сможем построить иерархию Parent-child в многомерной модели данных для измерения «Клиенты». Аналогичное поле присутствует в представлениях dbo.Products и dbo.Managers.
Прежде чем начать проектировать хранилище данных, необходимо определиться с его схемой. Существует две схемы хранилища данных — это звезда [10] и снежинка [11]. Обе схемы имеют свои плюсы и минусы, и их сравнение выходит за пределы данной статьи. Я выбрал схему снежинка, руководствуясь тем, что при переходе на SQL Server 2012 и использовании в будущем self-service BI пользователям, вероятно, будет удобнее оперировать более нормализованными данными из хранилища данных при разработке собственных моделей данных в PowerPivot for Excel [12]. Структура разработанного мной хранилища данных изображена на следующем рисунке.
Рис. 4. Структура хранилища данных
Таблицы dim.DimDates (даты), dim.DimOffices (офисы), dim.DimRegions (регионы России) были заполнены один раз и не предполагают автоматического обновления. Таблица dim.DimOffices содержит наименования офисов компании. Таблица dim.DimDates содержит сведения о датах для соответствующего измерения в многомерной модели данных. В таблицах измерений содержится суррогатный ключ [13], выполняющий роль первичного ключа. Это связано с тем, что ключи записей в различных источниках данных могут пересекаться.
При создании многомерной модели данных было создано представление Data Source View [14], в которое были включены все таблицы из хранилища данных, кроме таблицы stage.FactSales. Эта таблица будет использоваться только для временного хранения данных о продажах перед загрузкой в таблицу фактов [15] fact.FactSales.
В кубе Sales реализованы две группы мер (см. рис. 5).
Рис. 5. Меры
Группа мер Cross Products And Projects For Product Matrix обеспечивает связь много-ко-многим между измерениями Товары и Каналы сбыта для товарной матрицы.
Список измерений изображен на рисунке 6.
Рис. 6. Измерения
Для измерений Товары, Клиенты, Менеджеры реализована иерархия Parent-child.
Рис. 7. Измерение Товары
Для управления доступом к многомерной базе данных создана роль Analists, которой предоставлены права Read и Drillthrough для куба Sales. Права Drillthrough позволяют пользователям получать расшифровку с информацией о том, как были рассчитаны значения ячеек в отчете.
Рис. 8. Роль Analists
Чтобы развернуть многомерную базу данных на сервере, указываем в свойствах проекта имя экземпляра SQL-сервера SSAS, имя базы данных на сервере и в меню BIDS [16] нажимаем Deploy. Подключаемся к экземпляру SSAS с помощью SMS [17] и видим, что многомерная база данных была создана.
Рис. 9. Многомерная база данных Sales OLAP
Наиболее трудоемкий этап при проектировании решений Business Intelligence — это, разработка ETL-пакетов. Связано это с тем, что источники данных, как правило, имеют разную структуру, а данные, хранящиеся в них, содержат ошибки и имеют различный формат. Например, пол сотрудника в разных базах данных, может быть представлен буквами М и Ж или цифрами 0 и 1, и перед загрузкой этих данных в хранилище, необходимо выполнить их очистку и приведение к общему виду. Кроме того, в хранилище данных необходимо обновлять только те данные, которые были введены или изменены с момента последней загрузки. Это только основные сложности, на самом деле их гораздо больше. Однако благодаря инструментам SSIS большинство подобных проблем могут быть решены. В моей реализации данные в таблицах измерений обновляются полностью, т.е. новые записи добавляются, а существующие записи перезаписываются. Таблица фактов очищается и заполняется снова за период по умолчанию равный трем месяцам. Глубина обновления таблицы фактов в месяцах хранится в конфигурации SSIS пакетов, которая представляет из себя отдельную таблицу в хранилище данных.
Рис. 10. Пакеты SSIS
На рисунке 10 изображены 4 пакета SSIS, назначение которых следующее:
Логика (Control Flow) мастер-пакета следующая (см. рис. 11).
Рис. 11. Пакет Update DW and Process Sales OLAP
Рассмотрим каждый элемент этой схемы:
Описанные выше пакеты развернуты на экземпляре SQL-сервера SSIS. Для автоматического запуска мастер-пакета на SQL-сервере создано задание Update DW and Process Sales OLAP (см. рис. 12).
Рис. 12. SQL Job для запуска пакета SSIS
Для контроля выполнения ETL-процесса в задании настроено уведомление специалистов службы поддержки по e-mail о завершении задания (см. рис. 13).
Рис. 13 Настройка уведомления о выполнении задания по e-mail
Доступ к многомерной базе данных предоставлен сотрудникам организации с помощью включения их доменных учетных записей в роль Analists многомерной базы данных с помощью SMS (см. рис. 14).
Рис. 14. Членство в роли Analists
Для обучение пользователей был записан 15 минутный видео-ролик, в котором были продемонстрированы возможности MS Excel, позволяющие подключиться к многомерной базе данных и построить отчет с помощью объекта PivotTable Report. Один из возможных вариантов отчета изображен на рисунке 15.
Рис. 15. Пример отчета PivotTable Report в Excel
Требования заказчика были реализованы в полной мере. Бета-тестирование выполнялось ключевыми пользователями компании, ежедневно формирующими отчеты о продажах. В своем отчете ключевые пользователи охарактеризовали созданное решение как очень удобное, быстрое и достаточное для проведение всестороннего анализа продаж. Для оценки решения привожу некоторые цифры:
Автор: Query
Источник [18]
Сайт-источник PVSM.RU: https://www.pvsm.ru
Путь до страницы источника: https://www.pvsm.ru/news/54832
Ссылки в тексте:
[1] OLTP: http://ru.wikipedia.org/wiki/OLTP
[2] 1С: http://1c.ru/
[3] конфигураций: http://ru.wikipedia.org/wiki/1%D0%A1:%D0%9F%D1%80%D0%B5%D0%B4%D0%BF%D1%80%D0%B8%D1%8F%D1%82%D0%B8%D0%B5
[4] хранилище данных: http://ru.wikipedia.org/wiki/%D0%A5%D1%80%D0%B0%D0%BD%D0%B8%D0%BB%D0%B8%D1%89%D0%B5_%D0%B4%D0%B0%D0%BD%D0%BD%D1%8B%D1%85
[5] SQL Server Database Engine: http://msdn.microsoft.com/en-us/library/ms144296.aspx
[6] SSAS: http://ru.wikipedia.org/wiki/Microsoft_Analysis_Services
[7] ETL: http://ru.wikipedia.org/wiki/ETL
[8] SSIS: http://en.wikipedia.org/wiki/SQL_Server_Integration_Services
[9] PivotTable: http://en.wikipedia.org/wiki/Pivot_table
[10] звезда: http://ru.wikipedia.org/wiki/%D0%A1%D1%85%D0%B5%D0%BC%D0%B0_%D0%B7%D0%B2%D0%B5%D0%B7%D0%B4%D1%8B
[11] снежинка: http://ru.wikipedia.org/wiki/%D0%A1%D1%85%D0%B5%D0%BC%D0%B0_%D1%81%D0%BD%D0%B5%D0%B6%D0%B8%D0%BD%D0%BA%D0%B8
[12] PowerPivot for Excel: http://msdn.microsoft.com/en-us/library/ee210644.aspx
[13] суррогатный ключ: http://ru.wikipedia.org/wiki/%D0%A1%D1%83%D1%80%D1%80%D0%BE%D0%B3%D0%B0%D1%82%D0%BD%D1%8B%D0%B9_%D0%BA%D0%BB%D1%8E%D1%87
[14] Data Source View: http://technet.microsoft.com/en-us/library/ms175364(v=sql.105).aspx
[15] таблицу фактов: http://ru.wikipedia.org/wiki/%D0%A2%D0%B0%D0%B1%D0%BB%D0%B8%D1%86%D0%B0_%D1%84%D0%B0%D0%BA%D1%82%D0%BE%D0%B2
[16] BIDS: http://en.wikipedia.org/wiki/Business_Intelligence_Development_Studio
[17] SMS: http://ru.wikipedia.org/wiki/SQL_Server_Management_Studio
[18] Источник: http://habrahabr.ru/post/212349/
Нажмите здесь для печати.