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

КУБЗНАЧЕНИЕ

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2013 Excel для iPad Excel Web App Excel для iPhone Excel для планшетов с Android Excel для телефонов с Android Еще. Меньше

В этой статье описаны синтаксис формулы и использование функции КУБЗНАЧЕНИЕ в Microsoft Excel.

Описание

Возвращает агрегированное значение из куба.

Синтаксис

Аргументы функции КУБЗНАЧЕНИЕ описаны ниже.

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

Замечания

При оценке функции КУБЗНАЧЕНИЕ в ячейке временно выводится сообщение «#ОЖИДАНИЕ_ДАННЫХ…», пока все данные не будут найдены.
Если для аргумента «выражение_элемента» используется ссылка на ячейку, и эта ссылка содержит функцию КУБ, то «выражение_элемента» использует многомерное выражение для элемента в ячейке, на которую указывает ссылка, а не значение, которое отображается в этой ячейке.
Если имя подключения не является допустимым подключением к книге, хранящимся в книге, функция CUBEVALUE возвращает #NAME? (значение ошибки). Если сервер OLAP не запущен, недоступен или возвращает сообщение об ошибке, функция CUBEVALUE возвращает #NAME? (значение ошибки).
Если хотя бы один элемент кортежа недопустим, функция CUBEVALUE возвращает #VALUE! (значение ошибки).

Функция КУБЗНАЧЕНИЕ возвращает ошибку «#Н/Д» в следующих случаях:

Неправильный синтаксис аргумента «выражение_элемента».
Элемента, определяемого аргументом «выражение_элемента», не существует в кубе.
Кортеж не является допустимым, поскольку для указанных значений отсутствует пересечение. (Такая ситуация возможна для нескольких элементов из одной и той же иерархии).
Множество содержит по меньшей мере один элемент с измерением, отличным от других элементов.
Функция КУБЗНАЧЕНИЕ может возвращать значение ошибки «#Н/Д» при ссылке на сеансовый объект, например на вычисляемый компонент или именованный набор, в сводной таблице при совместном использовании подключения, когда сводная таблица удалена или происходит преобразование таблицы в формулы. (На вкладке Параметры в группе Сервис нажмите кнопку Средства OLAP, а затем — кнопку Преобразовать в формулы.)

Проблема: пустые значения преобразуются в пустые строки

Если в excel ячейка не содержит данных, так как вы никогда не изменяли ее или удалили содержимое, ячейка содержит пустое значение. Во многих системах баз данных пустое значение называется значением NULL. Пустое значение или значение NULL буквально означает «Нет значения». Однако формула никогда не может возвращать пустую строку или значение NULL. Формула всегда возвращает одно из трех значений: числовое значение; текстовое значение, которое может быть строкой нулевой длины или значением ошибки, например #NUM! или #VALUE.

Если формула содержит функцию CUBEVALUE, подключенную к базе данных OLAP, и запрос к этой базе данных приводит к значению NULL, Excel преобразует это значение NULL в строку нулевой длины, даже если формула возвращает числовое значение. Это может привести к ситуации, когда диапазон ячеек содержит сочетание числовых и строковых значений нулевой длины, и эта ситуация может повлиять на результаты других формул, ссылающихся на этот диапазон ячеек. Например, если A1 и A3 содержат числа, а A2 содержит формулу с функцией CUBEVALUE, которая возвращает строку нулевой длины, следующая формула вернет #VALUE! :

Чтобы предотвратить такую ситуацию, следует проверять ячейки на наличие пустой строки с помощью функции ЕТЕКСТ, а затем использовать функцию ЕСЛИ для замены пустой строки на 0 (ноль), как показано в следующем примере.

Функцию КУБЗНАЧЕНИЕ можно также вложить в условие ЕСЛИ, которое возвращает значение «0», если функция КУБЗНАЧЕНИЕ возвращает пустую строку, как показано в следующем примере.

=ЕСЛИ (КУБЗНАЧЕНИЕ(«Продажи»,»[Показатели].[Сумма]»,»[Время].[2004]»,»[Все товары].[Напитки]»)=»», 0, КУБЗНАЧЕНИЕ(«Продажи»,»[Показатели].[Сумма]»,»[Время].[2004]»,»[Все товары].[Напитки]»))

Обратите внимание на то, что функция СУММ не требует проверки на наличие пустой строки, так как при вычислении ее значения пустые строки автоматически игнорируются.

Анализ данных кубов OLAP в Service Manager с помощью Excel

Поддержка этой версии Service Manager завершена. Мы рекомендуем выполнить обновление до Service Manager 2022.

Service Manager включает предопределенные кубы данных OLAP( Microsoft Online Analytic Processing), которые подключаются к хранилищу данных для извлечения данных, чтобы управлять ими с помощью Microsoft Excel в табличном режиме. При открытии куб данных отображается в виде листа, содержащего пустой отчет сводной таблицы. Сведения, указывающие источник данных OLAP, встроены в лист. При открытии отчета или обновлении подключения к данным, приложение Excel использует службы SQL Server Analysis Services (SSAS), чтобы подключиться к хранилищу данных для получения ключевых показателей эффективности (KPI) и других данных. После открытия текущий лист содержит моментальный снимок или подмножество данных из хранилища данных. При сохранении листа сведения о подключении к источнику данных, ключевые показатели эффективности и все внесенные вами настройки сохраняются вместе с ним. Если вы сохраните лист в библиотеке анализа, его можно будет повторно открыть без использования консоли Service Manager.

Ключевые показатели эффективности, включенные в кубы Service Manager данных, представляют собой предопределенные, специальные вычисляемые меры, определенные на сервере, которые позволяют отслеживать ключевые показатели эффективности, такие как состояние (соответствует ли текущее значение определенному числу?) и тренд (какое значение с течением времени?). При отображении этих показателей KPI в сводной таблице сервер может отправлять соответствующие значки, представленные в стиле нового набора значков Excel, для отображения уровней состояния, находящихся выше или ниже определенного порога (например, значком стоп-сигнала), а также тренда значения — рост или убывание (к примеру, с помощью значков со стрелками).

Сводные таблицы могут помочь быстро и легко создавать полезные отчеты. Сводные таблицы, отображаемые в Service Manager кубах данных, включают множество предопределенных категорий ключевых показателей эффективности, называемых группами мер или измерениями. Эти группы предоставляют обзор данных с высочайшего уровня классификации и позволяют облегчить фокусировку анализа. Большинство групп мер имеют множество дополнительных уровней подкатегорий и индивидуальных полей. Все категории, подкатегории и поля содержатся в списке полей сводной таблицы. Чтобы создать простой отчет, выполните следующие действия:

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

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

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

Просмотр и анализ куба данных Service Manager OLAP с помощью Excel

Следующую процедуру можно использовать для просмотра и анализа куба данных OLAP (Microsoft Online Analytic Processing) из System Center Service Manager с Microsoft Excel. Можно также сохранить книги в библиотеке аналитики. С помощью списка полей PivotTable можно перетаскивать поля из куба в книгу. Чтобы использовать следующую процедуру, на компьютере с консолью Service Manager необходимо установить Microsoft Excel 2007 или более поздней версии.

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

Просмотр и анализ куба OLAP в приложении Excel

В консоли Service Manager выберите Data Warehouse, разверните узел Data Warehouse и выберите Кубы.
В области Кубики выберите имя куба, а затем в разделе Задачи выберите Анализ куба в Excel. Например, выберите значение SystemCenterWorkItemsCube , чтобы анализировать данный куб.
Когда в Excel откроется лист, в него можно перетащить поля из списка полей сводной таблицы и создать срезы и диаграммы.
- Например, чтобы увидеть общее количество в данный момент открытых инцидентов, разверните группу IncidentDimGroupи выберите пункт Открытые инциденты.
- Можно добавить дополнительные поля, чтобы выполнить более сложный анализ. К примеру, можно добавить компьютеры из измерения ComputerDim , выбрав поле DisplayName , чтобы увидеть количество инцидентов, затрагивающих отдельные компьютеры.
При желании вы можете сохранить рабочую книгу в общую папку или на другой общий ресурс (например, в библиотеку анализа). Дополнительные сведения о библиотеке анализа см. в статье Использование библиотеки анализа.

Использование срезов Excel для просмотра Service Manager данных куба OLAP

Наиболее полезные данные отчетов, доступные из Service Manager, представлены в виде кубов данных. Одним из способов просматривать данные кубов и манипулировать ими являются сводные таблицы Microsoft Excel. Срезы в Excel можно использовать для фильтрации данных сводной таблицы.

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

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

Дополнительные сведения о срезах Excel см. в статье Использование срезов для фильтрации данных сводной таблицы на веб-сайте Microsoft Office.

Дальнейшие действия

Что такое 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: развенчиваем мифы

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

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

Что такое куб данных

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

Дизайн-паттерн реализован в основном в двух контекстах:

Как предварительно агрегированная таблица в реляционной базе данных.
Как объект данных в специализированной OLAP-системе.

Сегодня эти проблемы стоят не так остро. Но к этой теме мы вернёмся позже.

Кубы данных в реляционных БД

Рассмотрим пример — таблицу с данными по продажам по региону, магазину и продукту:

Чтобы создать куб данных из взятого для примера дата-сета, нужно агрегировать сумму цен по каждой комбинации разрезов. В PostgreSQL и MS SQL имеется подблок GROUP BY под названием CUBE, который сделает эту работу за вас.

Вот как выглядит запрос CUBE с этими данными:

SELECT SUM(price) as total_sales, region, store, product FROM sales GROUP BY CUBE(region, store, product);

Поскольку у взятого для примера дата-сета есть три разреза: регион, магазин, продукт, — вышеуказанный запрос выдаст восемь сгруппированных множеств и 29 строк данных (исходя из количества уникальных значений по разрезам).

Чтобы рассчитать общее количество сгруппированных множеств, созданных кубом данных, воспользуйтесь формулой: 2^number_of_dimensions.

Сгруппированные множества для этого примера:

(region, store, product), (region, store), (region, product), (store, product), (region), (store), (product), ()

В этом кубе данных нет ничего чрезмерно сложного: мы просто обобщили данные по каждому сочетанию разрезов. В прошлом дата-инженер создавал похожую таблицу и передавал её аналитику в виде общего представления по продажам.

Кубы данных в OLAP-системах

Как мы показали выше, куб данных можно реализовать в таблице стандартной БД, но их чаще используют в приложении Online Analytical Processing (OLAP).

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

Краткий исторический экскурс

Сейчас давайте ненадолго вернёмся в прошлое и разберёмся, что такое OLAP-системы и почему их вообще создали.

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

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

Приход OLAP-систем

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

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

Roll up: объединить показатели в категории разрезов уровнем выше (город => область).
Drill Down: разбить обобщённые категории на категории уровнем ниже (область => город).
Slice and Dice: выбрать сегмент данных из одного или нескольких разрезов.
Pivot: поменять оси табличного представления.

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

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

OLAP drill up&down en.png со страницы Wikipedia

Вчера и сегодня

С тех пор как разработчики разворачивали кубы данных и OLAP-системы в качестве решения для бизнес-аналитики, технологический ландшафт кардинально изменился. Эффективность обработки данных экспоненциально выросла, а благодаря облачным платформам вроде AWS, GCP и VK Cloud, ещё и существенно подешевела. Кроме того, колоночные хранилища упростили доступ к большому объёму данных при стандартных нагрузках.

Благодаря этим переменам необходимость в кубах и OLAP-системах заметно снизилась.

Сегодня аналитики могут безо всяких проблем на лету агрегировать данные по разным разрезам с помощью платформ типа BigQuery и Snowflake. Да и использование GUI для сведения воедино больших объёмов данных уже не вызывает трудностей. Такие инструменты, как DOMO и PowerBI, позволяют аналитикам с лёгкостью фрагментировать и анализировать данные вдоль и поперёк.

Заключение

Вернёмся к исходному вопросу — так что же такое OLAP-куб? Если очень коротко, это многомерная сводная таблица в OLAP-системе. Если не брать в расчёт особенности технической реализации, она похожа на сводную таблицу Excel.

Команда VK Cloud развивает собственные Big Data-решения. Будем признательны, если вы их протестируете и дадите обратную связь. Для тестирования пользователям при регистрации начисляем 3000 бонусных рублей.