Understanding the Team Foundation Server Data Warehouse

Teams WIT Tools - Understanding the TFS Cube

"The initial learning curve for the TFS Cube is pretty steep. It is quite overwhelming to figure out the relationships between the large number of dimensions and measure groups in the TFS cube at first. In this blog entry I will explain some of the most commonly used perspectives and show how you can easily create Excel reports from them. 

*Note* that this blog entry is still relevant to you even if you don't have perspectives on your cube.  What I cover here will help you better understand the cube schema.  In the demos below I connect Excel to cube perspectives.  However you can create the exact same reports by connecting to the Team System Cube.

..."

sigh... I SO could have used this a few weeks ago.

I recently added a new field to my Agile/Bug work items (related post) and needed to add that to a TFS report.

None of the existing reports really had what I wanted so I decided to start from scratch. And so began one of those, "I thought this would be easy? What was I thinking..." tasks.

Starting with the SQL Server 2005 Report Builder, I was able to create a report pretty quickly (once I found out how to create a Model... One my of TFS books came through with that tip).

That worked, but while Report Builder is good ad-hoc report engine (it's primary purpose) it's somewhat limited. I wanted reports that had some of the functionality that the in-the-TFS-box reports had (like true URL based navigation not the Report Building drill down stuff, etc).

It was time to upgrade to the Visual Studio Report Designer...

Um... well... let me just say that there's somewhat of a learning curve there. While I expected a curve when learning the Report Designer, the killer was trying to understand the data model. That's where the curve turned into learning rock climb.

After a good bit of work, copying and pasting, swearing and fine tuning I finally have the report I wanted as well as a better (not good, but better) understanding of the TFS data warehouse.

And THAT's where this post would have saved me days of work (and will still save me time in the future). Understanding the TFS data warehouse/cube and using it for reporting and analysis is an area where the TFS books/guides could use additional coverage. Most talk about creating custom reports, but don't go into near enough detail...

 

BTW, one of the things that saved me is that Report Server reports are just XML files. When I wasn't able to figure out why something was working in one report, but not mine, I was able to open them both as XML files and dig through the details/source. This was a life saver. I come from the old school where we learned report build via Crystal Reports (i.e. proprietary binary file format). I would have killed to be able to see the true source of the reports...  Thank you Microsoft.

(via marcus's Team System motivation and experience - TFS Warehouse Cube)

 

Related Past Post XRef:
Team Foundation Server Power Tool, Process Template Editor Tip - If you open a *.WIT file and it appears as a normal XML file, instead of in a Designer...