Как сделать куб данных в excel

Как создать olap куб в excel

Как создать куб OLAP в Excel

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

Как создать куб OLAP

Выберите параметр «Данные» вверху страницы, а затем «Сводная таблица и отчет сводной диаграммы».

Выберите «Внешний источник данных», прежде чем нажать «Далее», чтобы продолжить.

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

Выберите программу из списка, в котором находятся ваши данные. Это может быть Microsoft Excel, Microsoft Word, Microsoft Access или другая программа, которую вы использовали для хранения данных.

Выберите таблицы и поля, которые вас интересуют, прежде чем нажимать «Далее».

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

Выберите поля, которые вы хотите сделать видимыми как сводные поля. Когда закончите, нажмите «Далее».

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

Чтобы данные работали с Excel, выберите «Сохранить файл куба, содержащий все данные для куба». Нажмите «Готово», чтобы завершить создание куба OLAP.

Создаем OLAP куб. Часть 1

Продолжая тематику Многомерные кубы, OLAP и MDX и olap для маленькой компании, традиционно, предлагаю начать с простенького «Hello World» куба, который будет анализировать процессы и тенденции голосований на Хабре.

• сама суть Data Warehouse-а хранить «очищенные» данные, готовые для анализа, поэтому даже его изначальная структура может сильно отличаться от структуры нашей хабра-OLTP базы данных
• в HabraDW (так мы его назовем) мы вынесем только ту информацию, которая нам нужна будет для анализа, ничего лишнего
• к Data Warehouse не накладываются требования нормализации. Даже наоборот, денормализировав некоторые данные можно добиться более понятной схемы для построения куба, а также скорости загрузки данных в куб

Немного теории.

• чисто виртуальный (например, определенным как множество SELECT-ов или даже вызовов сложных хранимых процедур, которые каким-то образом определят входные данные для куба)
• вполне реальным, то есть существовать физически на каком-то сервере (или серверах)

Каким же должен быть Data Warehouse?

Все очень просто – ваш Data Warehouse должен иметь структуру формы звездочки (star model) или снежинки (snowflake model) и состоять из фактов (facts) и измерений (dimensions).

Факты – это фактические записи (records) о каком-то процессе, который мы хотим анализировать, например, процесс голосования на Хабра, или процесс изменения цены товара на бирже. Очень часто факты содержат какие-нибудь числовые данные, например, фактическое значение голоса или цены.

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

Определив где у нас факты, а где измерения — очень просто построить модель звезды.

Звезда.

В центре указываем нашу таблицу фактов, а лучами выводим измерения.

А теперь снежинка.

Снежинка — это та же звезда, только измерения могут зависеть от измерений следующего уровня, а те в свою очередь могут включать еще уровни.

Каждая из этих моделей имеет свои достоинства и недостатки и собственно выбор модели должен базироваться на требованиях к дизайну куба, скорости загрузки данных, дискового пространства и т.д.
Естественно, конечные Data Warehouse обычно намного сложнее и состоят из нескольких звезд или снежинок, которые могут совместно использовать общие измерения.

HabraDW.

Перейдем к собственно разработке нашего Data Warehouse-а.

• в какое время года/месяца/недели голосуют лучше/хуже/чаще
• как голосуют по пятницам и понедельникам (например)
• как влияет на результат голосования наличие в посте слов Microsoft, или Карма
• средняя активность пользователей, «пики» голосования
• и т.п.

• Таблица фактов FactHabravote – определяет кто, когда, за что и как именно проголосовал. Значение Vote в нашем случае будет +- 1, но тип поля позволяет расширить дельту голосами, например, +- 10
• Измерение времени DimTime – определяет нужные для анализа атрибуты времени (значения и названия)
• Измерение пользователей DimUser – определяет пользователей Хабра, пока только никнейм
• Измерение постов DimPost – определяет посты, в нашем случае содержит заголовок и булевые поля, определяющие содержит ли пост слова Microsoft и Карма.

Итоговая схема нашей звезды будет такой.

А здесь исходный SQL скрипт, который создает и наполняет (пока что только случайными данными) наше хранилище.

Ну вот, теперь все готово, чтобы загрузить данные в куб.
До встречи в следующей статье.

Power View и OLAP в Excel

Важно: В Excel для Microsoft 365 Excel 2021 Power View удаляется 12 октября 2021 г. В качестве альтернативы вы можете использовать интерактивный визуальный эффект, предоставляемый Power BI Desktop,который можно скачать бесплатно. Вы также можете легко импортировать книги Excel в Power BI Desktop.

В Excel вы можете подключаться к кубам OLAP (часто называемым многомерными кубами) и создавать интересные и привлекательные страницы отчетов с помощью Power View.

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

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

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

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

В области Поля Power View можно просмотреть доступные поля куба.

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

Группы мер — это коллекция мер, существующих в кубе.

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

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

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

Дополнительные ресурсы

Дополнительные сведения о Power View, OLAP и службах Analysis Services см. в следующих статьях:

Источник

БЛОГ

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

Power View и OLAP в Excel

Важно: В Excel для Microsoft 365 Excel 2021 Power View удаляется 12 октября 2021 г. В качестве альтернативы вы можете использовать интерактивный визуальный эффект, предоставляемый Power BI Desktop,который можно скачать бесплатно. Вы также можете легко импортировать книги Excel в Power BI Desktop.

В Excel вы можете подключаться к кубам OLAP (часто называемым многомерными кубами) и создавать интересные и привлекательные страницы отчетов с помощью Power View.

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

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

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

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

В области Поля Power View можно просмотреть доступные поля куба.

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

Группы мер — это коллекция мер, существующих в кубе.

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

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

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

Дополнительные ресурсы

Дополнительные сведения о Power View, OLAP и службах Analysis Services см. в следующих статьях:

Источник

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

Эта версия Service Manager достигла конца поддержки, рекомендуется выполнить обновление до Service Manager 2019.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник