Работа с OLAP-кубом в MS Excel

OLAP, SSAS  Работа Категория:  OLAP, SSAS EXCEL, ВИЗУАЛИЗАЦИЯ ДАННЫХ
Опубликовал:         11.01.2011               print
Интерактивный анализ многомерных данных в MS Excel

1. Получаем разрешение на доступ к OLAP-кубу SQL Server Analysis Services (SSAS)
2. На вашем компьютере должен быть установлен MS Excel 2016 / 2013 / 2010 (можно и MS Excel 2007, но в нем работать не удобно, и совсем бедная функциональность MS Excel 2003)
3. Открываем MS Excel, запускаем мастер настройки соединения с аналитической службой:




3.1 Указываем имя или IP-адрес действующего сервера OLAP (иногда требуется указать номер открытого порта, например, 192.25.25.102:80); используется доменная аутентификация:




3.2 Выбираем многомерную базу данных и аналитический куб (в случае наличия прав доступа к кубу):




3.3 Настройки соединения с аналитической службой будут сохранены в odc-файле на Вашем компьютере:




3.4 Выбираем вид отчета (сводная таблица/график) и указываем место для его размещения:




Если в книге Excel уже создано подключение, то им можно воспользоваться повторно: главное меню «Данные» -> «Существующие подключения» -> выбираем подключение в этой книге -> вставляем сводную таблицу в указанную ячейку.

4. Успешно подключились к кубу, можно приступать к интерактивному анализу данных:




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


Каждый раз, когда изменяется сводная таблица, на сервер OLAP автоматически отправляется MDX-инструкция, по исполнении которой возвращаются данные. Чем больше и сложнее объем обрабатываемых данных, рассчитываемых показателей, тем дольше время исполнения запроса. Отменить исполнение запроса можно нажатием клавиши Escape. Последние выполненные операции можно отменить (Ctrl+Z) или вернуть (Ctrl+Y).


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


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


После размещения иерархии в области строк / столбцов возможно скрыть отдельные уровни:




У ключевых атрибутов (реже - для атрибутов выше по иерархии) измерений могут быть свойства - описательные характеристики, которые могут отображаться как во всплывающих подсказках, так и в виде полей:




Если требуется отобразить сразу несколько свойств полей, то можно воспользоваться соответствующим диалоговым списком:




Определяемые пользователем наборы

В Excel 2010 появилась возможность интерактивного создания собственных (определяемых пользователем) наборов из элементов измерения:




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




Продвинутые пользователи могут создавать наборы, используя MDX конструкции:




Настройка свойств сводной таблицы

Посредством пункта "Параметры сводной таблицы..." контекстного меню (щелчок правой кнопкой мыши в рамках сводной таблицы) предоставляется возможность настройки сводной таблицы, например:
- вкладка "Вывод", параметр "Классический макет сводной таблицы" - сводная таблица становится интерактивной, можно перетаскивать поля (Drag&Drop);
- вкладка "Вывод", параметр "Показывать элементы без данных в строках" - в сводной таблице будут отображаться пустые строки, не содержащие ни одного значения показателя по соответствующим элементам измерений;
- вкладка "Разметка и формат", параметр "Сохранять форматирование ячеек при обновлении" - в сводной таблице можно переопределить и сохранить формат ячеек при обновлении данных;




Создание сводных диаграмм

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




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


Создание информационных панелей

Выделим исходную сводную таблицу, скопируем ее в буфер обмена (Ctrl+C) и вставим её копию (Ctrl+V), в которой изменим состав показателей:




Для одновременного управления несколькими сводными таблицами вставим срез (новый функционал, доступный, начиная с версии MS Excel 2010). Подключим наш Slicer к сводным таблицам – щелчок правой кнопкой мыши в рамках среза, выбор в контекстном меню пункта "Подключения к сводной таблице...". Следует отметить, что может быть несколько панелей срезов, которые могут обслуживать одновременно сводные таблицы на разных листах, что позволяет создавать скоординированные информационные панели (Dashboard).




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




Исполнение MDX запроса из Excel

  1. Прежде всего, необходимо выполнить операцию DRILLTHROUGH на каком-нибудь показателе, т.е. спуститься к детализированным данным (детализированные данные отображаются на отдельном листе), и открыть список подключений;
  2. Открыть свойства подключения, перейти на вкладку «Определение»;
  3. Выбрать тип команды по умолчанию, а в поле текста команды разместить заранее подготовленный MDX запрос;
  4. При нажатии кнопки после проверки правильности синтаксиса запроса и наличия соответствующих прав доступа запрос исполнится на сервере, а результат будет представлен в текущем листе в виде обычной плоской таблицы.
    Посмотреть текст MDX-запроса, генерируемого Excel, можно с помощью установки бесплатного дополнения OLAP PivotTable Extensions, которое предоставляет также и другие дополнительные функциональные возможности.



Перевод на другие языки

Аналитический куб поддерживает локализацию на русский и английский языки (при необходимости возможна локализация на другие языки). Переводы распространяются на наименования измерений, иерархий, атрибутов, папок, мер, а также элементы отдельных иерархий в случае наличия для них переводов на стороне учетных систем/ хранилища данных. Чтобы сменить язык, необходимо открыть свойства подключения и в строке подключения добавить следующую опцию:
Extended Properties="Locale=1033"
где 1033 - локализация на английский язык
1049 - локализация на русский язык




Дополнительные расширения Excel для Microsoft OLAP

Возможности работы с OLAP-кубами Microsoft возрастут, если использовать дополнительные расширения, например, OLAP PivotTable Extensions, благодаря которому можно пользоваться быстрым поиском по измерению:



Энергия идеи   dvbi.ru                    Последнее изменение: 2021-12-12 22:27:25Z         Возрастная аудитория: 14-70         Комментариев:  0
Теги:  Графики Примеры
Связанные статьи:

Пожалуйста, проголосуйте и ниже поставьте лайк:   rating


  Комментарии



Следующая статья:    Витрина данных кредитного портфеля
Предыдущая статья:  Внедрение OLAP. Тезисы