(495) 925-0049, ITShop интернет-магазин 229-0436, Учебный Центр 925-0049
  Главная страница Карта сайта Контакты
Поиск
Вход
Регистрация
Рассылки сайта
 
 
 
 
 

Business Intelligence средствами MS SQL Server 2008 R2 в компании, использующей системы учета 1С

Источник: habrahabr
Query

В этой статье я бы хотел описать основные этапы построение системы аналитической отчетности средствами MS SQL Server 2008 R2 в организации, использующей OLTP системы учета на платформе 1С. В статье описан мой первый опыт построения решений Business Intelligence.

image

Общие вводные данные

Компания, в которой я работаю, занимается оптовой торговлей и состоит приблизительно из 30 офисов, расположенных в регионах России. В каждом офисе существует информационная база данных 1С, в которой регистрируются данные о продажах. В организации используется два вида конфигураций баз данных 1С. Одна конфигурация используется в центральном офисе в Москве, вторая - в филиалах (в регионах России). В качестве СУБД, обеспечивающей работу систем 1С, используется Microsoft SQL Server 2008 R2 (SP2) Standard Edition (64-bit). Единая общая нормативно-справочная информация (НСИ) отсутствует. Справочник "Продукция" и некоторые другие справочники, являющиеся классификаторами продукции и контрагентов, синхронизируются по коду или другому идентификатору, которые хранятся в системах 1С. Одним из основных отчетов, используемых в организации, является отчет о продажах. Существующий отчет о продажах позволяет извлекать данные только из той системы, в которой он формируется. Сформированные отчеты выгружаются в MS Excel, где происходит их дальнейшая обработка. В связи с ростом компании и появлением новых офисов руководство поставило перед IT-подразделением задачу о разработке консолидированного отчета, позволяющего автоматически получать информацию о продажах в разрезе всех офисов организации.

Требования бизнеса

Основным требованием бизнеса было автоматическое формирование отчета о продажах по всем офисам компании. Кроме этого, в отчете должны быть данные о количестве и сумме продаж в следующих аналитических разрезах:

  • Период (год, квартал, месяц, день).
  • Продукция (включая атрибуты, классифицирующие продукцию).
  • Контрагенты (включая атрибуты, классифицирующие контрагентов).

Отчет должен позволять накладывать фильтры на выборку по любому из аналитических разрезов. В качестве фильтра может быть задано произвольное количество значений. Отчет должен формироваться не дольше минуты. Формирование отчета не должно существенно влиять на производительность учетных систем 1С. Реализация и дальнейшее сопровождение отчета должны быть минимально затратными.

Предварительная оценка и выбор решения

На основании имеющихся вводных данных и требований заказчику было предложено следующее решение:

  • Разработать хранилище данных, включающее всю информацию, необходимую для формирования консолидированного отчета о продажах.
  • Развернуть хранилище данных на экземпляре SQL-севера SQL Server Database Engine в центральном офисе.
  • Разработать многомерную модель данных, содержащую меры и измерения, необходимые для формирования отчета о продажах.
  • Развернуть многомерную базу данных, содержащую многомерную модель, на экземпляре SQL-севера SSAS в центральном офисе.
  • Разработать ETL-пакеты SSIS, с помощью которых будет производиться обновление данных в хранилище данных и в многомерной базе данных.
  • Развернуть пакеты SSIS на экземпляре SQL-сервера SSIS в центральном офисе.
  • Обеспечить автоматическое выполнение пакетов SSIS с уведомлением по e-mail специалистов технической поддержки о статусе выполнения пакетов.
  • Обеспечить доступ сотрудникам компании к многомерной базе данных для формирования консолидированного отчета о продажах с помощью объекта PivotTable Report в MS Excel.
  • Выполнить обучение сотрудников, занимающихся формированием отчетов о продажах.

Реализация решения

Этап №1. Сбор информации об источниках данных в системах 1С. Создание представлений (View) для получения доступа к необходимым данным

Перед началом проектирования хранилища я создал представления (View) в базах данных SQL, обеспечивающих работу систем 1С. У меня получилось два набора представлений: набор для базы данных в центральном офисе (см. рис. 1) и набор для баз данных в филиалах (рис. 2). Напомню, что структура баз данных в филиалах организации одинаковая, но отличается от структуры базы данных в центральном офисе.

image
Рис. 1. Представления в SQL-базе данных центрального офиса

image
Рис. 2. Представления в SQL-базах данных филиалов

Состав представлений в центральном офисе и филиалах получился разный, так как часть НСИ является общей и хранится в полном объеме в базе данных в центральном офисе. В частности речь идет о представлениях:

  • dbo.ChainStores (Торговые сети клиентов).
  • dbo.Countries (Классификатор стран мира).
  • dbo.Products (Продукция).
  • dbo.ProductAnalogs (Аналоги продукции).
  • dbo.ProductTypes (Классификатор типов продукции).
  • dbo.Projects (Классификатор видов клиентов).
  • dbo.ProjectsForProductMatrix (Классификатор видов продукции).
  • dbo.CrossProductsAndProjectsForProductMatrix (представление для обеспечения связи типа "много-ко-многим" между представлениями dbo.Products и dbo.ProjectsForProductMatrix).

Создание представлений в SQL-базах данных позволяет сделать решение более универсальным. Например, при изменении структуры таблиц в базах данных 1С нам не придется вносить изменения в ETL-пакеты, достаточно будет переделать представления.

Этап №2. Разработка структуры хранилища данных. Развертывание хранилища данных

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

image
Рис. 3. Представление dbo.Clients

Обратите внимание, что в представлении dbo.Clients существует поле ParentId. С помощью этого поля в последствии мы сможем построить иерархию Parent-child в многомерной модели данных для измерения "Клиенты". Аналогичное поле присутствует в представлениях dbo.Products и dbo.Managers.

Прежде чем начать проектировать хранилище данных, необходимо определиться с его схемой. Существует две схемы хранилища данных - это звезда и снежинка. Обе схемы имеют свои плюсы и минусы, и их сравнение выходит за пределы данной статьи. Я выбрал схему снежинка, руководствуясь тем, что при переходе на SQL Server 2012 и использовании в будущем self-service BI пользователям, вероятно, будет удобнее оперировать более нормализованными данными из хранилища данных при разработке собственных моделей данных в PowerPivot for Excel. Структура разработанного мной хранилища данных изображена на следующем рисунке.

image
Рис. 4. Структура хранилища данных

Таблицы dim.DimDates (даты), dim.DimOffices (офисы), dim.DimRegions (регионы России) были заполнены один раз и не предполагают автоматического обновления. Таблица dim.DimOffices содержит наименования офисов компании. Таблица dim.DimDates содержит сведения о датах для соответствующего измерения в многомерной модели данных. В таблицах измерений содержится суррогатный ключ, выполняющий роль первичного ключа. Это связано с тем, что ключи записей в различных источниках данных могут пересекаться.

Этап №3. Разработка многомерной модели данных. Развертывание многомерной базы данных

При создании многомерной модели данных было создано представление Data Source View, в которое были включены все таблицы из хранилища данных, кроме таблицы stage.FactSales. Эта таблица будет использоваться только для временного хранения данных о продажах перед загрузкой в таблицу фактов fact.FactSales.

В кубе Sales реализованы две группы мер (см. рис. 5).

image
Рис. 5. Меры

Группа мер Cross Products And Projects For Product Matrix обеспечивает связь много-ко-многим между измерениями Товары и Каналы сбыта для товарной матрицы.

Список измерений изображен на рисунке 6.

image
Рис. 6. Измерения

Для измерений Товары, Клиенты, Менеджеры реализована иерархия Parent-child.

image
Рис. 7. Измерение Товары

Для управления доступом к многомерной базе данных создана роль Analists, которой предоставлены права Read и Drillthrough для куба Sales. Права Drillthrough позволяют пользователям получать расшифровку с информацией о том, как были рассчитаны значения ячеек в отчете.

image
Рис. 8. Роль Analists

Чтобы развернуть многомерную базу данных на сервере, указываем в свойствах проекта имя экземпляра SQL-сервера SSAS, имя базы данных на сервере и в меню BIDS нажимаем Deploy. Подключаемся к экземпляру SSAS с помощью SMS и видим, что многомерная база данных была создана.

image
Рис. 9. Многомерная база данных Sales OLAP

Этап №4. Разработка ETL-пакетов. Развертывание ETL-пакетов. Настройка автоматического выполнения ETL-пакетов

Наиболее трудоемкий этап при проектировании решений Business Intelligence - это, разработка ETL-пакетов. Связано это с тем, что источники данных, как правило, имеют разную структуру, а данные, хранящиеся в них, содержат ошибки и имеют различный формат. Например, пол сотрудника в разных базах данных, может быть представлен буквами М и Ж или цифрами 0 и 1, и перед загрузкой этих данных в хранилище, необходимо выполнить их очистку и приведение к общему виду. Кроме того, в хранилище данных необходимо обновлять только те данные, которые были введены или изменены с момента последней загрузки. Это только основные сложности, на самом деле их гораздо больше. Однако благодаря инструментам SSIS большинство подобных проблем могут быть решены. В моей реализации данные в таблицах измерений обновляются полностью, т.е. новые записи добавляются, а существующие записи перезаписываются. Таблица фактов очищается и заполняется снова за период по умолчанию равный трем месяцам. Глубина обновления таблицы фактов в месяцах хранится в конфигурации SSIS пакетов, которая представляет из себя отдельную таблицу в хранилище данных.

image
Рис. 10. Пакеты SSIS

На рисунке 10 изображены 4 пакета SSIS, назначение которых следующее:

  • Update DW and Process Sales OLAP.dtsx - мастер-пакет, в котором реализована общая логика ETL-процесса и который запускает все остальные пакеты.
  • Import Dimensions and Facts from Moscow.dtsx - пакет для загрузки данных в таблицы измерений и фактов из базы данных центрального офиса в хранилище данных.
  • Import Dimensions and Facts from Filials.dtsx - пакет для загрузки данных в таблицы измерений и фактов из баз данных филиалов в хранилище данных.
  • Process Sales OLAP.dtsx - пакет, который выполняет обновление данных (процессинг) в многомерной базе данных.

Логика (Control Flow) мастер-пакета следующая (см. рис. 11).

image
Рис. 11. Пакет Update DW and Process Sales OLAP

Рассмотрим каждый элемент этой схемы:

  • Сначала выполняется Set Package's Variables Values (Execute SQL Task). Задача этого элемента - получить значения из конфигурации пакета и записать их в переменные пакета. В конфигурации пакета в том числе хранится информация о глубине обновления таблицы фактов в месяцах. Конфигурации пакета хранится в отдельной таблице в хранилище базы данных и может изменяться IT-специалистами.
  • Далее Insert Default Values In Dimensions (Execute SQL Task) выполняет проверку и заполнение хранилища данных пустыми элементами. Например, в таблице dim.DimProducts после выполнения этого задания должен появиться элемент с идентификатором (Id), равным нулю. Записи с нулевыми идентификаторами будут созданы во всех таблицах измерений для обеспечения логической целостности данных, так как все поля таблицы фактов определены как NOT NULL и имеют значение по умолчанию равное нулю. Наличие NULL-ов в таблице фактов приводит к ошибкам при процессинге многомерной базы данных.
  • Get List of Source OLTP Databases in Moscow (Execute SQL Task) получает список баз данных центрального офиса (в моем случае такая база данных одна, но для большей универсальности решения, я предположил, что их может быть несколько). Список баз данных хранится в таблице dim.DimOffices. Так же в этой таблице хранятся строки подключения к базам данных. Полученная выборка записывается в переменную пакета.
  • For All OLTP Databases in Moscow (Foreach Loop Container) выполняет обход выборки, полученной на предыдущем шаге, и для каждой строки выборки (т.е. для каждой базы данных) выполняет пакет Import Dimensions and Facts from Moscow.dtsx. Передача параметров из мастер-пакета вызываемому пакету происходит с помощью установки значений конфигурации пакета, которую выполняет задача Set Package Configurations (Execute SQL Task).
  • Следующие два шага Get List of Source OLTP Databases in Filials (Execute Package Task) и For All OLTP Databases in Filials (Foreach Loop Container) аналогичны двум предыдущим, только выполняются для баз данных филиалов.
  • Последний шаг Process Sales OLAP (Execute Package Task) запускает пакет обновления данных в многомерной базе данных.

Описанные выше пакеты развернуты на экземпляре SQL-сервера SSIS. Для автоматического запуска мастер-пакета на SQL-сервере создано задание Update DW and Process Sales OLAP (см. рис. 12).

image
Рис. 12. SQL Job для запуска пакета SSIS

Для контроля выполнения ETL-процесса в задании настроено уведомление специалистов службы поддержки по e-mail о завершении задания (см. рис. 13).

image
Рис. 13 Настройка уведомления о выполнении задания по e-mail

Этап №5. Предоставление доступа к многомерной база данных

Доступ к многомерной базе данных предоставлен сотрудникам организации с помощью включения их доменных учетных записей в роль Analists многомерной базы данных с помощью SMS (см. рис. 14).

image
Рис. 14. Членство в роли Analists

Этап №6. Обучение сотрудников организации

Для обучение пользователей был записан 15 минутный видео-ролик, в котором были продемонстрированы возможности MS Excel, позволяющие подключиться к многомерной базе данных и построить отчет с помощью объекта PivotTable Report. Один из возможных вариантов отчета изображен на рисунке 15.

image
Рис. 15. Пример отчета PivotTable Report в Excel

Выводы

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

  • На реализацию данного решения было потрачено 40 человеко-часов. Все описанные было выполнено одним человеком, т.е. мной. Предварительно я посетил курсы и успешно сдал экзамены Microsoft, получив сертификат Microsoft Certified Solutions Expert в области Business Intelligence.
  • Таблица фактов в рабочей базе данных содержит ~40 миллионов строк.
  • ETL-процесс выполняется приблизительно 20 минут.
  • Формирование отчетов выполняется в пределах нескольких секунд.

Ссылки по теме


 Распечатать »
 Правила публикации »
  Обсудить материал в конференции Microsoft »
Обсудить материал в конференции Дизайн, графика, обработка изображений »
Написать редактору 
 Рекомендовать » Дата публикации: 13.02.2014 
 

Магазин программного обеспечения   WWW.ITSHOP.RU
Microsoft Windows Professional 10, Электронный ключ
Microsoft Visual Studio Professional w/MSDN AllLng Software Assurance OLP 1 License No Level Qualified
Microsoft Office 365 для Дома 32-bit/x64. 5 ПК/Mac + 5 Планшетов + 5 Телефонов. Подписка на 1 год.
Microsoft SQL CAL 2017 Sngl OLP 1License NoLevel DvcCAL
Microsoft SQL Server Standard Core 2017 Sngl OLP 2Licenses NoLevel CoreLic Qualified
 
Другие предложения...
 
Курсы обучения   WWW.ITSHOP.RU
 
Другие предложения...
 
Магазин сертификационных экзаменов   WWW.ITSHOP.RU
 
Другие предложения...
 
3D Принтеры | 3D Печать   WWW.ITSHOP.RU
 
Другие предложения...
 
Новости по теме
 
Рассылки Subscribe.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Безопасность компьютерных сетей и защита информации
Новости ITShop.ru - ПО, книги, документация, курсы обучения
Программирование на Microsoft Access
CASE-технологии
СУБД Oracle "с нуля"
Компьютерные книги. Рецензии и отзывы
 
Статьи по теме
 
Новинки каталога Download
 
Исходники
 
Документация
 
Обсуждения в форумах
Помощь по MS Access (329)
Доброе время суток. Случайно оказался на этом сайте, искал статьи по OLAP. Вижу, что...
 
Как извлечь рисунки из файла Word (42)
Вообще-то есть еще способ - сделать в Word-е Copy рисунка, открыть Microsoft Photo Editor и там:...
 
Сдача экзаменов Microsoft гарантия 100% (13)
Сдача экзаменов Microsoft гарантия 100%. Дополнительная информация по E-mail.
 
Подключение JavaScript (305)
Всем привет! Я подключаю JavaScript нижеприведенным образом. Код: <script...
 
Как восстановить рабочий стол? Помогите срочно надо! (3)
Windows 7 максимальная. В учетной записи сменила пароль и выключила комп, после включения...
 
 
 



    
rambler's top100 Rambler's Top100