[TFS] Visualizando Queries (WIQ) no PowerBI

O Team Foundation Server (TFS) possui diversas opções para visualização das suas informações:

  • WorkItem Queries;
  • Dashboards;
  • Excel Reports conectados a base Analitica;
  • Reporting Services Reports, utilizando base relacional e analítica;
  • Sharepoint dashboards;

Para cada uma dessas opções, existem vários relatórios prontos. Você pode consultar informações detalhadas sobre eles aqui.

É comum em grandes empresas a necessidade de cruzar informações de sistemas que fazem gestão de portifólio/projetos/demandas, gestão de serviços de TI (ITSM) ou mesmo gestão financeira, com as informações geradas no TFS. Eu já me deparei várias vezes com essa necessidade.

Existem algumas alternativas para se implementar esses relatórios. Considerando as opções que citei acima, o Reporting Services é de longe a ferramenta mais flexível e que irá atender quase todos os cenários. Mas isso não significa que será uma tarefa simples. Você poderá utilizar a base de dados analitica ou a base de dados relacional (warehouse) se quiser maior flexibilidade (mais informações aqui).

Mas se as informações de que você precisa estiverem todas relacionadas a workitems, a opção mais simples, sem sombra de dúvidas, são as WorkItem Queries (WIQ). Você pode criar consultas e exportá-las para html. Você pode utilizá-las para montar gráficos e anexá-los aos dashboards. Você pode ainda abrir essas queries no Excel (através do plugin Team Foundation Server Office Integration), e no Excel utilizar diversas ferramentas (como Power Query, Power Pivot) para cruzar essas informações com as informações dos outros sistemas.

Contudo existe uma limitação do plugin de integração do TFS com Excel: ele exibe dados apenas de um único Team Project por vez. Se você precisar criar um relatório que cruze informações de todos os Team Projects, você terá que executar a query no TFS com a opção query across team projects, e exportar os dados copiando como html. Não é a melhor das opções, e está longe de ser um processo automático.

Recentemente me vi novamente tendo que montar relatórios consolidando dados do TFS com dados de outros sistemas corporativos. Para minha sorte, dessa vez eu precisava apenas de informações contidas em workitems, mas consolidadas entre todos os Team Projects.

Tentando achar um meio termo entre a limitação do plugin para Excel, e o esforço extra de se utilizar o Reporting Services, me perguntei: porque não o PowerBI? Com ele eu teria a flexibilidade do Excel, a robustez do Reporting Services e uma plataforma/experiência muito melhor que o Report Viewer Nativo ou o Sharepoint.

Parece uma evolução óbvia optar pelo PowerBI. Melhor ainda se existisse nas queries uma opção como "Abrir no PowerBI" igual existe para Excel. De fato até existe uma (excelente e desconhecida!) extensão que habilita isso!... mas apenas para VSTS :-(. Esse foi mais um argumento para eu convencer o cliente a migrar para VSTS, mas isso é assunto para um outro post.

Apesar de não ter o trabalho pronto, eu não desisti da idéia de utilizar a simplicidade das Workitem Queries (WIQ). Minha idéia era criar a query no site do TFS, com toda a facilidade que existe lá, e depois executar essa query de dentro do PowerBI. Sabendo que o PowerBI possibilita obter dados de REST APIs, resolvi utilizar as REST APIs do TFS para executar as queries criadas no TFS, depois seria fácil importar as informações dos outros sistemas e, usando PowerQuery, criar os relacionamentos e o relatório.

No entanto, não foi uma tarefa tão simples como imaginei. Passei por dois grandes obstáculos:

  • API do TFS: para executar uma WIQ via API REST, é necessário duas etapas:

    1. A primeira é chamar a API que executa a WIQ;
    2. A execução da WIQ retorna apenas o ID dos WorkItems, então é preciso para cada ID retornado recuperar as informações do workitem. Felizmente existe uma API que aceita um lote de até 200 IDs por execução, otimizando esse processo;

    Esse procedimento está muito bem documentado, a única diferença é que eu usei a API que busca por uma query armazenada no TFS.

    Saber o que fazer foi fácil, afinal estava tudo documentado. O obstáculo mesmo foi descobrir como implementar no PowerBI. É muito fácil chamar uma API REST no PowerBI mas, no caso, eu tinha que chamar a primeira API, e montar a chamada para a segunda API baseada nos dados que recebi da primeira.

    Para isso tive que aprender um pouco sobre a linguagem M, a linguagem utilizada pelo PowerQuery (presente no PowerBI e Excel) para "misturar" dados de várias fontes de dados. É uma linguagem funcional similar a F# e, por ser funcional, a maneira de se realizar loops no código é um pouco diferente de um simples "for ou foreach", mas após entender a lógica, fica bem intuitivo, e ainda tem sempre a alternativa de se utilizar funções recursivas.

  • Limitações de segurança do PowerBI: o segundo grande obstáculo foi sem dúvida limitações relacionadas a privacidade e segurança do PowerBI. E neste caso temos dois níveis:

    • PowerBI Desktop: a aplicação cliente utilizada para criar e testar os relatórios;
    • PowerBI.com, o serviço onde publicamos os relatórios.

    No PowerBI Desktop temos três opções de privacidade que definem como os dados das diferentes fontes de dados podem ser combinados entre si:

    • Privado: os dados não podem ser combinados com nenhuma outra fonte de dados;
    • Público: os dados podem ser combinados com qualquer fonte de dados;
    • Organizacional: os dados podem ser combinados apenas se a outra fonte de dados pertencer ao mesmo domínio da organização.

    Como ele verifica isso? No meu caso, REST API, baseado na URL que informei para a API. Essa opção de privacidade pode ser desabilitada, mas como é uma opção gravada localmente, específica para cada cliente do PowerBI Desktop, e o padrão é ela estar habilitada, é melhor configurá-la corretamente para que outras pessoas que utilizarem o mesmo arquivo não tenham problemas. Como eu estou combinando dados de duas APIs diferentes, essa configuração foi crítica.

    No PowerBI.com não existem essas opções de privacidade, ele simplesmente não permite combinar dados de fontes de dados de diferentes domínios. Além disso, o serviço é bem mais rigoroso ao checar as URLs utilizadas nas conexões, e tive que reescrever meu codigo algumas vezes para conseguir que ele executasse com sucesso no PowerBI.com. Essas são alguns referências que usei para contornar os problemas:

    Outro aspecto importante foi a questão da autenticação. Utilizando o PowerBI Desktop é bem tranquilo, você consegue autenticar normalmente com a autenticação Windows (default do TFS), e as credenciais ficam salvas de maneira segura localmente na ferramenta.

    Mas quando publicamos no PowerBI.com, temos que informar as credenciais para nossas conexões de dados, e não é possível utilizar credenciais Windows, a não ser que use um PowerBI gateway. A alternativa é utilizar autenticação Básica, que pode ser feita habilitando diretamente no IIS (única opção para TFS 2015 ou inferior) ou utilizando Personal Access Token (TFS 2017 acima). Em ambos os casos, é essencial que o TFS esteja configurado com um endpoint SSL, para evitar que essas credenciais trafeguem livremente pela internet, afinal iremos publicar o relatório num serviço público.

    Mesmo após resolvidas as questões de autenticação no PowerBI.com, não foi possível configurar a atualização automática dos dados. Sempre recebo o erro:

    [Unable to combine data] Section1/Query1/AutoRemovedColumns1 is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.

    Abri uma issue com o time do produto, que está em investigação. Parece se tratar de um problema conhecido, visto que tem outras pessoas que reportaram o mesmo problema. Para o meu caso, aceitamos o fato de mensalmente termos que manualmente atualizar o relatório e repuplicar no PowerBI.com.

Para conferir o resultado final dessa investigação você pode baixar o arquivo modelo aqui.

Ao abrir, ele irá pedir para informar os valores para os seguintes parâmetros necessários para identificar a query a ser executada:

Montei um simples relatório de exemplo:

O mais importante está aqui nas queries:

Eu criei seis funções para organizar o código:

  • GetQueryId: Recupera o guid da query que será executada pelo path informado;
  • ExecuteWIQ: Executa a query recebendo o guid recuperado pelo função anterior;
  • GetWorkItemIds: Gera uma lista com os IDs retornados pela execução da query;
  • GetFields: Gera uma lista com os campos retornados pela execução da query;
  • GetWorkitems: Recupera as informações detalhadas de uma lista de workitems, recebendo uma lista de IDs e de campos a serem retornados;
  • GetAllWorkitems: Função recursiva que recupera as informações de todos os IDs de workitems retornados pela query, montando lotes de 200 workitems até consumir todos os IDs retornados.

Além de organizar o código, as funções também evitam essa issue.

O código completo está abaixo:

Note um workaround que tive que fazer na MyQuery. É um comportamento meio estranho que descobri, descrevi em detalhes nessa thread do forum do PowerBi.

É possível utilizar esse mesmo exemplo para executar queries no VSTS. Irá funcionar igualmente, inclusive a parte de não conseguir fazer o refresh automático no PowerBI.com. Se quiser que o refresh funcione basta, em todo o código acima, substituir "Web.Content" por "VSTS.Content" e publicar. Neste caso você irá utilizar o VSTS Data Connector para PowerBI, ele cria um novo esquema de autenticação baseado em OAuth que habilita um grau de confiança que permite combinar dados de várias conexões. Veja a dica do site do connector:

Mais detalhes aqui.

Conclusão

O trabalho que eu tive para fazer tudo funcionar foi muito maior do que eu imaginava. Contudo, o maior trabalho foi realmente técnico. O PowerBI é uma ferramenta muito nova, está em constante evolução, e é comum achar alguns pequenos bugs.

Superados esses obstáculos, ficou muito mais simples agora montar relatórios baseados nas Workitems Queries. O PowerBI é muito flexível, e combinar dados de várias fontes de dados diferentes e depois disponibilizar de maneira segura numa plataforma própria para o compartilhamento desse tipo de informação com toda a empresa, vale todo o esforço inicial que eu tive.

Gostaram da idéia? Se tiverem qualquer dúvida ou feedback deixem um comentário.