Thursday, October 11, 2018

TFS Reporting using Power BI and REST services

I've been getting the question around reporting options in TFS, specifically on-premises, fairly often.  Currently we have a few options, using queries and charts or widgets, try to use the warehouse / analysis services setup or wait for the Analytics services to come on-prem. A few clients need more details than the queries can offer and do not want to spend the overhead to configure and run the warehouse but also do not want to wait indefinitely for Analytics to make the on-premises appearance.
I have been doing some high level stuff with Power BI Desktop and though I would give it a go. The problem I faced is that there is not a lot of material around how to do it. So let's see if we can help out a bit.

Firstly you would need to get Power BI Desktop.
After you have created a new empty report you need to decide what information you are looking for. As you know, you can get a lot of detail from the TFS/Azure DevOps REST services. This post should give you the basics to get started and report on information derived from pretty much any one of those sources.
After you have decided what information you are looking for, we can start. The example I'm going to use is going to be historic information around work history. That is a particularly cruel thing to get out of TFS and report on.
To start, we need to go and add the queries we need. To do this we will "Edit Queries".
In the Query Editor, we create a new blank source.
Give it a name and then select the advanced editor.
In the advanced editor we will create a "function" that will take in the TFS server URL, the Team Project, and in this case a start date as parameters.
The start date is when we start retrieving the history from.
The "source" will look something like this:
Line 4: This is where the REST service is being invoked and the resulting JSON is returned
Line 5: Next we need to extract the "values" element, which is an array, from the resulting JSON
Line 6: Convert the JSON array to a table with records, each record still contains a JSON like record object
Line 7: The object, in this case, looks something like this image We want to see more detail, so we will expand the "fields" property into a fields column
Line 8: If you look at the result now, it would like this image. A "fields" column with rows of "records", simply meaning we need to expand even further to extract the columns that we are looking for. You can see I have specified the columns and their columns headings in the Table.ExpandRecordColumn function
Line 9 and 10: Just doing some data type conversions on the columns that will make the report behave appropriately (allow summing or average on number fields for example)
Because this is a "function" we have just created, we can invoke it and pass in the correct parameters. If you click "done" on the advanced editor you should see your "query" in the left had pane.image Selecting it will prompt you for parameters that you can enter and then after clicking "Invoke" you should see some results
You can then rename and use that new table to create the best looking reports ever by clicking "Apply and Close" and start creating reports and charts etc…
Just as an example, the attached Power BI report has the work item history that we have just covered, as well as commit history on GIT repos. You may notice that the initial JSON format is slightly different, but the concepts are still the same.

Get the sample here

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.