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:

• Introduction in TFS Data
• What Tools can be used
• How to set up my reporting environment

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.

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.

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

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.

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

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])

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

Create a Pivot

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

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.

Happy Pivoting !!