Главная » Microsoft SQL Server, Базы данных » Хранилища данных в SQL Server 2005

0

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

Схема “звезда”

— это стандартный подход к структурированному хранению реляционных данных OLAP. В его основе лежат понятия измерений и мер. Измерение — это разделение на категории, или группировка данных, а мера — консолидируемое значение. Например, в задаче консолидации объема продаж по кварталам и подразделениям мерой является объем продаж, а измерениями — квартал и подразделение.

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

После этого производственные потребности обеспечат базис для построения схемы “звезда” или “снежинка”, являющейся каркасом хранилища (рис. 43.1).

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

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

Каждая таблица измерений состоит из первичного ключа, по которому строится отношение с таблицей фактов, и одного или нескольких атрибутов, разбивающих данные по категориям данного измерения. Например, измерение заказчика может содержать атрибуты имени, электронного адреса и индекса. В общем, измерение представляет денормализацию данных в системе OLTP. Например, измерение заказчика в AdventureWorksDW управляется, среди прочего, из таблиц Sales . Individual и Person. Contact базы данных AdventureWorks, а также полей, выделенных из столбца XML, описывающего демографические данные.

Иногда имеет смысл ограничить денормализацию, организуя зависимость одной таблицы измерений от другой, таким образом, изменяя схему “звезда” на схему “снежинка”. В качестве примера на рис. 43.2 показано, как измерение товаров базы данных AdventureWorks было организовано в схему “снежинка”. Информация категорий товаров и подкатегорий могла быть включена непосредственно в таблицу DimProduct, но вместо этого для создания системы категорий было создано несколько таблиц. Схема “снежинка” полезна для сложных измерений, в которых в противном случае возникают проблемы целостности, такие как назначение подкатегорий категориям (см. рис. 43.2), а также для больших измерений, где поднимается вопрос объема.

Обычно схема “снежинка” непривлекательна, так как вносит сложность и замедляет операции SQL, однако служба анализа устраняет большинство этих проблем. Если измерение можно сделать более цельным, используя схему “снежинка”, поступайте именно так, за исключением следующих случаев:

Рис. 43.2. Измерение со схемой “снежинка ”

Рис. 43.1. Простая схема “звезда ”

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

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

Единообразие

Определение схемы “звезда” позволяет быстро выполнять запросы OLAP к хранилищу данных, однако повышенные требования к единообразию делают необходимым соблюдение следующих правил.

?               При загрузке данных в хранилище некорректные и пустые значения должны замещаться своими печатными эквивалентами. Это позволит единоразово исследовать

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

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

?               Избегайте использования ключей OLTP для связывания таблиц фактов и измерений, несмотря на то, что это часто достаточно удобно. Подумайте о схеме “звезда”, содержащей финансовые данные, когда обновляется пакет бухучета с изменением при этом всех идентификаторов заказчиков. Если отношения в хранилище основать на столбце идентичности, то любые изменения в базе OLTP могут быть отражены небольшими изменениями данных в измерении заказчиков. Если же будет использован ключ OLTP, то придется преобразовать всю таблицу фактов.

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

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

Загрузка данных

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

Загрузка измерений

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

INSERT INTO Warehouse.dbo.dimProduct (ProductCode, ProductName)

SELECT stage.Code, stage.Name FROM Staging.dbo.Products stage LEFT OUTER JOIN Warehouse.dbo.dimProduct dim ON stage.Code=dim.ProductCode

WHERE dim.ProductCode is NULL

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

INSERT INTO Warehouse.dbo.dimOrderStatus (OrderStatusID, OrderStatusDesc) SELECT DISTINCT o.status, ISNULL(mos.Description, 1 Неизвестно 1)

FROM Staging.dbo.Orders о

LEFT OUTER JOIN Warehouse.dbo.dimOrderStatus os ON о.status=os.OrderStatusID LEFT OUTER JOIN Staging.dbo.map_order_status mos ON o.status = mos.Number WHERE os.OrderStatusID is NULL

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

INSERT INTO Warehouse.dbo.dimProduct (ProductCode, ProductName)

SELECT stage.Code, MAX(stage.Name)

FROM Staging.dbo.Orders stage

LEFT OUTER JOIN Warehouse.dbo.dimProduct dim ON stage.Code=dim.ProductCode WHERE dim.ProductCode is NULL

Загрузка таблиц фактов

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

?               естественный ключ, основанный на данных измерения (например, код товара);

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

Суррогатные ключи используют чаще всего; они отлично адаптируются к данным, получаемым из множества источников, однако при загрузке каждый из суррогатных ключей требует наличия объединения. Для примера предположим, что наша простая таблица фактов связана с таблицами измерений dimTime, dimProduct и dimCustomer. Если dimCustomer и DimProduct используют суррогатные ключи, загрузка может выглядеть так:

INSERT INTO Warehouse.dbo.factOrder

(OrderDate, CustomerlD, ProductID, OrderAmount)

SELECT o.Date, с.CustomerlD, p.ProductID, ISNULL(Amount,0)

FROM Staging.dbo.Orders о

INNER JOIN Warehouse.dbo.dimCustomer с ON o.CustCode = с.CustomerCode INNER JOIN Warehouse.dbo.dimProduct p ON o.Code = p.ProductCode

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

Процесс ETL (извлечения, преобразования и загрузки) состоит из большого числа относительно простых действий, модифицируемых по мере изменения источника данных. Централизуйте логику процесса ETL, насколько это возможно, документируйте ее неочевидные аспекты и осуществляйте строгий контроль. Если процесс потребует сопровождения, то это упростит раскрытие всех компонентов и истории их изменения. В этом отношении лучшими инструментами являются Integration Services и SourceSafe.

Знакомство со службой анализа

Лучший способ познакомиться со службой анализа и хранилищами состоит в использовании утилиты Business Intelligence Development Studio (BEDS) для создания базы данных службы анализа и ассоциированных таблиц на базе структур, определенных в учебной базе данных Ad venture Works. Начнем с идентификации и создания базы данных хранилища SQL Server. Затем откроем утилиту BEDS и создадим новый проект службы анализа.

Щелкните правой кнопкой мыши на узле Cubes в Solution Explorer и выберите в контекстном меню пункт New Cube. На первой странице открывшегося мастера кубов (Select Build Method) выберите пункт Build cube without data source (Построение куба без источника данных), установите параметр Use a Cube Template и выберите из списка шаблон, соответствующий вашей редакции SQL Server. Пройдите все остальные страницы мастера, принимая предложенные по умолчанию параметры, за исключением страницы Define Time Period, на которой отметьте несколько дополнительных периодов (например, Year/Quarter/Month), чтобы сделать измерение времени более интересным.

На заключительной странице мастера выберите опцию Generate Schema Now для запуска мастера генерации схем. Пройдите по страницам мастера, оставив без изменения предложенные по умолчанию параметры и указав только место размещения схемы. По завершении работы мастера будут созданы все объекты службы анализа и связанные с ними. Даже если сгенерированная схема не в полной мере удовлетворяет текущим потребностям, она представит нам довольно интересный образец.

Мастер кубов позволяет выполнить множество настроек определенной шаблоном структуры, и все необходимые изменения можно выполнить в проекте службы анализа вручную. Регенерация схемы может быть выполнена в любое время щелчком правой кнопкой мыши на проекте в Solution Explorer и выбором в контекстном меню пункта Generate Relation Schema.

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

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии