Кубы Olap в Excel, как создать

Обновлено: 02.07.2024

В этом разделе объясняется, как создать куб OLAP из запроса, извлекающего данные из реляционной базы данных, такой как Microsoft Access или Microsoft SQL Server. Для создания и использования этого типа куба OLAP не требуется серверный продукт OLAP. Чтобы понять и использовать эту возможность Microsoft Query, вы должны быть знакомы с управлением базами данных и использованием Microsoft Query для извлечения данных для отчетов сводных таблиц или сводных диаграмм Microsoft Excel.

В этом разделе содержится справочная информация о следующем:

Что такое куб OLAP?

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

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

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

Создание запроса данных

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

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

Добавление полей более одного раза Иногда может потребоваться использовать поле в кубе более одного раза. Например, вы можете захотеть суммировать поле «Продажи» двумя разными способами: суммируя суммы продаж и подсчитывая количество продаж. Или вы можете захотеть включить две разные версии поля «Дата»: в одной даты упорядочиваются по кварталам и месяцам, а в другой — по кварталам и неделям. Чтобы использовать поле в кубе более одного раза, необходимо добавить это поле в запрос один раз для каждого использования. Например, добавьте в запрос поле "Продажи" дважды, если вы планируете суммировать и подсчитывать значения в поле.

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

Сохранение запроса Если вы думаете, что позже вам может понадобиться повторно создать куб с другими полями, обязательно сохраните запрос в файле запроса базы данных (.dqy). Затем вы можете открыть файл .dqy, изменить запрос и создать новые кубы на основе измененного запроса.

Создание куба

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

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

Сохранение файла определения куба Независимо от того, сохраняете ли вы отдельный файл автономного куба, мастер сохраняет файл определения куба (.oqy). Этот файл содержит информацию, необходимую Microsoft Query для загрузки куба, чтобы вы могли его изменить, и информацию, необходимую Excel для построения куба в памяти или подключения к автономному файлу куба, если вы его создали.

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

Обновление и изменение куба

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

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

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

Поддержка этой версии 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 в проекте служб Analysis Services с помощью поставщика данных Excel и создавайте кубы OLAP для использования в аналитике, отчетах и т. д.

Создание источника данных для Excel

Начните с создания нового многомерного проекта Analysis Service и проекта интеллектуального анализа данных в Visual Studio. Затем создайте источник данных для данных Excel в проекте.

Файл ExcelFile в разделе "Аутентификация" должен быть установлен как действительный файл Excel.

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

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

После создания источника данных создайте представление источника данных.

В обозревателе решений щелкните правой кнопкой мыши Представления источников данных и выберите Новое представление источника данных.
Выберите только что созданный источник данных (CData Excel Source) и нажмите "Далее".
Выберите шаблон соответствия внешнего ключа, который соответствует базовому источнику данных, и нажмите «Далее».
Выберите таблицы Excel, которые нужно добавить в представление, и нажмите "Далее".
Назовите представление и нажмите "Готово".

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

Создание куба для Excel

Последним шагом перед обработкой проекта и развертыванием данных Excel в службах SSAS является создание кубов.

В обозревателе решений щелкните правой кнопкой мыши Кубы и выберите Новый куб.
Выберите "Использовать существующие таблицы" и нажмите "Далее".
Выберите таблицы, которые будут использоваться для таблиц групп мер, и нажмите кнопку Далее.
Выберите показатели, которые хотите включить в куб, и нажмите кнопку Далее.
Выберите создаваемые измерения на основе доступных таблиц и нажмите «Далее».
Проверьте все варианты и нажмите "Готово".

Обработка проекта

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

После настройки целевого сервера и базы данных щелкните проект правой кнопкой мыши и выберите "Обработать". В рамках этого шага может потребоваться сборка и развертывание проекта.После сборки и развертывания проекта нажмите «Выполнить» в мастере обработки базы данных.

CData Software — ведущий поставщик решений для доступа к данным и подключения. Наши коннекторы на основе стандартов упрощают доступ к данным и избавляют клиентов от сложностей интеграции с локальными или облачными базами данных, SaaS, API, NoSQL и большими данными.

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

PowerOLAP запускает Excel, отображая только что созданный рабочий лист:

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

Выбор участника страницы для просмотра в Excel

Теперь на листе отображаются данные для нового участника страницы, Канада. Вы можете повторить этот способ выбора через диалоговое окно «Выбрать члена», которое PowerOLAP сделал доступным в Excel, чтобы выбрать другие страны в измерении «Регионы». [Если бы это был четырехмерный, пятимерный и т. д. куб, вы могли бы выбрать любое количество членов Страницы для просмотра, многократно умножая свой потенциальный набор отчетов!]

Ввод данных из Excel

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

, которая является ячейкой на пересечении

Апрель

Теперь, чтобы увидеть обратное динамическое подключение к кубу PowerOLAP:

ПРИМЕЧАНИЕ. Вы не можете записывать данные в ячейки электронной таблицы элементов агрегирования или ячейки, управляемые формулой куба, как и в срезе).

Надежность и мощность подключения электронных таблиц к кубам PowerOLAP имеют решающее значение для использования продукта: PowerOLAP «разгружает» Excel своими вычислительными задачами — иерархии/агрегированные веса/формулы рассчитываются в механизме PowerOLAP по заданным диапазонам многомерных данных. ; кроме того, PowerOLAP избавляет пользователей/организации от необходимости поддерживать сотни и более связанных электронных таблиц.

Определение ссылок на куб

Двунаправленное соединение, показанное до сих пор, представляет собой функцию OLAPT (показана в ячейке A6). На самом деле, когда вы щелкнете ячейку A6 на листе, вы увидите в строке формул Excel единую формулу, которая ссылается на все ячейки листа, связанные с данными в кубе PowerOLAP:

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

Обратите внимание, что в области "Измерения" теперь отображаются текстовые поля, в которых можно ввести параметры для измерений "Месяцы", "Учетные записи" и "Регионы". На самом деле, февраль для вас «заранее выбран». Если вам нужен еще один член Months для вашей формулы, вы должны нажать кнопку «Выбрать», чтобы сделать другой выбор. Поскольку вы хотите выбрать данные за февраль, перейдите к измерениям «Учетные записи и регионы». Используйте кнопку «Выбрать» и соответствующие диалоговые окна «Выбор участника», чтобы выбрать данные для валовой прибыли и США соответственно.

После того, как вы сделаете эти выборы, диалоговое окно "Редактировать формулу PowerOLAP" будет выглядеть следующим образом:

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

Закрытие базы данных

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

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

Читайте также: