Кубы в эксель что это
Перейти к содержимому

Кубы в эксель что это

Что такое OLAP-куб и принцип его настройки

OLAP-куб – это инструмент, который напоминает сводную таблицу в Excel.

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

Всё начинается с того, что нужно вытащить числа по каким-то критериям.

Какие есть числа про проект? – Реквизиты-числа в объектах (задачах, например) и в справочниках.

Но нужно не просто взять и всё сложить, а вычленить по какому-то признаку. Этим признаком может быть:

реквизит-классификатор (выбор из преднастроенного списка);
реквизит-процесс (тот же классификатор, только с заданной последовательностью выбора значений);

OLAP на яблоках

Возьмём такую исходную таблицу (в терминологии ADVANTA – справочник):

Фрукт Количество
Яблоко 2
Груша 3
Апельсин 1
Яблоко 2
Груша 4
Апельсин 1
Яблоко 7
Груша 4
Апельсин 2

Первый этап преобразования – создать показатель-запрос, агрегировать (суммировать) данные по признаку:

Фрукт Количество
Яблоко 11
Груша 11
Апельсин 4

Усложним задачу. Предположим, что есть два разных проекта – проект Маши и проект Васи.

Проект Фрукт Количество
Маши Яблоко 2
Васи Груша 3
Маши Апельсин 1
Васи Яблоко 2
Маши Груша 4
Васи Апельсин 1
Маши Яблоко 7
Васи Груша 4
Маши Апельсин 2

Когда этот куб выстроится в виде OLAP-отчёта, получится:

Проект Фрукт Количество
Маши Яблоко 9
Груша 4
Апельсин 3
Васи Яблоко 2
Груша 7
Апельсин 1

Теперь представим, что измерений стало еще больше. Добавили критерий свежести.

Проект Фрукт Свежий Количество
Маши Яблоко да 2
Васи Груша да 3
Маши Апельсин нет 1
Васи Яблоко да 2
Маши Груша да 4
Васи Апельсин нет 1
Маши Яблоко да 7
Васи Груша да 4
Маши Апельсин нет 2
Маши Яблоко да 2
Васи Груша да 3
Маши Апельсин нет 1
Васи Яблоко да 2
Маши Груша да 4
Васи Апельсин да 1
Маши Яблоко нет 7
Васи Груша да 4
Маши Апельсин да 2

Но в OLAP-отчёте (или сводной таблице) просто появился еще один маркер «Свежесть»:

Проект Фрукт Свежий? Количество
Васи Апельсин да 1
нет 1
Груша да 14
Яблоко да 4
Маши Апельсин да 2
нет 4
Груша да 8
Яблоко да 11
нет 7

А можно показатели поменять местами…

Свежий? Фрукт Проект Количество
да Апельсин Васи 1
Маши 2
Груша Васи 14
Маши 8
Яблоко Васи 4
Маши 11
нет Апельсин Васи 1
Маши 4
Яблоко Маши 7

И так далее. Можно добавлять всё новые и новые измерения, по которым будет проводиться расчёт.
Измерений может быть не 2, как в обычной таблице, а не ограниченное количество:

От яблок к ADVANTA

«Какой фрукт», «Свежий?» и «Чей проект» – это измерения OLAP-куба;

то, что содержится в ячейках – результат вычислений по правилам из показателя.
В примере выше самый простой вариант – показатель-запрос с агрегированием… причём, судя по контексту, скорее всего, из справочника.
Этот же показатель-запрос – основа для всех последующих сложных вычислений.

OLAP-куб в ADVANTA – это заготовка вычислений.

Чтобы увидеть результат, нужно на основе созданного OLAP-куба создать OLAP-отчёт. И/или использовать этот OLAP-куб как источник для дальнейших вычислений.

  • product/olap/about.txt
  • Последнее изменение: 30.07.2020 13:46
  • — mayn

Если не указано иное, содержимое этой вики предоставляется на условиях следующей лицензии:
CC Attribution-Share Alike 4.0 International

Что такое кубы данных OLAP в Excel 2010?

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

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

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

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

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

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

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

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

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

Ris. 1. Analiz dannyh v Excel

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

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

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

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

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

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

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

Ris. 2. Preobrazovanie svodnoj tablitsy v nabor funktsij kuba

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

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

Ris. 3. Tablitsa na osnove formul kubov

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

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

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

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

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

= КУБЗНАЧЕНИЕ ( «ThisWorkbookDataModel» ;
«[Measures].[Total Sales]» ;
«[Products].[Category].[All].[Bikes]»

Ris. 4. Funktsiya KUBZNACHENIE v yachejke S10 vozvrashhaet obshhie prodazhi velosipedov kak i v svodnoj tablitse Рис. 4. Функция КУБЗНАЧЕНИЕ() в ячейке С10 возвращает продажи велосипедов за все годы, как и в сводной таблице Маленькая хитрость. Чтобы удобнее было читать формулы кубов, желательно, чтобы в каждой строке помещался только один аргумент. Можно уменьшить окно Excel. Для этого кликните на значке Свернуть в окно, находящемся в правом верхнем углу экрана. А затем отрегулируйте размер окна по горизонтали. Альтернативный вариант – принудительно переносить текст формулы на новую строку. Для этого в строке формул поставьте курсор в том месте, где хотите сделать перенос и нажмите Alt+Enter. Ris. 5. Svernut okno Рис. 5. Свернуть окно

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

Справка Excel абсолютно точна и абсолютно бесполезна для начинающих: КУБЗНАЧЕНИЕ(подключение; [выражение_элемента1]; [выражение_элемента2]; …) Подключение – обязательный аргумент; текстовая строка, представляющая имя подключения к кубу. Выражение_элемента – необязательный аргумент; текстовая строка, представляющая многомерное выражение, которое возвращает элемент или кортеж в кубе. Кроме того, «выражение_элемента» может быть множеством, определенным с помощью функции КУБМНОЖ. Используйте «выражение_элемента» в качестве среза, чтобы определить часть куба, для которой необходимо возвратить агрегированное значение. Если в аргументе «выражение_элемента» не указана мера, будет использоваться мера, заданная по умолчанию для этого куба. Прежде, чем перейти к объяснению синтаксиса функции КУБЗНАЧЕНИЕ, пару слов о кубах, моделях данных, и загадочном кортеже.

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

Кубы данных OLAP (Online Analytical Processing — оперативный анализ данных) были разработаны специально для аналитической обработки и быстрого извлечения из них данных. Представьте трехмерное пространство, где по осям отложены периоды времени, города и товары (рис. 5а). В узлах такой координатной сетки расположены значения различных мер: объем продаж, прибыль, затраты, количество проданных единиц и др. Теперь вообразите, что измерений десятки, или даже сотни… и мер тоже очень много. Это и будет многомерный куб OLAP. Создание, настройка и поддержание в актуальном состоянии кубов OLAP – дело ИТ-специалистов. Ris. 5a. Trehmernyj kub 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 Уровня. Ris. 5b. Ierarhiya kategorij tovarov Рис. 5б. Иерархия категорий товаров Элементы (отдельные члены) присутствуют на всех уровнях. Например, на уровне Category есть четыре элемента: Accessories, Bikes, Clothing, Components. Другие уровни имеют свои элементы. ­Меры – это вычисляемые значения, например, объем продаж. Меры в кубах хранятся в собственном измерении, называемом [Measures] (см. ниже рис. 9). Меры не имеют иерархий. Каждая мера рассчитывает и хранит значение для всех измерений и всех элементов, и нарезается в зависимости от того, какие элементы измерений мы поместим на оси. Еще говорят, какие зададим координаты, или какой зададим контекст фильтра. Например, на рис. 5а в каждом маленьком кубике рассчитывается одна и та же мера – Прибыль. А возвращаемое мерой значение зависит от координат. Справа на рисунке 5а показано, что Прибыль (в трех координатах) по Москве в октябре на яблоках = 63 000 р. Меру можно трактовать, и как одно из измерений. Например, на рис. 5а вместо оси Товары, разместить ось Меры с элементами Объем продаж, Прибыль, Проданные единицы. Тогда каждая ячейка и будет каким-то значением, например, Москва, сентябрь, объем продаж. Кортеж – несколько элементов разных измерений, задающие координаты по осям куба, в которых мы рассчитываем меру. Например, на рис. 5а Кортеж = Москва, октябрь, яблоки. Также допустимый кортеж – Пермь, яблоки. Еще один – яблоки, август. Не вошедшие в кортеж измерения присутствуют в нем неявно, и представлены членом по умолчанию [All]. Таким образом, ячейка многомерного пространства всегда определяется полным набором координат, даже если некоторые из них в кортеже опущены. Нельзя включить два элемента одного измерения в кортеж, не позволит синтаксис. Например, недопустимый кортеж Москва и Пермь, яблоки. Чтобы реализовать такое многомерное выражение потребуется набор двух кортежей: Москва и яблоки + Пермь и яблоки. Набор элементов – несколько элементов одного измерения. Например, яблоки и груши. Набор кортежей – несколько кортежей, каждый из которых состоит из одинаковых измерений в одной и той же последовательности. Например, набор из двух кортежей: Москва, яблоки и Пермь, бананы.

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

Вернемся к синтаксису функции КУБЗНАЧЕНИЕ. Воспользуемся автозавершением. Начните ввод формулы в ячейке: =КУБЗНАЧЕНИЕ( » Excel предложит все доступные в книге Excel подключения: Ris. 6. Podklyuchenie k modeli dannyh Power Pivot Рис. 6. Подключение к модели данных Power Pivot всегда называется ThisWorkbookDataModel Ris. 7. Podklyucheniya k dostupnym kubam Рис. 7. Подключения к кубам Продолжим ввод формулы (в нашем случае для модели данных): =КУБЗНАЧЕНИЕ( » ThisWorkbookDataModel » ; » Автозавершение предложит все доступные таблицы и меры модели данных: Ris. 8. Dostupnye elementy pervogo urovnya imena tablits i nabor mer Рис. 8. Доступные элементы первого уровня – имена таблиц и набор мер (выделен) Выберите значок Measures. Поставьте точку: =КУБЗНАЧЕНИЕ( » ThisWorkbookDataModel » ; » [Measures]. Автозавершение предложит все доступные меры: Ris. 9. Dostupnye elementy vtorogo urovnya v nabore mer Рис. 9. Доступные элементы второго уровня в наборе мер Выберите меру [Total Sales]. Добавьте кавычки, закрывающую скобку, нажмите Enter. =КУБЗНАЧЕНИЕ( » ThisWorkbookDataModel » ; » [Measures].[Total Sales] » ) Ris. 10. Formula KUBZNACHENIE v yachejke Excel Рис. 10. Формула КУБЗНАЧЕНИЕ в ячейке Excel Аналогичным образом можете добавить третий аргумент в формулу:

  • СТО МИ пользователя «Составление отчётов из OLAP-куба с помощью Microsoft Excel»

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

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

    Данная инструкция описывает действия по созданию шаблона отчёта и загрузке в него требуемых данных из OLAP-куба с помощью Microsoft Excel.

    Подключение к кубу

    Прежде, чем приступить к составлению отчётов, необходимо подключиться к соответствующему кубу. В качестве примера подключимся к кубу аналитики Lync. Если подключение с вашего компьютера к этому кубу производится впервые, то сначала потребуется его создать. Для этого надо перейти на закладку Данные панели инструментов Excel в выбрать пункт «Из других источников | Из служб аналитики» (рисунок 1).

    Создание подключения к кубу
    538 × 363 пикс. &nbsp Открыть в новом окне Создание подключения к кубу

    Затем задать имя сервера и нажать «Далее» . Выбранный пример куба расположен на сервере datahouse, поэтому вводим это имя в поле «Имя сервера» (рисунок 2).

    Ввод имени сервера
    516 × 359 пикс. &nbsp Открыть в новом окне Ввод имени сервера

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

    Выбор базы данных из списка
    526 × 373 пикс. &nbsp Открыть в новом окне Выбор базы данных из списка

    На следующем шаге задать подключению понятное имя и при необходимости заполнить поле описания, после чего нажать Готово (рисунок 4).

    Ввод имени и описания подключения
    516 × 431 пикс. &nbsp Открыть в новом окне Ввод имени и описания подключения

    В окне «Импорт данных», которое появится сразу после этого, можно просто нажать OK (рисунок 5).

    Импорт данных
    316 × 242 пикс. &nbsp Открыть в новом окне Импорт данных

    Если ранее вы уже создавали подключение к кубу Lync1 на вашем компьютере, его можно просто выбрать из списка и нажать Открыть (рисунок 6).

    Открытие существующего подключения
    555 × 522 пикс. &nbsp Открыть в новом окне Открытие существующего подключения

    Работа с мерами и измерениями куба

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

    Сводная таблица и панель со списком полей
    831 × 711 пикс. &nbsp Открыть в новом окне Сводная таблица и панель со списком полей

    Числовые поля, представляющие меры, то есть числовые данные, распределены по группам, отмеченным знаком (рисунок 8).

    Числовые данные
    282 × 183 пикс. &nbsp Открыть в новом окне Числовые данные

    Эти поля можно поместить только в область значений, в другие три области их поместить не получится (рисунок 9).

    Возможность помещения в область значений
    282 × 499 пикс. &nbsp Открыть в новом окне Возможность помещения в область значений

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

    Способы помещения полей в область значений
    280 × 273 пикс. &nbsp Открыть в новом окне Способы помещения полей в область значений

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

    Атрибуты измерений
    283 × 278 пикс. &nbsp Открыть в новом окне Атрибуты измерений

    Эти поля можно поместить в любую из трёх областей: Фильтр отчета, Названия строк и Названия столбцов. Это те поля, по которым отчёт будет сгруппирован и отфильтрован (рисунок 12).

    Помещение полей в области
    284 × 501 пикс. &nbsp Открыть в новом окне Помещение полей в области

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

    Если вы случайно закрыли панель полей, вернуть её назад можно щёлкнув правой кнопкой мыши на области таблицы и выбрав пункт меню «Показать список полей» (рисунок 13).

    Контекстное меню открытия списка полей
    285 × 326 пикс. &nbsp Открыть в новом окне Контекстное меню открытия списка полей

    Составление отчёта

    В качестве примера добавим в отчёт два числовых поля: Количество сессий и Продолжительность сессии (в мин.), рисунок 14.

    Пример добавления числовых полей
    282 × 272 пикс. &nbsp Открыть в новом окне Пример добавления числовых полей

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

    Отображение данных по полям
    440 × 270 пикс. &nbsp Открыть в новом окне Отображение данных по полям

    Как правило, сами по себе числовые значения не представляют практического интереса – они просто отражают итоговые данные по всему кубу. Для составления отчёта необходимо сгруппировать эти данные, разместив, например, имена пользователей в строках и даты в столбцах (рисунок 16).

    Группировка данных
    782 × 400 пикс. &nbsp Открыть в новом окне Группировка данных

    Для группировки данных по пользователям мы выбрали атрибут Имя измерения Пользователь 1 (рисунок 17).

    Группировка данных по пользователям
    283 × 502 пикс. &nbsp Открыть в новом окне Группировка данных по пользователям

    Для группировки данных по датам мы выбрали иерархию Год-Месяц-Дата измерения Время (рисунок 18).

    Группировка данных по датам
    283 × 502 пикс. &nbsp Открыть в новом окне Группировка данных по датам

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

    Иерархия
    458 × 181 пикс. &nbsp Открыть в новом окне Иерархия

    Однако, столбцы и строки, в которых присутствуют иерархии, можно раскрывать, просматривая те же данные в разрезе полей, расположенных на следующем уровне (рисунок 20).

    Раскрытие строк и столбцов с иерархией
    659 × 242 пикс. &nbsp Открыть в новом окне Раскрытие строк и столбцов с иерархией

    При раскрытии уровней иерархии появляются дополнительные колонки – промежуточные итоги. Если эти колонки не нужны (иногда они могут мешать), их можно скрыть, щёлкнув правой кнопкой мыши по заголовку такой колонки и сняв галочку с пункта «Промежуточные итоги» в выпадающем меню, представленном на рисунке 21.

  • Добавить комментарий

    Ваш адрес email не будет опубликован. Обязательные поля помечены *