Как Microsoft спрятала целый сервер и как его найти

— Видишь SSAS-суслика?
— Нет…
— И я не вижу. А он есть!

(Кадр из к/ф «ДМБ»)

Поддержка относительно новой табличной (табулярной) модели данных, в противовес устоявшейся многомерной OLAP, встроена в целый ряд продуктов компании Microsoft. Начиная с SQL-сервера (SS) и заканчивая Excel. И если с SS всё понятно – в нём содержится отдельный сепаратный продукт SQL Server Analysis Services (SSAS). А как же решена поддержка языка DAX-запросов и прочей табулярной функциональности в Power BI, SharePoint или Excel? Поищем «суслика» на примере Power BI Desktop.

Думаю, многие замечали, что при установке на компьютер Power BI Desktop, параллельно разворачиваются структуры папок Microsoft SQL Analysis Services (SSAS) Это опциональный серверный компонент, входящий в состав высших редакций SQL-сервера от Microsoft (в частности редакций Enterprise и Business Intelligence). Он отвечает (как легко догадаться из названия) за аналитическую функциональность и, в частности, создание OLAP-кубов и доступ к ним, а, начиная с 2012-й версии, и за полноценную поддержку новой идеологии многомерных хранилищ данных – табулярной (или табличной) модели данных. Этого «зверька» придумали для поддержки поколоночного хранения данных (в отличие от стандартного для OLTP строкового хранения) и работы со всем набором данных в оперативной памяти (in-memory). Чуть более подробно тут.

Структура рабочих папок Power BI Desktop (Stand-alone установка)

Включение в десктопный софт серверных компонентов выглядит достаточно логично с точки зрения разработки и главное дальнейшей поддержки единообразия табулярной модели данных, общей и для флагманского серверного продукта, и для десктопной среды разработки и даже для Excel. В частности в Power BI Desktop просто интегрировали серверное ядро SSAS, отвечающее за поддержку этой самой табулярной модели и DAX-запросов к данным. При запуске приложения Power BI Desktop стартует движок xVelocity (в девичестве vertiPaq) встроенный в сервер SSAS, который и выполняет всю черновую работу по обслуживанию DAX-запросов, распределению нагрузки, управлению памятью и передачу результатов рендеру, который уже и отображает результаты в виде графиков, гистограмм, сводных таблиц (matrix) и других визуальных элементов, доступных в среде Power BI.

Что нам даёт это знание внутренней кухни? А то, что подобная модульная структура позволяет использовать Power BI Desktop в качестве тестового сервера аналитики без необходимости развёртывания полноценной среды SQL сервера c Microsoft Analysis Services. И без соответствующей лицензионной нагрузки (т. е. бесплатно). Безусловно, этот подход нельзя использовать в промышленных решениях. Однако для быстрого развёртывания тестовой инфраструктуры и проверки построенной модели данных ничего лучше и не придумаешь.

Советы домашнему мастеру

Для определения актуального номера порта, на котором запустился локальный «карманный» Analysis Services проще всего использовать потрясающий бесплатный инструмент, «швейцарский нож» для любого DAX-хакера DAX Studio. При подключении из DAX Studio к вашей модели данных (при этом необходимый PBIX-файл должен быть открыт в Power BI Desktop) в статусной строке приложения отобразятся параметры подключения (как на рисунке ниже), которые можно использовать, для доступа к этой модели данных из других приложений, например из Excel или другого экземпляра Power Bi Desktop локально или по сети.

Номер порта при каждом следующем запуске PBI Desktop будет меняться. В моём примере это 52308. В вашем случае это будет, скорее всего, какое-то другое число из пула портов Power BI Desktop.

Для повторного подключения к этой модели данных потребуется уже не только случайно назначаемый номер порта, но и случайно генерируемое в виде GUID имя базы. Номер порта получается по методике, описанной выше, а идентификатор нужной БД (GUID модели данных) можно получить, сформировав в DAX Studio следующий SQL-запрос:

select * from $SYSTEM.DBSCHEMA_CATALOGS

В столбцах

CATALOG_NAME и DATABASE_ID

будет требуемое имя-GUID.

Локальное решение

По ссылке (123kB) доступен для скачивания разработанный мной PBIX-файл, который формирует список всех загруженных в память моделей данных (включая и встроенную в этот файл). Т. е. в списке будет столько строк, сколько PBIX-файлов запущено.
Обновлено:версия доработана для совместимости с августовским релизом PBI Desktop.

Disclaimer: Данный файл может быть использован только для демонстрационных целей и тестирования, предоставляется «как есть», со всеми багами, ошибками и прочая, на 90% сформирован через интерфейс Power Query / PowerPivot и не может служить хорошим примером для обучения программированию под Power BI.

Интерфейс программы

Для настройки, после нажатия на кнопку [Изменить запросы], следует изменить параметр

USER_NAME

и, возможно,

WORK_FOLDER

Советы домашнему мастеру

1. Stand-alone приложение
2. Приложение Windows Apps

Бинарный код приложения в обеих вариантах один и тот же только в первом случае вам придётся самостоятельно следить за выходом новых версий приложения, релизы которого Microsoft печёт как блины. Штатная частота обновлений Power BI Desktop один раз в месяц, Power BI Reporting Services в среднем один раз в квартал.

Установка через Microsoft Store избавит вас от контроля за обновлениями версий ПО, но есть небольшой (но отличный от нуля) шанс, что в обновлённой версии вы неожиданно столкнётесь с какими-либо проблемами, которых не наблюдалось в предыдущем релизе.

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

Думаю (при наличии достаточного интереса со стороны сообщества) продолжу эту тему на примере конкретных use cases.

Обзор алгоритмов интеллектуального анализа данных Microsoft Analysis Services

В настоящее время существует определенный пул Business Intelligence и Data Science инструментов, которые позволяют исследовать и визуализировать данные.

Microsoft Analysis Services – часть системы управления базами данных Microsoft SQL Server. Включает в себя набор служб, связанных с бизнес-анализом и исследованием данных. Службы Analysis Services предоставляют алгоритмы для использования в решениях интеллектуального анализа данных.

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

Алгоритмы используются внутри контейнеров моделей Analysis Services, которые в свою очередь создаются на основе структур Analysis Services.

Для работы с алгоритмами нам потребуются:

1. В качестве серверной части:
   • MS SQL Server с установленными службами Analysis Services
2. В качестве клиентской части:
   • MS Excel с активированной надстройкой “Интеллектуальный анализ данных”
   • MS Visual Studio с установленным Microsoft SQL Server Data Tools Business Intelligence
   • Microsoft SharePoint
3. Дополнительно потребуется:
   • Доступ к Analysis Services с разрешением на создание файлов в разделе Analysis Services

В MS Office 2013 и выше данные инструменты встроены в корпоративные версии MS Excel (достаточно активировать надстройку «Интеллектуальный анализ данных» в настройках MS Excel).

В MS Office 2010 надстройку необходимо устанавливать отдельно ссылка.

После активации надстройки в MS Excel появится вкладка «Интеллектуальный анализ данных».

В MS Excel последовательно выбираем — Интеллектуальный анализ данных > Соединение > Создать.

Заполняем настройки подключения со службами Analysis Services, проверяем соединение и выбираем – ОК.

2. Создание структуры интеллектуального анализа.

В MS Excel последовательно выбираем — Интеллектуальный анализ данных > Дополнительно > Создать структуру интеллектуального анализа данных.

В Мастере задачи последовательно выбираем – Внешний источник данных > Имя источника данных.

Заполняем поле «Запрос» и выбираем «Далее».

3. Выбор типов данных.

В нашем случае Наименование должности (поле DOLJ) является дискретной величиной (Discrete), а размер выплаты (поле ZARP) непрерывной (Continuous).

Указываем типы данных и выбираем «Далее».

4. Выбор процента проверочных данных.

Процент проверочных данных устанавливает соотношение качества анализа от качества проверки.

Указываем процент проверочных данных и выбираем «Далее».

На этом создание структуры интеллектуального анализа завершается.

5. Добавление модели к структуре.

В MS Excel последовательно выбираем — Дополнительно > Добавить модель к структуре.

6. Алгоритм «Дерево принятия решений».

Analysis Services содержит несколько алгоритмов интеллектуального анализа. Выбираем «Алгоритм дерева принятия решений».

6.1 Выбор входных и прогнозируемых полей.

В нашем случае, в таблицу была добавлена зависимость выплат от должности. Поэтому входными данными является «Должность(DOLJ)», а прогнозируемыми «Зарплата (ZARP)» Устанавливаем соответствия столбцов таблицы с их назначением и выбираем «Далее».

6.2 Результат работы алгоритма – «Дерево принятия решений».

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

На данной схеме слева направо отображены наиболее сильные из выявленных зависимостей.

Результат полностью удовлетворяет входным данным. При заполнении поля с выплатами действительно имелось правило, которое добавляло к выплатам для должностей с идентификаторами 13, 17, 15, … около 30%.

В качестве минуса стоит отметить, что алгоритм не может сгруппировать идентичные закономерности. В нашем случае должности 17,15 имели одинаковый добавочный процент.

7. Результат работы алгоритма – «Классификация».

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

8. Результат работы алгоритма — «Оценка».

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

9. Результата работы алгоритма – «Кластеризация».

В исследование было включено только поле с выплатами, так как алгоритм поддерживает лишь непрерывные данные.

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

10. Результат работы алгоритма – «Прогноз».

Особенностью данного алгоритма является необходимость использования временных меток. В нашем случае используется дата транзакции «INDATE».

Результат работы алгоритма представлен в интерактивном окне, где можно варьировать соотношение исследуемого и прогнозируемого диапазона данных.

Для тестирования работы алгоритма по всему временному диапазону была добавлена постоянная зависимость, увеличивающая на 40% значение каждое 15-е число месяца. Как видно из графика, алгоритм сохранил данную закономерность.

Резюмируем

Алгоритмы интеллектуального анализа данных применимы и могут быть полезны в работе аналитика.

В качестве преимуществ отмечаем:

• Интерактивный графический интерфейс и как следствие низкий порог входа.
• Служба Microsoft Analysis Services не требует отдельной лицензии. Входит в дистрибутив MS SQL server.
• В качестве клиента Microsoft Analysis Services могут выступать широко используемые в банке MS Excel, MS Sharepoint, MS Management Studio, MS Visual Studio.
• Клиент-серверная архитектура Microsoft Analysis Services производит ресурсоемкие аналитические задачи на стороне сервера.

В качестве недостатков отмечаем:

• Малое число моделей (алгоритмов).
• Ограниченное число настроек алгоритмов.
• Часть алгоритмов в качестве вывода результатов предоставляет лишь график без возможности экспорта табличных результатов.

При подготовке материала использовались источники:
https://habr.com/ru/articles/422799/
https://newtechaudit.ru/obzor-algoritmov-intellektualnogo-analiza-dannyh-microsoft-analysis-services/