Project Reporting Part 4 – Azure DevOps Analytics View Data Model and Power BI Report Designs

In previous post we explored how data from Azure DevOps projects can be exposed and made available for reporting in Power BI using Analytics View. In this post we look at the data model from Analytics View and some of the basic Power BI design concepts.

Project Reporting Part 1 – Crucial Contribution Project Team Makes in Reporting
Project Reporting Part 2 – Why Report on Delivery Data with Power BI
Project Reporting Part 3 – Reporting with Power BI for Azure DevOps

Azure DevOps Analytics View Data Model

Before doing project reporting from data in Azure DevOps, we need to discuss the data model available from Analytics View. From this article, several key points worth repeating are:

  • Entities and fields from the Analytics Views are flattened (denormalised) into a single table to eliminate the need to create relationships between multiple tables. For example, “Created By”, “Changed By” or “Resolved By” fields are modelled as a string and not by User ID Guid.
  • Historical data is modelled as snapshots for each time period, so time-trend reporting can be sliced by a single field called “Date” (included automatically with history Analytics View). If the Analytics Views is configured for daily update (see Post X for how to create your custom Analytics View), the snapshot is taken at midnight of each day. If the day is not yet completed, the snapshot value for the day will be based on the current value.
  • If the Analytics View is set up to pull all historical data, you must filter the dataset by “IsCurrent” = TRUE to show only those current records for work items.

For example, the below DAX Expression produces a table showing all daily snapshots of work item id 61. Notice it is only the last row with “Date” = 30/12/2018 00:00:00 where “Is Current” is True. This last row represents the latest value of this work item.


Reporting Design Guidelines

Recently I found this fairly decent list of guidelines here containing great advice for Power BI report building along with detailed guidelines for different types of visualisations. As a basic starter, here are some of the simple design elements I found most useful.

  • Always focus on the requirements before you build your visualisation. What kind of decisions does the reader want to be able to make based on this report? For example, a project report designed for a manager might contain overall health statistics such as “Total number of bugs closed” or “Number of user stories incomplete”. Whereas a report for a project team member who works as a developer might focus on more personal statistics such as “Number of incomplete tasks assigned to me”.
  • Don’t clutter the report canvas with too many text and visualisations. Use these to help you de-clutter.

For example:

  • Set “Show Guidelines” and “Snap Objects to Grid” to help you place objects onto the report page


  • Restrict the number of colours used and font style and fontsize. For example, once you decided on a font type, use it in all parts of visualisations containing text (Report Title, Chart title, Chart labels etc)


  • Use “Borders” and “Space” to separate objects visually on the report page


  • If you set a background colour or an image to the report page, make sure it doesn’t overwhelm the visualisations in front. For example, even with 50% transparency, a black coloured report background obscures the title and labels on the pie chart.


Another example is you can set a wallpaper image on the report page, however you will need to re-calibrate background transparency on the individual visualisations. In the screenshot below I uploaded a train track image as wallpaper for this report page at 20% transparency. But then the pie chart visual as well as the title text box will need to have their backgrounds adjusted to 50% transparency to allow the objects on the report to not be obscured.



  • Choose visualisation that help you tell your story about the data. For example, I wanted to display the proportion of number of work items and their “states” (New, Active, Closed …). The screenshot below shows 6 different types of visualisations displaying number of work items by their states. Which visualisation you choose to use depends on what story you want to draw attention to. If I were to draw attention to the large number of closed work items (64 – dark grey) vs the relatively small number of resolved work items (8 – yellow), I would likely select either the pie chart (top left) or the tree map (bottom middle).


In the next post, we shall discuss some of the questions a project team likely asks of a project report and how we can present this information using Power BI visualisations.

3 thoughts on “Project Reporting Part 4 – Azure DevOps Analytics View Data Model and Power BI Report Designs

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s