Как сделать куб в Excel
Обновлено: 06.07.2024
Применимо к: SQL Server Analysis Services Azure Analysis Services Power BI Premium
Новый куб можно создать с помощью мастера кубов в SQL Server Data Tools.
Чтобы создать новый куб
В обозревателе решений щелкните правой кнопкой мыши Кубы и выберите Новый куб.
На странице "Выбор метода создания" мастера кубов выберите "Использовать существующие таблицы" и нажмите "Далее".
Иногда вам может понадобиться создать куб без использования существующих таблиц. Чтобы создать пустой куб, выберите Создать пустой куб. Чтобы сгенерировать таблицы, выберите «Создать таблицы» в источнике данных.
На странице "Выбор таблиц групп мер" выполните следующие действия:
В списке Представление источника данных выберите представление источника данных.
В списке Таблицы групп мер выберите таблицы, которые будут использоваться для создания групп мер.
Нажмите "Далее".
На странице "Выбор мер" выберите меры, которые вы хотите включить в куб, и нажмите "Далее".
При желании вы можете изменить названия мер и групп мер.
На странице "Выбор существующих измерений" выберите существующие измерения для включения в куб, а затем нажмите "Далее".
Страница "Выбор существующих измерений" появляется, если в базе данных уже существуют измерения для любой из выбранных групп мер.
На странице "Выбор новых измерений" выберите новые измерения для создания и нажмите "Далее".
Страница "Выбрать новые измерения" появляется, если какие-либо таблицы подходят для таблиц измерений, и эти таблицы еще не использовались существующими измерениями.
На странице "Выбор отсутствующих ключей измерения" выберите ключ для измерения и нажмите кнопку "Далее".
Страница "Выбрать отсутствующие ключи измерений" появляется, если для какой-либо из указанных таблиц измерений не определен ключ.
На странице "Завершение работы мастера" введите имя нового куба и просмотрите структуру куба. Если вы хотите внести изменения, нажмите «Назад»; в противном случае нажмите Готово.
Вы можете использовать конструктор кубов после завершения работы мастера куба для настройки куба. Вы также можете использовать Dimension Designer для добавления, удаления и настройки атрибутов и иерархий в созданных вами измерениях.
Поддержка этой версии Service Manager подошла к концу, мы рекомендуем вам перейти на Service Manager 2022.
Service Manager включает предопределенные кубы данных Microsoft Online Analytical Processing (OLAP), которые подключаются к хранилищу данных для извлечения данных, чтобы вы могли манипулировать ими с помощью Microsoft Excel в виде таблиц. При открытии куб данных представляется в виде рабочего листа, содержащего пустой отчет сводной таблицы. Информация, определяющая источник данных OLAP, встроена в рабочий лист. Когда вы открываете отчет или обновляете подключение к данным, Excel использует службы Microsoft SQL Server Analysis Services (SSAS) для подключения к хранилищу данных для получения ключевых показателей эффективности (KPI) и других данных. После открытия текущий рабочий лист содержит моментальный снимок или подмножество данных из хранилища данных. Если вы сохраняете рабочий лист, информация о подключении к источнику данных, KPI и любые другие настройки, которые вы сделали, сохраняются вместе с ним. Если вы сохраните лист в библиотеке анализа, вы сможете открыть его позже, не используя консоль Service Manager.
КПЭ, включенные в кубы данных Service Manager, представляют собой предопределенные, специальные вычисляемые показатели, определенные на сервере, которые позволяют вам отслеживать КПЭ, такие как статус (соответствует ли текущее значение определенному числу?) и тренд ( какова ценность с течением времени?). Когда эти ключевые показатели эффективности отображаются в сводной таблице, сервер может отправлять соответствующие значки, похожие на новый набор значков Excel, чтобы указать уровни состояния, которые выше или ниже определенного порога (например, со значком стоп-сигнала), или значение имеет тенденцию вверх или вниз (например, со значком стрелки направления).
Сводные таблицы помогут вам быстро и легко создавать полезные отчеты. Сводные таблицы, отображаемые в кубах данных Service Manager, включают в себя множество предопределенных категорий KPI, называемых группами мер или измерениями. Эти группы представляют собой высший уровень категоризации и помогают изучить данные и сфокусировать анализ. В свою очередь, большинство групп мер имеют множество дополнительных уровней подкатегорий и отдельных полей. Все категории, подкатегории и поля содержатся в списке полей сводной таблицы. Например, вы можете создать простой отчет, выполнив следующие действия:
- Используя список полей сводной таблицы, выберите категорию и добавьте ее в виде строки.
- Выберите вторую категорию и добавьте ее в качестве столбца.
- Выберите категорию или подкатегорию, чтобы добавить значения.
После того как вы создали свой отчет, вы можете добавить любой уровень дополнительной сложности, сортируя, фильтруя, форматируя и добавляя вычисления и диаграммы. Вы также можете входить и выходить из категорий, продолжая анализ.
Для просмотра демонстрации создания отчета и управления данными в Excel с использованием данных из куба данных OLAP в сводной таблице см. раздел Детализация данных сводной таблицы.
Просмотр и анализ куба данных OLAP Service Manager с помощью Excel
Следующую процедуру можно использовать для просмотра и анализа куба данных Microsoft Online Analytical Processing (OLAP) из System Center — Service Manager с Microsoft Excel. Вы также можете сохранить свои рабочие книги в аналитической библиотеке. Используя список полей сводной таблицы, вы можете перетаскивать поля из куба в книгу. Чтобы использовать следующую процедуру, на компьютере с консолью Service Manager должен быть установлен Microsoft Excel 2007 или более поздней версии.
При первом анализе куба с помощью Excel загрузка может занять несколько минут.
Просмотр и анализ куба данных OLAP с помощью Excel
- В консоли Service Manager щелкните Хранилище данных, разверните узел Хранилище данных и щелкните Кубы.
- На панели «Кубики» выберите имя куба, а затем в разделе «Задачи» нажмите «Анализ куба в Excel». Например, выберите SystemCenterWorkItemsCube и проанализируйте его.
- Когда лист открывается в Excel, вы можете перетаскивать поля из списка полей сводной таблицы и создавать срезы и диаграммы.
- Например, если вы хотите просмотреть общее количество открытых инцидентов, разверните IncidentDimGroup и выберите Открытые инциденты.
- Вы можете добавить дополнительные поля для проведения более сложного анализа. Например, вы можете добавить компьютеры из измерения ComputerDim, выбрав поле DisplayName, чтобы увидеть количество инцидентов, затрагивающих разные компьютеры.
- При желании вы можете сохранить книгу в общей папке или другом месте общего доступа, например в библиотеке анализа. Дополнительную информацию о библиотеке анализа см. в разделе Как использовать библиотеку анализа.
Использование срезов Excel для просмотра данных куба Service Manager OLAP
Самые полезные отчетные данные, доступные в Service Manager, представлены в виде кубов данных. Одним из методов просмотра данных куба и управления ими является использование сводных таблиц в Microsoft Excel. Вы можете использовать срезы в Excel для фильтрации данных сводной таблицы.
Слайсеры — это простые в использовании компоненты фильтрации, содержащие набор кнопок, которые позволяют быстро фильтровать данные в отчете сводной таблицы без необходимости открывать раскрывающиеся списки для поиска элементов, которые нужно отфильтровать.
При использовании обычного фильтра отчета сводной таблицы для фильтрации нескольких элементов фильтр указывает только то, что фильтруются несколько элементов, и вам нужно открыть раскрывающийся список, чтобы найти сведения о фильтрации. Однако срез четко маркирует примененный фильтр и предоставляет подробные сведения, чтобы вы могли легко понять данные, отображаемые в отфильтрованном отчете сводной таблицы.
Дополнительную информацию о срезах Excel см. в разделе Использование срезов для фильтрации данных сводной таблицы на веб-сайте Microsoft Office.
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel для iPad Excel Web App Excel для iPhone Excel для планшетов Android Excel 2010 Excel 2007 Excel для Mac 2011 Excel для телефонов Android Excel Starter 2010 Еще. Меньше
В этой статье описаны синтаксис формулы и использование функции КУБЗНАЧЕНИЕ в Microsoft Excel.
Описание
Возвращает агрегированное значение из куба.
Синтаксис
КУБЗНАЧЕНИЕ(соединение, [выражение_члена1], [выражение_члена2], …)
Синтаксис функции КУБЗНАЧЕНИЕ имеет следующие аргументы:
Требуется подключение. Текстовая строка имени подключения к кубу.
Выражение_члена Необязательно. Текстовая строка многомерного выражения (MDX), результатом которой является элемент или кортеж в кубе. Кроме того, выражение_члена может быть набором, определенным с помощью функции КУБМНОЖ. Используйте member_expression в качестве среза, чтобы определить часть куба, для которой возвращается агрегированное значение. Если в member_expression не указана мера, используется мера по умолчанию для этого куба.
Примечания
Если для member_expression используется ссылка на ячейку, и эта ссылка на ячейку содержит функцию CUBE, то member_expression использует выражение MDX для элемента в ячейке, на которую указывает ссылка, а не значение, отображаемое в этой ячейке, на которую указывает ссылка.
Неверный синтаксис member_expression.
Член, указанный в member_expression, не существует в кубе.
Кортеж недействителен, так как для указанных значений нет пересечения. (Это может произойти с несколькими элементами из одной иерархии.)
Набор содержит по крайней мере один элемент с параметром, отличным от других элементов.
Проблема: нулевые значения преобразуются в строки нулевой длины
Чтобы предотвратить это, вы можете проверить строку нулевой длины с помощью функции ISTEXT и с помощью функции ЕСЛИ заменить нулевую длину на 0 (ноль), как показано в следующем примере:
Кроме того, вы можете вложить функцию КУБЗНАЧЕНИЕ в условие ЕСЛИ, которое возвращает значение 0, если функция КУБЗНАЧЕНИЕ возвращает строку нулевой длины, как показано в следующем примере:
=ЕСЛИ (КУБЗНАЧЕНИЕ("Продажи","[Показатели].[Прибыль]","[Время].[2004]","[Все продукты].[Напитки]")="", 0, КУБЗНАЧЕНИЕ ("Продажи","[Показатели].[Прибыль]","[Время].[2004]","[Все продукты].[Напитки]"))
Обратите внимание, что функция СУММ не требует этого теста для строки нулевой длины, поскольку она автоматически игнорирует строки нулевой длины при вычислении возвращаемого значения.
В этой главе из книги Формулы и функции Microsoft Excel 2010 наизнанку рассматриваются функции куба в Microsoft Excel 2010.
Функции куба появились в Microsoft Excel 2007. Они используются при подключении к внешним источникам данных SQL и предоставляют инструменты анализа. Кубы данных — это многомерные наборы данных, которые можно хранить в электронной таблице, предоставляя средства для суммирования информации из источника необработанных данных. Куб отличается от запросов в Microsoft Access или Microsoft SQL Server, поскольку данные в кубе уже сгруппированы в иерархии, а рассчитанные показатели сохраняются в кубе. Это дает пользователю два преимущества: сводная информация легкодоступна, а большинство ресурсоемких вычислений выполняется на сервере. Пользователю не нужно тратить много времени на консолидацию данных в Excel. Однако вы не можете использовать вычисляемые поля или элементы для сводной таблицы.
Чтобы использовать функции куба, вы должны работать с данными, доступными в одной из этих двух форм:
Через подключение к источнику данных SQL Server Analysis Services
В автономном кубе в локальной файловой системе пользователя
Эти условия ограничивают полезность функций куба. Чтобы вы могли работать с некоторыми примерами, примеры файлов, прилагаемые к этой книге, включают автономный куб и файлы подключения к данным для примера, описанного в главе 2, «Использование функций и PowerPivot».
Вы можете создавать и изменять автономный куб (файл с расширением .cub) в Excel.
Во-первых, необходимо установить подключение к службам Analysis Services с помощью Microsoft Query (на вкладке "Данные", "Запрос внешних данных/из другого источника/из Microsoft Query") или с помощью помощника по подключению данных (на вкладке "Вставка", выберите Сводная таблица/Использовать внешний источник данных). Затем нажмите кнопку OLAP Tools, как показано на рис. 14-1, чтобы открыть диалоговое окно Offline OLAP Settings. (OLAP расшифровывается как онлайн-аналитическая обработка.) Нажмите кнопку "Создать автономный файл данных", чтобы создать куб, и следуйте пошаговым инструкциям.
Рис. 14-1. Создание автономного куба.
Однако вам придется изменить образцы файлов подключения к данным (они имеют расширение .odc для подключения к книге или .oqy для Microsoft Query), поскольку путь к базе данных должен быть полным. Используйте Блокнот Windows, чтобы изменить пути следующим образом:
Используйте автономный образец файла cubeTest.xlsx. Этот образец файла и дополнительные файлы находятся в папке Chapter14. Дополнительную информацию о файлах примеров см. в разделе «Использование файлов примеров» на стр. xxiii.
Подготовленный образец рабочей тетради служит руководством. Чтобы избежать ненужных ошибок при изменении этого примера, выполните следующие действия, чтобы создать собственную книгу:
На вкладке "Вставка" выберите "Сводная таблица/Использовать внешний источник данных". (Можно выполнить поиск дополнительных элементов и использовать существующие файлы подключения к данным.)
Создайте макет и включите контент из источника данных.
Используйте функции куба.
Когда вы открываете книгу с подключениями к данным и используете настройки Excel по умолчанию, вы должны явно разрешить эти подключения (нажмите кнопку «Включить содержимое», как показано на рис. 14-2). Когда вы активируете документ в Excel 2010, документ становится доверенным, и вам не нужно подтверждать активацию снова, пока доверенный документ не будет сброшен в Центре управления безопасностью.
Рис. 14-2. Предупреждение системы безопасности, отображаемое при доступе Excel к внешним данным.
Если выбрать «Преобразовать в формулы» в меню «Инструменты OLAP» (см. рис. 14-1, показанный ранее в этой главе), Excel преобразует часть сводной таблицы или всю сводную таблицу в неформатированную таблицу с тем же содержимым, что и сводная таблица. Преимущество этого в том, что весь макет (столбцы и строки) фиксирован. Вы также можете включить фильтры.
Если вы знакомы с формулами, вы можете создавать отдельные структуры, подобные сводным таблицам, которые обеспечивают гибкую оценку данных.
Таблица 14-1 Обзор функций куба
Функция
Описание
Возвращает запрошенное свойство для ключевого показателя эффективности (KPI) куба
Возвращает элемент куба
Возвращает запрошенное свойство (атрибут) элемента куба
Возвращает n-й элемент набора
Определяет набор элементов для создания вложенного куба
Возвращает количество элементов в наборе
Возвращает агрегированное значение из куба данных
КУБЭЛЕКТРЭЛЕМЕНТ()
Синтаксис CUBEKPIMEMBER(соединение,имя_kpi,свойство_kpi,заголовок)
Определение Эта функция возвращает свойство ключевого показателя эффективности (KPI) и отображает имя KPI в ячейке.
Аргументы
connection (обязательно) Строка с именем подключения книги к кубу. После того, как вы введете первую кавычку, отобразятся существующие контекстно-зависимые подключения к данным (см. рис. 14-4, показанный далее в этой главе при описании функции КУБЭЛЕМЕНТ()).
kpi_name (обязательно) Указывает имя KPI в кубе.
kpi_property (обязательно) KPI состоит из нескольких компонентов, которые задаются целым числом (см. Таблицу 14-2).
Читайте также: