TFS Reports–Unleash them with PowerPivot

A long time ago I wrote a blog series about writing Custom Reports for Team Foundation Server. As you may or may not know, Team Foundation Server comes with a powerful reporting solution. A relational TFS Warehouse and a Data Cube which contains the aggregated and historical data.

In my previous posts I talked about how to set up a model so you can easily use the TFS Warehouse without breaking the model. I came up with an extra database called Tfs_Warehouse_Extensions that contains views, which in their turn query the TFS Warehouse, making it easier to build a reusable reporting model.

The previous post can be found here:

I also wrote a post on how reports or output can help you drive your ALM implementation (see here).

Back then I was heavily using SQL Server Reporting Services (SSRS) to build reports. Lately I got some reporting questions which I could not answer by SSRS. It was simply too hard. I decided to dive a bit into Excel PowerPivot and I must say…….I am hooked!

PowerPivot

PowerPivot is an add-in for Excel that can either be downloaded for Excel 2010 or be “switched on” in Excel 2013.

Once you enabled PowerPivot, You will see a nice tab in your Ribbon.

image

The great thing and main advantage of PowerPivot over SSRS is that you can bring in data from different sources and then easily combine this data. From the combined data you can create Pivot Tables and Charts which can be sliced and diced at will.

Because the data is first downloaded to Excel and must be manually refreshed, the creation and usage of charts and tables is fast and snappy, allowing people that uses them to easily slice, dice, switch and view their data.

The challenge

Lately I needed to create more and more reports that combined data from the TFS Warehouse (SQL Relational) and the cube. For example, I wanted to show remaining work per sprint start and end date.

Remaining work per date is easy. It can be pulled out of the TFS Cube instantly. But there is now way to see the start and end date of a sprint. It is simply not in the cube. We must pull this information from the relational warehouse. This is something that SSRS is not good at. The only way to do it in SSRS is create a Lookup function in the dataset, which evaluates each row. when there is 3 years of data, many teams and iterations and many work items, this is not acceptable when it comes to performance.

So, I tried this in PowerPivot and it worked. Let me walk you through an example where I want to get remaining work per start date of a sprint per area (or team).

Build the Data Model

So, let’s first create our data model in PowerPivot. Open Excel and navigate to the PowerPivot Tab. Click Manage (see picture above) and you are transferred to the Data Model.

First we need to create a Connection to the TFS Cube an build our query.

image

Connect to the Analysis Cube from the menu, Choose the TFS_Analysis database, and choose Design to build your MDX Query.

Build your query by choosing the work item dimension and drag the following fields to the design surface

  • Date
  • Area Path
  • Iteration Path
  • Remaining Work

image

When you are done, click OK. PowerPivot is now populated with Cube Data.

Same goes for the relational Query. I want to have a list of sprints including start and end date. I created a view for this in my Tfs_Warehouse_Extensions that contains this query. Surely you can connect to the Tfs_warehouse directly as well.

CREATE VIEW [dbo].[vw_AllIterations]
AS
SELECT  
i.IterationGUID
,i.ParentIterationGUID
,i.IterationPath
,i.IterationName 
,i.StartDate as SprintStartDate
,i.FinishDate as SprintEndDate 
,p.ProjectNodeName as ProjectName
FROM Tfs_Warehouse.dbo.DimIteration i
INNER JOIN Tfs_Warehouse.dbo.DimTeamProject p 
ON Convert(varchar(255),p.ProjectNodeGUID) = i.ProjectGUID
INNER JOIN Tfs_Warehouse.dbo.vDimIterationOverlay ito 
ON ito.IterationSk = i.IterationSK

 

Repeat the steps from above only connect to the Relation store instead.

image

Then there is a more tricky part, that deservers some explanation. Dates from the TFS Cube are not actually dates but strings. This makes it hard to work with the dates. Especially because the date format differs in cube and relational store.

To tackle this problem I created a helper excel sheet (which you can download here) that lists date, and formats the date in different ways. Copy this sheet in your workbook and select [Add to Data Model] to add it to PowerPivot

image

Now your PowerPivot contains 3 Tabs. With Data from the Cube (Remaining work from date), Data from the SQL Store (vw_AllIterations) and the helpers sheet with dates (CubeDateHelper).

Combining Data

Now it is time to combine some data. What we want is the vw_AllIterations Tab to contain the remaining work on start date. We need to do some things before we can do that.

  • Add a FormattedDate column to the cube sheet to format the date
  • Add a key column to the Cube sheet to uniquely identify a date/iteration/area
  • Add a key column to the vw_allIterations sheet to uniquely identify a date/iteration/area
  • Add a remaining work column to the vw_alliterations sheet to get the remaining work
Add a formatted date column

As said, the dates from the cube are not real dates. This makes it hard to work with. The CubeDateHelper sheets formats the date. With the LOOKUPVALUE function in PowerPivot we can get values from one sheet into another.

Add a column and enter the following function in the function bar.

=LOOKUPVALUE(CubeDateHelper[DateD-M-Y T:T:T];CubeDateHelper[CubeDateMDY];
[DateDateDate])

image

Set the Data Type of the column to [Date].

Add a key column to the Cube sheet to uniquely identify a date/iteration/area

Because dates occur multiple times (different iteration/area) I thought of adding a concatenated column to uniquely identify a date/area/iteration row.

In the cube sheet add a new column called [Key] and add the following function

=[Work ItemIteration PathIteration Path] & "_" &
LOOKUPVALUE('CubeDateHelper'[KeyFormat];'CubeDateHelper'[CubeDateMDY];
[DateDateDate])

This creates a concatted field. Add more fields when needed

Add a key column to the vw_allIterations sheet to uniquely identify a date/iteration/area

Do the same on the vw_AllIterations Tab. Add a key field by using this function

=[IterationPath]& "_" & LOOKUPVALUE('CubeDateHelper'[KeyFormat];
CubeDateHelper[DateD-M-Y T:T:T];[SprintStartDate])

When you get an error about incompatible data types, check that SprintStartDate is a [Date] and that the DateD-M-Y T:T:T field in the CubeDateHelper is also a [Date] field

Add a remaining work column to the vw_alliterations sheet to get the remaining work

Now that we have added the columns, we can combine the data. Add another column [remanining work] to the Vw_AllIterations Tab and lookup the Remaining work from the Cube sheet by using the [Key] field. Set the data type of the column to [Decimal] or [Whole Number]

=LOOKUPVALUE('Remaining work per Date'[MeasuresMicrosoft_VSTS_Scheduling_
RemainingWork];'Remaining work per Date'[Key];[Key])

When you have done this, you’ll fnd data in your sheet

image

Create a Pivot

When your data is ready you can select a sheet and choose to add a PivotTable or Chart.

image

Then you can unleash your creativity!!

 

I added some more fields, and data and see what it does !! Notice I added Slicers to easily slice the data.

image

Happy Pivoting !!

Advertisements

5 Responses to “TFS Reports–Unleash them with PowerPivot”

  1. Hello
    It looks very useful and I attempted this in our organization.
    However, I got stuck in the table Import Wizard while trying to add the Date dimension to the MDX query designer, it just says “Executing” for hours.
    Do you maybe have a solution for that?

    Thank you!

Trackbacks/Pingbacks

  1. Unleash the full potential of your TFS Cube | Jasper Gilhuis - July 17, 2014

    […] Read more about these great capabilities here: https://roadtoalm.com/2014/07/17/tfs-reportsunleash-them-with-powerpivot/ […]

  2. Unleash the full potential of your TFS Data | Jasper Gilhuis - July 17, 2014

    […] Read more about these great capabilities here: https://roadtoalm.com/2014/07/17/tfs-reportsunleash-them-with-powerpivot/ […]

  3. Create a Trend chart in Visual Studio Online | The Road to ALM - September 1, 2014

    […] them (here, here and here), how you should use them in your ALM implementation and how to use PowerPivot to your […]

%d bloggers like this: