I start using TFS 2010 cube to make a Product Burndown chart based on Story Points (Y axis) over time (X axis) and State (series) of User Story and Defect work item types of our custom process template (customized from User Story and Bug ones found in MSF Agile process template).
Basics...
With Excel, it was really simple to do it: connect to the cube, use Date and Work Item Dimensions with Story Points Measure and tada, it is done. Ok, well, in fact, it was not exactly what we would like, because we work on a legacy application...so we have User Stories and Defects on different subjects, what we have modelled using a Project work item type that contains related User Stories and Defects.
For example, we have a "Maintenance" Project (not really ending btw...) which contains all production bugs we are fixing, and "Project A" and "Project B" Projects, each ones classically having a given budget and start/end dates.
...deeper...outch ! Problem !
My team has several Projects on one application at the same time, each from 20 man days up to several hundreds man days, but we keep only one iteration/product backlogs for the whole team on this application. Then, each Product Backlog item is related to one Project.
Then, I would like to have a Product Burndown chart restricted to items related to a particular Project. It would help to see how its Product Backlog items are evolving over time and to manage effort needed to keep this Project on track.
I would think that Work Item Tree Dimension would help me...but trying to add it as filter to my Excel report, it does nothing !
In fact, it is an expected behaviour. I understand it digging into SQL Server Analysis Services features (I never had a look at before...) and TFS 2010 cube configuration. There are several explanations:
- Dimensions are associated to one/several Measure Group, and the 3 dimensions I 'd like to use are not all together in a same Measure Group, Measure Group examples:
- Work Item includes Work Item and Date Dimensions, but not Work Item Tree one
- Work Item To Tree includes Work Item and Work Item Tree Dimensions, but not Date one
- Story Points Measure is a calculated member associated with Work Item History Measure Group, and it is calculated based on hidden Story Points Measure of this Measure Group
Trust me that other Measure Groups do not include at least 2 of these Dimensions...and none include the 3.
Solution
So the solution was:
- to add a view to TFS datawharehouse combining Fact tables containing Work Item facts and Hierarchy facts (no change to datawharehouse loading process!)
ALTER VIEW vFactWorkItemHistoryToTree AS SELECT wih.*, witt.WorkItemTreeSk FROM dbo.FactWorkItemHistory wih INNER JOIN dbo.DimWorkItem wi1 on wih.WorkItemSK = wi1.WorkItemSK INNER JOIN dbo.DimWorkItem wi2 on wi2.System_Id = wi1.System_Id INNER JOIN dbo.vFactWorkItemToTree witt on wi2.WorkItemSK = witt.WorkItemSK GO - to change TFS cube DataSource to add the new view and link it to related Dim tables (derived from FactWorkItemHistory for example), just use the designer included in Business Intelligence Studio (you open Analysis Services database directly on server with it)
- to add a new Measure Group with the 3 Dimensions I need (derived from Work Item History Measure Group for example, with Work Item Tree dimension added)
- to add a calculated member based on hidden Story Points Measure of the new Measure Group (within Business Intelligence Studio, open Team System cube –> Calculations tab to add the new calculated member, and associate it with Measure Group with Calculation Properties icon)
-- Story Points with Hierarchy Tree dimension -- Just a part of MDX request to show we use vFactWorkItemHistoryToTree member of our new measure group... CREATE MEMBER CURRENTCUBE.[Measures]. [Microsoft_VSTS_Scheduling_StoryPoints_Tree] AS ... Sum ( [Date].[Date].[Date].MEMBERS.Item(0) : [Date].[Date].CurrentMember ,[Measures].[vFactWorkItemHistoryToTree Microsoft_VSTS_Scheduling_StoryPoints] ) ... 
...even deeper with the same solution
Then, I can do what I wanted with Excel, i.e filter on each Project to have a Burndown chart on each.
Note it can be declined with any work item hierarchy. For example, we also have a Release work item type in our process template, allowing to manage releases contents. Then we can follow how Release backlog evolves through a Burndown chart.
Don't be afraid to look at TFS cube, it took me 2 days to find out what I need (starting with no skills in SSAS...). It can be very powerful.
2 comments:
I would like to add a word about what happens when your TFS cube has to be rebuilt (from scratch).
It is really easy, just keep scripts under source control somewhere, it just takes few minutes to reconfigure the cube with this customizations.
When you change your Work Items, you will probably have to rebuild the cube. You can do it through TFS Administration Console in Reporting node with Start Rebuild command.
But you could perhaps only have to rebuild only the cube, and not the datawarehouse, then, you can use the TFSConfig RebuildWarehouse command line with /analysisServices parameter. It would be much faster than whole rebuild.
For reference : http://msdn.microsoft.com/en-us/library/ee349264.aspx
Post a Comment