How to get the time between two TFS Work Item States in SSAS (or any other report)?

Question

Is there a way in TFS's reports that you can get the amount of time taken for a work item to transition between states?

Looking at the data in the Tfs_Warehouse database I can see that there is a record of every revision of a work item, and using TSQL, I could write a query to get the ChangedDate of each state change and diff them. Sadly I lack any know how of SSAS and we don't have a BI specialist at the moment.

Can this be done in a way that I can produce a Report out of TFS without writing TSQL / API queries?

To clarify I'd like to know how long it took for a Bug (or an average for a subset of bugs) to move from Active -> Resolved (for example). We have more states than the MSF Agile Template so I cannot just use the Microsoft ActivatedDate / ResolvedDate fields for this.

Edit: I've added a bounty to try and get an answer.

Answer 1

Basically what you want is retrieve the ActivatedDate and ResolvedDate fields (or other date based ones) info for every bug then compute the timespan for each Work Item and do some per Work Item display or average. Am I right?

So, you have fields in the OLAP Cube for ActivatedDate and ResolvedDate, the reportable type is set to Dimension, which means you can't do what you want with these fields because they can't be used as values in Excel (which is what you'd need to do the maths). To make the matter more complex it looks like you only have the date and not the time, which would be not precise enough for you.

In short: SSAS/Olap/SSRS won't help you in this case...

Well, SSRS could help you but you would have to develop an TFS warehouse adaptor to inject the data you want in the TFS Warehouse: good luck with that! (it can be done, but the first time will be painful).

If you really need this info:

You can write a little program that will use the TFS API to make a Work Item Query then get the value of the fields you want and develop the logic you want. It can be an excel add-in if you want to populate a grid and generate a graph from it.

If you're interested in TFS and plan to keeping working with it: it's worth the investment thousand times. Writing apps is easy and the good thing is once you've made one you can easily tweak it for new needs.