29

Методические указания «Аналитические запросы »

по дисциплине «Постреляционные базы данных»

Виноградова М.В., к.т.н., доцент кафедры ИУ5

Томак К.С.

Учебно-методические материалы «Аналитические запросы » представляют собой методические указания к лабораторным работам по дисциплине «Постреляционные базы данных».

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

Оглавление

Цель работы 1

Время выполнения 1

Теоретическая часть 2

Кубы данных 2

Меры и измерения куба 5

Агрегаты 5

Перспективы 6

Практическая часть – Пример создания куба 7

Компоненты куба 7

Создание измерения 8

Определение куба и его свойств 14

Просмотр свойств куба и измерений в конструкторе кубов 19

Пример задания 26

Вопросы для самопроверки: 28

Требования к отчету 29

Список литературы 29

Цель работы

1. Изучить возможности построения и применения OLAP-систем.

2. Освоить методы определения источников данных, измерений и размерностей куба, построения перспектив.

3. Получить навыки работы с инструментальными средствами среды MS SQL Analysis Services и Server Business Intelligence Development Studio.

Время выполнения

Время выполнения лабораторных работ 2 часа.

Теоретическая часть

Кубы данных

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

Технология комплексного многомерного анализа данных получила название OLAP (On-Line Analytical Processing). OLAP — это ключевой компонент организации хранилищ данных.

В качестве примера реляционной базы данных, который мы будем использовать для иллюстрации принципов OLAP, воспользуемся базой данных Northwind, входящей в комплекты поставки Microsoft SQL Server или Microsoft Access и представляющей собой типичную базу данных, хранящую сведения о торговых операциях компании, занимающейся оптовыми поставками продовольствия. К таким данным относятся сведения о поставщиках, клиентах, компаниях, осуществляющих доставку, список поставляемых товаров и их категорий, данные о заказах и заказанных товарах, список сотрудников компании.

Для рассмотрения концепции OLAP воспользуемся представлением Invoices и таблицами Products и Categories из базы данных Northwind, создав запрос, в результате которого получим подробные сведения о всех заказанных товарах и выписанных счетах:

SELECT dbo.Invoices.Country,

dbo.Invoices.City,

dbo.Invoices.CustomerName,

dbo.Invoices.Salesperson,

dbo.Invoices.OrderDate,

dbo.Categories.CategoryName,

dbo.Invoices.ProductName,

dbo.Invoices.ShipperName,

dbo.Invoices.ExtendedPrice

FROM dbo.Products INNER JOIN

dbo.Categories ON dbo.Products.CategoryID = dbo.Categories.CategoryID INNER

JOIN

dbo.Invoices ON dbo.Products.ProductID = dbo.Invoices.ProductID

Этот запрос обращается к представлению Invoices, содержащему сведения обо всех выписанных счетах, а также к таблицам Categories и Products, содержащим сведения о категориях продуктов, которые заказывались, и о самих продуктах соответственно. В результате этого запроса мы получим набор данных о заказах, включающий категорию и наименование заказанного товара, дату размещения заказа, имя сотрудника, выписавшего счет, город, страну и название компании-заказчика, а также наименование компании, отвечающей за доставку. Результат обращения к этому представлению приведен на рис. 1.

Рис. 1. Результат обращения к представлению Invoices1

Какие агрегатные данные мы можем получить на основе этого представления? Обычно это ответы на вопросы типа:

• Какова суммарная стоимость заказов, сделанных клиентами из Франции?

• Какова суммарная стоимость заказов, сделанных клиентами из Франции и доставленных

компанией Speedy Express?

• Какова суммарная стоимость заказов, сделанных клиентами из Франции в 1997 году и

доставленных компанией Speedy Express?

Результатом любого из перечисленных выше запросов является число. Если в первом из запросов заменить параметр ‘France’ на ‘Austria’ или на название иной страны, можно снова выполнить этот запрос и получить другое число. Выполнив эту процедуру со всеми странами, мы получим следующий набор данных:

Country SUM (ExtendedPrice)	Argentina 7327.3
Austria 110788.4	Belgium 28491.65
Brazil 97407.74	Canada 46190.1
Denmark 28392.32	Finland 15296.35
France 69185.48	Germany 209373.6
… …

Полученный набор агрегатных значений (в данном случае — сумм) может быть интерпретирован как одномерный набор данных.

Теперь обратимся ко второму из приведенных выше запросов, который содержит два условия в предложении WHERE. Если выполнять этот запрос, подставляя в него все возможные значения параметров Country и ShipperName, мы получим двухмерный набор данных следующего вида (ниже показан фрагмент):

ShipperName

Country	Federal Shipping	Speedy Express	United Package
Argentina	1 210.30	1 816.20	5 092.60
Austria	40 870.77	41 004.13	46 128.93
Belgium	11 393.30	4 717.56	17 713.99
Brazil	16 514.56	35 398.14	55 013.08
Canada	19 598.78	5 440.42	25 157.08
Denmark	18 295.30	6 573.97	7 791.74
Finland	4 889.84	5 966.21	7 954.00
France	28 737.23	21 140.18	31 480.90
Germany	53 474.88	94 847.12	81 962.58
…	…	…	…

Такой набор данных называется сводной таблицей (pivot table) или кросс-таблицей (cross table, crosstab). Создавать подобные таблицы позволяют многие электронные таблицы и настольные СУБД — от Paradox для DOS до Microsoft Excel 2000.

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

Ячейки куба, показанного на рис. 2, содержат агрегатные данные, соответствующие находящимся на осях куба значениям параметров запроса в предложении WHERE. Можно получить набор двухмерных таблиц с помощью сечения куба плоскостями, параллельными его граням (для их обозначения используют термины cross-sections и slices). Очевидно, что данные, содержащиеся в ячейках куба, можно получить и с помощью соответствующего запроса с предложением GROUP BY. Кроме того, некоторые электронные таблицы (в частности, Microsoft Excel 2000) также позволяют построить трехмерный набор данных и просматривать различные сечения куба, параллельные его грани, изображенной на листе рабочей книги (workbook).

Если в предложении WHERE содержится четыре или более параметров, результирующий набор значений (также называемый OLAP-кубом) может быть 4-мерным, 5-мерным и т.д.

Рис. 2. Трехмерный набор агрегатных данных

Т.о. куб — это набор связанных мер и измерений, которые используются для анализа данных.

Меры и измерения куба

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

• Измерение — это группа атрибутов, которая представляет область интереса, относящегося к мерам в кубе, и которые используются для анализа мер в кубе. Например, измерение «Покупатель» может включать атрибуты «Имя покупателя», «Пол покупателя» и «Город покупателя», который может разрешить меры в кубе для анализа по «Имени покупателя», «Полу покупателя» и «Городу покупателя». Атрибуты собираются из столбцов в одной или более таблицах-источниках. Атрибуты внутри каждого измерения могут быть организованы в иерархии для предоставления путей анализа. 

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

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

Агрегаты

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

Кубы в технологии OLAP являются способом организации сводных данных в многомерные структуры. Измерения и их иерархии атрибутов отражают запросы, которые могут быть применены к кубу. Агрегаты хранятся в многомерной структуре в ячейках с координатами, заданными измерениями. Например, вопрос «Каков объем продаж продукта X в 1998 году в Северо-Западном регионе?» затрагивает три измерения («Продукт», «Время» и «География») и одну меру («Продажи»). Значение ячейки в кубе, расположенной в заданных координатах («Продукт X», «1998», «Северо-запад»), представляет собой ответ, одиночное числовое значение.

Перспективы

В службах Microsoft SQL Server 2005 Analysis Services (SSAS) можно использовать перспективу для упрощения восприятия куба в службах Analysis Services. Перспектива определяет просматриваемое подмножество куба, которое предоставляет точки зрения на данные куба, учитывающие особенности предприятия и приложения. Перспектива контролирует видимость объектов, содержащихся в кубе. В перспективе можно отображать или скрывать следующие объекты:

• Измерения

• Атрибуты

• Иерархии

• Группы мер

• Меры

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

• Вычисления (вычисляемые элементы, именованные наборы и команды сценариев)

• Действия

Например, в кубе Adventure Works образца базы данных Adventure Works DW служб Analysis Services содержится одиннадцать групп мер и двадцать одно различное измерение куба, представляющие собой продажи, прогнозы продаж и финансовые данные. Клиентское приложение может напрямую указывать весь куб, но такая точка зрения может быть перегружена для пользователя, пытающегося извлечь основные сведения о прогнозах продаж. Вместо этого пользователь может воспользоваться перспективой Цели продаж, чтобы ограничить представление куба Adventure Works объектами, относящимися к прогнозированию продаж.

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

Практическая часть – Пример создания куба

Службы Microsoft SQL Server Analysis Services используют как серверные, так и клиентские компоненты для предоставления приложениям бизнес-аналитики функций оперативной аналитической обработки (OLAP) и интеллектуального анализа данных. Серверный компонент служб SSAS реализован в виде службы Microsoft Windows. Службы SQL Server Analysis Services поддерживают работу нескольких экземпляров на одном компьютере, при этом каждый экземпляр служб SSAS реализован как отдельный экземпляр службы Windows.

Клиенты обмениваются данными со службами SSAS с помощью общедоступного стандарта XML для аналитики ( XMLA), который представляет собой протокол на базе SOAP для выполнения команд и получения ответов и предоставляется в виде веб-службы. Поэтому каждый экземпляр SSAS является Web-сервисом.