Подключение к данным с помощью запросов Power BI и OData

  • Статья

Azure DevOps Services | Azure DevOps Server 2022 — Azure DevOps Server 2019

Чтобы извлечь данные в Power BI, рекомендуется использовать запросы Open Data Protocol (OData). OData — это стандарт ISO/IEC, утвержденный OASIS, который определяет рекомендации по созданию и использованию REST API. Дополнительные сведения см. в документации по OData.

Чтобы быстро приступить к работе, проверка обзор примеров отчетов, использующих запросы OData. Дополнительные сведения о других подходах см. в обзоре интеграции Power BI.

Power BI может выполнять запросы OData, которые могут возвращать отфильтрованный или агрегированный набор данных в Power BI. Запросы OData имеют два преимущества:

  • Все фильтрация выполняется на стороне сервера. Возвращаются только необходимые данные, что приводит к более короткому времени обновления.
  • На стороне сервера данных можно предварительно выполнить предварительную настройку. Запрос OData может выполнять агрегаты, такие как свертка рабочих элементов и частоты сбоев сборки. Агрегаты выполняются на стороне сервера, и в Power BI возвращаются только статистические значения. При предварительной агрегации можно выполнять агрегирование между большими наборами данных, не требуя извлечения всех подробных данных в Power BI.

Вы узнаете, как выполнять следующие задачи:

  • Написание и проверка запросов OData.
  • Запустите запрос OData из Power BI.

Необходимые компоненты

  • Чтобы просмотреть данные аналитики и запросить службу, необходимо быть членом проекта с базовым доступом или большим доступом. По умолчанию все члены проекта предоставляют разрешения для запроса аналитики и определения представлений Аналитики.
  • Дополнительные сведения о других предварительных требованиях для включения служб и функций и общих действий по отслеживанию данных см. в статье "Разрешения и предварительные требования для доступа к аналитике".

Использование Visual Studio Code для записи и тестирования запросов OData

Самый простой способ записи и тестирования OData — использовать Visual Studio Code с расширением OData. Visual Studio Code — это бесплатный редактор кода, доступный в Windows, Mac и Linux. Расширение OData обеспечивает выделение синтаксиса и другие функции, которые полезны для написания и тестирования запросов.

Установка Visual Studio Code и расширение OData

  1. Установка Visual Studio Code.

  2. Откройте Visual Studio Code, выберите расширения и найдите odata. В списке результатов выберите vscode-odata и установите его.

  3. Создайте и сохраните файл OData в Visual Studio Code, например filename.odata. Присвойте ему имя, но оно должно иметь .odata расширение, чтобы включить функциональные возможности расширения OData.

Запись запроса OData

  1. Напишите запрос OData. Примеры запросов см. в обзоре примеров отчетов, использующих запросы OData.

    Следующий запрос возвращает первые 10 рабочих элементов в определенном пути к области.

  2. Замените {organization}, {project}а также {area path} значениями.

    https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/WorkItems?
    $select=WorkItemId,Title,WorkItemType,State,CreatedDate
    &$filter=startswith(Area/AreaPath,'{area path}')
    &$orderby=CreatedDate desc
    &$top=10

    Чтобы выполнять запросы между проектами, опустим /{project} полностью.

Дополнительные сведения см . в кратком справочнике по запросу OData.

После написания запроса в Visual Studio Code вы увидите выделение синтаксиса.

Снимок экрана: расширение OData Visual Studio Code с выделением синтаксиса.

Тестирование запроса OData

  1. Чтобы протестировать запрос OData, поместите курсор в любое место в тексте запроса и выберите "Просмотреть>палитру команд".

  2. В поле поиска введите odata , чтобы открыть все команды OData.

    Снимок экрана: палитра команд для расширения OData Visual Studio Code.

  3. Выберите OData: Открыть. Это действие объединяет многострочный запрос в однострочный URL-адрес и открывает его в браузере по умолчанию.

    • Результирующий набор запросов OData находится в формате JSON. Чтобы просмотреть результаты, установите расширение форматирования JSON для браузера. Для Chrome и Microsoft Edge доступны несколько вариантов.

      Снимок экрана: выходные данные JSON для расширения OData Visual Studio Code.

    • Если запрос имеет ошибку, служба Аналитики возвращает ошибку в формате JSON. Например, эта ошибка указывает, что запрос выбрал поле, которое не существует.

      Снимок экрана: ошибка JSON для расширения OData Visual Studio Code.

Убедившись, что запрос работает правильно, его можно запустить из Power BI.

Выполнение запроса OData из Power BI

Чтобы запустить запрос OData из Power BI, выполните действия, описанные в следующем разделе.

Объединение многострочного запроса OData в однострочный запрос

Прежде чем использовать запрос в Power BI, необходимо преобразовать многострочный запрос OData в однострочный запрос. Самый простой способ сделать это — использовать Visual Studio Code с расширением OData и использовать команду OData: Combine.

Примечание.

В файле filename.odata может потребоваться сначала создать копию текста многострочного запроса, а затем запустить OData: объединить в копию. Сначала выполните этот шаг, так как нет способа преобразовать однострочный запрос обратно в доступный для чтения многострочный запрос.

  1. В Visual Studio Code поместите запрос в любое место в тексте запроса и выберите "Просмотреть>палитру команд". В поле поиска введите odata. Затем в списке результатов выберите OData: Объединить.

    Многострочный запрос преобразуется в однострочный запрос.

    Снимок экрана: расширение OData Visual Studio Code, объединенное с однострочный запрос.

  2. Скопируйте всю строку для использования в следующем разделе.

Выполнение запроса из Power BI

  1. Выберите веб-канал OData для получения данных>. Дополнительные сведения см. в статье "Создание отчета Power BI с помощью запроса OData".

    Снимок экрана: команда веб-канала OData Power BI.

  2. В окне веб-канала OData в поле URL-адреса вставьте запрос OData, скопированный в предыдущем разделе, и нажмите кнопку "ОК".

    .

    Power BI отображает страницу предварительного просмотра.

    Снимок экрана: страница предварительного просмотра для веб-канала OData Power BI.

Указание параметров запроса

  1. Выберите "Изменить" на странице предварительного просмотра, чтобы открыть Редактор Power Query.

    Снимок экрана: Редактор Power Query открыть для веб-канала OData Power BI.

  2. Выберите Расширенный редактор на ленте.

    Снимок экрана: Расширенный редактор выбран для веб-канала OData Power BI.

  3. Прокрутите по горизонтали, чтобы просмотреть [Implementation="2.0"] параметр на панели запросов .

    Снимок экрана: веб-канал OData Power BI с Расширенный редактор прокрутился справа.

  4. Замените [Implementation="2.0"] следующей строкой:

    [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]

    Снимок экрана: строка замены.

    Примечание.

    Чтобы предотвратить ошибки регулирования, выполните следующие действия:

    • Указание Power BI ссылаться на OData версии 4.
    • Указать службе Аналитики опустить все значения, которые имеют значение NULL, что повышает производительность запросов. Power Query пытается устранить значения NULL в виде ошибок, создав другой запрос для каждого обнаруженного значения NULL. Это действие может привести к тысячам запросов, которые быстро превышают пороговое значение использования, за пределами которого ваша учетная запись пользователя будет регулироваться.

    Для успешного выполнения запроса OData к Службе Azure DevOps Analytics требуется следующее действие.

  5. Нажмите кнопку "ОК", чтобы закрыть Расширенный редактор и вернуться к Редактор Power Query Power BI. Для выполнения следующих необязательных действий можно использовать Редактор Power Query.

    • Переименуйте Query1 запрос как что-то более конкретное.
    • Преобразование столбцов в определенный тип. Power BI автоматически задает тип, но может потребоваться преобразовать столбцы в определенный тип данных.
    • Добавьте вычисляемые столбцы.
    • Удаление столбцов.
    • Разверните столбцы в определенные поля.

Создание отчета с помощью данных

Нажмите кнопку "Закрыть" и " Применить" , чтобы сохранить параметры и извлечь данные в Power BI. После обновления данных можно создать отчет, как правило, в Power BI.

Снимок экрана: кнопка