Средства Business Intelligence в Microsoft SQL Server 2005

В последние годы термин Business Intelligence (BI) стал одним из самых популярных в сфере IT. Под ним понимают приложения и технологии, позволяющие собрать, отобразить и проанализировать данные для принятия более качественных бизнес-решений. Тенденцией этих лет является перемещение средств BI из отдельных приложений в СУБД. В предлагаемой статье мы постараемся разобраться, что в этом плане дает разработчику широко используемый Microsoft SQL Server 2005.

Понятие Business Intelligence довольно емкое, и его нельзя полностью вписать в рамки одних только компьютерных технологий, однако мы не станем во всех подробностях обсуждать сам термин, ограничившись лишь весьма упрощенным определением, приведенным выше, и сфокусируем внимание на практической реализации решений BI на базе средств SQL Server 2005. Но сначала все-таки пару слов о том, почему BI стала такой модной именно сейчас.

Компьютеры используются в бизнесе уже достаточно давно. Объемы накопленных оцифрованных данных не только большие, но и, что немаловажно, охватывают практически все сферы человеческой деятельности. Существует мнение, что ключ к завтрашнему успеху бизнеса той или иной компании находится в тех массивах «старых» данных, которые уже ею накоплены. Остается лишь правильно их проанализировать, обнаружить скрытые закономерности и просто вычислить прогноз на ближайшее будущее.

Мнение, конечно, не бесспорное, но к размышлениям располагает. Кстати, такая оцифрованная история имеется не только у западных корпораций – многие отечественные компании также успели накопить приличные массивы архивных данных, и их объем очень быстро увеличивается. Усвоить эти цифры, выделить наиболее важные и отсеять ненужные, составить наглядные отчеты и проанализировать их становится все труднее. Так что вопрос «to BI or not to BI» уже не стоит. Обойтись без инструментов сбора, отображения и анализа, как бы они ни назывались, не получится.

Вполне естественно, что инструменты BI напрямую связаны с базами данных, а точнее, с СУБД. Microsoft SQL Server – достаточно широко распространенная СУБД, и интересующиеся наверняка немало о ней читали, в том числе и на страницах «Компьютерного Обозрения». И, как нередко бывает с продуктами Microsoft, SQL Server давно перестал быть просто СУБД, функции которых традиционно сводятся лишь к структурированному хранению данных и обеспечению эффективного доступа к ним. От версии к версии количество дополнительных функций и расширенных возможностей росло, и в последней редакции SQL Server 2005 они выстроились в цельный набор BI-инструментов, позволяющих использовать этот продукт как систему поддержки принятия бизнес-решений.

Следуя логике самого понятия BI, разработчики SQL Server выделили в составе продукта три набора сервисов:

• SQL Server Integration Services – для сбора и преобразования данных;
• SQL Server Reporting Services – для отображения данных в виде отчетов;
• SQL Server Analysis Services – для анализа данных.

А в качестве инструмента для разработки BI-решений на базе этих служб Microsoft предлагает специализированную версию Visual Studio – Business Intelligence Development Studio, также входящую в состав дистрибутива SQL Server 2005.

В середине прошлого года вышла расширенная бесплатная версия SQL Server 2005 with Advanced Services, в состав которой включены Reporting Services (усеченная версия, подробнее см. msdn2.microsoft.com/en-us/library/ms365166.aspx). Однако в данной статье мы будем рассматривать полноценный набор BI-служб, доступный только в коммерческих релизах продукта.

SQL Server Integration Services (SSIS)

Рис. 1. Логическая архитектура SQL Server 2005

Главным предназначением SSIS является извлечение данных из источника, преобразование их и закачка в приемник. Такое программное обеспечение еще называют ETL-средствами (Extract-Transform-Load). Однако, несмотря на вспомогательный характер SSIS, в архитектуре SQL Server 2005 этот компонент занимает центральное место (рис. 1). На практике только в очень редких случаях базы создаются с нуля, наполняются данными вручную, используются автономно и затем никуда не экспортируются. Как правило, любая информационная система должна взаимодействовать с другими. Так, базы данных большинства систем содержат достаточно статичные данные – справочники, которые не создаются в самой системе, а происходят из внешних источников и должны периодически загружаться в нее из уже имеющихся файлов или других систем. Примерами могут служить названия стран, телефонные коды городов, типы валют. С другой стороны, у системы могут быть потребители информации, которым периодически нужно передавать выборки данных в том или ином формате. Зачастую такие операции импорта/экспорта выполняются вручную, а это отнимает время и может привести к проблемам из-за присутствия человеческого фактора. Гораздо целесообразнее поручить подобную рутину службе SSIS, разработав специальные пакеты, обеспечивающие автоматизацию многих операций.

Рис. 2. Пример схемы выполнения пакета SSIS

Пакеты для SSIS можно создавать двумя способами – в простых случаях вполне подойдет Import/Export Wizard, позволяющий в несложном интерфейсе указать источник, приемник и выбрать данные, подлежащие переносу. Более серьезные пакеты создаются при помощи Business Intelligence Development Studio (BIDS), в которой доступны многие инструменты. Процесс разработки SSIS-пакета в BIDS обычно состоит из нескольких этапов, и на каждом из них с помощью визуального конструктора строится ряд блок-схем (рис. 2).

Control Flow определяет общий поток выполнения пакета. К примеру, при импорте файлов с FTP-сервера в базу данных цепочка действий может быть такой:

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

Набор доступных блоков достаточно большой, а если требуется что-то особенное, можно использовать скриптовый блок Script Task, который позволяет написать произвольный код (правда, только на Visual Basic).

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

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

SQL Server Reporting Services (SSRS)

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

• базы данных в SQL Server, где хранятся конфигурация SSRS, описания отчетов и моделей;
• Web-приложения Report Server, позволяющего просматривать отчеты;
• Web-приложения Report Manager, которое служит для администрирования SSRS.

Рис. 3. Business Intelligence Development Studio в режиме оформления внешнего вида отчета

В SSRS предусмотрено два способа создания отчетов – в BIDS и с помощью Report Builder. Если источники данных, структура отчета и его параметры известны заранее, то в этом случае нужно создать новый проект в BIDS, выбрав его соответствующий тип – Report Server. Для совсем простых отчетов есть несложный wizard, который задаст все необходимые вопросы и сам сгенерирует описание отчета. В более сложном варианте следует выполнить как минимум три операции – составить параметризированные запросы к базе данных, описать каждый параметр и в удобном дизайнере оформить внешний вид (рис. 3). Описав отчет и после предварительного просмотра убедившись в его корректности, остается только выложить его на сайт, где установлен Report Manager.

Рис. 4. Просмотр параметризованного иерархического отчета в окне браузера

После этого, используя обычный браузер, нужно зайти на соответствующую страничку, заполнить автоматически сгенерированную форму с параметрами и нажать View Report – отчет будет показан в том же окне браузера с минимальными удобствами – возможностью постраничного просмотра, печати, поиска и масштабирования изображения (рис. 4). Для дальнейшей работы над отчетом его можно экспортировать в любой из шести форматов – Excel, PDF, TIFF, Web-архив, XML или CSV – и сохранить на диске.

С помощью SSRS можно создавать отчеты трех видов – табличные, матричные (иерархические) и графические (диаграммы). Пользователям Microsoft Excel эти возможности хорошо знакомы. Кроме традиционной табличной формы отчета, используются иерархические отчеты, которые отображают агрегированные данные и дают возможность сворачивать и разворачивать разделы, переходя, например, от годового объема продаж к данным по каждому месяцу и продукту. В графических отчетах данные представляются в виде двух- или трехмерных столбиков, лент, графиков или секторных диаграмм.

Несмотря на то что SSRS входит в состав SQL Server и в первую очередь рассчитан на работу с этой СУБД, информация, на основании которой строятся отчеты, может также находиться в базах данных Oracle, хранилищах SAP BW, простых файлах или любых источниках, доступных через OLE DB или ODBC.

Легкий в освоении и использовании инструмент SSRS в ряде случаев может не подойти «as is». К примеру, формочка для указания параметров отчета генерируется автоматически, и ее дизайн может понравиться не всем. Но поскольку каждый отчет имеет свой URL-адрес, попасть на него можно и по прямой ссылке. При этом в строке запроса можно сразу указать требуемые значения всех параметров и, таким образом, отобразить готовый отчет. А благодаря тому что функциональность SSRS доступна через Web-службы, можно написать несложную программку (да хоть в Microsoft Word) и непосредственно получить требуемый отчет с определенными параметрами и, например, сразу в формате Excel. Свой программный код можно встраивать и прямо в отчет – скажем, для того, чтобы по какому-то сложному алгоритму заполнять одну из колонок таблицы. К сожалению, такой встроенный код пишется только на Visual Basic и без привычных удобств подсветки синтаксиса, IntelliSense и возможностей отладки. Правда, к отчету можно подключить отдельно созданную .NET-сборку в откомпилированном виде, что отчасти компенсирует эту недоработку.

Часто пользователю заранее трудно определить, какие именно отчеты ему нужны, из каких колонок они должны состоять и по каким параметрам их формировать. Для таких случаев SSRS позволяет создать модель данных, опубликовать ее на сайте и предоставить возможность самому пользователю с помощью автоматически загружаемого приложения Report Builder составлять отчеты «на лету». Для этого в BIDS предусмотрен специальный тип проекта Report Model. Его разработчик сам определяет источник данных для модели и выбирает таблицы и представления, которые должны быть доступны конечному пользователю, не знакомому с точной структурой БД. Например, в модель для формирования отчетов о продажах можно включить только три таблицы – продуктов, клиентов и заказов, скрыв при этом все служебные колонки. А Report Builder сам предложит возможности агрегировать денежные суммы, структурировать отчет по годам, кварталам, месяцам, дням. Таким образом, менеджер по продажам сможет легко создать для себя необходимый отчет и сохранить его на сервере SSRS для использования в следующий раз.

Хочется рассказать еще об одной удобной возможности SSRS – подписках. На отчеты, опубликованные в SSRS, можно подписаться и получать их автоматически по электронной почте с заданной регулярностью и в удобном формате. А в редакции SQL Server 2005 Enterprise Edition есть возможность задать условие, при котором должен приходить отчет. К примеру, можно оформить подписку на отчет о еженедельных продажах с указанием, что его получение потребуется только в том случае, если объем продаж упадет ниже порогового.

SQL Server Analysis Services (SSAS)

SSAS – самый сложный BI-компонент SQL Server 2005, к которому в полной мере применимо слово Intelligence. SSIS и SSRS обеспечивают возможность собрать и наглядно представить данные, необходимые для анализа и принятия взвешенного решения. И зачастую этих средств вполне достаточно, чтобы решение было именно таким и опиралось не только на интуицию (хотя надо признать, что интуиция некоторых людей превосходит даже самые изощренные компьютерные средства BI). Однако во многих случаях для настоящего серьезного анализа данных нужны более функциональные инструменты.

Анализ данных – целая наука, требующая и отдельного рассмотрения, и определенного уровня подготовки, но, предельно упрощая эту сложную дисциплину, скажем, что главным средством аналитика являются многомерные кубы, которые создаются на основе «сырых» данных. Каждый куб состоит из «размерностей» (dimension) и «мер» (measure). К примеру, при анализе данных о продажах размерностями могут служить дата реализации, категория товара, регион, в котором продан товар, производитель и т. д., мерами – количество реализованных товаров и вырученная сумма. «Вращая» подобный куб, аналитик получает ответы на такие вопросы, как: «В каком регионе было продано больше всего товаров такой-то категории?», «На какую сумму в прошлом месяце было реализовано товаров данного производителя?» или «Как вырос сбыт такого-то товара по сравнению с прошлым годом?».

SSAS позволяет строить такие многомерные кубы, объединяя данные из различных источников (кроме SQL Server, поддерживаются Oracle и провайдеры OLE DB) в так называемую унифицированную многомерную модель (Unified Dimensional Model, UDM). Для этого служит все та же Business Intelligence Development Studio, в которой есть соответствующий тип проекта Analysis Services Project.

Создание UDM состоит из нескольких относительно несложных шагов. Для начала необходимо определить источники данных. Затем для каждого источника построить представления – выделить интересующие таблицы, указать связи между ними и при необходимости добавить производные поля, которых нет в исходных таблицах (рис. 5).

Рис. 5. Подготовка представления данных для унифицированной многомерной модели в SSAS

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

Теперь можно строить куб. Эта операция достаточно проста – BIDS сама анализирует содержимое таблиц и предлагает, какие из них следует отнести к размерностям, а какие – к мерам (если она ошибется, это несложно установить вручную). Собственно генерация куба может занимать длительное время, особенно если источником данных являются файлы, зато после того как куб создан, работа с ним происходит быстро, причем как в самой среде BIDS, так и при помощи Excel (рис. 6). В качестве заголовков строк и колонок выбираются размерности, а для наполнения – меры.

Рис. 6. «Вращать» куб можно как в BIDS, так и в Excel

Кроме самих данных – измерений и мер – унифицированная многомерная модель SSAS содержит целый ряд дополнительной информации. Так, к данным куба можно «привязывать» сопутствующую информацию (отчеты из SSRS или Web-ссылки), для удобства работы с большими кубами можно выделять перспективы – логически самостоятельные части куба, описывать производные вычисляемые величины, определять KPI (Key Performance Indicators – ключевые индикаторы производительности). Понятие KPI – одно из самых важных в BI, поэтому хотелось бы рассказать о нем подробнее.

Анализируя данные, необходимо не только видеть числа, но и понимать их значение, правильно их оценивать. Мало знать, что за месяц продано 100 единиц товара, нужно еще представлять, много это или мало, хорошо или плохо, и сколько должно быть по плану. Вот для такой качественной оценки данных и предназначены ключевые индикаторы KPI. В каждом KPI определяются плановое (Goal) и фактическое (Value) значение показателя, а также пороговые точки между уровнями плохо/нормально/хорошо на текущий момент (Status) и с учетом перспективы (Trend). К примеру, мы хотим продавать в месяц по 80 единиц продукции. Это плановое значение. Реализация менее 80 единиц принесет убытки, а свыше 120 будет свидетельствовать о большом успехе и потребует расширения бизнеса. 80 и 120 – это пороговые значения для оценок плохо/нормально/хорошо. При этом нам бы хотелось, чтобы каждый месяц рост продаж составлял 10% – это хорошая тенденция, в то время как падение продаж на 10% мы бы оценили как плохую. При таком определении KPI и, например, продажах 110 единиц товара в предыдущем месяце можно сказать, что 100 – это нормальный статус продаж, но плохой тренд, необходимо принимать меры.

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

Рис. 7. Пример использования ключевых показателей производительности (KPI) в SSAS

Помимо этого, кубы SSAS могут поддерживать многоязычный интерфейс – для этого необходимо указать переводы различных наименований (размерностей, мер, действий, ключевых индикаторов и т. д.), чтобы каждый мог работать с одним и тем же кубом на родном языке. Кроме того, в самом кубе можно определить роли его пользователей и права доступа к различным компонентам.

Говоря об анализе информации, нельзя совсем обойти понятие Data Mining (дословно «раскопка данных») – процесс обнаружения в сырых данных ранее не известных закономерностей, которые могут быть использованы для принятия решений. Однако подробное рассмотрение этой технологии, а скорее, даже ее математической основы, требует отдельной публикации. Отметим лишь, что в SSAS присутствуют средства Data Mining, и ими можно воспользоваться в BIDS для анализа и прогнозирования как на основе куба, так и работая с сырыми данными без его построения. SSAS поддерживает девять видов алгоритмов Data Mining, включая деревья решений, нейронные сети, поиск ассоциативных правил, временные последовательности, кластеризацию, регрессионный анализ.

Заключение

Средства BI, входящие сегодня в состав SQL Server 2005, не являются самыми мощными и всеобъемлющими из известных – есть решения других компаний, которые раньше стали осваивать данный сегмент IT-рынка (например, SAP Business Warehouse). Однако решения Microsoft привлекательны своей простотой в освоении и использовании – они снабжены хорошей документацией, примерами баз данных, проектов, подробными учебниками, в том числе и на русском языке. Немаловажно и то, что они входят в поставку SQL Server 2005, и те, кто приобрел данную СУБД только ради хранения данных, со временем могут легко воспользоваться всем набором прилагаемых BI-инструментов.

Направление BI сейчас – одно из самых приоритетных для Microsoft. Свидетельство тому – покупка фирмы ProClarity и анонсы готовящегося к выходу летом нынешнего года Office Performance Point Server 2007, который обещает вобрать в себя наиболее совершенные средства анализа данных и построения прогнозов. Совсем недавно был выпущен второй пакет обновлений SQL Server 2005, причем в BI-компонентах их список весьма внушителен. Кроме устранения обнаруженных недоработок, Service Pack 2 включает полноценную поддержку ОС Windows Vista и новой версии офисного пакета (главным образом это касается взаимодействия Excel 2007 с SSAS и SharePoint Server 2007 с SSRS и SSAS).

В общем, еще несколько лет назад слова «Business Intelligence» ассоциировались с крупными аналитическими агентствами, высокооплачиваемыми бизнес-аналитиками и запредельно дорогим программным обеспечением, приобрести которое могли лишь крупные корпорации. Сейчас же, благодаря стараниям таких компаний, как Microsoft, приходит время простых, удобных и недорогих BI-инструментов, и воспользоваться ими может практически каждый.

Ready, set, buy! Посібник для початківців - як придбати Copilot для Microsoft 365