кубы в эксель что это
Сводные таблицы Excel
Набор инструментов ОLAP (Online Analytical Processing— оперативный анализ данных) позволяет эффективно извлекать и анализировать многомерные данные. В отличие от других типов баз данных, базы данных ОLAP разработаны специально для аналитической обработки и быстрого извлечения из них всевозможных наборов данных.
На самом деле существует несколько ключевых различий между стандартными реляционными базами данных, например Access или SQL Server, и базами данных OLAP. В реляционных базах данных информация представляется в виде записей, которые добавляются, удаляются и обновляются последовательно. В базах данных OLAP хранится только моментальный снимок данных. В базе данных OLAP информация заархивирована в виде единого блока данных и предназначается только для вывода по запросу. Хотя в базу данных OLAP и можно добавлять новую информацию, существующие данные редко редактируются и тем более удаляются.
Реляционные базы данных и базы данных OLAP при первом знакомстве различаются структурно. Реляционные базы данных обычно состоят из набора таблиц, которые связаны между собой. В отдельных случаях реляционная база данных содержит так много таблиц, что очень сложно определить, как же они все-таки связаны.
В базах данных OLAP взаимосвязь между отдельными типами данных определяется заранее и сохраняется в структуре, известной под названием кубов OLAP. В кубах данных хранятся полные сведения об иерархической структуре и связях базы данных, которые значительно упрошают навигацию по ней. К тому же создавать отчеты намного проще, если заранее известно, где располагаются извлекаемые данные и какие еще данные с ними связаны.
Основная же разница между реляционными базами данных и базами данных OLAP заключается в способе хранения информации. Данные в кубе OLAP редко представляются в общем виде. Кубы данных OLAP обычно содержат информацию, представленную в заранее разработанном формате. Таким образом, операции группировки, фильтрации, сортировки и объединения данных в кубах выполняются перед заполнением их информацией. Это делает извлечение и вывод запрашиваемых данных максимально упрощенной процедурой. В отличие от реляционных баз данных, нет необходимости в упорядочении информации должным образом перед выводом конечным пользователям.
Базы данных OLAP обычно настраиваются и поддерживаются администраторами IT-отдела. Если в вашей организации нет структуры, которая отвечает за управление базами данных OLAP, то можете обратиться к администратору реляционной базы данных с просьбой реализовать в корпоративной сети хотя бы отдельные OLAP-решения.
Многомерные кубы, OLAP и MDX
Довольно давно являюсь обитателем Хабра, но так и не доводилось читать статьи на тему многомерных кубов, OLAP и MDX, хотя тема очень интересная и с каждым днем становится все более актуальной.
Не секрет, что за тот небольшой промежуток времени развития баз данных, электронного учета и онлайн систем, самих данных накопилось очень много. Теперь же интерес также представляет полноценный анализ архивов, а возможно и попытка прогнозирования ситуаций для подобных моделей в будущем.
С другой стороны, большие компании даже за несколько лет, месяцев или даже недель могут накапливать настолько большие массивы данных, что даже их элементарный анализ требует неординарных подходов и жестких аппаратных требований. Такими могут быть системы обработки банковских транзакций, биржевые агенты, телефонные операторы и т.д.
Думаю, всем хорошо известны 2 разных подхода построения дизайна баз данных: OLTP и OLAP. Первый подход (Online Transaction Processing — обработка транзакций в реальном времени) рассчитан на эффективный сбор данных в реальном времени, второй же (Online Analytical Processing – аналитическая обработка в реальном времени) нацелен именно на выборку и обработку данных максимально эффективным способом.
Немного подробнее о возможностях
Быстрый доступ к данным
Собственно быстрый доступ к данным, независимо от размеров массива, и является основой OLAP систем. Так как основной упор именно на этом, хранилище данных обычно строится по принципам, отличным от принципов реляционных баз данных.
Здесь, время на выборку простых данных измеряется в долях секунды, а запрос, превышающий несколько секунд, скорее всего, требует оптимизации.
Преагрегация
Кроме быстрой выборки существующих данных, также предоставляется возможность преагрегировать «наиболее вероятно-используемые» значения. Например, если мы имеем ежедневные записи о продажах какого-то товара, система может преагрегировать нам также месячные и квартальные суммы продаж, а значит, если мы запросим данные помесячно или поквартально, система нам мгновенно выдаст результат. Почему же преагрегация происходит не всегда – потому, что теоретически возможных комбинаций товаров/времени/и т.д. может быть огромное количество, а значит, нужно иметь четкие правила для каких элементов агрегация будет построена, а для каких нет. Вообще тема учета этих правил и собственно непосредственного дизайна агрегаций довольно обширна и сама по себе заслуживает отдельную статью.
Иерархии
Закономерно, что анализируя данные и строя конечные отчеты, возникает потребность учитывать то, что месяцы состоят из дней, а сами образуют кварталы, а города входят в области, которые в свою очередь являются частью регионов или стран. Хорошая новость то, что OLAP кубы изначально рассматривают данные с точки зрения иерархий и взаимоотношений с другими параметрам одной и той же сущности, так что построение и использования иерархией в кубах – дело очень простое.
Работа с временем
Так как в основном анализ данных происходит на временных участках, именно времени в OLAP системах выделено особое значение, а значит, просто определив для системы, где у нас тут время, в дальнейшем можно с легкостью пользоваться функциями типа Year To Date, Month To Date (период от начала года/месяца и до текущей даты), Parallel Period (в этот же день или месяц, но в прошлом году) и т.п.
Язык доступа к многомерным данным
MDX (Multidimensional Expressions) — язык запросов для простого и эффективного доступа к многомерным структурам данных. И этим все сказано – внизу будет несколько примеров.
Key Performance Indicators (KPI)
Ключевые показатели эффективности — это финансовая и нефинансовая система оценки, которая помогает организации определить достижение стратегических целей. Ключевые показатели эффективности могут быть достаточно просто определены в OLAP системах и использоваться в отчетах.
Дата майнинг
Интеллектуальный анализ данных (Data Mining) — по сути, выявление скрытых закономерностей или взаимосвязей между переменными в больших массивах данных.
Английский термин «Data Mining» не имеет однозначного перевода на русский язык (добыча данных, вскрытие данных, информационная проходка, извлечение данных/информации) поэтому в большинстве случаев используется в оригинале. Наиболее удачным непрямым переводом считается термин «интеллектуальный анализ данных» (ИАД). Впрочем, это отдельная, не менее интересная тема для рассмотрения.
Многоуровневое кэширование
Собственно для обеспечения наиболее высокой скорости доступа к данным, кроме хитрых структур данных и преагрегаций, OLAP системы поддерживают многоуровневое кэширование. Кроме кэширования простых запросов, также кэшируются части вычитанных из хранилища данных, агрегированные значения, вычисленные значения. Таким образом, чем дольше работаешь с OLAP кубом, тем быстрее он, по сути, начинает работать. Также существует понятие «разогрев кэша» — операция, подготавливающая OLAP систему к работе с конкретными отчетами, запросами или всем вместе взятым.
Поддержка мультиязычности
Да-да-да. Как минимум Analysis Services 2005/2008 (правда, Enterprise Edition) нативно поддерживают мультиязычность. Достаточно привести перевод строковых параметров ваших данных, и клиенту, указавшему свой язык, будут приходить локализированные данные.
Многомерные кубы
Так что же все-таки эти многомерные кубы?
Представим себе 3-х мерное пространство, у которого по осям Время, Товары и Покупатели.
Точка в таком пространстве будет задавать факт того, что кто-то из покупателей в каком-то месяце купил какой-то конкретный товар.
Фактически, плоскость (или множество всех таких точек) и будет являться кубом, а, соответственно, Время, Товары и Покупатели – его измерениями.
Представить (и нарисовать) четырехмерный и более куб немного сложнее, но суть от этого не меняется, а главное, для OLAP систем совершенно неважно в скольких измерениях вы будете работать (в разумных пределах, конечно).
Немного MDX
Итак, в чем же прелесть MDX – скорее всего в том, что описывать нужно не то как мы хотим выбрать данные, а что именно мы хотим.
Например,
Что означает – хочу количество iPhone-ов, проданных в июне и июле в Мозамбике.
При этом я описываю какие именно данные я хочу и как именно я хочу их увидеть в отчете.
Красиво, не правда ли?
А вот чуть посложнее:
Фактически, вначале определяем формулу подсчета «среднего размера покупки» и пытаемся сравнить – кто же (какой пол), за один заход в магазин Apple, тратит больше денег.
Сам язык чрезвычайно интересен и для изучения и для использования, и, пожалуй, заслуживает немало обсуждений.
Заключение
На самом деле, данная статья очень мало покрывает даже базовых понятий, я бы назвал ее «appetizer» — возможность заинтересовать хабра-сообщество данной тематикой и развивать ее дальше. Что же касается развития – тут огромное непаханое поле, а я буду рад ответить на все интересующие вопросы.
Функции для работы с кубами (справка)
Важно: Вычисляемые результаты формул и некоторые функции листа Excel могут несколько отличаться на компьютерах под управлением Windows с архитектурой x86 или x86-64 и компьютерах под управлением Windows RT с архитектурой ARM. Дополнительные сведения об этих различиях.
Чтобы просмотреть подробную справку о функции, перейдите по нужной ссылке в представленном ниже списке.
Возвращает свойство ключевого показателя эффективности (КПЭ) и отображает его имя в ячейке. КПЭ представляет собой количественную величину, такую как ежемесячная валовая прибыль или ежеквартальная текучесть кадров, используемой для контроля эффективности работы организации.
Возвращает элемент или кортеж из куба. Используется для проверки существования элемента или кортежа в кубе.
Возвращает значение свойства элемента из куба. Используется для подтверждения того, что имя элемента внутри куба существует, и для возвращения определенного свойства для этого элемента.
Возвращает n-й, или ранжированный, элемент в множестве. Используется для возвращения одного или нескольких элементов в множестве, например лучшего продавца или 10 лучших студентов.
Определяет вычисляемое множество элементов или кортежей, отправляя выражение для множества в куб на сервере, который создает множество, а затем возвращает его в Microsoft Excel.
Возвращает число элементов в множестве.
Возвращает агрегированное значение из куба.
Анализ данных кубов 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, включают множество стандартных категорий ключевых показателей эффективности, называемых группами мер или измерениями. Эти группы предоставляют обзор данных с высочайшего уровня классификации и позволяют облегчить фокусировку анализа. Большинство групп мер имеют множество дополнительных уровней подкатегорий и индивидуальных полей. Все категории, подкатегории и поля содержатся в списке полей сводной таблицы. Чтобы создать простой отчет, выполните следующие действия:
После создания отчета можно добавить любой дополнительный уровень сложности, в том числе сортировку, фильтрацию, форматирование, вычисления и диаграммы. Продолжая анализ, вы также можете входить в категории и покидать их.
демонстрация создания отчета и манипулирования данными в Excel помощью данных из куба данных OLAP в сводной таблице см. в разделе детализация данных сводной таблицы.
Просмотр и анализ куба данных Service Manager OLAP с Excel
для просмотра и анализа куба данных Microsoft OLAP из System Center-Service Manager с Microsoft Excel можно использовать следующую процедуру. Можно также сохранить книги в библиотеке аналитики. С помощью списка полей PivotTable можно перетаскивать поля из куба в книгу. для использования следующей процедуры на компьютере с консолью Service Manager необходимо установить Microsoft Excel 2007 или более поздней версии.
Если анализ куба с помощью Excel выполняется впервые, загрузка может занять несколько минут.
Просмотр и анализ куба OLAP в приложении Excel
использование срезов Excel для просмотра данных Service Manager куба OLAP
Наиболее полезные данные отчетов, доступные в Service Manager, представлены в виде кубов данных. Одним из способов просматривать данные кубов и манипулировать ими являются сводные таблицы Microsoft Excel. Срезы в Excel можно использовать для фильтрации данных сводной таблицы.
Срезы — это простые в использовании компоненты фильтрации с набором кнопок, позволяющих быстро фильтровать данные в отчете сводной таблицы без необходимости открывать выпадающие списки для обнаружения подлежащих фильтрации элементов.
При использовании обычного фильтра отчетов сводной таблицы для фильтрации по нескольким элементам, фильтр отображает только то, что фильтруются несколько элементов — для того, чтобы узнать более точные сведения о фильтрации, потребуется открыть раскрывающийся список. Однако срез сразу показывает примененный фильтр и предоставляет сведения, позволяющие с легкостью понять данные, отображенные в отфильтрованном отчете сводной таблицы.
дополнительные сведения о Excel срезах см. в разделе использование срезов для фильтрации данных сводной таблицы на Microsoft Office веб-сайте.
Аналитические функции в Excel (функции кубов)
Microsoft постоянно добавляет в Excel новые возможности в части анализа и визуализации данных. Работу с информацией в Excel можно представить в виде относительно независимых трех слоев:
Рис. 1. Анализ данных в Excel: а) исходные данные, б) мера в Power Pivot, в) дашборд; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Скачать заметку в формате Word или pdf, примеры в формате Excel
Функции кубов и сводные таблицы
Наиболее простым и в тоже время очень мощным средством представления данных являются сводные таблицы. Они могут быть построены на основе данных, содержащихся: а) на листе Excel, б) кубе OLAP или в) модели данных Power Pivot. В последних двух случаях, помимо сводной таблицы, можно использовать аналитические функции (функции кубов) для формирования отчета на листе Excel. Сводные таблицы проще. Функции кубов сложнее, но предоставляют больше гибкости, особенно в оформлении отчетов, поэтому они широко применяются в дашбордах.
Дальнейшее изложение относится к формулам кубов и сводным таблицам на основе модели Power Pivot и в нескольких случаях на основе кубов OLAP.
Простой способ получить функции кубов
Когда (если) вы начинали изучать код VBA, то узнали, что проще всего получить код, используя запись макроса. Далее код можно редактировать, добавить циклы, проверки и др. Аналогично проще всего получить набор функций кубов, преобразовав сводную таблицу (рис. 2). Встаньте на любую ячейку сводной таблицы, перейдите на вкладку Анализ, кликните на кнопке Средства OLAP, и нажмите Преобразовать в формулы.
Рис. 2. Преобразование сводной таблицы в набор функций куба
Числа сохранятся, причем это будут не значения, а формулы, которые извлекают данные из модели данных Power Pivot (рис. 3). Получившуюся таблицу вы может отформатировать. В том числе, можно удалять и вставлять строки и столбцы внутрь таблицы. Срез остался, и он влияет на данные в таблице. При обновлении исходных данных числа в таблице также обновятся.
Рис. 3. Таблица на основе формул кубов
Функция КУБЗНАЧЕНИЕ()
Это, пожалуй, основная функция кубов. Она эквивалентна области Значения сводной таблицы. КУБЗНАЧЕНИЕ извлекает данные из куба или модели Power Pivot, и отражает их вне сводной таблицы. Это означает, что вы не ограничены пределами сводной таблицы и можете создавать отчеты с бесчисленными возможностями.
Написание формулы «с нуля»
Вам не обязательно преобразовывать готовую сводную таблицу. Вы можете написать любую формулу куба «с нуля». Например, в ячейку С10 введена следующая формула (рис. 4):
Рис. 4. Функция КУБЗНАЧЕНИЕ() в ячейке С10 возвращает продажи велосипедов за все годы, как и в сводной таблице
Маленькая хитрость. Чтобы удобнее было читать формулы кубов, желательно, чтобы в каждой строке помещался только один аргумент. Можно уменьшить окно Excel. Для этого кликните на значке Свернуть в окно, находящемся в правом верхнем углу экрана. А затем отрегулируйте размер окна по горизонтали. Альтернативный вариант – принудительно переносить текст формулы на новую строку. Для этого в строке формул поставьте курсор в том месте, где хотите сделать перенос и нажмите Alt+Enter.
Рис. 5. Свернуть окно
Синтаксис функции КУБЗНАЧЕНИЕ()
Справка Excel абсолютно точна и абсолютно бесполезна для начинающих:
КУБЗНАЧЕНИЕ(подключение; [выражение_элемента1]; [выражение_элемента2]; …)
Подключение – обязательный аргумент; текстовая строка, представляющая имя подключения к кубу.
Выражение_элемента – необязательный аргумент; текстовая строка, представляющая многомерное выражение, которое возвращает элемент или кортеж в кубе. Кроме того, «выражение_элемента» может быть множеством, определенным с помощью функции КУБМНОЖ. Используйте «выражение_элемента» в качестве среза, чтобы определить часть куба, для которой необходимо возвратить агрегированное значение. Если в аргументе «выражение_элемента» не указана мера, будет использоваться мера, заданная по умолчанию для этого куба.
Прежде, чем перейти к объяснению синтаксиса функции КУБЗНАЧЕНИЕ, пару слов о кубах, моделях данных, и загадочном кортеже.
Некоторые сведения о кубах OLAP и моделях данных Power Pivot
Кубы данных OLAP (Online Analytical Processing — оперативный анализ данных) были разработаны специально для аналитической обработки и быстрого извлечения из них данных. Представьте трехмерное пространство, где по осям отложены периоды времени, города и товары (рис. 5а). В узлах такой координатной сетки расположены значения различных мер: объем продаж, прибыль, затраты, количество проданных единиц и др. Теперь вообразите, что измерений десятки, или даже сотни… и мер тоже очень много. Это и будет многомерный куб OLAP. Создание, настройка и поддержание в актуальном состоянии кубов 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 Уровня.
Рис. 5б. Иерархия категорий товаров
Элементы (отдельные члены) присутствуют на всех уровнях. Например, на уровне Category есть четыре элемента: Accessories, Bikes, Clothing, Components. Другие уровни имеют свои элементы.
Меры – это вычисляемые значения, например, объем продаж. Меры в кубах хранятся в собственном измерении, называемом [Measures] (см. ниже рис. 9). Меры не имеют иерархий. Каждая мера рассчитывает и хранит значение для всех измерений и всех элементов, и нарезается в зависимости от того, какие элементы измерений мы поместим на оси. Еще говорят, какие зададим координаты, или какой зададим контекст фильтра. Например, на рис. 5а в каждом маленьком кубике рассчитывается одна и та же мера – Прибыль. А возвращаемое мерой значение зависит от координат. Справа на рисунке 5а показано, что Прибыль (в трех координатах) по Москве в октябре на яблоках = 63 000 р. Меру можно трактовать, и как одно из измерений. Например, на рис. 5а вместо оси Товары, разместить ось Меры с элементами Объем продаж, Прибыль, Проданные единицы. Тогда каждая ячейка и будет каким-то значением, например, Москва, сентябрь, объем продаж.
Кортеж – несколько элементов разных измерений, задающие координаты по осям куба, в которых мы рассчитываем меру. Например, на рис. 5а Кортеж = Москва, октябрь, яблоки. Также допустимый кортеж – Пермь, яблоки. Еще один – яблоки, август. Не вошедшие в кортеж измерения присутствуют в нем неявно, и представлены членом по умолчанию [All]. Таким образом, ячейка многомерного пространства всегда определяется полным набором координат, даже если некоторые из них в кортеже опущены. Нельзя включить два элемента одного измерения в кортеж, не позволит синтаксис. Например, недопустимый кортеж Москва и Пермь, яблоки. Чтобы реализовать такое многомерное выражение потребуется набор двух кортежей: Москва и яблоки + Пермь и яблоки.
Набор элементов – несколько элементов одного измерения. Например, яблоки и груши. Набор кортежей – несколько кортежей, каждый из которых состоит из одинаковых измерений в одной и той же последовательности. Например, набор из двух кортежей: Москва, яблоки и Пермь, бананы.
Автозавершение в помощь
Вернемся к синтаксису функции КУБЗНАЧЕНИЕ. Воспользуемся автозавершением. Начните ввод формулы в ячейке:
Excel предложит все доступные в книге Excel подключения:
Рис. 6. Подключение к модели данных Power Pivot всегда называется ThisWorkbookDataModel
Рис. 7. Подключения к кубам
Продолжим ввод формулы (в нашем случае для модели данных):
Автозавершение предложит все доступные таблицы и меры модели данных:
Рис. 8. Доступные элементы первого уровня – имена таблиц и набор мер (выделен)
Выберите значок Measures. Поставьте точку:
=КУБЗНАЧЕНИЕ( » ThisWorkbookDataModel » ; » [Measures].
Автозавершение предложит все доступные меры:
Рис. 9. Доступные элементы второго уровня в наборе мер
Выберите меру [Total Sales]. Добавьте кавычки, закрывающую скобку, нажмите Enter.
=КУБЗНАЧЕНИЕ( » ThisWorkbookDataModel » ; » [Measures].[Total Sales] » )
Рис. 10. Формула КУБЗНАЧЕНИЕ в ячейке Excel
Аналогичным образом можете добавить третий аргумент в формулу: