(Возврат на основную страницу)

Содержание номера за Апрель 2007 год

Почему Microsoft не заботится об администраторах баз данных?

Во время работы конференции мне удалось тесно пообщаться с некоторыми членами группы разработки Microsoft, конкретнее — с группой разработки компонента Visual Studio, предназначенного для разработчиков баз данных (Visual Studio for database Professionals, подроднее — по ссылке http://msdn2.microsoft.com/en-us/teamsystem/aa718807.aspx/). Моими собеседниками были Герт Драперс (Gert Drapers) и Камерон Скиннер (Cameron Skinner). Во время нашего разговора стало совершенно очевидно, что Microsoft не слишком-то думает об администраторах баз данных, в отличие от разработчиков, к которым относится с безусловным пиететом.

Основная проблема, о которой я говорил применительно к VSTS, состоит в том, что нужно залезать в VS.Net, хотя гораздо полезнее была бы встроенная функциональность в SQL Server Management Studio (SSMS). Ведь именно там администраторы баз данных проводят большую часть времени. Помимо них, выпали из внимания специалисты, которые выступают как в роли администраторов, так и в роли разработчиков и вынуждены держать открытыми два инструмента. Предположение, что администраторам претит простой доступ к коду, абсурдно. Однако мне кажется, я понял, что имеется в виду... оффлайновое редактирование. Все, что вы делаете в коде, выполняется в оффлайне и не содержится отдельно от производственной среды. Это, однако, не означает, что вам придется отделять одно от другого. Разработчики сказали, что они хотят сократить разрыв между разработкой и внедрением, но все, чего они добились, — это разделить нас стеной. Совершенно очевидно, что любая разработка выполняется через Visual Studio. Вот видите: чтобы администратор добрался до кода через Visual Studio, от него потребуются титанические усилия. Начать с того, что ни одного нужного соединения в студии нет. Придется определять все новые соединения и, если те поменяются, потратить усилия на приведения их в актуальное состояние. Даже файл помощи размещается отдельно. Вам приходилось когда­нибудь сохранять ссылку на статью оперативной помощи, чтобы потом в помощи для Visual Studio обнаружить, что вашей ссылки там нет? Это просто не работает!

Я администратор, и, создавая код, я пишу его в SSMS — не в студии. Поэтому, чтобы поместить что­нибудь в хранилище кода, я должен копировать и вставлять в студийный проект. В результате студия становится всего лишь средством хранения моего кода. Как мы все знаем, в SSMS отсутствует что­либо, хотя бы отдаленно напоминающее Intellisense. Существуют некоторые весьма интересные возможности, но все — исключительно внутри студии. При этом SSMS не может похвастаться ничем особенным с точки зрения функциональности, а то, что выдается за великие достижения, просто невозможно использовать. Отчеты состояния — отличный пример. Они настолько медленно работают, что не стоило и стараться вставлять их в продукт. А вы когда­нибудь пытались щелкнуть правой кнопкой мыши таблицу и выбрать в меню «select to»? Это все равно, что выжимать кровь из репы. По большей части ожидание тянется, и тянется, и тянется... вы понимаете, о чем я. При этом в QA (Query analyzer) все это работало быстро и у меня лично
с этим инструментом были только мелкие проблемы. Между прочим, QA написал Герт Драперс. Теперь же SSMS и близко не дотягивает до прежнего уровня, и я не встречал никого, кто был бы доволен этим инструментом. Несколько неплохих идей там есть, но они пока не материализовались ни во что пригодное.

Я действительно надеялся, что некоторые проблемы будут решены в SP2, но это не произошло. Вы можете спросить, почему? Ответ прост. MS наплевать на администраторов баз данных. Это совершенно понятно. Если вы делаете серьезные разработки, выполняйте их в студии, а не в среде SSMS. Очевидно, что уважающий себя программист не унизится до SSMS, и администратор не может считаться серьезным разработчиком, если он программирует в клиентской среде.

Я бы желал видеть пакет клиентских инструментов. Они помогли бы администраторам решить все проблемы, с которыми мы сейчас сталкиваемся в SSMS, и дали бы нормальную среду. Все разработчики, с которыми мне приходилось иметь дело, рассказывали, что они знают про проблемы в SSMS. Неудивительно, ведь прошел целый год. Так почему же они не были исправлены в SP2? Верно, вы и сами догадались, потому что MS игнорирует нужды администраторов. Если такие же проблемы были найдены в студии, их устранили бы еще в SP1, если не раньше.

И еще одно. Хотя и верно, что администраторам нужно открывать студию для работы с инструментами VSTS, можно поспорить, что MS использует эту модель для разведения задач. Отделяя администраторов от разработчиков, специалистов, совмещающих функ­ции разработчиков и администраторов, принуждают более четко осознавать, что они делают, и, по сути дела, они меняют их роль. Не уверен, что именно об этом думали проектировщики среды, но теперь они могут сказать: «Ну и отстаньте от нас».

Интеллектуальная обработка данных и хранилищ данных в SQL Server 2005. Часть 1

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

Microsoft SQL Server 2005 является полноценной платформой интеллектуальной обработки данных, предоставляющей возможности, инструменты и функ­циональность для создания и классических, и инновационных аналитических приложений. В табл. 1 приведен обзор компонентов системы интеллектуальной обработки данных и связанных с ними компонентов Microsoft SQL Server 2000 и SQL Server 2005.

В SQL Server 2005 добавлены два новых компонента: SQL Server Management Studio и SQL Server Business Intelligence Development Studio. Другие основные компоненты интеллектуальной обработки данных — Integration Services, Analysis Services OLAP, Ana­lysis Services Data Mining и Reporting Services — в SQL Server 2005 значительно изменены и улучшены. Реляционная база данных SQL Server 2005 содержит несколько новых важных возможностей. Хотя инструменты запросов в Microsoft Office и не являются частью SQL Server 2005, их текущие версии смогут работать с SQL Server 2005. Функциональность интеллектуальной обработки данных в Microsoft Office будет развиваться в следующих версиях Microsoft Office.

Набор инструментов SQL Server 2005 Business In­tel­li­gence предоставляет полную интеграцию приложений интеллектуальной обработки данных:

•    Разработка Business Intelligence Development Studio — это первая интегрированная среда, созданная для разработчиков систем интеллектуальной обработки данных. Основанная на Visual Studio 2005, Business Intelligence Development Studio представляет собой мощную, интегрированную, профессиональную платформу разработки для специалистов в области систем интеллектуальной обработки данных. Отладка, контроль исходного кода, разработка скриптов и кода доступны во всех компонентах приложения интеллектуальной обработки данных.

•    Интеграция Инструмент Integration Services полностью переписан для выполнения сложных высокоскоростных интеграции, преобразования и синтеза очень больших объемов данных. Создавать и отлаживать пакеты в Business Intelligence De­ve­lopment Studio теперь просто приятно. Integration Services, Analysis Services и Reporting Services работают вместе и обеспечивают одинаковое представление данных из гетерогенных источников.

•    Хранение SQL Server 2005 размывает границы между реляционными и многомерными базами данных. Вы можете хранить данные в реляционной базе данных, в многомерной базе данных или использовать новую функциональность — упреждающий кеш (Proactive Cache) для использования преимуществ обоих типов баз данных.

•    Анализ Систему добычи данных (Data Mining) в SQL Server всегда было легко использовать. Теперь же она стала еще лучше после добавления новых важных алгоритмов, таких как Association Rules, Time Series, Regression Trees, Sequence Clustering, Neural Nets и Nave Bayes. Также в кубы Analysis Ser­vices была добавлена новая важная аналитическая функциональность: система ключевых показателей производительности (Key Performance In­di­ca­tor), скрипты MDX и другие встроенные сложные методы анализа. Доставка отчетов и среда управления отчетами в Reporting Services упрощают распространение сложной аналитики среди самой широкой аудитории.

•    Отчетность Reporting Services расширяет платформу Microsoft Business Intelligence для более легкого доступа к ней бизнес­пользователя, которому необходимы результаты анализа. Reporting Services — это управляемая среда отчетности предприятия, устанавливаемая и управляемая через веб­сервисы. Отчеты могут быть персонализированы и могут доставляться в различных форматах с рядом интерактивных возможностей и возможностей печати. Результаты сложного анализа могут попасть к широкой аудитории с помощью распространения отчетов в виде источников данных для систем интеллектуальной обработки данных. Нововведением в SQL Server 2005 является Report Builder. Report Builder предназначен для создания пользователями собственных отчетов своими силами. Инструменты компании Microsoft и ее партнеров для создания пользовательских запросов и аналитики останутся популярными для доступа к данным в Analysis Services и реляционных базах данных.

•    Управление SQL Server Management Studio объединяет управление всеми компонентами SQL Server 2005. С помощью Management Studio компоненты платформы интеллектуальной обработки данных получают улучшенные масштабируемость, надежность, доступность и программируемость. Эти улучшения предоставляют значительные преимущества специалисту в области интеллектуальной обработки данных.

Основной задачей компонентов интеллектуальной обработки данных в SQL Server 2005 является поддер­жка разработки и использования систем интеллектуальной обработки данных на предприятиях любого размера и всеми сотрудниками — не только менеджерами и аналитиками, но и руководителями оперативных подразделений и партнерами. Для выполнения этой задачи SQL Server 2005 создан как полноценная, интегрированная, легкая в использовании система, которая публикует данные в виде веб­служб, обеспечивает прекрасную производительность на обычном аппаратном обеспечении и содержит множество новых возможностей, которые вы сможете использовать для разработки инновационных аналитических приложений.

С чего начать работу с SQL Server 2005?

Первое, что вы заметите во время установки SQL Server 2005, — это то, что процесс установки компонентов интегрирован. Вам больше не нужно отдельно запускать установочные программы для дополнительных компонентов, таких как Analysis Services. Если некоторый компонент, например Reporting Services, недоступен для установки, скорее всего это значит, что ваш компьютер не удовлетворяет требованиям для установки этого компонента. Вы можете посмотреть файл Readme с полным описанием требований для уста­новки компонентов. Необходимо установить следую­щие компоненты:

•    ядро реляционной базы данных SQL Server;

•    Integration Services;

•    Analysis Services;

•    Reporting Services;

•    SQL Server Management Studio (набор инструментов управления базами данных);

•    Business Intelligence Development Studio (набор инструментов разработки приложений интеллектуальной обработки данных).

Reporting Services требует установленной и правильно настроенной службы IIS. Мы настойчиво рекомендуем выделить время на настройку и установку IIS, так как Reporting Services является неотъемлемой частью набора компонентов SQL Server 2005 Business Intelligence.

Клиенты, знакомые с Analysis Services, могут быть удивлены отсутствием репозитория метаданных в Ana­ly­sis Services. В SQL Server 2000 репозиторий Analysis Services поставлялся в виде базы данных Micro­soft Access, но в Analysis Services 2005 репозитория метаданных нет. Вместо него метаданные базы данных Analysis Services хранятся в виде XML­файлов и управляются Analysis Services. При желании для этих XML­файлов может быть установлен контроль исходного кода.

Мы рекомендуем вам использовать Business In­tel­li­gence Development Studio для разработки и SQL Server Management Studio для управления объектами базы данных интеллектуальной обработки данных и их поддержки. Вы можете создать пакеты Integration Services, кубы Analysis Services и модели добычи данных и в SQL Server Management Studio, но Business In­tel­li­gence Development Studio предлагает больше возможностей для разработки и отладки приложений интеллектуальной обработки данных.

Скорее всего вы сможете узнать больше, создавая новые приложения вместо обновления существующих пакетов DTS и баз данных Analysis Services. Пересоздание с нуля существующего пакета или базы данных, если таковые имеются, может оказаться более полезным. После того как вы ознакомитесь с новыми инструментами, возможностями и концепциями, вам будет проще обновить существующие объекты.

Многие клиенты будут использовать инструменты SQL Server для разработки систем со знакомой им структурой интеллектуальной обработки данных из одной или более систем­источников данных, используя Integration Services для заполнения реляционного хранилища данных с измерениями, которое в свою очередь будет использоваться для заполнения базы данных Analysis Services. Но SQL Server 2005 предоставляет большое количество возможностей для отступления от традиционной схемы, исключая некоторые компоненты или делая их виртуальными.

Реляционное хранилище данных

Ядро реляционной базы данных SQL Server 2005 включает несколько интересных возможностей для создания и поддержки различных приложений с хранилищами данных. Эти возможности включают:

•    табличные секции, обеспечивающие быструю загрузку данных и упрощенную поддержку очень больших таблиц;

•    простое создание сервера отчетности;

•    улучшения в Transact­SQL, включая новые типы данных и новые аналитические функции;

•    выполнение онлайновых операций над индек­сами;

•    гранулированные операции резервного копирования/восстановления;

•    быструю инициализацию файлов.

Сервер отчетности

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

Microsoft SQL Server 2005 добавляет две новых возможности, зеркалирование баз данных (database mirroring) и снимки баз данных (database snapshot), которые облегчают создание и поддержку сервера отчетности. Теперь сервер отчетности SQL Server может иметь задержку обновления данных меньшую, чем раз в день. Также сервер отчетности спроектирован как резервная система для транзакционной системы.

Чтобы создать сервер отчетности, сначала сделайте зеркальную копию базы данных, новую функциональность в SQL Server 2005, которая позволяет получить резервную систему (standby system) для «горячей замены» (high availability). Дополнительную информацию вы можете прочитать в «Database Mirroring Con­cepts» в SQL Server Books Online. К зеркальной копии базы данных нет прямого доступа, и тут поможет еще одна новая функциональность — снимки базы данных.

Создание снимка базы данных на ее зеркальной копии предоставляет дополнительную копию данных для формирования отчетов. Снимок базы данных — это доступная только для чтения копия базы данных, сделанная в определенный момент времени, которая не обновляется по мере обновления данных в исходной базе. Снимки баз данных — это большая тема для обсуждения с множеством сценариев использования; дополнительную информацию вы можете прочитать в «Understanding Database Views» в SQL Server Books Online. Сейчас достаточно отметить, что снимки баз данных очень эффективно хранят информацию в плане адресного пространства. Одновременно может существовать множество снимков баз данных для полного отражения данных для отчетности, хотя надо учитывать, что поддержка снимка базы данных оказывает некоторое влияние на саму транзакционную базу данных, на основе которой создан этот снимок.

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

Табличные секции

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

Секционирование помогает:

•    улучшить управляемость таблиц и индексов;

•    улучшить производительность выполнения запросов на многопроцессорных компьютерах.

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

Ниже представлены три шага, описанные в «Crea­ting Partitioned Tables and Indexes» в SQL Server Books Online, которые требуются для создания секционированной таблицы:

1.  Создание функции секционирования, которая определяет, как таблица, использующая эту функцию, будет секционироваться.

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

3.  Создание таблицы или индекса с использованием схемы секционирования.

Несколько таблиц могут использовать одну и ту же схему секционирования.

В этой статье рассматривается Range секционирование таблиц фактов, но эта статья не является полным описанием или учебным пособием по секционированию таблиц. Дополнительную информацию о секционировании таблиц смотрите в SQL Server Books Online.

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

•    Секционированная таблица ведет себя так же, как и целая таблица.

•    Запросы к секционированной таблице корректно обрабатываются.

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

Использование табличных секций для быстрой загрузки данных

Большинство приложений хранилищ данных пытается загрузить все увеличивающиеся объемы данных в течение малого — и все уменьшающегося — времени, отведенного на окно загрузки. Типичный процесс загрузки начинается с получения данных из нескольких систем­источников данных, после чего следуют шаги очистки, трансформации и синтеза данных из этих источников. Приложение управления данными ограничено тем, что должно выполнить весь процесс получения, трансформации и загрузки данных в течение окна загрузки. Обычно бизнес­пользователи системы настойчиво требуют, чтобы время, в течение которого хранилище данных будет недоступно для выполнения их запросов, было минимальным. Шаг записи в приложении управления данными, в течение которого в существующее хранилище данных добавляются новые данные, должен быть рассчитан таким образом, чтобы данные загружались быстро и с минимальным влиянием на работу пользователей.

Для быстрой загрузки данных модель восстановления базы данных должна быть либо Bulk Logged, либо Simple, а таблица должна быть либо пустой, либо содержать данные без индексов. Если эти требования выполняются, возможна загрузка без логирования. В SQL Server 2000 до появления секционированных таблиц эти требования обычно выполнялись только при начальной загрузке данных истории в хранилище данных. Некоторые клиенты с большими хранилищами данных строили квазисекционированную структуру, создавая представление с UNION ALL для объединения отдельных физических таблиц; эти таблицы заполнялись при каждой загрузке без логирования. Такое решение проблемы не было идеальным. Но теперь в SQL Server 2005 секционированные таблицы обеспечивают превосходную функциональность.

В SQL Server 2005 вы не можете выполнить прямую загрузку данных в секцию без логирования. Однако вы можете осуществить загрузку данных в отдельную таблицу, которую мы назовем псевдосекцией. При соблюдении определенных условий вы можете произвести переключение секций в секционированной таблице и включить псевдосекцию в секционированную таблицу, что является операцией над метаданными, которая выполняется очень быстро. Такой подход удовлетворяет двум следующим требованиям:

•    минимизации общего времени загрузки: загрузка в псевдосекцию выполняется без логирования;

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

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

Использование табличных секций для быстрого удаления данных

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

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

При необходимости вы можете переопределить функцию секционирования для объединения всех пустых секций в одну.

Извлечение, преобразование и загрузка данных (Extract, Transformation, and Loading, ETL)

Integration Services (преемник DTS в SQL Server 2000) является нововведением в SQL Server 2005. DTS — популярный компонент SQL Server 2000, но Integration Services был полностью переработан по сравнению с DTS, чтобы стать ETL платформой предприятия. Integration Services предоставляет широкий инструментарий и очень высокую производительность, которые требуются для ETL приложений уровня предприятия. Integration Services является полностью программируемой, интегрируемой и расширяемой системой, что делает ее идеальной ETL платформой.

В следующем разделе и табл. 2, 3, 4, 5 перечислены возможности Integration Services. Более подробную информацию о применении Integration Services для разработки ETL систем смотрите в SQL Server Books Online.

Использование Integration Services разработчиком DTS 2000

Пользователи DTS 2000 разработали набор методов для выполнения сложных операций. Эти методы — особенно написание самообновляющихся паке­тов — в Integration Services не нужны. Используйте переменные и инфраструктуру конфигураций для написания динамических пакетов; не пытайтесь писать самообновляющиеся пакеты.

Элегантная инфраструктура переменных и конфигураций также уменьшает необходимость создания сложных систем вложенных пакетов. Хорошо спроектированный единый пакет обычно удовлетворяет множеству требований; например, пакет может повторно использоваться в различных конфигурациях для загрузки большого количества таблиц измерений в хранилище данных с измерениями. В DTS 2000 сложная сеть DTS пакетов может состоять из 50–100 пакетов; в Integration Services десять пакетов уже считаются сложной сетью пакетов.

Analysis Services

SQL Server 2000 Analysis Services состоит из двух основных дополняющих друг друга функциональных частей: On­Line Analytical Processing (OLAP) и Data Mining. Эти два компонента присутствуют и в Analysis Services 2005 в качестве краеугольных камней аналитических приложений.

Улучшения, внесенные в OLAP функциональность Analysis Services 2005 (табл. 6), можно сгруппировать в две категории:

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

•    Повышение доступности аналитических приложений на предприятии.

Существует два основных пути создания аналитической базы данных:

•    Полностью вручную Начав с источника данных, обычно с реляционного источника данных, определить измерения, кубы, ключевые показатели производительности, расчеты и модели добычи данных (data mining models). Этот путь подходит для клиентов с существующим хранилищем данных или рынком данных. На первом экране мастера Cube Wizard этот вариант называется «Use existing DB/Data Warehouse».

•    Настраиваемый шаблон Начав с шаблона, определить и сгенерировать все приложение, включая реляционные базы данных, пакеты Integration Services и OLAP базу данных Analysis Services. Эти компоненты разработаны и созданы для эффективной совместной работы как единое приложение. Этот вариант подходит для клиентов, которые устанавливают полное решение интеллектуальной обработки данных из шаблона. На первом экране мастера Cube Wizard этот вариант называется «Design BI model without data source».

И для одного, и для другого подхода базовый дизайн системы предполагает известную структуру интеллектуальной обработки данных из одной или нескольких систем­источников данных, поставляющих данные для реляционного хранилища данных с измерениями, которое в свою очередь используется для заполнения базы данных Analysis Services. Однако SQL Server 2005 предоставляет много возможностей для отступления от этой традиционной схемы, исключая различные компоненты или делая их виртуальными. Некоторые альтернативные системы рассматриваются в разделе Универсальная модель измерений (Unified Dimensional Model) далее в этой статье.

Создание пользовательской базы данных из существующего источника данных

Первый метод создания базы данных Analysis Services лучше знаком пользователям SQL Server 2000. Создание базы данных начинается с базы­источника данных любой структуры:

•    база данных с измерениями, структурированная в виде таблиц фактов и таблиц измерений;

•    любая другая структура базы данных, включая нормализованные транзакционные системы.

Возможность получать данные из нормализованной базы данных является серьезным отличием от Analysis Services 2000, который требовал наличия структуры измерений типа «звезда» (star), «снежинка» (snowflake) или «плоская структура» (flattened). Возможность получения данных из нормализованной базы данных упрощает разработку приложений интеллектуальной обработки данных с очень низкой задержкой обновления данных.

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

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

Источники данных и представления источников данных

Первым шагом к построению нового аналитического приложения является создание нового проекта Analysis Services в Business Intelligence Development Studio. После создания пустого проекта вам нужно создать источник данных (Data Source) для соединения с базой­источником данных, которая может находиться в любой поддерживаемой реляционной системе управления базами данных. В источнике данных хранится информация для соединения с базой­источником данных. В представлениях источника данных (Data Source Views) содержится информация о наборе таблиц в базе­источнике данных. Эта информация не ограничена физической структурой таблиц в базе­источнике данных; вы можете добавить такую информацию, как связи, удобные названия для таблиц и столбцов, вычисляемые столбцы и именованные запросы. Представления источника данных могут совместно использоваться различными проектами интеллектуальной обработки данных. Представления источника данных особенно полезны, когда:

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

•    база данных Analysis Services использует данные из множества таких источников данных, как базы данных, серверы, плоские файлы и различные РСУБД;

•    разработчик системы интеллектуальной обработки данных не имеет административных прав на базу­источник данных и не имеет прав на создание физических представлений в базе­источнике данных или на изменение базы­источника данных;

•    разработчику системы интеллектуальной обработки данных требуется работать в автономном режиме, без соединения с базой­источником данных. Задачи дизайна и разработки при этом решаются на представлении источника данных, которое отсоединено от базы­источника данных.

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

Создание измерений и кубов

После создания представления источника данных вы можете создать куб, нажав правой кнопкой мыши кнопку Cubes в панели Solution Explorer и выбрав New cube. Вам будет предложено выбрать автоматическое построение куба (auto build). Если вы решите использовать автоматическое построение куба, вам надо будет выбрать, какой куб создавать — оптимизированный для разворачивания (pivoting) или оптимизированный для отчетности (reporting). Инструмент автоматического построения куба исследует базу данных и количество взаимосвязей между данными в представлении источника данных и определяет таблицы как таблицы фактов, таблицы измерений или промежуточные таблицы между таблицами фактов и измерений, с помощью которых моделируется связь многие­ко­многим. Разница между оптимизацией кубов и измерений для разворачивания или отчетности небольшая. Единственным отличим является попытка при автоматическом построении куба создать иерархические связи между атрибутами в измерении. Так как иерархии очень легко создавать и удалять, выбор типа оптимизации не является критичным.

Если нажать кнопку Finish сразу после этого начального окна, Cube Wizard создаст базу данных, измерения, иерархии, атрибуты и кубы Analysis Services. После этого вы сможете вручную отредактировать сгенерированную мастером структуру, но, в общем случае, структура базы данных получается лучше, если двигаться по всем страницам мастера, делая в них обдуманный выбор.

После экспериментов с Cube Wizard взгляните на Dimension Wizard, который вы можете запустить, нажав правой кнопкой мыши Dimensions на панели So­lu­tion Explorer, и быстро создать с его помощью самые сложные измерения. После тщательного определения больших измерений, таких как Product, Customer и Time, вы можете запустить Cube Wizard и добавить в базу данных эти заранее подготовленные измерения, когда понадобится.

Создание и развертывание

Рассмотренные до этого момента шаги просто создавали определения и структуры измерений и кубов в виде XML­файлов на компьютере, используемом для разработки. Business Intelligence Development Studio и Configuration Manager позволяют управлять процессом создания и развертывания вашего проекта на целевом сервере. По умолчанию целевым сервером разработки является ваш локальный сервер. Вы можете создать альтернативные конфигурации для развертывания в разных средах. Ключевые свойства проекта, такие как название целевого сервера и строки соединения с источниками данных, могут различаться в разных конфигурациях.

Для предварительного просмотра и тестирования кубов и измерений во время процесса разработки создайте и разверните проект на заранее определенном целевом сервере, выбрав Deploy в главном меню Business Intelligence Development Studio. Также вы можете нажать F5 или выбрать Debug Start в главном меню Business Intelligence Development Studio. Будут запущены один или несколько инструментов отладки и просмотра, в зависимости от того, что вы указали при выборе Deploy. В зависимости от контекста процесс развертывания запустит браузер кубов, отладчик MDX­скриптов или браузер ключевых показателей производительности.

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

Возможно, вы захотите добавить в прототип некоторые из наиболее сложных компонентов базы данных Analysis Services — ключевые показатели производительности (Key Performance Indicators), действия (Actions) и вычисления (Calculations). Если ваша база данных будет использоваться разными пользовательскими группами, которым будут нужны разные представления данных, рассмотрите использование проекций (Perspectives) и различных планов безопасности. Если вы планируете развернуть базу данных в разных странах для пользователей, говорящих на разных языках, вы можете добавить локализованные имена объектов с помощью инструмента Translations. И, наконец, в прототипе нужно оценить различные физические конфигурации, такие как разделы (Partitions) и различные варианты упреждающего кеширования (Proactive Caching).

После разработки базы данных Analysis Service объекты базы данных разворачиваются на тестовом, промежуточном или рабочем сервере. Результат проекта, полученный на этапе сборки, может быть использован в качестве входящей структуры в утилите развертывания Analysis Services. Эта утилита помогает развертывать базу данных.

Создание пользовательской базы данных из шаблона

Ранее мы описали основные шаги создания пользовательской базы данных Analysis Services из некоторого источника. Такой подход с помощью Cube Wizard и Dimension Wizard аналогичен стандартному методу создания базы данных в Analysis Services 2000.

Альтернативным методом создания аналитического приложения SQL Server 2005 является выбор «Design BI model without data source» во втором окне мастера Cube Wizard. Путь по окнам мастера аналогичен такому же пути в SQL Server 2000 Accelerator for Business Intelligence. Мастер генерирует полноценное пользовательское приложение из шаблона, включая сложные структуры измерений и аналитические компоненты, а также при необходимости добавляет реляционное хранилище данных и пакеты Integration Services. Шаблоны могут поставляться Microsoft, интеграторами или независимыми поставщиками программного обеспечения.

Вы можете создать идентичные базы данных Ana­lysis Services с помощью мастеров двумя путями — создать базу данных из базы­источника данных или базу данных из шаблона. Первый подход подразумевает, что вы создаете систему полностью вручную. Имена объектов и структур произвольны и создаются на основе имен и структур базы­источника данных. Из шаблона также создается пользовательская база данных, но начальный дизайн создается на основе экс­пертного шаблона для определенной об­ласти.

Многие пользователи комбинируют эти два подхода. Широко распространенным случаем является создание базы данных Analysis Services из существующего источника и генерация измерения Time из шаблона.

Универсальная модель измерений

Analysis Services 2005 размывает границу между реляционной и многомерной OLAP базами данных. Базы данных OLAP всегда давали огромные преимущества аналитическим приложениям. Основными из этих преимуществ являются:

•    превосходная производительность выполнения запросов;

•    аналитическая глубина;

•    легкость использования бизнес­аналитиками.

Но раньше эти преимущества имели свою цену. В базах данных OLAP, включая Analysis Services 2000, было тяжело реализовать:

•    сложные схемы, включая связи многие­ко­многим;

•    детализированную отчетность по большому набору атрибутов;

•    данные с небольшой задержкой обновления.

Скомбинировав лучшие качества традиционных OLAP анализа и реляционной структуры, Analysis Ser­vi­ces 2005 представляет единую универсальную модель измерений, которая удовлетворяет требования, предъявляемые и к одной, и к другой системе. Набор кубов и измерений, определенных в SQL Server 2005, называют универсальной моделью измерений (Unified Dimensional Model, UDM). Преимущества и гибкость UDM внесут большие коррективы в процесс разработки. Раньше архитектор системы интеллектуальной обработки данных выбирал или реляционное, или OLAP хранилище, сравнивая преимущества этих альтернативных инфраструктур и издержки на них. Теперь архитектор разрабатывает универсальную модель измерений и определяет точку между двумя традиционными полюсами, в которой нужно расположить логический дизайн и физическую конфигурацию системы Analysis Services.

Измерения, основанные на атрибутах

Analysis Services 2005 создает куб скорее вокруг атрибутов измерений, чем иерархий измерений. В Analysis Services 2000 измерения создавались в основном на основе иерархий, таких как {Year, Month, Day} или {Country, Region, City}. Такие иерархии требовали строгих связей между уровнями данных. Атрибуты, которые позиционировались как свойства членов и виртуальные измерения, были вторым сортом. Хотя и была возможность добавлять атрибуты в физические измерения, проблемы с производительностью не давали широко использовать эту технологию. Пользователи, знакомые с реляционными структурами, были озадачены таким большим вниманием к иерархиям в базах данных OLAP.

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

Пользователи с опытом работы в OLAP понимают значение строгих иерархий, когда вы можете быть уверены, что Cities строго относятся к Regions и Countries. Такие естественные иерархии существуют и должны определяться, когда это необходимо: производительность выполнения запросов повышается при использовании таких иерархий.

В качестве примера рассмотрим измерение Customer. Реляционная таблица из источника данных имеет восемь столбцов:

•    CustomerKey;

•    CustomerName;

•    Age;

•    Gender;

•    Email;

•    City;

•    Region;

•    Country.

Соответствующее измерение Analysis Services должно иметь семь атрибутов:

•    Customer (ключ типа integer, CustomerName в качестве имени);

•    Age, Gender, Email, City, Region, Country.

Есть естественная иерархия данных по {Country, Region, City, Customer}. Для более удобной навигации разработчик приложения может решить создать вторую иерархию по {Age, Gender}. Бизнес­пользователи не увидят никакой разницы между двумя иерархиями, но естественная иерархия выигрывает из­за индексной структуры — скрытой от пользователей — которая понимает иерархические связи.

Наиболее важные преимущества новой структуры измерений:

•    Измерения не требуется загружать в память. В результате измерения могут быть очень большими (были протестированы измерения с сотнями миллионов членов).

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

•    Устраняется дублированная информация в измерениях; измерения становятся меньше.

•    Производительность обработки измерений повысилась, так как ядро использует возможности параллелизма при обработке.

Типы измерений

В Analysis Services 2000 есть два типа измерений: Re­gu­lar hierarchical и Parent­child. В Analysis Services 2005 добавлены новые важные структуры измерений. Некоторые из этих структур имеют странные названия, но эти названия общеприняты в литературе по интеллектуальной обработке данных.

•    Ролевое измерение (Role Playing) У измерения есть несколько ролей в зависимости от контекста. Например, измерение [Time] может быть использовано для [Order Date] и [Ship Date]. В SQL Ser­ver 2005 ролевое измерение сохраняется один раз и после этого может использоваться множество раз. При этом минимизируются дисковое пространство и время обработки.

•    Измерение факта (Fact) Измерение факта, или «вырожденное» измерение имеет связь один­к­одному с такими фактами, как номер транзакции. Измерение факта как таковое используется не для анализа, а для идентификации. Например, для нахождения определенной транзакции или определения транзакций, составляющих агрегатную ячейку.

•    Ссылочное измерение (Reference) Измерение связано с таблицей фактов не напрямую, а через другое измерение. Типичным примером является ссылочное измерение [Geography], которое связано и с измерением [Customer], и с измерением [Sales Force]. Ссылочное измерение может быть получено из провайдера данных и включено в кубы без изменения фактов.

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

•    Измерение многие­ко­многим (Many to Many) Такие измерения иногда называют измерениями с множеством значений. В большинстве измерений один факт соединен с одним и только одним членом измерения. Измерения многие­ко­многим соединяют один факт с множеством членов измерения. Например, у клиента банка есть множество счетов (Checking, Savings); у счета есть множество клиентов (Mary Smith; John Smith). Измерение [Customer] имеет множество членов, которые относятся к одной транзакции по счету. В SQL Server 2005 измерения многие­ко­многим поддерживают сложную аналитику, когда измерения не связаны напрямую с таблицей фактов, и расширяют модель измерений за пределы классической схемы типа «звезда».

Группы мер и проекции

В Analysis Services 2005 представлены группы мер (Measure Groups) и проекции (Perspectives) для упрощения разработки и развертывания аналитической базы данных. В Analysis Services 2000 пользователям было необходимо создавать множество физических кубов. Каждый куб соответствовал определенному набору измерений, а также обычно и определенной реляционной таблице фактов. Виртуальные кубы объединяли множество таблиц фактов в удобной и прозрачной для пользователя форме, но были излишне сложны для разработчика при их создании.

В SQL Server 2005 наиболее типичной ситуацией будет ситуация, когда имеется один физический куб, содержащий одну или более группу мер. Факты в группе мер имеют определенную гранулярность, определяемую пересечением иерархий измерений. Запросы при этом автоматически перенаправляются в нужные группы мер. На физическом уровне в группах мер определяются разделы, аналогичные разделам в Analysis Services 2000.

Большое приложение может предоставлять пользователю большое количество измерений, групп мер и самих мер, что может затруднить навигацию по ним. Проекция, создаваемая в Cube Editor во вкладке Perspectives, создает «представление» части куба. Чтобы обеспечить необходимый уровень персонализации, роль доступа может быть связана с соответствующим набором проекций.

Мы ожидаем, что большинство баз данных Analysis Services 2005 будет состоять из одного куба с множе­ством групп мер и множеством проекций.

Другие важные улучшения в структурах фактов куба и производительности выполнения запросов:

•    Меры могут содержать значение «null»; в SQL Server 2000 меры со значением «null» обрабатывались как нули.

•    Производительность выполнения запросов для мер Distinct Count повышена на несколько порядков для кубов с правильно определенными разделами.

•    Доступ к различным системам управления базами данных обеспечивается расширяемой картриджной инфраструктурой. Картридж для СУБД определяет, как оптимизировать команды SQL для выполнения запросов к реляционной структуре и записи в нее. Картриджи для дополнительных реляционных СУБД можно легко добавить; картридж реализован в виде XSL­файла.

Расчеты и аналитика

Одним из наиболее сильных аргументов в пользу использования такого аналитического сервера, как Analysis Services, является его возможность выполнять сложные вычисления централизованно. Analysis Services всегда обеспечивал сложную аналитику, но некоторые сложные концепции было трудно реализовать.

Одной из таких концепций является мера с частичным суммированием (semi­additive measure). Большинство обычных мер, таких как [Sales], производит агрегацию по всем измерениям: [Total Sales] за весь период — это продажи всех товаров всем клиентам за все время. Напротив, мера с частичным суммированием по некоторым измерениям должна производить суммирование, а по некоторым — нет. Наиболее типичным случаем является остаток, например количество единиц товара на складе. Агрегированный остаток по вчерашнему и сегодняшнему дням, конечно, не равен сумме вчерашнего и сегодняшнего остатков. Вместо этого результатом будет последний остаток, хотя иногда бывает и первый. В Analysis Services 2000 вы были бы должны определить сложное вычисление MDX для получения корректной меры. В Analysis Services 2005 начальный и конечный остатки являются встроенными агрегатными типами.

Меры Distinct Count также значительно улучшены в SQL Server 2005. Меру Distinct Count теперь можно определять на строковых данных, а также можно создавать запросы, выполняющие Distinct Count на произвольном наборе данных. SQL Server 2000 выполнял Distinct Count только на заранее определенной иерархической структуре.

Мастер Time Intelligence создает измерение вычислений времени, содержащее вычисления, сравнивающие некоторый период с предыдущим периодом, скользящие средние значения и другие часто используемые конструкции вычислений времени.

MDX-скрипты

Язык MultiDimensional Expressions (MDX) является очень мощным языком, который использовался в Analysis Services 2000 для определения вычислений и правил обеспечения безопасности. Но MDX является настолько же сложным, насколько и мощным. Ana­lysis Services 2005 предоставляет новую модель вычислений в виде MDX­скриптов (MDX Scripts), которая использует упрощенные конструкции и синтаксис.

MDX также является языком запросов к системам Analysis Services. Такие инструменты выполнения запросов, как Excel Pivot Tables, генерируют запросы MDX на основе пользовательских действий drag and drop. Такое использование языка MDX не относится к MDX­скриптам; MDX­скрипты используются для серверных объектов, таких как вычисляемые члены и вычисления ячеек, а не для пользовательских за­просов.

После создания куба в Analysis Services 2005 этот куб содержит только структуру, данных в нем нет. MDX­скрипт является частью такой структуры. Одна команда MDX­скрипта всегда определена по умолчанию для выполнения агрегирований по умолчанию. Команда MDX­скрипта по умолчанию содержит единственное выражение: Calculate;

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

MDX­скрипт может содержать следующие команды, разделенные точками с запятой:

•    выражения области видимости для ограничения области видимости выражения;

•    задание формул и значений;

•    определения вычисляемых членов;

•    определения именованных наборов данных.

В пользовательском интерфейсе для разработки кубов в Business Intelligence Development Studio скрипты MDX — вычисляемые члены и именованные наборы — создаются в окне представления Cal­cu­lations. MDX­скрипт можно просматривать в окне по умолчанию Calculations Form, что позволяет проверить корректность синтаксиса, или в окне Calculations Script, в котором MDX­скрипт представлен в виде набора команд, разделенных точками с запятой. Вы можете переключаться между этими окнами, однако для работы окна Form на данный момент требуется, чтобы во всем скрипте был корректный синтаксис.

Существует несколько основных ключевых особенностей MDX­скриптов:

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

•    У вычисления может быть ограничена область видимости: выражение SCOPE позволяет вам определить одно или более вычислений на определенной области куба. Например:

SCOPE ([Customers].[Country].[Country].[USA]);
  [Measures].[Sales] = 100;
END SCOPE;

•    Области видимости могут быть вложенными.

•    Вычисление может кешироваться: ключевое слово CACHE указывает, что результат вычисления скрипта должен быть сохранен на диске, а не рассчитан в режиме реального времени. Кешированные вычисления обеспечивают очень высокую производительность для запросов на больших кубах, которые содержат множество сложных вычислений. Когда входные данные в кешированное вычисление изменяются, оно удаляется и пересоздается.

•    MDX­скрипты можно отлаживать. Вы можете двигаться по MDX­скрипту пошагово, видя состояние куба на каждом шаге.

Хранимые процедуры

В Analysis Services 2005 представлены хранимые процедуры для расширения возможностей, предоставляемых пользовательскими функциями (user defined functions, UDFs). Хранимая процедура может быть написана на любом языке общеязыковой среды разработки, таком как C++, Visual Basic или C. Хранимые процедуры упрощают разработку базы данных, так как общий код пишется один раз, хранится в одном месте, а затем повторно используется в других хранимых процедурах, вычислениях и пользовательских запросах.

Существует два типа хранимых процедур:

•    Хранимые процедуры функций MDX похожи на любую другую MDX­функцию и обеспечивают механизм простого расширения языка MDX.

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

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

Ключевые показатели производительности

В Analysis Services 2005 представлена система ключевых показателей производительности (Key Per­for­mance Indicator, KPI) для определения на сервере вычислений, необходимых для оценки эффективности работы вашего бизнеса. Эти KPI могут выводиться в отчетах, порталах и электронных досках сообщений, с помощью API доступа к данным и инструментов Microsoft и сторонних производителей.

Разные авторы и вендоры используют понятие «KPI» для обозначения разных концепций. В Microsoft SQL Server Analysis Services 2005 понятие KPI точно описывается следующими четырьмя шагами:

•    Значение, которое должно быть получено: физическая мера, такая как Sales, вычисляемая мера, такая как Profit, или вычисление, которое было определено в KPI.

•    Целевое значение: значение (или выражение MDX, выдающее значение), которое определяет цель для меры.

•    Статус: выражение MDX для оценки текущего статуса значения в виде нормализованного значения в диапазоне от ­1 (очень плохо) до +1 (очень хорошо).

•    Тенденция: выражение MDX для оценки текущей тенденции значения. Становится ли значение лучше или хуже относительно целевого значения?

Пример нескольких KPI, выведенных на веб­странице, показан на рис. 1 (см. цветное изображение на прилагаемом компакт­диске).

Табл. 1.
Обзор компонентов системы интеллектуальной обработки данных и связанных с ними компонентов Microsoft SQL Server 2000 и SQL Server 2005

Табл. 2. Разработка пакетов

Табл. 3. Поток управления

Табл. 4. Поток данных

Табл. 5. Разработка и отладка

Табл. 6

Удачной вам даты

Некоторые вещи настолько часто встречаются в нашей повседневной жизни, что мы просто воспринимаем их как данное; они надежно работают день за днем, и мы даже не задумываемся о том, что происходит «за кулисами». В этой статье Александр Козак описывает некоторые из своих открытий, связанных с датами.

Не так давно один из наших руководителей проекта спросил меня, есть ли в SQL Server функция, которая возвращала бы только дату — без временной части. Конечно же, я знал ответ, поскольку все время писал обходной код на SQL примерно так:

SELECT sampleDate = CONVERT(VARCHAR(50), Date1, 101)
FROM tbl_SampleDate

или так:

SELECT sampleDate = CAST(DATEPART(mm, Date1)
AS varchar(50)) + '/' + CAST(DATEPART(dd, Date1)
AS varchar(50)) + '/' + CAST(DATEPART(yy, Date1)
AS varchar(50))
FROM tbl_SampleDate

Это выглядит уродливо и не способствует скорости обработки, но выполняет свою задачу. Однако, если вам необходимо проделывать это часто или в очень больших базах данных, как делали мы, то, скорее всего, придется искать пути оптимизации кода.

(Возврат на основную страницу)