Combine Hierarchy/Work Item/Date dimensions in TFS 2010 cube

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.

Team Foundation Server Check In Policy Pack

I search over the web for some Check In Policies for TFS, and some were founds, some really interesting in my case. I think to:

• old reference but interesting http://msmvps.com/blogs/vstsblog/archive/2006/12/08/available-check-in-policies-for-team-foundation-server.aspx
• newer references http://checkinpolicypack.codeplex.com/

But there are several drawbacks:

• TFS Power Tools Check In Policy pack is lacking open source...you cannot contribute, too bad when you miss just an adjustment to an existing policy
• Configurability go from none to too much
• Too much packages with different heterogeneous deliverables (package, docs...)

So why not an open sourced Check In Policy pack, on codeplex for example...I think we can gain from homogenization of development practices for Check-In Policies.

Here are some practices I think to:

• MSI deployment with Wix : one feature by policy for example to allow flexible installation
• Policy configuration management unified
• Encapsulate common functionnalities in shared components (path exclusion...)

I was thinking to this pack to share my first Check In policy around Work Items, when I discovered that TFS bundled one and TFS Power Tools one are really basic and not contributable (do not allow link/hierarchy query) !!

Could be great if several projects could merge in one...

First ALM France UG session

The session

I do not remember how I found this new user group, but I was really happy to assist to this first session.

Contents were "ecumenical", differents points of view and different communities represented (ok a little bit more of .NET one...oddly...). It was a first good point :).

Next, very important, I meet and talk with interesting people, and I hope we will share more in the future through this group.

Quickly about content, what is ALM? François Merand gives 3 major axes : Governance, Dev and Exploitation. Simple, great. So it is not just source control or continuous integration...

Then, Lucian Precup and Arnaud Heritier have made a quick overview of Open Source ALM ecosystem : Maven, IDE, IceScrum, Mantis, Git...and many more.

Daniel Cohen-Zardi followed with a presentation of his company and their product Code Fluent Entities, that proposes a DSL abstracting languages (he does not use the term DSL...but it seems like that to me).

The main points were around 3 concepts : agility, model-driven and executable models. I agree with him on these points. But for the solution, I remain to be convinced for now by this type of product...I prefer coding approach around DDD architecture.

Finally, François Tonic talked about his vision of ALM.

The group

To start, for french people, go to http://blog.alm-france.org/, for others hope it will develop in other countries ! :)

I am looking forward to Google Group discussion list to continue discussions...

Some ideas/reflexions to discuss...

1) We heard a lot the word "industrialization"...opposed to "craftsmanship"...we have to take care of these words use, there are a lot of misconception around industrialization...like over-standardization/normalization (quality ivory tower), over-specialization (like in classic industry)...to be discussed in the group :)

2) I consider ALM practices as central in day to day work, and badly initial formation are lacking in this field (at least mine and some of my colleagues...). There is certainly something to do about that.

3) I would like to share my current experience around ALM, and more importantly to have feedback...my boss and me were thinking about that...perhaps a good chance to try...:)

Thanks again for the creation of this group !