Хранилища данных Использование Excel как клиента OLAP

Использование MS Excel 2007 для OLAP анализа данных
Инструмент «Сводная таблица».
Электронные таблицы Excel (начиная с Excel 2000), могут использоваться для анализ данных, расположенных в многомерных базах данных MS SQL Server.
Операции OLAP анализа данных могут выполняться при помощи инструментов Excel - «Сводная таблица/сводная диаграмма» и инструмент «Автофильтр». Одно из главных достоинств такого способа анализа данных - дополнительная возможность использовать для анализа весь обширный инструментарий Microsoft Excel (диаграммы, формулы и т.д.).
Для проведения анализа данных многомерного гиперкуба необходимо подключиться к заранее подготовленному гиперкубу на сервере данных или открыть файл данных для автономной работы (.CUB).

PowerPivot for Excel 2010 Architecture

PowerPivot for Excel 2010 Architecture

Подключение к источнику данных
Что бы произвести выбор данных из многомерного гиперкуба для последующего размещения в ячейках листа таблицы необходимо:
Запустить MS Excel 2007
Перейти на ленту «Вставка»
Установить курсор в ячейку листа рабочей книги, начиная с которого будут размещаться данные.
Выбрать кнопку «Сводная таблица»

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

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

Подключение к источнику данных
В окне выбора источника данных выберите пункт «+Подключение к новому источнику данных», для запуска мастера настройки подключений данных.
Выберите тип источника данных «Microsoft SQL Server Analyses Services».

Подключение к источнику данных
В окне выбора базы данных укажите название многомерной базы данных, в которой находится гиперкуб «Max-min-DM-Cube-Katkov-h»и выделите гиперкуб «MM-DM-Иванов» с которым вы будете работать.

Подключение к источнику данных
Укажите название файла описания подключения для дальнейшего сохранения.

Диапазон ячеек и панель «Список полей сводной таблицы».
Панель «Список полей сводной таблицы» содержит списки названий измерений и показателей многомерного гиперкуба, а также области макета для указания размещения в строках, столбцах или страницах меток измерений-классификаторов и выбора состава исследуемых данных(показателей).
Расположение указанных элементов в таблице выполняется путем перетаскивания (операция Drag&drop).

Изменение макета таблицы осуществляется «перетаскиванием»

Порядок выполнения основных OLAP –операций. Дезагрегирование.
Если необходимо произвести дезагрегирование данных по какому-либо из элементов измерения необходимо произвести двойной щелчок мышью на этом элементе или воспользоваться командой контекстного меню «Отобразить детали», вызываемого правой кнопкой мыши при щелчке по названию уровня измерения.

Порядок выполнения основных OLAP –операций. Агрегирование.
Для агрегирования ранее дезагрегированных элементов необходимо еще раз произвести двойной щелчок мышью на выбранном элементе измерения или командой контекстного меню «Скрыть детали» .

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

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

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

Вращение гиперкуба
Одновременно на одной оси могут размещаться более одного измерения.

Выбор значений измерений по условиям для переменных. «Автофильтр».
Операции отбора значений измерений и/или значений показателей выполняются в основном с использованием инструмента «Автофильтр».
Для использования автофильтра небходимо расположить ряд отбираемых значений по вертикали, в колонке.

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

Выбор значений измерений по условиям для переменных. «Автофильтр».
Инструмент «Автофильтр» позволяет выполнять такие варианты отбора как: «Первые N – наибольших / наименьших из списка/из списка в %», условие отбора отдельных значений типа «равно, не равно, начиная с , заканчивая на, включая, не начиная с , пустые , не пустые », интервалов, полуинтервалов и отрезков с использованием условий «больше, меньше, больше или равно, меньше или равно» и комбинации условий с использованием логических операторов И/ИЛИ.

Выбор значений измерений по условиям для переменных. «Автофильтр».
При этом необходимо учитывать необходимость исключения автоматически подводимых итогов для каждого уровня классификатора «Время», которые так же отображаются по строкам и могут повлиять на результаты отбора.
НЕПРАВИЛЬНО

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

Что бы исключить автоматическое подведения итогов
НЕПРАВИЛЬНО
ПРАВИЛЬНО

Виды представления «панель полей сводной таблицы».

Создание сводных диаграмм для OLAP-куба.
При необходимости в Excel можно построить сводную диаграмму, синхронизированную со сводной таблицей. Для этого достаточно нажать кнопку «Мастер диаграмм» на панели инструментов «Сводные таблицы» и, если нужно, отредактировать внешний вид диаграммы .

Создание сводных диаграмм для OLAP-куба.

Создание сводных диаграмм для OLAP-куба.

Автономная работа с данными «куба»
При необходимости в Excel можно создать выборку данных для Автономной работы без подключения к серверу OLAP
Выборка данных сохраняется в локальный файл (кэш) с расширением .cub

Автономная работа с данными «куба»
Мастер позволяет выбрать сохраняемые измерения и показатели для автономной работы ( кроме вычисляемых показателей).