Integration Services для формирования хранилищ данных

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

Для решения перечисленных задач служит компонент SQL Server Integration Services, позволяющий интегрировать и преобразовывать данные. В число возможностей компонента входит копирование и загрузка файлов, отправка сообщений электронной почты как реакция на события, обновление хранилища данных, вызов процедур очистки и исследования данных, управление объектами и данными в базе данных SQL Server.

Загрузка данных в хранилище данных и формирование аналитических моделей

В классическом варианте данные сначала выгружаются (как правило, без преобразований) из источников данных в отдельную базу — промежуточную область. Далее при загрузки данных из этой области в хранилище данных данные уже значительно преобразуются и переводятся в специальную схему хранения (в итоге процесс преобразования не замедляет исходные базы). При необходимости (например, для увеличения скорости последующего извлечения) части данных переводятся в «витрины данных» (как правило, базы в схеме «звезда»). Из витрин и/или хранилища данных формируются (обновляются) аналитические модели.

Классический вариант загрузки данных в ХД, формирования витрин и аналитических моделей

Противоположный подход – формировать многомерную аналитическую модель напрямую из источника (если это SQL Server) с помощью функции «проактивного кэширования». Соответственно, между этими двумя противоположными вариантами существует множество промежуточных решений. Дополнительно проекты формирования хранилища данных могут быть дополнены функциями интеграции с системой управления нормативно-справочной информацией (НСИ) и функциями очистки данных.

Графическая разработка интеграционных пакетов

Интеграционные решения разрабатываются в программе SQL Server Data Tools (SSDT; расширение среды разработки Visual Studio). С помощью этой среды разработчики могут совместно создавать интеграционные проекты используя специальную графическую нотацию. Благодаря этой нотации проекты являются само-документируемыми; в итоге значительно уменьшаются затраты и увеличивается скорость сопровождения и внесения изменений в проекты.

Структура интеграционного проекта

Интеграционный проект состоит из пакетов. Каждый пакет содержит в себе определенный алгоритм выполнения задач, может быть запущен на выполнение, и в свою очередь может вызывать на выполнение другие пакеты (входящие в алгоритм).

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

Поток данных – это особый вид задачи в потоке управления, предназначенный для извлечения данных из источников (через адаптеры чтения), а затем преобразования и загрузки данных в целевые базы (например, в хранилище данных; через адаптеры записи). В число преобразований входит комбинирование данных через joins, unions и т.д.; проверка данных через правила, сопоставления, оценку качества; преобразование данных в целевой формат.

Поток данных в составе потока управления интеграционного проекта. Источник - Microsoft

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

Размещение и администрирование

Разработанное интеграционное решение размещается в каталоге SSIS в виде модели проекта (.ispac). Для администрирования проектов, задания расписания выполнения пакетов, запуска пакетов с параметрами, формирования отчетности о результатах выполнения пакетов и т.д. используется SQL Server Management Studio (SSMS).

Журналирование работы интеграционных пакетов

В SQL Server 2012 встроена функция журналирования работы интеграционных пакетов и на базе журнала построены стандартные отчеты. В отчете верхнего уровня (All Executions report) можно посмотреть историю запусков каждого конкретного пакета (успешность, расположение, время начала и окончания, продолжительность). Через гиперссылки каждый запуск пакета можно детализировать вплоть до уровня конкретного события (в том числе можно посмотреть диагностические сообщения и значения переменных в момент возникновения события).

Отчет производительности запусков (Execution Performance report) отображает график изменения продолжительности времени работы пакета.

Фрагмент отчета о производительности интеграционного пакета

Также предусмотрен отчет со статистикой всех запусков всех пакетов (Dashboard report) и аналогичной возможностью детализации.

Демонстрация SSIS (разработка, размещение и администрирование)

Дополнительные материалы

  1. TechEd 2011 North America. DBI317. What’s New in Microsoft SQL Server Code-Named “Denali” for SQL Server Integration ServicesWhat’s New in Microsoft SQL Server Code-Named “Denali” for SQL Server Integration ServicesWhat’s New in Microsoft SQL Server Code-Named “Denali” for SQL Server Integration ServicesWhat’s New in Microsoft SQL Server Code-Named “Denali” for SQL Server Integration Services
  2. TechEd Australia 2011. DAT307DAT307. Denali SSIS and Data Quality Enhancements
  3. MSDN Library. SQL Server Integration Services
  4. SQLCAT. SSIS 2008 — world record ETL performance

Navicon CDI для синхронизации данных о клиентах

Решение компании «Навикон», партнера Microsoft, под названием Navicon CDI (customer data integration – интеграция данных о клиентах) на базе Microsoft SQL Server 2012 обеспечивает централизацию и синхронизацию информации о клиентах, находящейся в различных информационных системах.

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

Ниже приведена демонстрация работы системы для выявление и устранения дубликата в справочнике клиентов – физических лиц (видео не содержит звука).

В текущий момент ведется разработка новой версии. Следите за новостями…

Дополнительно: http://www.navicongroup.ru/biztalk/management_nsi/cdi.php

Практические рекомендации по использованию DQS 2012

В дополнение к обзорной статье про Data Quality Services хотелось бы поговорить о некоторых практических аспектах использования этого компонента, входящего в состав SQL Server.

Для решения каких задач можно использовать DQS

DQS можно использовать для следующих задач:

  • автоматическая выверка, очистка и удаление дублей в больших массивах данных о физических и юридических лицах:
  • проверка корректности формата e-mail адресов, телефонов, номеров паспорта, ИНН, КПП, банковских реквизитов, СНИЛС и т.д.;
  • простановка почтовых индексов на основании адреса;
  • проверка корректности, согласованности и определение пола  по имени и отчеству;
  • корректировка и приведение к стандартному виду почтовых адресов (при наличии общей базы адресов);
  • и т.д. (пожалуйста, высылайте дополнения к этим примерам)

Создание базы знаний DQS с использованием составных доменов и бизнес-правил

При создании базы знаний задаются домены (соответствующие колонкам исходных наборов данных), которые будут проходить очистку и дедупликацию. При необходимости можно задавать составные домены, т.е. домены, состоящие из нескольких обычных доменов.

Для имеющихся доменов можно создавать бизнес-правила, обеспечивающие корректность данных в домене, в том числе:

  • типичные альтернативные названия и правила их преобразования в правильные названия;
  • отслеживание допустимых диапазонов значений, правильных типов данных;
  • проверка соответствия шаблону (например, что e-mail представляет одно или несколько слов, разделенных точками плюс символ «@» плюс как минимум два слова разделенных точками; при этом слово – последовательность латинских букв, цифр и некоторых разрешенных символов);
  • правила корректировки составных доменов (например, если город – Екатеринбург, то субъект РФ – Свердловская область);
  • правило по отправке данных для очистки с помощью специального облачного сервиса (например, сверить адрес с общей базой адресов);
  • и так далее.

Очистка данных в автоматическом режиме (проект SSIS c преобразованием DQS Cleansing)

В типовом пакете очистки данных новые справочные данные сравниваются с нормативно-справочной информацией (НСИ). Если данные есть в системе управления НСИ, они направляются в промежуточную область хранилища данных, а если нет, они могут быть направлены в преобразование DQS Cleansing для предварительной очистки (источник: Matt Masson. EIM presentation material from DevTeach Montreal).

Пример потока данных с применением очистки DQS. Источник: Matt Masson. EIM presentation material from DevTeach Montreal

В результате очистки преобразованием DQS Cleansing данные классифицируются: новые, корректные и откорректированные.

Откорректированные данные снова сравниваются с данными в системе управления НСИ. Если соответствие найдено, они направляются в промежуточную область хранилища данных.

Если соответствие откорректированных данных все еще не найдено, данные направляются в промежуточную таблицу для ручной обработки с помощью DQS наравне с новыми записями, а также в систему управления НСИ наравне с корректными записями, которых нет в НСИ.

Гибкая дедупликация данных в ручном режиме (проект DQS)

Для более гибкой настройки дедупликации можно использовать клиентское приложение DQS, где с помощью Matching Policy индивидуально для каждого домена можно задать его вес при сопоставлении, а также при необходимости указать требование изначальной идентичности (источник: Jeremy Kashel. DQS Matching Vs SSIS Fuzzy Grouping and Lookup).

Задание весов доменов в Matching Policy. Источник: Jeremy Kashel. DQS Matching Vs SSIS Fuzzy Grouping and Lookup

DQS выполняет сопоставление данных и дедупликацию путем сравнения каждой строки со всеми остальными строками с использованием настроек Matching Policy, заданных в базе знаний.

Обнаруженые дублирующие записи. Источник: Jeremy Kashel. DQS Matching Vs SSIS Fuzzy Grouping and Lookup

Результат сопоставления с учетом ручных корректировок может быть экспортирован в таблицу SQL Server или файл CSV для дальнейшей обработки.

Дедупликация данных в автоматическом режиме (проект SSIS c преобразованием Fuzzy Gouping)

Для дедупликации данных в автоматическом режиме в SQL Server 2012 можно использовать преобразование Fuzzy Grouping, которое в дополнительной колонке _key_out формирует идентификатор группы (ссылку на канонический идентификатор _key_in), а в колонке _score выдает стпень похожести (между 0 и 1). Источник: BI Monkey. SSIS, DQS Cleansing & the Balanced Data Distributor.

Пример потока обработки данных с использованием преобразования Fuzzy Grouping. Источник: BI Monkey. SSIS, DQS Cleansing & the Balanced Data Distributor SSIS, DQS Cleansing & the Balanced Data Distributor

Уровень похожести записей устанавливается с помощью специального бегунка в дополнительных настройка преобразования Fuzzy Grouping.

Распараллеливание потоков данных для ускорения пакета с DQS

Для применения DQS над большими объемами данных (сотни тысяч и миллионы записей) можно использовать распараллеливание потока данных с помощью компонента «Balanced Data Distributor» (источник: BI Monkey. SSIS, DQS Cleansing & the Balanced Data Distributor).

DQS с применением BDD (Balanced Data Distributor). Источник: BI Monkey. SSIS, DQS Cleansing & the Balanced Data Distributor SSIS, DQS Cleansing & the Balanced Data Distributor

Файл Excel 2013 со статистикой в разрезе субъектов РФ – PowerPivot и Power View

По просьбам общественности публикую текущую версию файла Excel 2013 со статистикой в разрезе субъектов РФ. Модель данных подготовлена с помощью PowerPivot и отображается в Excel с помощью Power View.

Файл находится в разделе «Аналитические модели» на сайте проекта http://public-intelligence.ru.

В ближайшие дни опубликую вебкасты о том, как устроена аналитическая модель в разрезе субъектов и бюджетной классификации, а также о том, как ее дополнять…

Читайте также:

  1. Анализ данных из произвольных источников с использованием PowerPivot
  2. Новое в Excel 2013 Preview для бизнес-аналитики
  3. Как общество может отслеживать результаты государственной деятельности с помощью Excel 2013

Установка бизнес-аналитики SQL Server 2012 SP1 для SharePoint 2013

Стандартная последовательность установки

Часто возникает задача подготовки тестовой или демонстрационной среды для решений Microsoft BI. Далее приведены рекомендации по созданию такой среды для Microsoft BI 2013. Для промышленного решения некоторые из приведенных ниже рекомендаций могут не подойти.

Для установки функций бизнес-аналитики SQL Server 2012 SP1 для одиночного сервера SharePoint 2013 используйте следующую последовательность.

  1. Установите SharePoint Server 2013 и включите использование Excel Services.
  2. Установите Analysis Services в режиме SharePoint (подробнее; см. «Step 1»).
  3. Сделайте настройки Excel Services в SharePoint Server 2013, чтобы использовался экземпляр Analysis Services, установленный на шаге 2 (подробнее; см. «Step 2»).
  4. Установите «Reporting Services в режиме SharePoint» и «надстройку Reporting Services для SharePoint» (подробнее).
  5. Сконфигурируйте «Reporting Services SharePoint Service» и не менее одного «Reporting Services Service Application» (подробнее).

Решение проблем установки

В это разделе я хотел бы дать некоторые рекомендации и замечания по установке, чтобы избежать ошибок установки (пожалуйста, добавляйте свои замечания и комментарии).

В частности, возможна ситуация, когда на сервере ранее было инсталлировано аналогичное решение предыдущих или предварительных версий (Technology Preview). В этом случае возможны нестандартные ситуации.

Ниже приведены проверенные мною рекомендации.

1. Проверьте, что работаете под учетной записью с административными правами и запускаете все программы, вносящие изменения, под администратором.

2. При установке с нуля не забудьте включить доменную учетную запись, под которой будет работать SharePoint в роль SQL Server Admin. При этом SQL Server и SharePoint должны стартовать под одной доменной учетной записью (подробнее).

3. Если ранее на машине был установлен SharePoint 2013 Preview, вручную удалите связанные пулы приложений и сайты с помощью «IIS Manager».

4. Включите доменного пользователя в группы (подробнее):

  • IIS_IUSRS
  • SQLServerFDHostUser$<MachineName>$<MSSQLSERVERInstance>
  • SQLServerMSASUser$<MachineName>$<MSSQLSERVERInstance>
  • SQLServerMSSQLUser$<MachineName>$<MSSQLSERVERInstance>
  • SQLServerSQLAgentUser$<MachineName>$<MSSQLSERVERInstance>
  • WSS_ADMIN_WPG
  • WSS_RESTRICTED_WPG_V4
  • WSS_WPG

5. Вместо запуска мастера конфигурирования SharePoint 2013 попробуйте выполнить следующие инструкции из «SharePoint 2013 Management Shell», а затем повторно запустите «SharePoint 2013 Products Configuration Wizard» (подробнее):

cd «C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\BIN»

PSCONFIG.EXE -cmd configdb create SkipRegisterAsDistributedCacheHost -server office2013bi -database SP2013_ConfigDb -admincontentdatabase SP2013_Admin_Content -user <домен>\<пользователь> -password <пароль>  -passphrase <фраза-пароль>

6. Если в журналах наблюдаются ошибки с ADOMD.NET, а в PerformancePoint не получается вывести список многомерных кубов, используйте перенаправление сборок в файле web.config (подробнее). Должно быть так: <bindingRedirect oldVersion=»10.0.0.0″ newVersion=»11.0.0.0″ />.

7. В настройках «Central Administration, Manage Services Applications, PerformancePoint Service Application, PerformancePoint Service Application Settings» не забудьте включить флаг «Use the EffectiveUserName connection string property instead of Windows delegation», чтобы пользователь подсоединялся к SSAS под своей учетной записью.

8. Reporting Services в режиме SharePoint нужно устанавливать после установки и настройки SharePoint. Если установили раньше – деинсталлируйте, и установите заново.

9. Если неправильно работает SQL Server PowerPivot Service Application, попробуйте его полностью деинсталлировать и установить заново. При удалении могут потребоваться дополнительные ручные операции. Я использовал «SELECT Id, classid, parentid, name, status, version, properties FROM objects WHERE name like ‘%PowerPivot%’», а затем из PowerShell команды для соответствующих GUID вида «stsadm -o uninstallfeature -id ’1a33a234-b4a4-4fc6-96c2-8bdb56388bd5′ -force». После этого деинсталлировал с помощью программы «PowerPivot for SharePoint Configuration» (подробнее).

Дополнительная информация

«Большие данные» и HDInsight (Hadoop от Microsoft) для обработки петабайт информации

Последняя версия этой статьи: https://microsoftbi.ru/basics/bigdata/hdinsight/

В настоящее время становятся все более актуальными решения для работы с большими объемами данных (в английской терминологии – «Big Data»). Под «большими данными» понимаются данные больших объемов (петабайты информации), возникающие или обновляющиеся с большой скоростью, а также данные с большим разнообразием типов данных.

Примеры сценариев использования «больших данных»:

  • анализ социальных сетей;
  • мониторинг и оптимизация дорожного движения, работы веб-приложений, оборудования;
  • анализ ухода клиентов;
  • исследование природных ресурсов, медицинские и прочие научные исследования;
  • обнаружение мошенничества;
  • анализ рекламных кампаний.

Решение Microsoft для работы с большими данными состоит из задач:

  • управления данными, которое реализуется через SQL Server Parallel Data Warehouse для управления сотнями терабайт структурированных данных; SQL Server StreamInsight для выявления сложных событий в реальном времени и HDInsight для обработки петабайт неструктурированной информации;
  • обогащения данных через каталог статистических данных Azure Marketplace Datamarket;
  • исследования данных с помощью PowerPivot и Power View для Excel и SharePoint.

Hadoop

Архитектурно Hadoop состоит из файловой системы Hadoop Distributed File System (HDFS) и механизма вычислений MapReduce, поверх которых могут использоваться различные расширения.

Структура узлов HDFS

Hadoop Distributed File System (HDFS) – это файловая система, предназначенная для хранения очень больших файлов с возможностью потокового доступа к данным, работающая на кластерах из произвольного аппаратного обеспечения.

Файлы в HDFS разбиваются на блоки, если размер файла превышает размер блока. По умолчанию размер блока в HDFS может составлять 64, 128 или 256 Мбайт, и каждый блок реплицируется в трех экземплярах.

Кластер HDFS состоит из управляющего узла (NameNode) и узлов хранения данных (DataNode). NameNode управляет пространством имен файловой системы (дерево файлов и мета-данные файлов и каталогов).

При чтении файла из HDFS клиент получает адреса расположения блоков от управляющего узла и самостоятельно осуществляет последовательное чтение с узлов блоков файла. При этом для каждого блока выбирается «ближайший узел».

Благодаря тому, что клиент извлекает данные с узлов данных напрямую HDFS может масштабироваться до большого количества конкурентных клиентов, т.к. трафик распределен между узлами данных в кластере.

Механизм MapReduce

Поверх файловой системы HDFS находится механизм MapReduce, который позволяет на уровне узлов данных сделать извлечение данных и расчет на основании этих данных, а на главном узле подготовить общий ответ на основании данных от всех узлов данных.

Microsoft HDInsight

25 октября 2012 года на конференции Strata Conference-Hadoop World было представлено решение HDInsight (Hadoop для Windows), которое может быть размещено как в пределах собственных корпоративных центров данных организаций, так и в облаке. В текущий момент с сайта microsoft.com/bigdata можно скачать предварительную версию HDInsight для изучения и функционального тестирования.

Открытые компоненты для Hadoop

Существует множество компонентов Hadoop, которые можно использовать в проектах на базе HDInsight. Ниже приведен список наиболее значимых компонентов.

  • Java, Phython, JavaScript, .NET, C# (языки разработки);
  • HBase (OLTP база данных);
  • Pig (организация потоков данных);
  • Sqoop (перемещение больших объемов данных);
  • Hive ODBC Driver (доступ к данным, в том числе из PowerPivot);
  • HCatalog (управление мета-данными);
  • Pegasus (интеллектуальный анализ графов);
  • Hive (SQL-подобное хранилище данных);
  • Mahout (машинное самообучение);
  • R (статистический пакет).

Дополнительные материалы

  1. Официальный сайт Microsoft Big Data
  2. Microsoft at Strata-Hadoop World 2012
  3. Simplifying Big Data for the Enterprise
  4. Материалы конференции Strata Conference-Hadoop World

Новое в SharePoint 2013 Preview для бизнес-аналитики

Функции SharePoint 2013 Preview для бизнес-аналитики можно разделить на 3 группы:

  1. Инструменты исследования данных позволяют объединять данные из различных источников, создавать интерактивные отчеты и исследовать данные. Реализуются через функции Excel 2013 и Excel Services.
  2. Возможности визуализации предоставляют различные варианты отображения и совместного анализа данных в пределах организации. Реализуются через функции Power View, PerformancePoint Services, Visio Services.
  3. Функции контроля позволяют управлять аналитическими решениями и отслеживать выполнение стандартов соответствия. Реализуются через Control Management Server для SharePoint.

Excel Services

Excel Services позволяет пользователям просматривать и взаимодействовать с книгами Excel (в том числе с аналитическими моделями PowerPivot), опубликованнными на портале SharePoint.

Новые возможности Excel Services в SharePoint 2013 включают:

  1. Непосредственно с портала (через веб-браузер) можно модифицировать таблицы (PivotTable) и диаграммы (PivotChart) на базе аналитической модели BISM, а также выполнять операции детализации данных (Drill Down To) и запрашивать предлагаемые способы просмотра данных (Data Exploration). Также поддерживаются расчетные меры и расчетные элементы, созданные в Excel. Новый элемент визуализации «шкала времени» (TimeLine) также поддерживается. Т.е. Excel Services поддерживает перечисленные функции так же, как и Excel 2013.
  2. Application BI Servers. Администраторы могут настроить выделенные серверы SQL Server Analysis Services для поддержки дополнительных аналитических возможностей Excel Services.

PerformancePoint Services

В SharePoint 2013 PerformancePoint Services продолжает оставаться средством для создания произвольных информационных панелей с возможностью отображения карт показателей и отчетов (интерактивных, со строгим оформлением, диаграмм и таблиц из Excel, схем из Visio и т.д.). Этот компонент позволит создать нестандартные панели, которые невозможно реализовать с помощью Power View.

К числу новых возможностей SharePoint 2013 PerformancePoint Services относятся:

  1. Пользователи смогут полностью мигрировать информационные панели (включая связанные объекты, в том числе .aspx файлы) для других пользователей, серверов и коллекций сайтов. В том числе можно мигрировать отдельные элементы, а также использовать для этого PowerShell.
  2. Новые фильтры получили ряд новых возможностей, таких как поиск.
  3. Поддержка эффективных пользователей Analysis Services.
  4. PerformancePoint поддерживается на iPad в веб-брайзере Safary.

Visio Services

Компонент Visio Services – это сервисное приложение, позволяющее пользователям просматривать на портале рисунки Visio, в том числе и подсоединенные к источниках данных (отражающие значения данных на схемах). В SharePoint 2013 Visio Services появился параметр управления кэшем и соответствующие правила управления этим параметром, обновлены команды PowerShell, добавлена возможность комментирования на схемах Visio (*.vsdx), что является важным дополнением для возможностей командной работы на портале.

Другие улучшения

Центр Бизнес-аналитики имеет новое оформление и стал проще в использовании.

Дополнительная информация