Excel как из olap куба получить sql запрос mdx

Обновлено: 01.07.2024

OLAP - это аббревиатура OnLine Analytical Processing, т.е. оперативный анализ данных. Пользователь получает естественную, интуитивно понятную модель данных, организуя их в виде многомерных массивов-КУБОВ (Cubes).

У каждого куба есть своя многомерная система координат. Измерениями (Dimensoins) системы координат служат основные атрибуты анализируемого бизнес-процесса (блюда, валюты. скидки, время и т.д.).

На пересечении координат находятся данные, количественно характеризующие процесс - факты (Facts). Другими словами, фактами можно назначать характеристики процесса, для которых имеет смысл операция сложения или нахождение min (max).

"Разрезая" куб по разным направлениям, можно получить сводные (например, по годам) или детальные (по дням) отчеты.

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

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

Справочник находится в пункте главного меню Настройки > OLAP отчеты > Кубы и отчеты

Иерархически связь можно изобразить так:

Схемы кубов -> кубы -> отчеты (связь один ко многим)

Поля (размерности и факты) кубов выбирают из структуры БД с помощью свойства "Поля куба".

Каждая схема куба характеризуется собственной главной таблицей (свойство схемы кубов) и собственной структурой. Для работы со структурой куба, дважды кликните мышью куб в области Элементы или в области значений свойства куба Поля куба.

Для формирования куба необходимо хорошо знать размещение данных в таблицах БД!

Ниже отображено окно формирования полей куба.

Основные свойства кубов, на которые слудует обратить внимание:

Назначение
Периодичность - периодичность генерации куба
Тип источника
Таймаут построения кубов (в свойствах сервера отчетов)

Перед просмотром отчета на основе куба необходимо куб пересчитать! Построение отчета после пересчета куба возможно только при наличии лицензии для сервера отчетов и ключа Guardant!

Важные замечания

в тексте запроса не допускается использование констукций group by / order by - запрос из куба сохраняется в SQL базе как представление (view), в представлениях не допускается группировка и сортировка
запрос обязательно должен вернуть хотя бы одно числовое значение (int, float) поля - это ограничение накладывает движок куба в менеджерской. Куб требует наличие хотя бы одного факта и обязан считать итоги по факту, считать итоги по строковому полю невозможно
вложенные запросы некорректно обрабатываются при разборе запроса куба, решение - собирать данные из таблиц через join и указывать нужные поля из общей выборки
в таблице GENERATEDPROPDATAS хранятся значения всех расширенных свойств для всех сущностей БД. Для ограничения типа сущности необходимо включать в запрос ссылку на справочник (OBJECTREFNO) и идентификатор свойства (RKTYPEIDENT)

Создание Кассового отчета

Для создания собственного (пользовательского) кассового отчета, следует выполнить следующие операции:

1. Создайте новый куб, в свойстве "Назначение" которого укажите "Для кассовых отчетов". Обязательно укажите в свойстве куба "Сист. Имя" значение, по которому бэнд в макете куба будет находить нужный куб;

2. Создайте новый документ: справочник "Документы и Макеты", выделите группу документов "Пользовательские отчеты", вызовите из нее всплывающее меню, команда "Новый документ". Заполните свойства документа: Название, Альт.название(не обязательно), Статус. На страницу документа отчета OLAP кубы добавьте свой куб;

3. На странице "Макеты печати" документа создайте макет отчета, в редакторе макета отчета добавьте необходимые секции и поля в отчет;

4. Добавьте макет отчета в используемую схему печати в одноименном справочнике.

Примечание для версии старше 7.4.21.ХХ! Для отображения на кассе отчета с сервера отчетов необходимо в свойствах макета сделать пометку об этом.

Возможные ошибки

При настройке свойств куба может возникнуть следующая ошибка:

5003:Exception during cube Куб для отчетов по расходу блюд(18) procession: 'Exception Field 'SHIFTDATE' not found'

ProcessErrorException:UCSERR(5004):Exception "Exception Field 'SHIFTDATE' not found" during cube loading.

В этом случае необходимо убрать фильтр пересчета.

В значении свойства Тип Фильтра необходимо выставить Фильтровать и Добавить Данные, а не Фильтровать Данные .

Иногда возникает потребность импортировать/загрузить данные из кубов Microsoft SQL Server Analysis Services (MS SSAS) в обычную реляционную БД. Ну или просто работать с данными как с обычными табличными.

Сделать это в принципе не сложно, но потребуется несколько хитрых манипуляций.
Итак, по шагам:

0. Предположим у нас есть:
MS SQL Server (называется SQLSERVER) содержит БД DEST_DB, где мы и хотим обрабатывать данные из куба с сервера SSAS.
MS SSAS (называется SSAS) содержит OLAP БД (называется OLAP_DB). В ней есть куб SALES_Cube с двумя мерами Sales и Profit (которые относятся к группе мер Sales_Results) и с двумя измерениями Departments и Fact_Dates. При этом измерение Fact_Dates имеет иерархию YMD с уровнями Year, Month, Day.
Мы хотим получить выборку сумм продаж и прибыли по всем отделам и всем датам.
MDX запрос, который вернет нам это:

select
[Measures].[Sales],
[Measures].[Profit]
> on columns,
([Departments].AllMembers, [Fact_Dates].[Day].AllMembers) on rows
from [SALES_Cube]

Если хотим ограничить выборку только теми комбинациями данных, для которых точно есть рассчитанные агрегаты, (а это имеет смысл сделать для выборки, если у вас много значений измерений, которые в комбинациях друг с другом дадум геометрически огромное количество вариантов - и зачем нам "переваривать" варианты, для которых точно нет агрегатов?) можно использовать функцию Exists:

select
[Measures].[Sales],
[Measures].[Profit]
> on columns,
Exists(([Departments].AllMembers, [Fact_Dates].[Day].AllMembers), ,"Sales_Results") on rows
from [SALES_Cube]

Если нужно ограничить выборку какими-то критериями - считаем, что с MDX все знакомы. ;-)

1. В SQLSERVER создать прилинкованный сервер, ссылающийся на источник данных OLAP - как это сделать описано здесь. Пусть прилинкованный сервер называется LOCAL_SSAS

2. Чтобы получить данные на сервере SQLSERVER с сервера SSAS нужно использовать OPENQUERY. Соответственно, это можно оформить в хранимой процедуре. Но чтобы к данным можно было обращаться как к таблице, можно это оформить в виде функции. Например так:

create function [dbo].[OLAP_SALES_Cube]()
RETURNS @tRes TABLE
(
Department varchar(255),
FDY varchar(255),
FDM varchar(255),
FDD varchar(255),
Sales float,
Profit float
)
begin

insert into @tRes
SELECT *
FROM
OPENQUERY(
[LOCAL_SSAS],
'
select
[Measures].[Sales],
[Measures].[Profit]
> on columns,
Exists(([Departments].AllMembers, [Fact_Dates].[Day].AllMembers), ,"Sales_Results") on rows
from [SALES_Cube]
'
)

На что тут стоит обратить внимание:

3. Вот и всё - функцию можно использовать как удобно на свое усмотрение: select * from [dbo].[OLAP_SALES_Cube]()

Эта версия 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, для отображения уровней состояния, находящихся выше или ниже определенного порога (например, значком стоп-сигнала), а также тренда значения — рост или убывание (к примеру, с помощью значков со стрелками).

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

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

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

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

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

Рис. 9.13. Обычная сводная таблица OLAP

С помощью нескольких щелчков вы можете преобразовать любую сводную таблицу OLAP в набор формул куба данных. Например вы хотите создать базу ссылок по которым можно скачать фильмы бесплатно. Поместите курсор в любом месте сводной таблицы, щелкните на кнопке Средства OLAP контекстной вкладки ленты Параметры и выберите команду Преобразовать в формулы (Convert to Formulas), как показано на рис. 9.14.

Рис. 9.14. Выбор этой команды вызывает преобразование сводной таблицы в формулы куба данных

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

Рис. 9.15. В Excel можно преобразовать фильтры данных сводной таблицы в статические формулы

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

Спустя несколько секунд сводная таблица будет заменена формулами, которые выполняются в кубах данных для вывода в окне Excel необходимой информации. Обратите внимание на то, что любое стилевое форматирование при этом удаляется из сводной таблицы (рис. 9.16).

Как же могут практически пригодиться описанные возможности? Учитывая то, что просматриваемые вами значения теперь не являются частью объекта сводной таблицы, можно добавлять столбцы, строки и вычисляемые элементы, комбинировать их с другими внешними источниками, а также изменять отчет самыми произвольными способами, в том числе и перетаскивая формулы. В частности, на рис. 9.17 показан отчет, в котором скомбинированы данные полей Accessories и Bikes. Не забывайте о том, что все значения, содержащиеся в отчете, представлены формулами, ссылающимися на куб OLAP с помощью настроенного ранее соединения.

Рис. 9.17. Отчет Bike Products построен с помощью формул, ссылающихся на конвертированный куб OLAP

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