Извлечение метаданных из Power BI

Умение доставать метаданные из дашбордов может существенно упростить рабочий процесс. Особенно когда перед вами задачи описательного характера. Заполнить файл с мерами, подключениями или другой метаинформацией — это могут быть вашими типичными задачами. Как сделать это просто и быстро? Поможет известная среди PBI разработчиков утилита DAX Studio

DAX Studio — это мощное и специализированное приложение, предназначенное для работы с языком DAX (Data Analysis Expressions), который используется в Microsoft Power BI, SQL Server Analysis Services (SSAS) и Excel Power Pivot для создания вычислений и анализа данных.

Основные фишки:

1. Редактор DAX: С удобный интерфейс для написания и тестирования выражений DAX.

2. Подключение к источникам данных: С помощью DAX Studio можно подключаться к различным источникам данных, таким как модели Power BI, Tabular модели в SSAS или файлы Excel, что позволяет осуществлять анализ на одном месте.

3. Улучшенный анализ производительности: Позволяет выполнять анализ производительности, выявляя узкие места в ваших вычислениях и помогая оптимизировать их для повышения скорости вычислений.

4. Визуализация данных: Возможности визуализации результатов запроса, позволяя быстро увидеть выходные данные ваших DAX-выражений.

5. Экспорт данных: Можно экспортировать данные в различные форматы, включая CSV и Excel, что упрощает обмен информации и дальнейший анализ.

6. Совместимость: Программа поддерживает различные версии DAX, что позволяет пользователям работать с различными моделями данных без необходимости перевода данных или изменений.

DAX Studio работает с DMVs

Динамические представления управления (DMVs) — это особый механизм в Microsoft SQL Server, который предоставляет пользователям и администраторам базы данных возможность получать информацию о состоянии сервера, текущих процессах, статистике выполнения запросов и многом другом.

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

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

2. Анализ запросов: DMVs позволяют выявлять ресурсоемкие запросы, анализировать планы выполнения и находить узкие места в работе SQL-операций.

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

4. Управление индексами: Они предоставляют информацию о том, как используются индексы, и помогают выявить возможности для оптимизации структуры данных.

5. Отладка и диагностика: DMVs могут помочь в диагностике проблем, таких как высокая задержка запросов или ошибки выполнения.

Основные типы DMVs

Существует несколько типов динамических представлений управления, и их можно условно разделить на несколько категорий:

1. Системные представления: Они предоставляют информацию о сервере в целом, включая настройки конфигурации, агрегированные данные о производительности и состояние соединений. Примеры: sys.dm_os_sys_info, sys.dm_exec_connections.

2. Представления процессов: Эти DMVs предоставляют информацию о текущих процессах и активных запросах. Например, sys.dm_exec_requests, который показывает информацию о запросах, выполняющихся в данный момент, включая время их выполнения и состояние.

3. Представления планов выполнения: Эти представления помогают анализировать планы выполнения запросов. Например, sys.dm_exec_query_stats хранит информацию о статистике выполнения запросов, что позволяет оптимизировать их выполнение.

4. Представления памяти: Эти DMVs показывают, как используется память сервером. Примером является sys.dm_os_memory_clerks.

5. Представления индексов: Данные о состоянии и производительности индексов собираются в представлении sys.dm_db_index_usage_stats.

Примеры использования DMVs

Мониторинг текущих запросов:

SELECT *
FROM sys.dm_exec_requests
WHERE session_id > 50; -- Выборка всех запросов, кроме системных

Получение статистики выполнения запросов:

SELECT TOP 5
qs.total_worker_time AS TotalCPUTime,
qs.total_elapsed_time AS TotalElapsedTime,
qs.execution_count AS ExecutionCount,
SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS QueryText
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY TotalCPUTime DESC;

Анализ блокировок:

SELECT
blocking_session_id AS BlockingSessionID,
session_id AS BlockedSessionID,
wait_type, wait_time, wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0; -- Только заблокированные запросы

Оставлю справку с полным набором команд DMVs

От теории к практике

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

Начнем:

1. Открываем файл pbix

2. В главном меню открываем вкладку → Внешние инструменты →
Выбираем утилиту DAX Studio

3. Открывается окно для запросов

Для мер нам понадобиться следующий код:

SELECT MEASURE_NAME, EXPRESSION
FROM $SYSTEM.MDSCHEMA_MEASURES
WHERE MEASURE_IS_VISIBLE

В ответе мы получаем имена мер и их DAX код. Также в условии мы исключаем скрытые меры WHERE MEASURE_IS_VISIBLE → не очевидно, но подставляется TRUE.

5. Для подключений нам понадобиться следующий код:

SELECT [Name], QueryDefinition
FROM $SYSTEM.TMSCHEMA_PARTITIONS
WHERE SystemFlags = 0

В ответе получаем название и код запроса
В названии подключений прибавляется UUID

По опыту, если требуется убрать UUID, то по соотношению → трудозатраты/оптимизация подходят регулярные выражения
Например:

1. Сайт https://ru.rakko.tools/tools/83/

2. Подставляем в поиск по regExp выражение " -.+ "

3. Заменяем на пробел

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

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