Как создать олап куб в excel - Word и 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

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

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

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

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

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

  dvbi.ru


2011-01-11 16:57:00Z
Последнее изменение: 2021-12-12 22:27:25Z

Возрастная аудитория: 14-70

Комментариев:  0

Источник

Кубы данных OLAP (Online Analytical Processing — оперативный анализ данных) позволяют эффективно извлекать и анализировать многомерные данные. В отличие от других типов баз данных, базы данных OLAP разработаны специально для аналитической обработки и быстрого извлечения из них всевозможных наборов данных. На самом деле существует несколько ключевых различий между стандартными реляционными базами данных, такими как Access или SQL Server, и базами данных OLAP. [1]

Рис. 1. Для подключения куба OLAP к книге Excel воспользуйтесь командой Из служб аналитики

Скачать заметку в формате Word или pdf

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

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

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

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

Подключение к кубу данных OLAP

Чтобы получить доступ к базе данных OLAP, сначала нужно установить подключение к кубу OLAP. Начните с перехода на вкладку ленты Данные. Щелкните на кнопке Из других источников и выберите в раскрывающемся меню команду Из служб аналитики (рис. 1).

При выборе указанной команды на экране появится диалоговое окно мастера подключения к данным (рис. 2). Основная его задача — это помочь вам установить соединение с сервером, который будет использован программой Excel при управлении данными.

1. Сначала нужно предоставить Excel регистрационную информацию. Введите в полях диалогового окна имя сервера, регистрационное имя и пароль доступа к данным, как показано на рис. 2. Щелкните на кнопке Далее. Если вы подключаетесь с помощью учетной записи Windows, то установите переключатель Использовать проверку подлинности Windows.

Рис. 2. Введите регистрационные данные

2. Выберите в раскрывающемся списке базу данных, с которой будете работать (рис. 3). В текущем примере используется база данных Analysis Services Tutorial. После выбора этой базы данных в расположенном ниже списке предлагается импортировать все доступные в ней кубы OLAP. Выберите необходимый куб данных и щелкните на кнопке Далее.

Рис. 3. Выберите рабочую базу данных и куб OLAP, который планируете применять для анализа данных

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

Рис. 4. Измените описательную информацию о соединении

4. Щелкните на кнопке Готово, чтобы завершить создание подключения. На экране появится диалоговое окно Импорт данных (рис. 5). Установите переключатель Отчет сводной таблицы и щелкните на кнопке ОК, чтобы начать создание сводной таблицы.

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

Структура куба OLAP

В процессе создания сводной таблицы на основе базы данных OLAP вы заметите, что окно области задач Поля сводной таблицы будет отличаться от такового для обычной сводной таблицы. Причина кроется в упорядочении сводной таблицы так, чтобы максимально близко отобразить структуру куба OLAP, присоединенного к ней. Чтобы максимально быстро перемещаться по кубу OLAP, необходимо детально ознакомиться с его компонентами и способами их взаимодействия. На рис. 6 показана базовая структура типичного куба OLAP.

Рис. 6. Базовая структура куба данных OLAP

Как видите, основные компоненты куба OLAP – это размерности, иерархии, уровни, члены и меры:

Размерности. Основная характеристика анализируемых элементов данных. К наиболее общим примерам размерностей относятся Products (Товары), Customer (Покупатель) и Employee (Сотрудник). На рис. 6 показана структура размерности Products.
Иерархии. Заранее определенная агрегация уровней в указанной размерности. Иерархия позволяет создавать сводные данные и анализировать их на различных уровнях структуры, не вникая во взаимосвязи, существующие между этими уровнями. В примере, показанном на рис. 6, размерность Products имеет три уровня, которые агрегированы в единую иерархию Product Categories (Категории товаров).
Уровни. Уровни представляют собой категории, которые агрегируются в общую иерархию. Считайте уровни полями данных, которые можно запрашивать и анализировать отдельно друг от друга. На рис. 6 представлены всего три уровня: Category (Категория), SubCategory (Подкатегория) и Product Name (Название товара).
Члены. Отдельный элемент данных в пределах размерности. Доступ к членам обычно реализуется через OLАР-структуру размерностей, иерархий и уровней. В примере на рис. 6 члены заданы для уровня Product Name. Другие уровни имеют свои члены, которые в структуре не показаны.
Меры — это реальные данные в кубах OLAP. Меры сохраняются в собственных размерностях, которые называются размерностями мер. С помощью произвольной комбинации размерностей, иерархий, уровней и членов можно запрашивать меры. Подобная процедура называется «нарезкой» мер.

Теперь, когда вы ознакомились со структурой кубов OLAP, давайте по-новому взглянем на список полей сводной таблицы. Организация доступных полей становится понятной и не вызывает нареканий. На рис. 7 показано, как в списке полей представляются элементы сводной таблицы OLAP.

Рис. 7. Список полей сводной таблицы OLAP

В списке полей сводной таблицы OLAP меры выводятся первыми и обозначаются значком суммирования (сигма). Это единственные элементы данных, которые могут находиться в области ЗНАЧЕНИЯ. После них в списке указываются размерности, обозначенные значком с изображением таблицы. В нашем примере используется размерность Customer. В эту размерность вложен ряд иерархий. После развертывания иерархии можно ознакомиться с отдельными уровнями данных. Для просмотра структуры данных куба OLAP достаточно перемещаться по списку полей сводной таблицы.

Ограничения, накладываемые на сводные таблицы OLAP

Работая со сводными таблицами OLAP, следует помнить, что взаимодействие с источником данных сводной таблицы осуществляется в среде Analysis Services OLAP. Это означает, что каждый поведенческий аспект куба данных, начиная с размерностей и заканчивая мерами, которые включены в куб, также контролируется аналитическими службами OLAP. В свою очередь, это приводит к ограничениям, накладываемым на операции, которые можно выполнять в сводных таблицах OLAP:

нельзя поместить в область ЗНАЧЕНИЯ сводной таблицы поля, отличные от мер;
невозможно изменить функцию, применяемую для подведения итогов;
нельзя создать вычисляемое поле или вычисляемый элемент;
любые изменения в именах полей отменяются сразу же после удаления этого поля из сводной таблицы;
не допускается изменение параметров поля страницы;
недоступна команда Показать страницы;
отключен параметр Показывать подписи элементов при отсутствии полей в области значений;
отключен параметр Промежуточные суммы по отобранным фильтром элементам страницы;
недоступен параметр Фоновый запрос;
после двойного щелчка в поле ЗНАЧЕНИЯ возвращаются только первые 1000 записей из кеша сводной таблицы;
недоступен флажок Оптимизировать память.

Создание автономных кубов данных

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

Если все же нужно анализировать OLAP-данные при отсутствии подключения к сети, создайте автономный куб данных. Это отдельный файл, который представляет собой кеш сводной таблицы. В этом файле хранятся OLAP-данные, просматриваемые после отключения от локальной сети. Чтобы создать автономный куб данных, сначала создайте сводную таблицу OLAP. Поместите курсор в сводную таблицу и щелкните на кнопке Средства OLAP контекстной вкладки Анализ, входящей в набор контекстных вкладок Работа со сводными таблицами. Выберите команду Автономный режим OLAP (рис. 8).

Рис. 8. Создание автономного куба данных

На экране появится диалоговое окно Настройка автономной работы OLAP (рис. 9). Щелкните на кнопке Создать автономный файл данных. На экране появится первое окно мастера создания файла куба данных. Щелкните на кнопке Далее, чтобы продолжить процедуру.

Рис. 9. Начальное окно мастера создания автономного куба данных

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

Рис. 10. Укажите размерность и уровни, включаемые в автономный куб данных

Щелкните на кнопке Далее для перехода к третьему шагу (рис. 11). В этом окне нужно выбрать члены или элементы данных, которые не будут включаться в куб. Если флажок не установлен, указанный элемент не будет импортироваться и занимать лишнее место на локальном жестком диске.

Рис. 11. He устанавливайте флажки для элементов данных, которые не должны включаться в автономный куб данных

Укажите расположение и имя куба данных (рис. 12). Файлы кубов данных имеют расширение .cub.

Рис. 12. Укажите имя и расположение файла куба данных

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

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

Применение функций куба данных в сводных таблицах

Функции куба данных, которые применяются в базах данных OLAP, могут запускаться и из сводной таблицы. В устаревших версиях Excel вы получали доступ к функциям кубов данных только после установки надстройки Пакет анализа. В Excel 2013 данные функции встроены в программу, а потому доступны для применения. Чтобы в полной мере ознакомиться с их возможностями, рассмотрим конкретный пример.

Один из самых простых способов изучения функций куба данных заключается в преобразовании сводной таблицы OLAP в формулы куба данных. Эта процедура очень простая и позволяет быстро получить формулы куба данных, не создавая их «с нуля». Ключевой принцип — заменить все ячейки в сводной таблице формулами, которые связаны с базой данных OLAP. На рис. 13 показана сводная таблица, связанная с базой данных OLAP.

Рис. 13. Обычная сводная таблица OLAP

Поместите курсор в любом месте сводной таблицы, щелкните на кнопке Средства OLAP контекстной вкладки ленты Анализ и выберите команду Преобразовать в формулы (рис. 14).

Рис. 14. Преобразование сводной таблицы в формулы куба данных

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

Рис. 15. В Excel можно преобразовать фильтры данных сводной таблицы в статические формулы

Спустя несколько секунд вместо сводной таблицы отобразятся формулы, которые выполняются в кубах данных и обеспечивают вывод в окне Excel необходимой информации. Обратите внимание на то, что при этом удаляются ранее примененные стили (рис. 16).

Рис. 16. Взгляните на строку формул: в ячейках содержатся формулы куба данных

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

Добавление вычислений в сводные таблицы OLAP

В предыдущих версиях Excel в сводных таблицах OLAP не допускались пользовательские вычисления. Это означает, что в сводные таблицы OLAP было невозможно добавить дополнительный уровень анализа подобно тому, как это делается в обычных сводных таблицах, допускающих добавление вычисляемых полей и элементов (подробнее см. Вычисляемые поля и вычисляемые элементы в Excel 2013; прежде чем продолжить чтение, убедитесь, что вы хорошо знакомы с этим материалом).

В Excel 2013 появились новые инструменты OLAP — вычисляемые меры и вычисляемые элементы многомерных выражений. Теперь вы не ограничены использованием мер и элементов в кубе OLAP, предоставленных администратором базы данных. Вы получаете дополнительные возможности анализа путем создания пользовательских вычислений.

Знакомство с MDX. При использовании сводной таблицы вместе с кубом OLAP вы отсылаете базе данных запросы MDX (Multidimensional Expressions — многомерные выражения). MDX — это язык запросов, применяемый для получения данных из многомерных источников (например, из кубов OLAP). В случае изменения или обновления сводной таблицы OLAP соответствующие запросы MDX передаются базе данных OLAP. Результаты выполнения запроса возвращаются обратно в Excel и отображаются в области сводной таблицы. Таким образом обеспечивается возможность работы с данными OLAP без локальной копии кеша сводных таблиц.

При создании вычисляемых мер и элементов многомерных выражений применяется синтаксис языка MDX. С помощью этого синтаксиса сводная таблица обеспечивает взаимодействие вычислений с серверной частью базы данных OLAP. Примеры, рассматриваемые в книге, основаны на базовых конструкциях MDX, демонстрирующих новые функции Excel 2013. Если необходимо создавать сложные вычисляемые меры и элементы многомерных выражений, придется потратить время на более глубокое изучение возможностей MDX.

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

Рис. 17. В сводную таблицу OLAP будет добавлена мера, вычисляющая среднюю цену единицы товара

Поместите курсор в любом месте сводной таблицы и выберите контекстную вкладку Анализ из набора контекстных вкладок Работа со сводными таблицами. В раскрывающемся меню Средства OLAP выберите пункт Вычисляемая мера многомерного выражения (рис. 18).

Рис. 18. Выберите пункт меню Вычисляемая мера многомерного выражения

На экране появится диалоговое окно Создание вычисляемой меры (рис. 19).

Рис. 19. В данном окне создается вычисляемая мера

Выполните следующие действия:

1. Присвойте вычисляемой мере имя.

2. Выберите группу мер, в которой будет находиться новая вычисляемая мера. Если этого не сделать, Excel автоматически поместит новую меру в первую доступную группу мер.

3. В поле Многомерное выражение (MDX) введите код, задающий новую меру. Чтобы ускорить процесс ввода, воспользуйтесь находящимся слева списком для выбора существующих мер, которые будут использованы в вычислениях. Дважды щелкните на нужной мере, чтобы добавить ее в поле Многомерное выражение. Для вычисления средней цены продажи единицы товара используется следующее многомерное выражение:

IIF (

[Measures].[Internet Sales-Order Quantity] = 0

,NULL

,[Measures].[Internet Sales-Sales Amount]/

[Measures].[Internet Sales-Order Quantity]

)

4. Кликните ОК.

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

После завершения создания новой вычисляемой меры перейдите в список Поля сводной таблицы и выберите ее (рис. 20).

Рис. 20. Добавьте в сводную таблицу OLAP новую вычисляемую меру

Только что созданная вычисляемая мера добавила еще один уровень анализа в сводную таблицу (рис. 21).

Рис. 21. В сводной таблице появилась новая вычисляемая мера

Область действия вычисляемой меры распространяется только на текущую книгу. Другими словами, вычисляемые меры не создаются непосредственно в кубе OLAP сервера. Это означает, что никто не сможет получить доступ к вычисляемой мере, если только вы не откроете общий доступ к рабочей книге либо не опубликуете ее в Интернете.

Создание вычисляемых элементов многомерных выражений. Вычисляемый элемент многомерного выражения представляет собой OLAP-версию обычного вычисляемого элемента. Идея заключается в создании нового элемента данных, основанного на некоторых математических операциях, выполняемых по отношению к существующим элементам OLAP. В примере, показанном на рис. 22, используется сводная таблица OLAP, включающая сведения о продажах за 2005–2008 годы (с поквартальной разбивкой). Предположим, нужно выполнить агрегирование данных, относящихся к первому и второму кварталам, создав новый элемент First Half of Year (Первая половина года). Также объединим данные, относящиеся к третьему и четвертому кварталам, сформировав новый элемент Second Half of Year (Вторая половина года).

Рис. 22. Мы собираемся добавить новые вычисляемые элементы многомерных выражений, First Half of Year и Second Half of Year

Поместите курсор в любом месте сводной таблицы и выберите контекстную вкладку Анализ из набора контекстных вкладок Работа со сводными таблицами. В раскрывающемся меню Средства OLAP выберите пункт Вычисляемый элемент многомерного выражения (рис. 23).

Рис. 23. Создание нового вычисляемого элемента многомерного выражения

На экране появится диалоговое окно Создание вычисляемого элемента (рис. 24).

Рис. 24. Окно Создание вычисляемого элемента

Выполните следующие действия:

1. Присвойте вычисляемой мере имя.

2. Выберите родительскую иерархию, для которой создаются новые вычисляемые элементы. Настройке Родительский элемент присвойте значение Все. Благодаря этой настройке Excel получает доступ ко всем элементам родительской иерархии при вычислении выражения.

3. В окне Многомерное выражение введите синтаксис многомерного выражения. Чтобы немного сэкономить время, воспользуйтесь отображенным слева списком для выбора существующих элементов, используемых в многомерном выражении. Дважды щелкните на выбранном элементе, и Excel добавит его в окно Многомерное выражение. В примере, показанном на рис. 24, вычисляется сумма первого и второго кварталов:

[Ship Date].[Calendar Quarter].&[2005]&[1] +

[Ship Date].[Calendar Quarter]. &[2005]& [2] +

[Ship Date].[Calendar Quarter]. &[2006]&[1] + …

4. Щелкните ОК. Excel отобразит только что созданный вычисляемый элемент многомерного выражения в сводной таблице. Как показано на рис. 25, новый вычисляемый элемент отображается вместе с другими вычисляемыми элементами сводной таблицы.

Рис. 25. Excel добавляет новый вычисляемый элемент в поле сводной таблицы

На рис. 26 иллюстрируется аналогичный процесс, применяемый для создания вычисляемого элемента Second Half of Year.

Рис. 26. Повторите описанный ранее процесс для создания других вычисляемых элементов многомерного выражения

Обратите внимание: Excel даже не пытается удалить исходные элементы многомерного выражения (рис. 27). В сводной таблице по-прежнему отображаются записи, соответствующие 2005–2008 годам с поквартальной разбивкой. В рассматриваемом случае это не страшно, но в большинстве сценариев следует скрывать «лишние» элементы во избежание появления конфликтов.

Рис. 27. Excel отображает созданный вычисляемый элемент многомерного выражения наравне с исходными элементами. Но все же лучше удалять исходные элементы во избежание конфликтов

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

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

Управление вычислениями OLAP. В Excel поддерживается интерфейс, позволяющий управлять вычисляемыми мерами и элементами многомерных выражений в сводных таблицах OLAP. Поместите курсор в любом месте сводной таблицы и выберите контекстную вкладку Анализ из набора контекстных вкладок Работа со сводными таблицами. В раскрывающемся меню Средства OLAP выберите пункт Управление вычислениями. В окне Управления вычислениями доступны три кнопки (рис. 28):

Создать. Создание новой вычисляемой меры или вычисляемого элемента многомерного выражения.
Изменить. Изменение выбранного вычисления.
Удалить. Удаление выделенного вычисления.

Рис. 28. Диалоговое окне Управление вычислениями

Выполнение анализа «что, если» по данным OLAP. В Excel 2013 можно выполнять анализ «что, если» для данных, находящихся в сводных таблицах OLAP. Благодаря этой новой возможности можно изменять значения в сводной таблице и повторно вычислять меры и элементы на основании внесенных изменений. Можно также распространить изменения обратно на куб OLAP. Чтобы воспользоваться возможностями анализа «что, если», создайте сводную таблицу OLAP и выберите контекстную вкладку Анализ, находящуюся в наборе контекстных вкладок Работа со сводными таблицами. В раскрывающемся меню Средства OLAP выберите команду Анализ «что, если» –> Включить анализ «что, если» (рис. 29).

Рис. 29. После включения анализа «что, если» можно изменять данные в сводной таблице

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

Рис. 30. Выберите пункт Учесть изменение при расчете сводной таблицы, чтобы внести изменения в сводную таблицу

По умолчанию правки, внесенные в сводную таблицу в режиме анализа «что, если», являются локальными. Если же вы хотите распространить изменения на сервер OLAP, выберите команду для публикации изменений. Выберите контекстную вкладку Анализ, находящуюся в наборе контекстных вкладок Работа со сводными таблицами. В раскрывающемся меню Средства OLAP выберите пункты Анализ «что, если» – > Опубликовать изменения (рис. 31). В результате выполнения этой команды включится «обратная запись» на сервере OLAP, что означает возможность распространения изменений на исходный куб OLAP. (Чтобы распространять изменения на сервер OLAP, нужно обладать соответствующими разрешениями на доступ к серверу. Обратитесь к администратору баз данных, который поможет вам получить разрешения на доступ в режиме записи к базе данных OLAP.)

Рис. 31. В Excel 2013 можно распространить изменения обратно на исходный куб OLAP

[1] Заметка написана на основе книги Джелен, Александер. Сводные таблицы в Microsoft Excel 2013. Глава 9.

Источник

Время на прочтение
3 мин

Количество просмотров 4.7K

Всем привет! Меня зовут Сергей Коньков — я работаю архитектором в компании CloudReports. Сегодня я расскажу, как мы создали продукт, который помогает пользователям работать с данными и в какой-то мере соединяет два мира аналитики: Excel и облачные хранилища данных.

Задача

BigQuery и другие аналитические хранилища в сочетании с современными BI инструментами перевернули работу с данными за последние годы. Возможность обрабатывать терабайты информации за секунды, интерактивные дашборды в DataStudio и PowerBI, сделали работу очень комфортной.

Однако если посмотреть глубже, можно увидеть — выиграли от этих изменений в основном профессионалы, владеющие SQL и Python и бизнес пользователи на руководящих позициях, для которых разрабатываются дашборды.
А как быть с сотнями миллионов сотрудников, для которых главным инструментом анализа был и остается Microsoft Excel? Они в каком-то смысле, остались за бортом новых изменений. Это менеджеры по продажам, владельцы малого бизнеса, руководители небольших отделов. Освоить PowerBI у них нет времени. Все что им остается это экспортировать данные из отчетов в свой любимый Excel и продолжить работу там, но это не очень удобно, занимает время и есть ограничения по объему данных.

Мы часто наблюдаем, как наши клиенты использующих Google BigQuery загружают данные в Excel с помощью различных коннекторов, натыкаясь на ограничения. И родилась идея: если Excel не теряет популярности, а данные уходят в облака, то давайте придумаем способ как помочь пользователю работать из Excel с облаком.

Вспоминаем OLAP

Да, сегодня Excel по-прежнему самый популярный инструмент для работы с информацией в мире. А Сводная таблица, это то что используют миллионы пользователей каждый день. А раньше было еще больше. Если вы работали с данными в крупной компании десять лет назад вы наверняка слышали про технологию OLAP кубов от Microsoft и других вендоров, которые создаются поверх реляционных SQL баз, и позволяют получать результаты обработки миллионов строк данных за секунды. Самым популярным способом работы с OLAP кубами была и есть сводная таблица Excel. К слову OLAP по прежнему очень распространен в корпоративном мире, это все так же часть Microsoft SQL Server, однако имеет ряд ограничений по объемам и скорости обработки и все больше уступает рынок облачным аналитическим хранилищам.

Так вот в решении этой задачи нам поможет OLAP. Как я уже писал выше в Excel есть готовый клиент для работы с OLAP, мы будем использовать его.

Kогда Microsoft выводил на рынок данную технологию был опубликован открытый протокол для работы с OLAP базами — XMLA (XML для аналитики). Именно этот протокол и использует Excel когда подключается к OLAP серверу. Все работает примерно так:

Решение

Идея проста — вместо OLAP сервера мы сделаем Python приложение , которое будет делать следующее:

принимать XMLA запросы от Excel
конвертировать логику XMLA запроса в SQL код
отправлять SQL запрос в BigQiery
полученный от BigQuery ответ конвертировать в XMLA и отправлять обратно в Excel

Данное приложение (App) можем опубликовать в облаке, так как Excel имеет возможность отправлять запросы XMLA запросы по протоколу HTTPS. Все будет работать примерно так:

Использование

После того как мы разработали и опубликовали приложение, администратору BigQuery для начала использования достаточно просто создать таблицу и определить для соответсnвующих полей типы агрегации (сумма, минимум, максимум и т.д.). Далее пользователь в Excel используя подключение к службам аналитики (OLAP) соединяется с нашим сервисом:

После этого мы получаем доступ к таблице BigQuery непосредственно из сводной таблицы. И можем легко «играть» с данными.

Кроме того, мы реализовали в данном сервисе слой кэширования данных для ускорения запросов и экономии затрат на BigQuery.

Что дальше

Сейчас мы активно тестируем сервис на своих клиентах и думаем над добавлением нового функционала.

Например, SQL запросы наряду с BigQuery поддерживают и другие облачные хранилища данных. Добавив один класс в наше приложение мы реализовали аналогичный механизм для ClickHouse. Скоро будет готова версия для Snowflake и Amazon Redshift.

Будем рады услышать вопросы и мнение коллег в комментариях.

Источник

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

Если вам все же необходимо анализировать OLAP-данные после отключения от сети, создайте автономный куб данных. Автономный куб данных — это отдельный файл, который представляет собой кеш сводной таблицы и хранит OLAP-данные, просматриваемые после отключения от локальной сети. OLAP-данные, скопированные в сводную таблицу, можно распечатать, на сайте http://everest.ua подробно об этом рассказано.

Чтобы создать автономный куб данных, сначала создайте сводную таблицу OLAP. Поместите курсор в пределах сводной таблицы и щелкните на кнопке Средства OLAP (OLAP Tools) контекстной вкладки Параметры (Tools), входящей в группу контекстных вкладок Работа со сводными таблицами (PivotTable Tools). Выберите команду Автономный режим OLAP (Offline OLAP) (рис. 9.8).

Рис. 9.8. Создание автономного куба данных

На экране появится диалоговое окно настроек автономного куба данных OLAP. Щелкните в нем на кнопке Создать автономный файл данных (Create Offline Data File). Вы запустили мастер создания файла куба данных. Щелкните на кнопке Далее (Next), чтобы продолжить процедуру.

Cначала необходимо указать размерности и уровни, которые будут включаться в куб данных. В диалоговом окне необходимо выбрать данные, которые будут импортироваться из базы данных OLAP. Идея состоит в том, чтобы указать только те размерности, которые понадобятся после отключения компьютера от локальной сети. Чем больше размерностей укажете, тем больший размер будет иметь автономный куб данных.

Щелкните на кнопке Далее для перехода к следующему диалоговому окну мастера. В нем вы получаете возможность указать члены или элементы данных, которые не будут включаться в куб. В частности, вам не потребуется мера Internet Sales-Extended Amount, поэтому флажок для нее будет сброшен в списке. Сброшенный флажок указывает на то, что указанный элемент не будет импортироваться и занимать лишнее место на локальном жестком диске.

На последнем этапе укажите расположение и имя куба данных. В нашем случае файл куба будет назван MyOfflineCube.cub и будет располагаться в папке Work.

Файлы кубов данных имеют расширение .cub

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

Источник

Рассмотрим, каким
образом можно построить OLAP
– куб, используя простейший оперативный
источник данных (ОИД) в Excel.

Средствами разработки
OLAP
кубов в Excel
являются сводные таблицы.

Сводные таблицы
создают с помощью мастера сводных
таблиц.

Рассмотрим случай
применения сводной таблицы, когда ОИД
находится в рабочей книге Excel.
Откройте MS
Excel.

Выделите щелчком
мышки корешок первого рабочего листа,
так как показано на рисунке.

Введите с клавиатуры
имя – ОИД (оперативный источник данных).
Нажмите Enter.

На рабочем листе
Введите данные в соответствии с таблицей,
показанной на рис.1.

При вводе
используйте форму ввода.

Рис.1. Исходный ОИД
(Модель хранилища данных)

Внимание!

Для применения
формы выполните следующие действия.

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

Далее выполнить
команду Данные Форма. Открыть форму ,
ввести данные. Добавлять записи (строки
таблицы) кнопкой Добавить.

Задание
1. Создание сводной таблицы

Запустите мастер
сводных таблиц, воспользовавшись
командами: Данные,
Сводная
таблица.
В результате
появляется первое окно Мастера сводных
таблиц, в котором следует
выбрать источник данных. Поскольку
в рассматриваемом примере источник
данных находится в рабочей таблице,
выберем в поле «Создать
таблицу на основе данных, находящихся
…» переключатель
«в списке
или базе данных Microsoft Excel».
После выбора
источника данных следует нажать кнопку
Далее.
В следующем диалоговом окне мастера
(рис.2) в поле Диапазон
нужно указать диапазон ячеек, где
располагается таблица исходных данных.
Внимание!
Если курсор находится внутри диапазона
источника данных, то диапазон будет
указан автоматически. После завершения
второго шага в алгоритме построения
сводных таблиц следует нажать кнопку
Далее.

Рис.2. Диалоговое
окно мастера сводных таблиц

Шаг 3 заключается
в определении места расположения
сводной таблицы: на существующем листе
или на новом и создании макета сводной
таблицы. Выберите переключатель Новый
лист и нажмите
кнопку Макет.
В диалоговом окне Макет (рис. 3) поля
базы данных расположены в правой части
макета в виде кнопок. Каждую из них
следует разместить (перетащить с помощью
«мыши») в одну из 4 областей макета
сводной таблицы: область страниц, строк,
столбцов, данных. Следует учитывать
типы перетаскиваемых полей. Поля
– измерения
можно добавлять в области страниц,
столбцов или строк. Поля
– меры
(числа), добавлять только в область
данных. В рассматриваемом ОИД, мерами
будут столбцы: Количество
и Стоимость
услуги.
Таким образом,
формируется куб OLAP.
Добавляя в каждую из областей страниц,
строк и столбцов только
по одному полю – измерению,
мы получим трехмерный куб. Многомерные
кубы получаются, если в эти области
размещать по нескольку полей. При
размещении кнопки поля в области
страниц, строк, столбцов имена полей
становятся заголовками строк, столбцов
и страниц соответственно. При размещении
кнопки поля в области данных мастер
сводных таблиц использует формулу СУММ
(SUM)
по умолчанию, если значение поля
является числом, и формулу СЧЕТ (COUNT),
если поле содержит нечисловое значение.
При определении макета сводной таблицы
необязательно все поля должны быть
размещены в указанных областях, в
каждой области можно разместить одно
или более полей. Если порядок размещения
полей вам не понравился, вы можете
вынести поле за пределы макета.

Рис.3. Макет сводной
таблицы

Щелкните левой
кнопкой манипулятора мышь по полю
Наименование
и, удерживая кнопку мыши, перетащите
поле в область Страницы;
получим первое измерение.
Щелкните левой
кнопкой манипулятора мышь по полю
Клиент
и перетащите его в область Столбцы.
Получим второе измерение.
Щелкните левой
кнопкой манипулятора мышь по полю
Сотрудники
и перетащите поле в область Строки.
Получим
третье измерение.
Щелкните левой
кнопкой манипулятора мышь по полю
Количество
и перетащите его в область Данные.
В куб будет добавлена мера. В результате
получится макет сводной таблицы, который
представлен на рис.4. Нажмите кнопки ОК
и Готово.

Рис.4. Мастер сводных
таблиц после размещения на нем полей

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

Щелкните дважды
по ярлычку вновь появившегося рабочего
листа. Переименуйте рабочий лист, выбрав
имя Услуги
БНТИ. Щелкните
по раскрывающемуся списку значения
поля страницы (все) и выберите
Ксерокопирование.
В результате получите сводную таблицу,
представленную на рис.5. С помощью этой
таблицы можно легко установить, каким
клиентам, и в каком объеме
оказывались услуги по ксерокопированию,
а также определить общий объем
услуг по
ксерокопированию.

Рис.5. Сводная
таблица, содержащая услуги по
ксерокопированию

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

Источник

Сводные таблицы Excel

Если вам все же необходимо анализировать OLAP-данные после отключения от сети, создайте автономный куб данных. Автономный куб данных — это отдельный файл, который представляет собой кеш сводной таблицы и хранит OLAP-данные, просматриваемые после отключения от локальной сети. OLAP-данные, скопированные в сводную таблицу, можно распечатать, на сайте http://everest.ua подробно об этом рассказано.

Рис. 9.8. Создание автономного куба данных

Файлы кубов данных имеют расширение .cub

Работа с файлами автономного куба

автономный файл куба (. cub) хранит данные в форме куба OLAP (Online Analytical Processing). Эти данные могут представлять часть базы данных OLAP на сервере OLAP или могут создаваться независимо от базы данных OLAP. Используйте автономный файл куба, чтобы продолжить работу с отчетами сводной таблицы и сводной диаграммы, если сервер недоступен или когда вы отключены от сети.

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

При работе с отчетом сводной таблицы или сводной диаграммы, основанными на исходных данных сервера OLAP, вы можете с помощью мастера автономного куба скопировать исходные данные в отдельный файл автономного куба на компьютере. Для создания этих автономных файлов необходимо, чтобы поставщик данных OLAP поддерживал такую возможность, например MSOLAP из служб Microsoft SQL Server Analysis Services, установленных на компьютере.

Примечание: Создание и использование файлов автономных кубов из служб Microsoft SQL Server Analysis Services регулируется термином и лицензированием установки Microsoft SQL Server. Ознакомьтесь с соответствующими сведениями о лицензировании версии SQL Server.

Работа с мастером автономного куба

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

Перевод данных в автономный режим и их обратное подключение

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

Ниже описаны основные шаги, которые следует выполнить для автономной работы с данными, а затем снова перевести данные в Интернет.

Создайте или откройте сводную таблицу или отчет сводной диаграммы, основанную на данных OLAP, к которым вы хотите получить доступ в автономном режиме.

Создание автономный файл куба на компьютере. В разделе Создание файла автономного куба из базы данных OLAP-сервера (ниже в этой статье).

Отключение от сети и работа с файлом автономного куба.

Подключитесь к сети и повторно подключите файл куба автономно. Ознакомьтесь с разделом Повторное подключение файла автономного куба к базе данных OLAP-сервера (ниже, в этой статье).

Обновление файла автономного куба с новыми данными и повторное создание автономного файла куба. Ознакомьтесь с разделом обновление и повторное создание файла автономного куба (ниже в этой статье).

БЛОГ

Только качественные посты

Что такое Сводные таблицы Excel и OLAP кубы

Смотрите видео к статье:

OLAP – это англ. online analytical processing, аналитическая технология обработки данных в реальном времени. Простым языком – хранилище с многомерными данными (Куб), еще проще – просто база данных, из которой можно получить данные в Excel и проанализировать с помощью инструмента Excel – Сводные таблицы.

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

Чтобы было понятно, давайте сравним «Обычную таблицу» со «Сводной таблицей»

Обычная таблица:

Сводная таблица:

Основное отличие Сводных таблиц – это наличие окна «Список полей сводной таблицы», из которого можно выбирать нужные поля и получать любую таблицу автоматически!

Как пользоваться

Откройте файл Excel, который подключен к OLAP-кубу, например «BIWEB»:

Теперь, что это означает и как этим пользоваться?

Перетащите нужные поля, чтобы получить, например, такую таблицу:

«Плюсики» позволяют детализировать отчет. В этом примере «Бренд» детализируется до «Сокращенных названий», а «Квартал» до «Месяца», т.е. так:

Аналитические функции в Excel (функции кубов)

Microsoft постоянно добавляет в Excel новые возможности в части анализа и визуализации данных. Работу с информацией в Excel можно представить в виде относительно независимых трех слоев:

«правильно» организованные исходные данные
математика (логика) обработки данных
представление данных

Рис. 1. Анализ данных в Excel: а) исходные данные, б) мера в Power Pivot, в) дашборд; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Скачать заметку в формате Word или pdf, примеры в формате Excel

Функции кубов и сводные таблицы

Наиболее простым и в тоже время очень мощным средством представления данных являются сводные таблицы. Они могут быть построены на основе данных, содержащихся: а) на листе Excel, б) кубе OLAP или в) модели данных Power Pivot. В последних двух случаях, помимо сводной таблицы, можно использовать аналитические функции (функции кубов) для формирования отчета на листе Excel. Сводные таблицы проще. Функции кубов сложнее, но предоставляют больше гибкости, особенно в оформлении отчетов, поэтому они широко применяются в дашбордах.

Дальнейшее изложение относится к формулам кубов и сводным таблицам на основе модели Power Pivot и в нескольких случаях на основе кубов OLAP.

Простой способ получить функции кубов

Когда (если) вы начинали изучать код VBA, то узнали, что проще всего получить код, используя запись макроса. Далее код можно редактировать, добавить циклы, проверки и др. Аналогично проще всего получить набор функций кубов, преобразовав сводную таблицу (рис. 2). Встаньте на любую ячейку сводной таблицы, перейдите на вкладку Анализ, кликните на кнопке Средства OLAP, и нажмите Преобразовать в формулы.

Рис. 2. Преобразование сводной таблицы в набор функций куба

Числа сохранятся, причем это будут не значения, а формулы, которые извлекают данные из модели данных Power Pivot (рис. 3). Получившуюся таблицу вы может отформатировать. В том числе, можно удалять и вставлять строки и столбцы внутрь таблицы. Срез остался, и он влияет на данные в таблице. При обновлении исходных данных числа в таблице также обновятся.

Рис. 3. Таблица на основе формул кубов

Функция КУБЗНАЧЕНИЕ()

Это, пожалуй, основная функция кубов. Она эквивалента области Значения сводной таблицы. КУБЗНАЧЕНИЕ извлекает данные из куба или модели Power Pivot, и отражает их вне сводной таблицы. Это означает, что вы не ограничены пределами сводной таблицы и можете создавать отчеты с бесчисленными возможностями.

Написание формулы «с нуля»

Вам не обязательно преобразовывать готовую сводную таблицу. Вы можете написать любую формулу куба «с нуля». Например, в ячейку С10 введена следующая формула (рис. 4):

Рис. 4. Функция КУБЗНАЧЕНИЕ() в ячейке С10 возвращает продажи велосипедов за все годы, как и в сводной таблице

Маленькая хитрость. Чтобы удобнее было читать формулы кубов, желательно, чтобы в каждой строке помещался только один аргумент. Можно уменьшить окно Excel. Для этого кликните на значке Свернуть в окно, находящемся в правом верхнем углу экрана. А затем отрегулируйте размер окна по горизонтали. Альтернативный вариант – принудительно переносить текст формулы на новую строку. Для этого в строке формул поставьте курсор в том месте, где хотите сделать перенос и нажмите Alt+Enter.

Рис. 5. Свернуть окно

Синтаксис функции КУБЗНАЧЕНИЕ()

Справка Excel абсолютно точна и абсолютно бесполезна для начинающих:

КУБЗНАЧЕНИЕ(подключение; [выражение_элемента1]; [выражение_элемента2]; …)

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

Выражение_элемента – необязательный аргумент; текстовая строка, представляющая многомерное выражение, которое возвращает элемент или кортеж в кубе. Кроме того, «выражение_элемента» может быть множеством, определенным с помощью функции КУБМНОЖ. Используйте «выражение_элемента» в качестве среза, чтобы определить часть куба, для которой необходимо возвратить агрегированное значение. Если в аргументе «выражение_элемента» не указана мера, будет использоваться мера, заданная по умолчанию для этого куба.

Прежде, чем перейти к объяснению синтаксиса функции КУБЗНАЧЕНИЕ, пару слов о кубах, моделях данных, и загадочном кортеже.

Некоторые сведения о кубах OLAP и моделях данных Power Pivot

Кубы данных OLAP (Online Analytical Processing — оперативный анализ данных) были разработаны специально для аналитической обработки и быстрого извлечения из них данных. Представьте трехмерное пространство, где по осям отложены периоды времени, города и товары (рис. 5а). В узлах такой координатной сетки расположены значения различных мер: объем продаж, прибыль, затраты, количество проданных единиц и др. Теперь вообразите, что измерений десятки, или даже сотни… и мер тоже очень много. Это и будет многомерный куб OLAP. Создание, настройка и поддержание в актуальном состоянии кубов OLAP – дело ИТ-специалистов.

Рис. 5а. Трехмерный куб OLAP

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

Power Pivot – относительно новая фишка Microsoft. Это встроенная в Excel и отчасти независимая среда с привычным интерфейсом. Power Pivot значительно превосходит по своим возможностям стандартные сводные таблицы. Вместе с тем, разработка кубов в Power Pivot относительно проста, а самое главное – не требует участия ИТ-специалиста. Microsoft реализует свой лозунг: «Бизнес-аналитику – в массы!». Хотя модели Power Pivot не являются кубами на 100%, о них также можно говорить, как о кубах (подробнее см. вводный курс Марк Мур. Power Pivot и более объемное издание Роб Колли. Формулы DAX для Power Pivot).

Основные компоненты куба – это измерения, иерархии, уровни, элементы (или члены; по-английски members) и меры (measures). Измерение – основная характеристика анализируемых данных. Например, категория товаров, период времени, география продаж. Измерение – это то, что мы можем поместить на одну из осей сводной таблицы. Каждое измерение помимо уникальных значений включает элемент [ALL], выполняющий агрегацию всех элементов этого измерения.

Измерения построены на основе иерархии. Например, категория товаров может разбиваться на подкатегории, далее – на модели, и наконец – на названия товаров (рис. 5б) Иерархия позволяет создавать сводные данные и анализировать их на различных уровнях структуры. В нашем примере иерархия Категория включает 4 Уровня.

Рис. 5б. Иерархия категорий товаров

Элементы (отдельные члены) присутствуют на всех уровнях. Например, на уровне Category есть четыре элемента: Accessories, Bikes, Clothing, Components. Другие уровни имеют свои элементы.

Меры – это вычисляемые значения, например, объем продаж. Меры в кубах хранятся в собственном измерении, называемом [Measures] (см. ниже рис. 9). Меры не имеют иерархий. Каждая мера рассчитывает и хранит значение для всех измерений и всех элементов, и нарезается в зависимости от того, какие элементы измерений мы поместим на оси. Еще говорят, какие зададим координаты, или какой зададим контекст фильтра. Например, на рис. 5а в каждом маленьком кубике рассчитывается одна и та же мера – Прибыль. А возвращаемое мерой значение зависит от координат. Справа на рисунке 5а показано, что Прибыль (в трех координатах) по Москве в октябре на яблоках = 63 000 р. Меру можно трактовать, и как одно из измерений. Например, на рис. 5а вместо оси Товары, разместить ось Меры с элементами Объем продаж, Прибыль, Проданные единицы. Тогда каждая ячейка и будет каким-то значением, например, Москва, сентябрь, объем продаж.

Кортеж – несколько элементов разных измерений, задающие координаты по осям куба, в которых мы рассчитываем меру. Например, на рис. 5а Кортеж = Москва, октябрь, яблоки. Также допустимый кортеж – Пермь, яблоки. Еще один – яблоки, август. Не вошедшие в кортеж измерения присутствуют в нем неявно, и представлены членом по умолчанию [All]. Таким образом, ячейка многомерного пространства всегда определяется полным набором координат, даже если некоторые из них в кортеже опущены. Нельзя включить два элемента одного измерения в кортеж, не позволит синтаксис. Например, недопустимый кортеж Москва и Пермь, яблоки. Чтобы реализовать такое многомерное выражение потребуется набор двух кортежей: Москва и яблоки + Пермь и яблоки.

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

Автозавершение в помощь

Вернемся к синтаксису функции КУБЗНАЧЕНИЕ. Воспользуемся автозавершением. Начните ввод формулы в ячейке:

Excel предложит все доступные в книге Excel подключения:

Рис. 6. Подключение к модели данных Power Pivot всегда называется ThisWorkbookDataModel

Рис. 7. Подключения к кубам

Продолжим ввод формулы (в нашем случае для модели данных):

Автозавершение предложит все доступные таблицы и меры модели данных:

Рис. 8. Доступные элементы первого уровня – имена таблиц и набор мер (выделен)

Выберите значок Measures. Поставьте точку:

=КУБЗНАЧЕНИЕ( » ThisWorkbookDataModel » ; » [Measures].

Автозавершение предложит все доступные меры:

Рис. 9. Доступные элементы второго уровня в наборе мер

Выберите меру [Total Sales]. Добавьте кавычки, закрывающую скобку, нажмите Enter.

=КУБЗНАЧЕНИЕ( » ThisWorkbookDataModel » ; » [Measures].[Total Sales] » )

Рис. 10. Формула КУБЗНАЧЕНИЕ в ячейке Excel

Аналогичным образом можете добавить третий аргумент в формулу:

VBA в Excel Объект Excel.PivotTable и работа со сводными таблицами и кубами OLAP в Excel

10.8 Работа со сводными таблицами (объект PivotTable)

Объект Excel.PivotTable, программная работа со сводными таблицами и кубами OLAP в Excel средствами VBA, объект PivotCache, создание макета сводной таблицы

В процессе работы большинства предприятий накапливаются так называемые необработанные данные (raw data) о деятельности. Например, для торгового предприятия могут накапливаться данные о продажах товаров — по каждой покупке отдельно, для предприятий сотовой связи — статистика нагрузки на базовые станции и т.п. Очень часто менеджменту предприятия необходима аналитическая информация, которая генерируется на основе необработанной — например, посчитать вклад каждого вида товара в доходы предприятия или качество обслуживания в зоне данной станции. Из необработанной информации такие сведения извлечь очень тяжело: нужно выполнять очень сложные SQL-запросы, которые выполняются долго и часто мешают текущей работе. Поэтому все чаще в настоящее время необработанные данные сводятся вначале в хранилище архивных данных — Data Warehouse, а затем — в кубы OLAP, которые очень удобны для интерактивного анализа. Проще всего представить себе кубы OLAP как многомерные таблицы, в которых вместо стандартных двух измерений (столбцы и строки, как в обычных таблицах), измерений может быть очень много. Обычно для описания измерений в кубе используется термин «в разрезе». Например, отделу маркетинга может быть нужна информация во временном разрезе, в региональном разрезе, в разрезе типов продукта, в разрезе каналов продаж и т.п. При помощи кубов (в отличие от стандартных SQL-запросов) очень просто получать ответы на вопросы типа «сколько товаров такого-то типа было продано в четвертом квартале прошлого года в Северо-Западном регионе через региональных дистрибьюторов.

Конечно же, в обычных базах данных такие кубы не создать. Для работы с кубами OLAP требуются специализированные программные продукты. Вместе с SQL Server поставляется база данных OLAP от Microsoft, которая называется Analysis Services. Есть OLAP-решения от Oracle, IBM, Sybase и т.п.

Для работы с такими кубами в Excel встроен специальный клиент. По-русски он называется Сводная таблица (на графическом экране он доступен через меню Данные -> Сводная таблица), а по-английски — Pivot Table. Соответственно, объект, который представляет этот клиент, называется PivotTable. Необходимо отметить, что он умеет работать не только с кубами OLAP, но и с обычными данными в таблицах Excel или баз данных, но многие возможности при этом теряются.

Сводная таблица и объект PivotTable — это программные продукты фирмы Panorama Software, которые были приобретены Microsoft и интегрированы в Excel. Поэтому работа с объектом PivotTable несколько отличается от работы с другими объектами Excel. Догадаться, что нужно сделать, часто бывает непросто. Поэтому рекомендуется для получения подсказок активно использовать макрорекордер. В то же время при работе со сводными таблицами пользователям часто приходится выполнять одни и те же повторяющиеся операции, поэтому автоматизация во многих ситуациях необходима.

Как выглядит программная работа со сводной таблицей?

Первое, что нам потребуется сделать — создать объект PivotCache, который будет представлять набор записей, полученных с источника OLAP. Очень условно этот объект PivotCache можно сравнить с QueryTable. Для каждого объекта PivotTable можно использовать только один объект PivotCache. Создание объекта PivotCache производится при помощи метода Add() коллекции PivotCaches:

Dim PC1 As PivotCache

Set PC1 = ActiveWorkbook.PivotCaches.Add(xlExternal)

PivotCaches — стандартная коллекция, и из методов, которые заслуживают подробного рассмотрения, в ней можно назвать только метод Add(). Этот метод принимает два параметра:

SourceType — обязательный, определяет тип источника данных для сводной таблицы. Можно указать создание PivotTable на основе диапазона в Excel, данных из базы данных, во внешнем источнике данных, другой PivotTable и т.п. На практике обычно OLAP есть смысл использовать только тогда, когда данных много — соответственно нужно специализированное внешнее хранилище (например, Microsoft Analysis Services). В этой ситуации выбирается значение xlExternal.
SourceData — обязательный во всех случаях, кроме тех, когда значение первого параметра — xlExternal. Собственно говоря, определяет тот диапазон данных, на основе которого и будет создаваться PivotTable. Обычно принимает объект Range.

Следующая задача — настроить параметры объекта PivotCache. Как уже говорилось, этот объект очень напоминает QueryTable, и набор свойств и методов у него очень похожий. Некоторые наиболее важные свойства и методы:

ADOConnection — возможность возвратить объект ADO Connection, который автоматически создается для подключения к внешнему источнику данных. Используется для дополнительной настройки свойств подключения.
Connection — работает точно так же, как и одноименное свойство объекта QueryTable. Может принимать строку подключения, готовый объект Recordset, текстовый файл, Web-запрос. файл Microsoft Query. Чаще всего при работе с OLAP прописывается строка подключения напрямую (поскольку получать объект Recordset, например для изменения данных, большого смысла нет — источники данных OLAP практически всегда доступны только на чтение). Например, настройка этого свойства для подключения к базе данных Foodmart (учебная база данных Analysis Services) на сервере LONDON может выглядеть так:

PC1.Connection = «OLEDB;Provider=MSOLAP.2;Data Source=LONDON1;Initial Catalog = FoodMart 2000»

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

свойство LocalConnection позволяет подключиться к локальному кубу (файлу *.cub), созданному средствами Excel. Конечно, такие файлы для работы с «производственными» объемами данных использовать очень не рекомендуется — только для целей создания макетов и т.п.
свойство MemoryUsed возвращает количество оперативной памяти, используемой PivotCache. Если PivotTable на основе этого PivotCache еще не создана и не открыта, возвращает 0. Можно использовать для проверок, если ваше приложение будет работать на слабых клиентах.
свойство OLAP возвращает True, если PivotCache подключен к серверу OLAP.
OptimizeCache — возможность оптимизировать структуру кэша. Изначальная загрузка данных будет производиться дольше, но потом скорость работы может возрасти. Для источников OLE DB не работает.

Остальные свойства объекта PivotCache совпадают с аналогичными свойствами объекта QueryTable, и поэтому здесь рассматриваться не будут.

Главный метод объекта PivotCache — это метод CreatePivotTable(). При помощи этого метода и производится следующий этап — создание сводной таблицы (объекта PivotTable). Этот метод принимает четыре параметра:

TableDestination — единственный обязательный параметр. Принимает объект Range, в верхний левый угол которого будет помещена сводная таблица.
TableName — имя сводной таблицы. Если не указано, то автоматически сгенерируется имя вида «СводнаяТаблица1».
ReadData — если установить в True, то все содержимое куба будет автоматически помещено в кэш. С этим параметром нужно быть очень осторожным, поскольку неправильное его применение может резко увеличить нагрузку на клиента.
DefaultVersion — это свойство обычно не указывается. Позволяет определить версию создаваемой сводной таблицы. По умолчанию используется наиболее свежая версия.

Создание сводной таблицы в первой ячейке первого листа книги может выглядеть так:

PC1.CreatePivotTable Range («A1»)

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

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

Полагаться на пользователя в том, что он правильно разместит элементы во всех четырех областях, трудно. Кроме того, это может занять определенное время. Поэтому часто требуется расположить данные в сводной таблице программным образом. Эта операция производится при помощи объекта CubeField. Главное свойство этого объекта — Orientation, оно определяет, где будет находиться то или иное поле. Например, помещаем измерение Customers в область столбцов:

PT1.CubeFields («[Customers]»).Orientation = xlColumnField

Затем — измерение Time в область строк:

PT1.CubeFields («[Time]»).Orientation = xlRowField

Затем — измерение Product в область страницы:

PT1.CubeFields («[Product]»).Orientation = xlPageField

И наконец, показатель (числовые данные для анализа) Unit Sales:

PT1.CubeFields(«[Measures].[Unit Sales]»).Orientation = xlDataField

Теперь сводная таблица создана и с ней вполне можно работать. Однако часто необходимо выполнить еще одну операцию — раскрыть нужный уровень иерархии измерения. Например, если нас интересует поквартальный анализ, то нужно раскрыть уровень Quarter измерения Time (по умолчанию показывается только самый верхний уровень). Конечно, пользователь может сделать это самостоятельно, но не всегда можно рассчитывать, что он догадается, куда щелкнуть мышью. Программным образом раскрыть, например, иерархию измерения Time на уровень кварталов для 1997 года можно при помощи объектов PivotField и PivotItem:

Источник

Natalli01 Пользователь Сообщений: 39 Регистрация: 16.03.2013	Помогите в создании куба в excel 2010, пожалуйста

R Dmitry

Пользователь

Сообщений: 3103
Регистрация: 22.12.2012

Excel,MSSQL,Oracle,Qlik

17.03.2013 00:32:29

Создание автономного куба Olap поддерживается в 2003 офисе.

Тынц

Для 2010 используйте надстройку PowerPivot и будет Вам счастье.

Спасибо

Natalli01 Пользователь Сообщений: 39 Регистрация: 16.03.2013	и как с ней работать, чтобы создать куб?

R Dmitry

Пользователь

Сообщений: 3103
Регистрация: 22.12.2012

Excel,MSSQL,Oracle,Qlik

17.03.2013 12:19:39

В PP не надо создавать куб, главное импортировать данные необходимые для построения PT
Все необходимые данные будут храниться автономно в этом же файле excel.

PowerPivot

Спасибо

Natalli01 Пользователь Сообщений: 39 Регистрация: 16.03.2013	Добавила данные из access в PP, потом построила PT. А как сделать сводную диаграмму, чтобы данные были в 3-х мерном виде?

R Dmitry

Пользователь

Сообщений: 3103
Регистрация: 22.12.2012

Excel,MSSQL,Oracle,Qlik

17.03.2013 13:24:01

Что в Вашем понимании 3-х мерная диаграмма?

Спасибо

Natalli01 Пользователь Сообщений: 39 Регистрация: 16.03.2013

Natalli01 Пользователь Сообщений: 39 Регистрация: 16.03.2013	в excel 2003 можно было нажать кнопку создать куб olap и построить диаграмму по 3-м осям, а excel 2010 где можно найти эту кнопку? и постоить 3-х мерную диаграмму?

R Dmitry

Пользователь

Сообщений: 3103
Регистрация: 22.12.2012

Excel,MSSQL,Oracle,Qlik

17.03.2013 13:39:48

На вкладке «Параметры» при активной сводной, кнопка сводная диаграмма, выбираете нужную.

Спасибо

Natalli01 Пользователь Сообщений: 39 Регистрация: 16.03.2013	все равно строится диаграмма с 2-я осями((

R Dmitry

Пользователь

Сообщений: 3103
Регистрация: 22.12.2012

Excel,MSSQL,Oracle,Qlik

#11

17.03.2013 13:44:56

пример в студию

Спасибо

Natalli01 Пользователь Сообщений: 39 Регистрация: 16.03.2013	Вот пример Прикрепленные файлы PP.rar (61.79 КБ)

R Dmitry

Пользователь

Сообщений: 3103
Регистрация: 22.12.2012

Excel,MSSQL,Oracle,Qlik

#13

17.03.2013 20:00:47

Выберите тип : «Объемная гистограмма» она отображает 3 оси
В вашем примере достаточно и 2-х осей.

Спасибо

Natalli01 Пользователь Сообщений: 39 Регистрация: 16.03.2013	спасибо Может, подскажите, как создать диаграмму как на рисунке Или где можно прочесть про создание многомерных кубов Изменено: Natalli01 — 17.03.2013 20:26:26

Sergey Nego Пользователь Сообщений: 3 Регистрация: 16.09.2016 Nego Sergey	#15 16.09.2016 11:54:22 Самый простой и эффективный способ работать с OLAP- кубами это Excel – сводные таблицы! Кому интересно, как выглядят OLAP- кубы в Excel 2016

Источник

PivotTable Services с точки зрения пользователя

Объекты, применяемые при работе с PivotTable Services

Применение PivotTable Services в приложениях

Создание сводной таблицы на основе запроса к реляционной базе данных

Создание и просмотр локальных OLAP-кубов

Чтение локальных и серверных OLAP-кубов

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

Заключение

OLAP

В двух статьях, посвященных применению Web-компонентов Microsoft
Office (см. компакт-диски к № 11 и 12’2000), мы рассмотрели использование компонента
PivotTable List для анализа, фильтрации и суммирования данных, получаемых из
различных источников, а также для представления их в удобном для интерпретации
виде. Этот компонент позволил нам реализовать простейшую OLAP-функциональность
в различных бизнес-приложениях.

В настоящей статье мы продолжим рассмотрение реализации OLAP
в бизнес-приложениях. В качестве OLAP-инструмента будем использовать Microsoft
Excel, а в качестве средства разработки приложения, которое им управляет, —
Borland Delphi (для этой цели подойдет любая версия этого продукта начиная с
3.0). Однако для решения подобных задач подойдет и любое другое средство, поддерживающее
COM и механизмы Automation.

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

PivotTable Services с точки зрения пользователя

Для начала напомним, что сводная таблица Excel (pivot table)
представляет собой интерактивную таблицу, применяемую для суммирования или статистического
анализа большого количества исходных данных, обычно содержащихся в одном из
диапазонов ячеек Excel либо являющихся результатом запроса к какой-либо базе
данных. Строки и столбцы подобной интерактивной таблицы базируются на данных
из нескольких столбцов исходной таблицы. Ячейки сводной таблицы представляют
собой суммы значений одного из числовых полей исходной таблицы. При этом суммирование
производится для тех строк исходной таблицы, в которых значения столбцов, образовавших
столбцы и строки сводной таблицы, совпадают с именами строки и столбца сводной
таблицы, на пересечении которых находится данная ячейка. В некоторых публикациях
сводные таблицы иногда называют перекрестными таблицами, или кросс-таблицами
(crosstabs).

Каким образом можно получить в Excel сводную таблицу? Для этого
следует выбрать пункт меню Data | PivotTable and PivotChart Report (Данные
| Сводная таблица). После этого управление будет передано мастеру PivotTable
and PivotChart Wizard (Мастер сводных таблиц). Используя этот мастер, можно
указать источник данных (который иногда называется таблицей фактов — fact
table). Им может быть как диапазон ячеек самого Excel, так и результат запроса
к внешней базе данных, доступной через интерфейсы OLE DB или ODBC. При этом
можно выбрать поля таблицы, участвующие в формировании результата запроса, или
создать многотабличный запрос с помощью Microsoft Query.

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

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

В отличие от компонента PivotTable List данные из исходной
таблицы или запроса в самой сводной таблице показать нельзя. Тем не менее фактические
данные из исходной таблицы получить все-таки возможно. Делается это с помощью
пункта контекстного меню Group and Online | Show Detail (Группа и структура
| Отобразить детали) конкретной ячейки, при этом фактические данные помещаются
на вновь созданный лист рабочей книги.

Как мы уже отмечали выше, Excel PivotTable Services позволяет
создавать сводные таблицы на основе диапазонов Excel и запросов к базам данных.
Помимо этого сводные таблицы можно создавать также на основе OLAP-кубов, полученных
с помощью Microsoft SQL Server 7.0 или 2000 либо с помощью других OLAP-серверов,
доступных через OLE DB или ADO. В случае когда основой для создания сводной
таблицы является OLAP-куб, PivotTable Wizard не позволит переносить любое из
полей в любую область — в этом случае поля, которые можно использовать
для формирования строк, столбцов и фильтров, и поля, предназначенные для суммирования,
уже определены заранее в соответствии с тем, к какому типу OLAP-данных они принадлежат.
Если сводная таблица основана на OLAP-кубе, отображение исходных данных также
недоступно, то в OLAP-кубах этих данных просто нет.

Следует отметить, что Excel и сам может создавать локальные
OLAP-кубы, сохраняемые в файлах *.cub, либо описания кубов, сохраняемые в файлах
*.oqy и используемые при пересчете куба на основе исходной базы данных (такое
описание содержит сведения об источнике данных, запрос, использовавшийся при
создании куба, а также сведения о его структуре).

В общем случае OLAP-куб представляет собой структуру, содержащую
многомерные OLAP-данные, то есть измерения (dimensions) — описательные
данные, составляющие оси многомерного куба, и меры (measures) —
суммируемые количественные величины. Измерения могут содержать многоуровневые
иерархии значений, а меры являются агрегатными данными (суммы, средние, максимальные
или минимальные значения, количество записей) на основе полей реляционной СУБД,
представляющих интерес с точки зрения статистического анализа. Создавая OLAP-куб
на основе базы данных или запроса, мы преобразуем «плоские» наборы данных в
многомерные.

В большинстве случаев OLAP-кубы создаются в многомерных базах
данных, управляемых OLAP-серверами (такими как Microsoft SQL Server). Однако
иногда бывает удобно создать локальное многомерное хранилище данных в виде файла,
в котором данные организованы подобным способом. Это можно сделать с помощью
все тех же Microsoft PivotTable Services, например непосредственно из Excel
либо из другого приложения, применяющего Microsoft PivotTable Services. Отметим,
что OLAP-сервер для создания или использования такого OLAP-куба не требуется.

Вернемся на несколько шагов назад, к процессу создания локального
куба с помощью Excel. После создания запроса к базе данных пользователь может
вместо перехода к редактированию сводной таблицы выбрать опцию создания локального
OLAP-куба. После этого управление будет передано мастеру создания локальных
кубов — OLAP Cube Wizard. Этот эксперт позволяет создать локальный
OLAP-куб, отвечая на вопросы последовательно появляющихся диалогов.

Однако, несмотря на удобство и простоту использования мастеров
создания сводных таблиц и локальных кубов, использование PivotTable Services
по-прежнему остается процедурой, сложной для многих пользователей Excel, поскольку
в этом случае от них требуется набор весьма нетривиальных знаний о структуре
исходной базы данных, именах таблиц и полей, типах данных, а также представлений
о том, какими должны быть измерения будущего куба, что представляет собой их
иерархическая структура и какие поля следует выбрать в качестве его мер. Поэтому
во многих случаях наилучший способ предоставить пользователям возможность анализа
данных и при этом уберечь их от всех этих сложностей — это создавать приложения,
применяющие автоматизацию Excel (краткое введение в автоматизацию Excel можно
найти в статье Наталии Елмановой «Автоматизация приложений Microsoft Office
в примерах, часть 1», см. компакт-диск № 11’2000). Ниже мы покажем, как это
сделать. В частности, мы рассмотрим, как создавать в приложениях сводные таблицы
и локальные OLAP-кубы, как читать готовые локальные и серверные OLAP-кубы, как
создавать диаграммы, основанные на сводных таблицах и OLAP-кубах, и как сохранять
эти диаграммы в виде графических файлов.

Объекты, применяемые при работе с PivotTable Services

Перед тем как создавать приложения с использованием PivotTable Services, мы
должны понять, какие объекты Excel следует для этого применять. Небольшая часть
объектной модели Excel, применяемая для управления PivotTable Services, показана
на рис. 2.

Главный объект, применяемый при создании сводных таблиц, — объект PivotCache,
предоставляющий интерфейс к кэшу в оперативной памяти, создаваемому для данных
конкретной сводной таблицы (обратите внимание на то, что в объектной модели
компонента PivotTable List такого или подобного ему объекта нет). Этот
объект доступен как один из членов коллекции PivotCaches, получаемой при вызове
метода PivotCaches объекта Excel Workbook. Для создания нового объекта
PivotCache нам следует применить метод Add коллекции PivotCaches,
имеющий два параметра. Первый из них указывает на тип источника данных (листинг
1).

Второй параметр этого метода необязателен. Он может представлять
собой диапазон ячеек Excel или комбинацию из так называемой строки связи ADO
(ADO connection string) и имени источника данных. Этот параметр не требуется,
если первый параметр равен xlExternal и, следовательно, не важен для
наших будущих примеров — в них мы будем использовать только внешние источники
данных.

Наиболее важным из методов объекта PivotCache является метод CreatePivotTable,
который создает сводную таблицу (и добавляет новый объект PivotTable в коллекцию
PivotTables объекта Worksheet), основанную на источнике данных, определенном
в следующих свойствах объекта PivotCache (табл. 1).

Метод CreatePivotTable имеет два параметра. Первый из
них указывает на левую верхнюю ячейку будущей сводной таблицы, второй определяет
имя объекта PivotTable, например:

PC.CreatePivotTable 
  (WB.Worksheets[1].Cells[3,1],’PivotTable1');

Способ создания сводных таблиц или чтения OLAP-кубов следующий:
сначала мы создаем объект PivotCache, определяем его свойства Connection,
CommandType и CommandText, а затем вызываем его метод CreatePivotTable.
Ниже мы более подробно расскажем, как это сделать.

Применение PivotTable Services в приложениях

Во всех последующих примерах (которые можно найти на нашем
CD-ROM) мы будем использовать компонент Delphi TOleContainer со страницы
System палитры компонентов Delphi, применяемый обычно в качестве контейнера
OLE-документов (подавляющее большинство средств разработки, поддерживающих COM,
содержит подобные компоненты или объекты). В этом случае мы можем отобразить
окно Excel непосредственно на форме Delphi, а также решить, хотим ли мы предоставить
пользователю доступ к меню и инструментальной панели Excel (в последнем случае
необходимо просто поместить на форму компоненты TPanel и TMainMenu).

Первое, что следует сделать, — это создать экземпляр приложения
Excel внутри OLE-контейнера:

var
 WB : Variant; //объект Workbook
 PC : Variant; //объект PivotCache
 PT : Variant; //объект PivotTable
...
// Создаем объект в OLE-контейнере
 OleContainer1.CreateObject(‘Excel.Sheet’,False);
 WB := OleContainer1.OleObject;

Следующим шагом будет создание нового объекта PivotCache:

// Добавляем новый объект PivotCache
 PC := WB.PivotCaches.Add(xlExternal);

Затем нам нужно установить значения свойств Connection,
CommandType и CommandText объекта PivotCache и создать
сводную таблицу. Эти свойства зависят от типа источника данных. В следующем
разделе данной статьи мы расскажем более подробно, как это делается в случае
различных источников данных.

Создание сводной таблицы на основе запроса
к реляционной базе данных

Для создания сводной таблицы, основанной на запросе к базе
данных, мы будем использовать ODBC-источник, указывающий на базу данных NORTHWIND
из комплекта поставки Microsoft Access или Microsoft Office Professional (в
нашем случае источник данных называется NW):

const
 //ODBC-источник для создания сводных таблиц
 ODBC_DSN = ‘NW’;
…
 PC.Connection := ‘ODBC;DSN=’+ODBC_DSN;

Поскольку сводная таблица будет основана на запросе, его текст и будет значением
свойства CommandText объекта PivotCache (листинг
2). Затем мы создаем сводную таблицу (листинг 3).

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

Для любого поля в запросе к базе данных, на котором будет основана сводная
таблица, мы можем определить свойство Orientation соответствующего объекта
PivotField, доступного с помощью коллекции PivotFields (листинг 4).
Возможные значения свойства Orientation приведены в листинге
5.

Если в области строк, столбцов, фильтров или суммируемых данных сводной таблицы
более одного поля, мы можем расположить их в удобном для нас порядке (например,
в соответствии с уровнями той или иной иерархии данного измерения), используя
свойство Position объекта PivotField (листинг
6).

В результате выполнения приведенных выше фрагментов кода внутри OLE-контейнера
появится сводная таблица с соответствующими строками, столбцами и страницами
(рис. 3).

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

Создание и просмотр локальных OLAP-кубов

Для создания и просмотра локального OLAP-куба, основанного на запросе к базе
данных, необходимо создать объект PivotCache (с помощью метода Add
коллекции PivotCaches):

PC:=WB.PivotCaches.Add(xlExternal);

Этот объект будет содержать данные OLAP-куба, который мы собираемся
создать.

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

Определение параметров соединения, указывающих на будущий куб и источник
фактических данных для него, например:
```
OLEDB;Provider=MSOLAP;
      Initial Catalog=[OCWCube]; Data 
      Source=c:datanorthwind.mdb 
```
Остальные части этой строки отвечают за создание куба с помощью PivotTable
Services.
Предложение CREATE CUBE, в котором описываются измерения куба и уровни их
иерархии, а также меры куба. В общем случае оно имеет вид, показанный в листинге
7.
Предложение INSERT INTO, имеющее в общем случае следующий вид:

InsertInto=INSERT INTO OCWCube
    (
     <Dimension Name>.<Level Name>,
     [<Dimension Name>.<Level Name>,…]
     <Measure Name>,
     [<Measure Name>,… ]
    )
    OPTIONS ATTEMPT_ANALYSIS

Запрос SQL SELECT, возвращающий набор данных, используемых для создания
куба.

Пример кода для создания локального куба представлен в листинге
8.

Подробное описание синтаксиса предложений CREATE CUBE и INSERT
INTO можно найти в SQL Server 2000 Books Online, в разделе, посвященном программированию
Analysis Services.

Отметим, что файл *.oqy, формируемый с помощью Microsoft Query,
содержит строку, которую мы только что сформировали.

Теперь мы можем установить свойства Connection, CommandType
и CommandText вновь созданного объекта PivotCache:

//Определим свойства объекта PivotCache
PC.Connection := Connstr;
PC.CommandType := xlCmdCube;
PC.CommandText := ‘OCWCube’;

И наконец, нам следует определить, какие поля нужно использовать для формирования
строк, столбцов, страниц и какие меры необходимо отобразить в области данных
(листинг 9).

В данном случае мы воспользовались коллекцией CubeFields объекта PivotTable.
Обратите внимание на то, что при создании приложения с помощью Delphi обратиться
к членам коллекции CubeFields по имени нельзя, поэтому приходится использовать
их порядковые номера в коллекции (отметим, что при использовании Visual Basic
такой проблемы нет).

Сводная таблица, основанная на вновь созданном кубе, показана на рис.
4.

Обратите внимание: в данном случае в качестве полей для формирования
столбцов, строк и фильтров (страниц) мы можем использовать только измерения
куба, а в качестве полей для формирования области данных — меры куба (в
данном случае это поле SUM of ExtendedPrice). Эти ограничения, как уже было
сказано выше, являются следствием отсутствия фактических данных в OLAP-кубах.

Чтение локальных и серверных OLAP-кубов

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

Для открытия локального куба нам нужно определить параметры соединения для
доступа к кубу. В число этих параметров входят имя OLE DB-провайдера (в данном
случае это OLE DB Provider for OLAP Services) и местоположение *.cub-файла (листинг
10).

Для открытия OLAP-куба, созданного с помощью Microsoft SQL
Server 7.0 OLAP Services, нам нужно определить строку связи для доступа к OLAP-серверу
и имя OLAP-куба:

PC.Connection := ‘OLEDB; Provider=MSOLAP; ‘+
‘Data Source=maindesk;Initial Catalog=FoodMart; ‘
PC.CommandType := xlCmdCube;
PC.CommandText := ‘Sales’;

Если куб создан с помощью Microsoft SQL Server 2000 Analysis
Services, строка связи выглядит так:

PC.Connection := ‘OLEDB;Provider=MSOLAP.2;’+
‘Data Source=maindesk;Initial Catalog=FoodMart 2000; ‘
PC.CommandType := xlCmdCube;
PC.CommandText := ‘OCWCube’;

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

Как и в случае локальных кубов, отображение исходных фактических
данных в такой сводной таблице невозможно: серверные кубы, как и локальные,
фактических данных не содержат.

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

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

const
 xlColumnClustered = $00000033;
...
 WB.Charts.Add;
 WB.Charts[1].ChartType := xlColumnClustered;

Полученная диаграмма изображена на рис. 5.

Может возникнуть вполне закономерный вопрос: почему при создании
диаграммы мы не указали диапазон ячеек, из которого для нее берутся данные?
Дело в том, что если сводная таблица Excel в данный момент активна (то есть
имеет фокус ввода), она по умолчанию становится источником данных для будущей
диаграммы. Однако в общем случае при создании диаграмм диапазон, безусловно,
должен быть выбран.

Приведенный ниже фрагмент кода иллюстрирует, как сохранить
диаграмму в графическом файле формата BMP:

var
 BMP: TBitmap;
...
if SaveDialog2.Execute then
 begin
 //Копируем в буфер обмена область диаграммы
 WB.Charts[1].ChartArea.Copy;
 //Создаем объект TBitmap
 BMP:=TBitmap.Create;
 //Помещаем в него содержимое буфера обмена
 BMP.LoadFromClipboardFormat(cf_BitMap,
 ClipBoard.GetAsHandle(cf_Bitmap),0);
 //Сохраняем объект TBitmap в файле
 BMP.SaveToFile(SaveDialog2.FileName);
 //Освобождаем ресурсы
 BMP.Free;
 end;

В этом примере мы копируем график в буфер обмена, используя метод Copy
объекта ChartArea. Затем мы создаем объект TBitmap, помещаем в
него содержимое буфера обмена и сохраняем этот объект в файле.

Заключение

В данной статье мы рассмотрели, как можно применять PivotTable
Services в приложениях с помощью автоматизации Excel. Теперь мы знаем, как создать
сводную таблицу, основанную на запросе к базе данных, создать и прочесть локальный
OLAP-куб и сформировать строки, столбцы, фильтры и суммируемые данные в сводной
таблице. Помимо этого мы также выяснили, как получить сводную таблицу, основанную
на OLAP-кубах, созданных с помощью Microsoft SQL Server OLAP Services или Microsoft
SQL Server 2000 Analysis Services. Наконец, мы рассмотрели, как создать сводную
диаграмму и сохранить ее в графическом файле.

В дальнейшем мы продолжим рассмотрение OLAP. Нашей следующей
темой будет применение объектов Decision Support Objects (DSO), доступных пользователям
Microsoft SQL Server и применяемых для создания OLAP- и Data Mining-решений.

КомпьютерПресс 12’2000

Источник

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

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

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

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

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

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

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

Задача

Вспоминаем OLAP

Решение

Использование

Что дальше

Сводные таблицы Excel

Работа с файлами автономного куба

БЛОГ

Только качественные посты

Что такое Сводные таблицы Excel и OLAP кубы

Как пользоваться

Аналитические функции в Excel (функции кубов)

Функции кубов и сводные таблицы

Простой способ получить функции кубов

Функция КУБЗНАЧЕНИЕ()

Написание формулы «с нуля»

Синтаксис функции КУБЗНАЧЕНИЕ()

Некоторые сведения о кубах OLAP и моделях данных Power Pivot

Автозавершение в помощь

VBA в Excel Объект Excel.PivotTable и работа со сводными таблицами и кубами OLAP в Excel

10.8 Работа со сводными таблицами (объект PivotTable)

PivotTable Services с точки зрения пользователя

Объекты, применяемые при работе с PivotTable Services

Применение PivotTable Services в приложениях

Создание сводной таблицы на основе запроса к реляционной базе данных

Создание и просмотр локальных OLAP-кубов

Чтение локальных и серверных OLAP-кубов

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

Заключение

А вот еще интересные статьи:

Создание сводной таблицы на основе запроса
к реляционной базе данных