Conectar-se com dados usando consultas do Power BI e OData

  • Artigo

Azure DevOps Services | Azure DevOps Server 2022 - Azure DevOps Server 2019

Para extrair dados para o Power BI, recomendamos que você use consultas OData (Open Data Protocol). O OData é um padrão OASIS aprovado pela ISO/IEC que define as melhores práticas para criar e consumir APIs REST. Para obter mais informações, consulte a documentação do OData.

Para começar rapidamente, confira a Visão geral dos relatórios de exemplo que usam consultas OData. Para obter informações sobre outras abordagens, consulte a visão geral da integração do Power BI.

O Power BI pode executar consultas OData, que podem retornar um conjunto de dados filtrado ou agregado para o Power BI. As consultas OData têm duas vantagens:

  • Toda a filtragem é feita no lado do servidor. Somente os dados necessários são retornados, o que leva a tempos de atualização mais curtos.
  • Você pode pré-agregar dados no lado do servidor. Uma consulta OData pode realizar agregações, como rollup de item de trabalho e taxas de falha de build. As agregações são realizadas no lado do servidor e somente os valores agregados são retornados ao Power BI. Com a pré-agregação, você pode realizar agregações em grandes conjuntos de dados, sem precisar extrair todos os dados detalhados para o Power BI.

Neste artigo, você aprenderá como:

  • Escreva e teste consultas OData.
  • Execute uma consulta OData do Power BI.

Pré-requisitos

  • Nível de acesso: você deve ser membro de um projeto com acesso básico ou superior.
  • Permissão: por padrão, os membros do projeto têm permissão para consultar o Analytics e criar vistas da propriedade.
  • Para obter mais informações sobre outros pré-requisitos relacionados à ativação de serviços e recursos e atividades gerais de rastreamento de dados, consulte Permissões e pré-requisitos para acessar o Analytics.

Usar o Visual Studio Code para escrever e testar consultas OData

A maneira mais fácil de escrever e testar o OData é usar o Visual Studio Code com a extensão OData. O Visual Studio Code é um editor de código gratuito disponível para Windows, Mac e Linux. A extensão OData fornece realce de sintaxe e outras funções úteis para escrever e testar consultas.

Instalar o Visual Studio Code e a extensão OData

  1. Instalar o Visual Studio Code.

  2. Abra o Visual Studio Code, selecione Extensões e pesquise odata. Na lista de resultados, selecione vscode-odata e instale-o.

  3. Crie e salve um arquivo OData no Visual Studio Code, por exemplo, filename.odata. Nomeie-o como quiser, mas ele deve ter uma .odata extensão para habilitar a funcionalidade de extensão OData.

Escreva a consulta OData

  1. Escreva a consulta OData. Para obter consultas de exemplo, consulte a Visão geral de relatórios de exemplo que usam consultas OData.

    A consulta a seguir retorna os 10 principais itens de trabalho em um caminho de área específico.

  2. Substitua {organization}, {project} e {area path} pelos seus valores.

    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

    Para consultar entre projetos, omita /{project} totalmente.

Para obter mais informações, consulte Referência rápida de consulta OData.

Depois de escrever a consulta no Visual Studio Code, você deverá ver o realce de sintaxe.

Captura de tela que mostra a extensão OData do Visual Studio Code com realce de sintaxe.

Testar a consulta OData

  1. Para testar a consulta OData, coloque o cursor em qualquer lugar no texto da consulta e selecione Exibir>Paleta de Comandos.

  2. Na caixa de pesquisa, insira odata para exibir todos os comandos OData.

    Captura de tela que mostra a paleta de comandos para a extensão OData do Visual Studio Code.

  3. Selecione OData: Abrir. Essa ação combina a consulta de várias linhas em uma URL de uma linha e a abre no navegador padrão.

    • O conjunto de resultados da consulta OData está no formato JSON. Para exibir os resultados, instale a extensão JSON Formatter para seu navegador. Várias opções estão disponíveis para Chrome e Microsoft Edge.

      Captura de tela que mostra a saída JSON para a extensão OData do Visual Studio Code.

    • Se a consulta tiver um erro, o serviço do Analytics retornará um erro no formato JSON. Por exemplo, esse erro informa que a consulta selecionou um campo que não existe.

      Captura de tela que mostra o erro JSON para a extensão OData do Visual Studio Code.

Depois de verificar se a consulta funciona corretamente, você pode executá-la no Power BI.

Executar a consulta OData do Power BI

Para executar a consulta OData do Power BI, siga as etapas na próxima seção.

Combinar a consulta OData de várias linhas em uma consulta de linha única

Antes de usar a consulta no Power BI, você deve converter a consulta OData de várias linhas em uma consulta de linha única. A maneira mais simples de fazer isso é usar o Visual Studio Code com a extensão OData e usar o comando OData: Combinar .

Observação

No arquivo filename.odata, talvez você queira primeiro criar uma cópia do texto da consulta de várias linhas e, em seguida, executar OData: Combine na cópia. Execute esta etapa primeiro porque não há como converter a consulta de linha única de volta em uma consulta de várias linhas legível.

  1. No Visual Studio Code, coloque sua consulta em qualquer lugar no texto da consulta e selecione Exibir>Paleta de Comandos. Na caixa de pesquisa, digite odata. Em seguida, na lista de resultados, selecione OData: Combinar.

    A consulta de várias linhas é convertida em uma consulta de linha única.

    Captura de tela que mostra a extensão OData do Visual Studio Code combinada a uma consulta de linha única.

  2. Copie a linha inteira para uso na próxima seção.

Executar a consulta no Power BI

  1. Selecione Obter feed OData de dados>. Para obter mais informações, consulte Criar um relatório do Power BI com uma consulta OData.

    Captura de tela que mostra o comando de feed OData do Power BI.

  2. Na janela do feed OData, na caixa URL, cole a consulta OData que você copiou na seção anterior e selecione OK.

    .

    O Power BI exibe uma página de visualização.

    Captura de tela que mostra a página de visualização do feed OData do Power BI.

Especificar opções de consulta

  1. Selecione Editar na página de visualização para abrir o Editor do Power Query.

    Captura de tela que mostra o Editor do Power Query aberto para o feed OData do Power BI.

  2. Selecione Editor Avançado na faixa de opções.

    Captura de tela que mostra o Editor Avançado selecionado para o feed OData do Power BI.

  3. Role horizontalmente para exibir o [Implementation="2.0"] parâmetro no painel Consulta .

    Captura de tela que mostra o feed OData do Power BI com o Editor Avançado rolado para a direita.

  4. Substitua [Implementation="2.0"] pela seguinte string:

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

    Captura de tela que mostra a cadeia de caracteres de substituição.

    Observação

    Para evitar erros de limitação, execute as seguintes ações:

    • Instrua o Power BI a fazer referência ao OData v4.
    • Instrua o serviço do Analytics a omitir todos os valores nulos, o que melhora o desempenho da consulta. Power Query tenta resolver valores nulos como erros gerando outra consulta para cada valor nulo encontrado. Essa ação pode resultar em milhares de consultas, o que excede rapidamente o limite de uso, além do qual sua conta de usuário é limitada.

    A ação a seguir é necessária para que o Power BI execute com êxito uma consulta OData no Serviço Azure DevOps Analytics.

  5. Selecione OK para fechar o Editor Avançado e retornar ao Editor do Power Query do Power BI. Você pode usar o Editor do Power Query para executar as seguintes ações opcionais:

    • Renomeie a Query1 consulta como algo mais específico.
    • Transforme colunas em um tipo específico. O Power BI detecta automaticamente o tipo, mas talvez você queira converter colunas em um tipo de dados específico.
    • Adicione colunas computadas.
    • Remover colunas.
    • Expanda colunas em campos específicos.

Criar um relatório usando os dados

Selecione Fechar e Aplicar para salvar suas configurações e extrair os dados para o Power BI. Depois que os dados forem atualizados, você poderá criar um relatório como faria normalmente no Power BI.

Captura de tela que mostra o botão Fechar e Aplicar do Power BI.