Учебная дисциплина «Хранилища данных» для студентов специальности Бизнес- информатика профиля «Архитектура предприятия» Лекция 11 СОЗДАНИЕ OLAP-КУБОВ С ИСПОЛЬЗОВАНИЕМ MICROSOFT SQL SERVER Учебные вопросы: 1.Создание OLAP-кубов с использованием Microsoft SQL SERVER 2.Особенности показателей в кубах данных Microsoft SQL SERVER Учебные вопросы: 1.Создание OLAP-кубов с использованием Microsoft SQL SERVER 2.Особенности показателей в кубах данных Microsoft SQL SERVER

Цель лекции – сформировать представление у студентов о средствах и порядке создания OLAP-кубов с использованием Microsoft SQL SERVER.

Литература 1.Информационные технологии управления : Учебник / Б. В. Черников. - М. : Форум, 2008 ; М. : Инфра-М, – 351 с.. - (Высшее образование). (ГРИФ) 2.Советов Б.Я., Цехановский В.В Информационные технологии: Учебник для вузов / - 3-е изд., стереотип. - М. : Высшая школа, [2] с. : ил, табл. - Библиогр.: с ISBN : УДК 002.6(075.8)004(075.8). 3.Консалтинг: от бизнес-стратегии к корпоративной информационно-управляющей системе / Г. Н. Калянов. - М. : Горячая линия-Телеком, с 4.Жуковский О.И. Информационные технологии: Учебное пособие. Рекомендовано СИБРУМЦ для межвузовского использования в качестве учебного пособия. – Томск : ТУСУР, с. : ил. - Библиогр.: с ISBN Проектирование реляционных хранилищ данных [Текст] : справочное издание / В. Е. Туманов, С. В. Маклаков. - М. : ДИАЛОГ- МИФИ, с 1.Информационные технологии управления : Учебник / Б. В. Черников. - М. : Форум, 2008 ; М. : Инфра-М, – 351 с.. - (Высшее образование). (ГРИФ) 2.Советов Б.Я., Цехановский В.В Информационные технологии: Учебник для вузов / - 3-е изд., стереотип. - М. : Высшая школа, [2] с. : ил, табл. - Библиогр.: с ISBN : УДК 002.6(075.8)004(075.8). 3.Консалтинг: от бизнес-стратегии к корпоративной информационно-управляющей системе / Г. Н. Калянов. - М. : Горячая линия-Телеком, с 4.Жуковский О.И. Информационные технологии: Учебное пособие. Рекомендовано СИБРУМЦ для межвузовского использования в качестве учебного пособия. – Томск : ТУСУР, с. : ил. - Библиогр.: с ISBN Проектирование реляционных хранилищ данных [Текст] : справочное издание / В. Е. Туманов, С. В. Маклаков. - М. : ДИАЛОГ- МИФИ, с

Вопрос 1. Создание OLAP-кубов с использованием Microsoft SQL SERVER Как и измерение, куб можно создать с помощью соответствующего мастера или непосредственно в редакторе кубов. В качестве примера создадим куб, основанный на нашем хранилище данных Northwind_Mart и использующий созданные выше измерения. Запустить мастер создания кубов можно командой New Cube | Wizard из контекстного меню элемента Cubes. Первое, что следует сделать после запуска мастера, выбрать таблицу фактов для будущего куба. В нашем случае это таблица Sales_Fact. Далее из таблицы фактов следует выбрать одно или несколько полей, на основе которых вычисляются меры куба (то есть поля, данные которых подлежат суммированию либо обработке с помощью других агрегатных функций). Выберем поля Line Item Total, Line Item Quantity и Line Item Discount (рис. 1). Как и измерение, куб можно создать с помощью соответствующего мастера или непосредственно в редакторе кубов. В качестве примера создадим куб, основанный на нашем хранилище данных Northwind_Mart и использующий созданные выше измерения. Запустить мастер создания кубов можно командой New Cube | Wizard из контекстного меню элемента Cubes. Первое, что следует сделать после запуска мастера, выбрать таблицу фактов для будущего куба. В нашем случае это таблица Sales_Fact. Далее из таблицы фактов следует выбрать одно или несколько полей, на основе которых вычисляются меры куба (то есть поля, данные которых подлежат суммированию либо обработке с помощью других агрегатных функций). Выберем поля Line Item Total, Line Item Quantity и Line Item Discount (рис. 1).

Рисунок 1 - Выбор мер куба

Следующим шагом будет выбор коллективных измерений, используемых в этом кубе, а также создание недостающих частных измерений. Выберем коллективные измерения Employee.PC, Employee.Regular, Time.YQMD, Product и Customer. Кроме того, добавим новое измерение Shipper, нажав кнопку New Dimension в диалоговой панели выбора измерений. Это приведет к запуску уже знакомого нам мастера создания измерений; в последней из диалоговых панелей мастера в этом случае мы можем выбрать, каким будет создаваемое измерение частным или коллективным (рис. 2).

Рисунок 2 - Выбор мер куба

Рисунок 3 - Редактор кубов

Теперь попробуем добавить к нашему кубу вычисляемые значения, то есть значения, которые не хранятся в самом кубе, а вычисляются «на лету». Типичным примером такого значения может быть дополнительная мера, вычисленная на основе уже имеющихся. При вычислениях можно использовать как функции из библиотеки, входящей в состав Analysis Services, так и выражения VBA, а также собственные библиотеки функций (последние следует зарегистрировать в Analysis Services). Для создания вычисляемых выражений следует выбрать раздел Calculated Members и из контекстного меню выбрать опцию New Сalculated Member. После этого будет запущен построитель выражений (Calculated Member Builder), в котором можно создавать и редактировать выражения, перетаскивая мышью имена измерений и их уровней, мер, имена функций. Например, перенесем в поле для выражения имена мер [Measures].[Line Item Total] и [Measures].[Line Item Discount], поставим между ними знак вычитания, а в качестве значения Member Name ведем Discounted Total (рис. 4). Теперь попробуем добавить к нашему кубу вычисляемые значения, то есть значения, которые не хранятся в самом кубе, а вычисляются «на лету». Типичным примером такого значения может быть дополнительная мера, вычисленная на основе уже имеющихся. При вычислениях можно использовать как функции из библиотеки, входящей в состав Analysis Services, так и выражения VBA, а также собственные библиотеки функций (последние следует зарегистрировать в Analysis Services). Для создания вычисляемых выражений следует выбрать раздел Calculated Members и из контекстного меню выбрать опцию New Сalculated Member. После этого будет запущен построитель выражений (Calculated Member Builder), в котором можно создавать и редактировать выражения, перетаскивая мышью имена измерений и их уровней, мер, имена функций. Например, перенесем в поле для выражения имена мер [Measures].[Line Item Total] и [Measures].[Line Item Discount], поставим между ними знак вычитания, а в качестве значения Member Name ведем Discounted Total (рис. 4).

Рисунок 4 - Редактор вычисляемых выражений

Процесс создания куба на этом не завершен мы создали его определение, но не производили никаких вычислений. Прежде чем произвести вычисления, напомним, что существует несколько способов хранения агрегатных данных (мы уже обсуждали эти способы в предыдущих статьях данного цикла). Для данного примера вполне подойдет хранение всех данных в многомерной базе данных (MOLAP), так как объем исходных данных невелик. Однако в других случаях следует оценить, какой способ хранения наиболее выгоден для данной задачи. Еще один вопрос, который следует решить при создании многомерного хранилища данных сколько агрегатов следует хранить? Агрегаты это заранее вычисленные агрегатные данные, соответствующие ячейкам куба. Чем их больше, тем быстрее выполняются запросы к многомерному хранилищу и тем больше объем самого хранилища. Поэтому в общем случае требуется некое их количество, позволяющее осуществить разумный баланс между компактностью и производительностью. Процесс создания куба на этом не завершен мы создали его определение, но не производили никаких вычислений. Прежде чем произвести вычисления, напомним, что существует несколько способов хранения агрегатных данных (мы уже обсуждали эти способы в предыдущих статьях данного цикла). Для данного примера вполне подойдет хранение всех данных в многомерной базе данных (MOLAP), так как объем исходных данных невелик. Однако в других случаях следует оценить, какой способ хранения наиболее выгоден для данной задачи. Еще один вопрос, который следует решить при создании многомерного хранилища данных сколько агрегатов следует хранить? Агрегаты это заранее вычисленные агрегатные данные, соответствующие ячейкам куба. Чем их больше, тем быстрее выполняются запросы к многомерному хранилищу и тем больше объем самого хранилища. Поэтому в общем случае требуется некое их количество, позволяющее осуществить разумный баланс между компактностью и производительностью.

Для определения количества агрегатов и их вычисления следует запустить Storage Design wizard мастер создания многомерного хранилища. Для этого в редакторе кубов следует выбрать пункт меню Tools | Design Storage. В первой диалоговой панели следует указать способ хранения данных MOLAP, ROLAP или HOLAP (в нашем примере мы выберем MOLAP). Затем выбрать, какова должна быть производительность при выполнении запросов (либо будущий максимальный объем хранилища). После этого можно нажать на кнопку Start и получить зависимость производительности от объема хранилища (рис. 5). Для определения количества агрегатов и их вычисления следует запустить Storage Design wizard мастер создания многомерного хранилища. Для этого в редакторе кубов следует выбрать пункт меню Tools | Design Storage. В первой диалоговой панели следует указать способ хранения данных MOLAP, ROLAP или HOLAP (в нашем примере мы выберем MOLAP). Затем выбрать, какова должна быть производительность при выполнении запросов (либо будущий максимальный объем хранилища). После этого можно нажать на кнопку Start и получить зависимость производительности от объема хранилища (рис. 5).

Рисунок 5 - Определение количества агрегатов

Рисунок 6 - Вычисление агрегатных данных

Рисунок 7 - Просмотр сечений куба

Система агрегирования представляет собой внутренний механизм, обеспечивающий управляемую «свертку» данных по иерархиям размерностей. Если не управлять агрегированием, то оно выполняется по умолчанию, то есть свертка показателей производится с использованием функции суммирования снизу вверх (в нашем примере от дней до лет). Управляя этим механизмом, мы можем: изменять функции агрегирования, используемые для свертки данных; изменять порядок свертки (выполнять ее не по всем размерностям); определять, на каких уровнях иерархии будет производиться свертка данных и т. д. Показатели агрегирования данных могут быть: аддитивными (additive); полуаддитивными (semiadditive); неаддитивными (nonadditive). Система агрегирования представляет собой внутренний механизм, обеспечивающий управляемую «свертку» данных по иерархиям размерностей. Если не управлять агрегированием, то оно выполняется по умолчанию, то есть свертка показателей производится с использованием функции суммирования снизу вверх (в нашем примере от дней до лет). Управляя этим механизмом, мы можем: изменять функции агрегирования, используемые для свертки данных; изменять порядок свертки (выполнять ее не по всем размерностям); определять, на каких уровнях иерархии будет производиться свертка данных и т. д. Показатели агрегирования данных могут быть: аддитивными (additive); полуаддитивными (semiadditive); неаддитивными (nonadditive). Вопрос 2. Особенности показателей в кубах данных Microsoft SQL SERVER

Аддитивные показатели, также называемые полноаддитивными, агрегируются со всеми размерностями, включенными в группы показателей. Говоря другими словами (в терминах Microsoft), они пересекаются с любым членом размерности, то есть выбор любого члена любой размерности приводит к пересчету агрегатов показателей. Полуаддитивные показатели агрегируются относительно некоторых (не всех) размерностей. Например, показатель, определяющий количество товара на руках, может агрегироваться по географическому признаку, но не агрегироваться по остальным размерностям, поскольку к ним не должен относиться. О таких показателях говорят, что они не пересекаются с некоторыми размерностями. Агрегирование такого показателя по размерности «время» просто бессмысленно, поскольку нас не интересует, сколько товара было на руках в какой-то период времени. Неаддитивные показатели не агрегируются по размерностям, но могут быть посчитаны для любой ячейки куба. Например, подсчитываемый показатель, возвращающий процент дохода, не может быть агрегирован из значений процентов своих дочерних ячеек других размерностей. Аддитивные показатели, также называемые полноаддитивными, агрегируются со всеми размерностями, включенными в группы показателей. Говоря другими словами (в терминах Microsoft), они пересекаются с любым членом размерности, то есть выбор любого члена любой размерности приводит к пересчету агрегатов показателей. Полуаддитивные показатели агрегируются относительно некоторых (не всех) размерностей. Например, показатель, определяющий количество товара на руках, может агрегироваться по географическому признаку, но не агрегироваться по остальным размерностям, поскольку к ним не должен относиться. О таких показателях говорят, что они не пересекаются с некоторыми размерностями. Агрегирование такого показателя по размерности «время» просто бессмысленно, поскольку нас не интересует, сколько товара было на руках в какой-то период времени. Неаддитивные показатели не агрегируются по размерностям, но могут быть посчитаны для любой ячейки куба. Например, подсчитываемый показатель, возвращающий процент дохода, не может быть агрегирован из значений процентов своих дочерних ячеек других размерностей.

Особенности сохранения кубов: пространство на диске не выделяется под пустые ячейки; выполняется сжатие данных; сложный механизм агрегирования определяет, каким образом будет произведена свертка данных на каждом уровне иерархии размерностей, чтобы при этом не пострадало качество. Подключение к простым размерностям К простым размерностям относятся: размерности, состоящие из одной таблицы (сбалансированные и несбалансированные); размерности, состоящие из нескольких таблиц (сбалансированные и несбалансированные); размерности типа «родитель-потомок». Особенности сохранения кубов: пространство на диске не выделяется под пустые ячейки; выполняется сжатие данных; сложный механизм агрегирования определяет, каким образом будет произведена свертка данных на каждом уровне иерархии размерностей, чтобы при этом не пострадало качество. Подключение к простым размерностям К простым размерностям относятся: размерности, состоящие из одной таблицы (сбалансированные и несбалансированные); размерности, состоящие из нескольких таблиц (сбалансированные и несбалансированные); размерности типа «родитель-потомок».

Рисунок 8 – Команда на создание нового куба

Рисунок 9 – Распределение таблиц среза по измерениям и фактам куба

Рисунок 10 – Сохранение куба

Расчет (процессинг) куба В панели Solution Explorer (Проводник решений) в проекте Analysis Services RTS-Data-2 на кубе RTS-Data-2 (Индекс РТС) щелкните правой кнопкой мыши и затем выберите Process (Процессинг) из контекстного меню, произойдет процессинг куба. В панели Process Cube Main Sale Measures (Процессинг куба Главные показатели сбыта) щелкните Run (Выполнить); выполнится процессинг. Расчет (процессинг) куба В панели Solution Explorer (Проводник решений) в проекте Analysis Services RTS-Data-2 на кубе RTS-Data-2 (Индекс РТС) щелкните правой кнопкой мыши и затем выберите Process (Процессинг) из контекстного меню, произойдет процессинг куба. В панели Process Cube Main Sale Measures (Процессинг куба Главные показатели сбыта) щелкните Run (Выполнить); выполнится процессинг.

Рисунок 11 – Процессинг обновления куба

Выводы 1)при создании куба из таблицы фактов следует выбрать одно или несколько полей, на основе которых вычисляются меры куба, выбрать коллективные измерения, которые будут использоваться в этом кубе, а также создать недостающие частные измерения. При необходимости можно добавить к кубу вычисляемые значения; 2)агрегаты это заранее вычисленные агрегатные данные, соответствующие ячейкам куба. Чем их больше, тем быстрее выполняются запросы к многомерному хранилищу и тем больше объем самого хранилища; 3)для вычисления агрегатных данных следует оценить, какой способ хранения наиболее выгоден для данной задачи, и определить на основе баланса производительности и объема хранилища, какое количество агрегатов следует создать. 1)при создании куба из таблицы фактов следует выбрать одно или несколько полей, на основе которых вычисляются меры куба, выбрать коллективные измерения, которые будут использоваться в этом кубе, а также создать недостающие частные измерения. При необходимости можно добавить к кубу вычисляемые значения; 2)агрегаты это заранее вычисленные агрегатные данные, соответствующие ячейкам куба. Чем их больше, тем быстрее выполняются запросы к многомерному хранилищу и тем больше объем самого хранилища; 3)для вычисления агрегатных данных следует оценить, какой способ хранения наиболее выгоден для данной задачи, и определить на основе баланса производительности и объема хранилища, какое количество агрегатов следует создать.

Контрольные вопросы 1.Опишите основные этапы процесса создания OLAP-кубов с использованием Microsoft SQL SERVER. 2. Дайте понятие агрегатных данных (агрегатов) и охарактеризуйте способы их хранения в Microsoft SQL Server. 3.Охарактеризуйте основные функции системы агрегирования данных в Microsoft SQL Server. 4.Перечислите и охарактеризуйте показатели агрегирования данных в Microsoft SQL Server. 1.Опишите основные этапы процесса создания OLAP-кубов с использованием Microsoft SQL SERVER. 2. Дайте понятие агрегатных данных (агрегатов) и охарактеризуйте способы их хранения в Microsoft SQL Server. 3.Охарактеризуйте основные функции системы агрегирования данных в Microsoft SQL Server. 4.Перечислите и охарактеризуйте показатели агрегирования данных в Microsoft SQL Server.