background preloader

Excel-MDX

Facebook Twitter

MDX (язык запросов) MDX (англ. Multidimensional Expressions) — язык запросов для простого и эффективного доступа к многомерным структурам данных, наподобие языка SQL для реляционных баз данных. MDX впервые был представлен в 1997 году компанией Microsoft как часть OLEDB спецификации для OLAP (авторство языка MDX принадлежит Моше Пасуманскому). В том или ином виде поддержка MDX реализована в продуктах следующих компаний (в алфавитном порядке): George Spofford, Sivakumar Harinath, Chris Webb, Dylan Hai Huang, Francesco Civardi: MDX-Solutions: With Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase.

Wiley, 2006, ISBN 0-471-74808-0Mosha Pasumansky).Mark Whitehorn, Rob Zare: Fast Track to MDX. ISBN 1-84628-174-1. Создание и редактирование сценариев многомерных выражений. На вкладке Вычисления конструктора кубов нажмите кнопку Создать команду сценария на панели инструментов для создания пустого сценария. После создания новый сценарий отображается с пустым именем на панели Организатор сценариев на вкладке «Вычисления». Символы, вводимые на панели «Выражения вычисления», отображаются как имя элемента на панели Организатор сценариев. Следовательно, можно в первую строку ввести имя с комментарием для упрощения определения сценария на панели Организатор сценариев. Дополнительные сведения см. в разделе Введение в сценарии многомерных выражений в Microsoft SQL Server 2005.

Дополнительные сведения о производительности запросов многомерных выражений и вычислений см. в разделе «Написание эффективных многомерных выражений» Руководства по производительности служб SQL Server 2005 Analysis Services. Для создания выражений с синтаксисом многомерных выражений можно использовать панель «Выражения вычисления». Retrieving Data from an Analytical Data Source. Журнал "КомпьюАрт" | Введение в MDX. Полина Трофимова, Алексей Шуленин Предисловие Область действия и основное назначение Основные понятия многомерной модели Пример многомерной модели (куб «Учет рейсов») Кубы и ячейки Базовые термины языка MDX Пример запроса Описание осей результирующего набора Функции и выражения языка MDX Навигация по дереву измерений Выражения, не зависящие от уровня На том же уровне (CurrentMember; PrevMember) Уровень ниже, уровень выше Другие функции и выражения NonEmpty Пример визуализации конструирования MDX-запроса в клиентских приложениях Предисловие DX (MultiDimensional eXpressions — язык запросов к многомерным данным) был впервые введен в рамках спецификации OLE DB for OLAP для работы с многомерными кубами.

Эта статья носит вводный характер. Область действия и основное назначение уществуют два основных аспекта использования MDX (рис. 1). На графике (рис. 2) изображена зависимость возможностей разработчика MDX от изученной им функциональности языка. Задачи языка можно определить следующим образом: Кубы и ячейки или: Справочник по многомерным выражениям. OLAP PivotTable Extensions - Home. Журнал "КомпьютерПресс" | Введение в MDX. Полина Трофимова, Алексей Шуленин Предисловие Область действия и основное назначение Основные понятия многомерной модели Пример многомерной модели (куб «Учет рейсов») Кубы и ячейки Базовые термины языка MDX Пример запроса Описание осей результирующего набора Функции и выражения языка MDX Навигация по дереву измерений Выражения, не зависящие от уровня На том же уровне (CurrentMember; PrevMember) Уровень ниже, уровень выше Другие функции и выражения NonEmpty Пример визуализации конструирования MDX-запроса в клиентских приложениях Предисловие DX (MultiDimensional eXpressions — язык запросов к многомерным данным) был впервые введен в рамках спецификации OLE DB for OLAP для работы с многомерными кубами.

Эта статья носит вводный характер. Область действия и основное назначение уществуют два основных аспекта использования MDX (рис. 1). На графике (рис. 2) изображена зависимость возможностей разработчика MDX от изученной им функциональности языка. Задачи языка можно определить следующим образом: Кубы и ячейки или: Работа с OLAP-кубом в MS Excel. Журнал "КомпьютерПресс" | Блеск и нищета сводных таблиц. Часть 1. Павел Сухарев Сводные отчеты: новые задачи Несколько слов о шкалах измерений Отчеты 3D Заключение В настоящее время различные виды задач количественного анализа данных присутствуют в деятельности любой организации. Один из эффективных способов их решения заключается в переводе структурированных данных из реляционных источников в многомерные базы данных и проведении всех последующих вычислений на уровне OLAP-хранилищ.

Сегодня такие операции можно выполнить во многих прикладных программах различных производителей, среди которых центральное место, безусловно, занимают сводные таблицы (Pivot Table) Microsoft Excel. Во­вторых, вся работа с аналитическими данными построена на активном использовании интерактивной среды формирования отчетов. Невольно напрашивается вопрос: а велика ли она, эта плата за универсальность?

Сводные отчеты: новые задачи Начнем с того, что MDX может применяться как средство «тюнинга» обычных сводных таблиц. Рис. 1. Несколько слов о шкалах измерений Отчеты 3D Рис. 2. Рис. 3. Журнал "КомпьютерПресс" | Блеск и нищета сводных таблиц. Часть 2. Павел Сухарев В первой части статьи была затронута тема адаптации сводных таблиц Microsoft Excel средствами языка MDX под решение отдельных задач прикладного уровня. В частности, было рассказано, каким образом можно повысить наглядность представления многомерных данных в сводных таблицах.

Напомним, что решение заключалось в форматировании ячеек отчета при помощи многомерных выражений, написанных на языке MDX и хранящихся на OLAP-сервере. Направление визуализации данных является удобной площадкой для демонстрации различных возможностей MDX. Чтобы создать в отчете посредством цветового кодирования третье измерение, понадобилось потратить всего 5 минут на написание нескольких строчек кода. Сфера интересов обычных бизнес-пользователей в большинстве случаев ограничена небольшими массивами информации, имеющими довольно простую структуру. К счастью, современное развитие информационных технологий позволяет формулировать данный вопрос в менее ультимативной форме. Дата; дирекция; услуга. Рис. 1. Журнал "КомпьютерПресс" | Блеск и нищета сводных таблиц. Часть 3. Павел Сухарев Несколько слов об OLE DB Обратная запись в Microsoft Excel Настройка обратной записи в аналитическом кубе Заключение В части 2 данной статьи была затронута тема доработки сводных таблиц посредством создания собственных запросов к многомерным базам данных из среды Microsoft Excel.

Собственные MDX-запросы — не единственный способ просто и быстро повысить эффективность работы со сводными таблицами. Microsoft Excel является основной, а во многих случаях и единственно доступной для большинства пользователей программой бизнес-анализа. Несколько слов об OLE DB Базовым интерфейсом для доступа к внешним данным в Microsoft Excel являются поставщики данных OLE DB. Microsoft Excel обращается к серверу Microsoft Analysis Server посредством OLE DB-провайдера версии MSOLAP.4. Продемонстрируем, каким образом можно настроить доступ к аналитическим серверам из реляционных сред. Рис. 1. В поле Product name следует указать подходящую версию OLE DB-провайдера. Рис. 2. Вернемся к нашей проблеме. Журнал "КомпьютерПресс" | Блеск и нищета сводных таблиц. Часть 4. Павел Сухарев Естественные ограничения сводных отчетов Методы создания сводных отчетов с расширенной функциональностью Альтернативные интерфейсы доступа к многомерным данным Предыдущие части статьи были посвящены обзору различных методов работы с многомерными данными посредством сводных таблиц Microsoft Excel.

Здесь же мы поговорим об альтернативном подходе — наборе методов КУБ(). Прежде чем перейти к рассказу о возможностях функций семейства КУБ(), сделаем небольшое отступление и перечислим некоторые существенные ограничения стандартных сводных отчетов Microsoft Excel. Естественные ограничения сводных отчетов Напомним, что сводная таблица представляет собой набор измерений, который пользователь может расположить либо на осях отчета — области столбцов или строк, либо в фильтре отчета — области фильтров. Измерения не зависят друг от друга, благодаря чему, собственно, и обеспечивается гибкость настройки отчета. Представленная таблица имеет простую структуру. Рис. 1. Рис. 2. Рис. 3. Рис. 4. Журнал "КомпьютерПресс" | Блеск и нищета сводных таблиц. Часть 5. Павел Сухарев Несколько слов о пользовательских свойствах измерений Отображение пользовательских свойств в таблицах Microsoft Excel В части 4 данной статьи мы завершили обзор основных функций семейства КУБ().

С помощью базовых операторов КУБЭЛЕМЕНТ() и КУБЗНАЧЕНИЕ() в книге Microsoft Excel можно составить отчет по результатам работы практически любого типового MDX-запроса. На этом возможности, предоставляемые интерфейсом КУБ(), не исчерпываются. Несколько слов о пользовательских свойствах измерений Прежде чем перейти к теме статьи, кратко рассмотрим, что собой представляют свойства измерений и для чего они используются. Как известно, базовой категорией OLAP от Microsoft Microsoft является измерение (Dimension), которое состоит из набора связанных между собой атрибутов и, как правило, ассоциируется с отдельной таблицей измерения (dimension table) в базе данных. На самом деле такая терминология не совсем удачно отражает реальное положение дел и приводит к некоторой путанице в понятиях. . {} on 0. Журнал "КомпьютерПресс" | Блеск и нищета сводных таблиц. Часть 6.

Павел Сухарев Вспомогательные функции КУБМНОЖ() и КУБЧИСЛОЭЛМНОЖ() Что скрывается за строкой «Выражение_множества» Сортировка в функции КУБМНОЖ() Несколько слов о контексте выражения Сортировка в функции КУБМНОЖ(): продолжение Функция КУБЧИСЛОЭЛМНОЖ() Заключение Вспомогательные функции КУБМНОЖ() и КУБЧИСЛОЭЛМНОЖ() В наборе операторов КУБ() есть специальные сервисные функции, помогающие организовать и значительно упростить работу с аналитическими отчетами. Оператор КУБМНОЖ() предоставляет пользователям удобный способ создания наборов для их последующего использования в аналитических отчетах. КУБМНОЖ(подключение, выражение_множества, подпись, порядок_сортировки, сорт_по) Первый аргумент стандартен для всех операторов КУБ() — он представляет собой текстовую строку «подключение», в которой задается имя подключения к аналитическому кубу.

Что скрывается за строкой «Выражение_множества» Строка «Выражение_множества» определяет некоторое MDX-выражение, которое должно быть рассчитано в кубе. Рис. 1. Набор 1. Журнал "КомпьютерПресс" | Блеск и нищета сводных таблиц. Часть 7. Павел Сухарев В прошлых статьях цикла были рассмотрены две базовые функции семейства КУБ() — КУБЗНАЧЕНИЕ() и КУБЭЛЕМЕНТ(). Потенциала, заложенного в этих функциях, достаточно для построения в среде Microsoft Excel сводных отчетов практически произвольного формата.

Более того, логика исполнения функций предполагает использование в качестве аргументов значений из обычных ячеек листа книги, что открывает перед пользователем широкие возможности по созданию отчетов, объединяющих данные из разнородных источников. В то же время указанные функции обладают рядом «врожденных» недостатков, самым существенным из которых можно считать статичность создаваемых на их базе отчетов. Концепция использования функции КУБПОРЭЛЕМЕНТ() довольна проста. Допустим, перед нами стоит задача составления детализированного по месяцам отчета о продажах, совершенных за первое полугодие.

[Янв], [Фев], [Мар], [Апр], [Май], [Июн] Набор 1 Каким образом можно выйти из сложившейся тупиковой ситуации? [Дир_1] Набор 2 ([Дир_1], [Янв]), Журнал "КомпьютерПресс" | Блеск и нищета сводных таблиц. Часть 8. Павел Сухарев Неопределенные значения в многомерной среде Важные правила обращения с наборами элементов при составлении многомерных отчетов Заключение В предыдущей части статьи мы рассмотрели применение функции КУБПОРЭЛЕМЕНТ() для вывода одномерных наборов. Данная функция позволяет пользователям, даже не знающим языка MDX, показывать на листе Microsoft Excel большие и сложные наборы элементов из многомерного пространства. Однако на практике чаще всего встречаются ситуации, когда на одной оси сводного отчета нужно расположить сразу несколько измерений.

Кроме того, отчеты, созданные с помощью функций КУБМНОЖ() и КУБПОРЭЛЕМЕНТ(), можно сделать гораздо удобнее для пользователей, если применять в них различные стандартные элементы управления Microsoft Excel — флажки, переключатели, кнопки. Настоящая статья призвана дать краткое теоретическое обоснование всем методам, которые используются в «продвинутых» режимах работы оператора КУБПОРЭЛЕМЕНТ(). Неопределенные значения в многомерной среде Набор 1. Журнал "КомпьютерПресс" | «Блеск и нищета» сводных таблиц Часть 9. Создание отчета со сложной структурой. Павел Сухарев Настоящий материал подводит итог всего цикла статей, посвященного основным функциям КУБ(): КУБЭЛЕМЕНТ(), КУБСВОЙСТВОЭЛЕМЕНТА(), КУБПОРЭЛЕМЕНТ(), КУБМНОЖ(), КУБЧИСЛОЭЛМНОЖ().

Нам останется изучить только последнего представителя этого семейства — КУБЭЛЕМЕНТКИП(), который стоит особняком и предназначен для работы с ключевыми индикаторами производительности (КИПами). В статье рассказывается о практическом воплощении идей, которые обсуждались в предыдущих частях. В ней описывается, как построить сложный динамический отчет, используя для этого только операторы КУБ() в различных комбинациях.

На одной оси в нем размещаются сразу два измерения; для каждого из этих измерений имеется возможность менять состав, включенных в форму элементов. Перечисленные требования на первый взгляд не кажутся слишком сложными для реализации. Тем не менее данная задача успешно решается. На рис. 1 исходные элементы измерений представлены двумя группами пользовательских элементов формы типа «флажок». Журнал "КомпьютерПресс" | Блеск и нищета сводных таблиц. Часть 10. Павел Сухарев Описание бюджетной модели Немного финансовой терминологии Составляем единый план счетов Настройка финансовых счетов в MS Analysis Подготовка аналитического куба к использованию измерения Account Создание измерения типа Account Создание правил пересчета Определение правил форматирования ячеек Последние штрихи Заключение В предыдущих статьях цикла мы довольно подробно рассмотрели основные операторы семейства КУБ(), посредством которых можно управлять выводом данных из многомерного хранилища в клиентское приложение.

В этой статье нам хотелось бы сделать небольшое отступление от основной темы повествования и обсудить вопрос разработки приложений, использующих измерения со встроенной бизнес-логикой. Теперь давайте посмотрим на аналитические приложения с позиции пользователя-практика. К примеру, значение статьи «Прибыль» бюджета «Баланс» определяется на основании статьи «Чистая прибыль» бюджета «Прибыли и убытки». Описание бюджетной модели Рис. 1. Рис. 2. Чему равны результаты квартала? Журнал "КомпьютерПресс" | Блеск и нищета сводных таблиц. Часть 11. Павел Сухарев Предыдущие статьи данного цикла были посвящены возможностям операторов семейства КУБ(), позволяющих выполнять MDX-вычисления в программе MS Excel.

В отдельном материале рассказывалось об измерении Account, выступающем в роли фундамента для большинства современных финансовых приложений. В настоящей статье рассматриваются операции, которые, условно говоря, являются обратными к функциям КУБ(). Мы обсудим, каким образом функции листа Microsoft Excel запускаются на стороне OLAP-сервера. Однако на практике с завидной регулярностью встречаются задачи, для решения которых недостаточно даже столь обширных возможностей. Формула 1 Сразу отметим, что среди базового набора MDX-операторов нет функции для определения NPV. В Microsoft Excel ситуация с экономическими вычислениями намного лучше. Рис. 1. Финансовые функции из программы Microsoft Excel, как, впрочем, и функции из других разделов, уже давно являются общепринятым стандартом. Рис. 2. Выражение 1 with select measures.NPV on 0 Рис. 3.

Журнал "КомпьютерПресс" | Блеск и нищета сводных таблиц. Часть 12. Журнал "КомпьютерПресс" | Блеск и нищета сводных таблиц. Часть 10.