I love tracking user stories in SharePoint. It's free (assuming you have access to a Windows Server 2003 or 2008 machine with WSS turned on) and is extremely flexible. Adding new custom columns is easy. Adding custom views is easy. Bulk editing is amazingly simple and powerful (assuming you're willing to use IE). And building a custom home page "portal" with all the states of your team's workflow (e.g. unassigned, stories I'm working on, my resolved stories, to peer test, etc) is easy and is intuitive to use.

This 13 minute screencast shows you how to create a user stories list in SharePoint and build burn up and burn down charts off of the data using Microsoft Excel. It includes the custom fields you'll need in SharePoint, the formula's you'll have to create in Excel, and shows tips along the way like how to simplify refreshing your reports when data changes.

Once you've watched the screencast if you want to implement this yourself here is the information you'll need:

Custom SharePoint Columns

• Iteration (Choice column e.g. Iteration00, Iteration01, zBacklog)
• Released (Date - the date the iteration was closed out)
• Points (Number)
• Created Date (for demo only, you should be able to use "Created" on your project)

Calculated Columns in Excel

• Total Points to Date
  • The total points on a specific date including backlog, current iteration and all completed stories
  • =SUMIF([Created Date],CONCATENATE("<=",Table_owssvr_1[[#This Row],[Released]]),[Points])
• Completed To Date
  • Total points for stories released prior to current story's release date
  • =SUMIF([Released],CONCATENATE("<=",Table_owssvr_1[[#This Row],[Released]]),[Points])
• Backlog
  • What remains to be completed at a point in time (e.g. don't include user stories added to the backlog in iteration 4 if this is for a task in iteration 03)
  • =Table_owssvr_1[[#This Row],[Total Points to Date]]-Table_owssvr_1[[#This Row],[Completed to Date]]

Burndown Pivot Table Values

• Row: Released
• Filter: Released After [Some Date]
• Values: Max(Backlog)

Burnup Pivot Table Values

• Row: Released
• Filter: Released After
• Values
  • Max(Total Points to Date)
  • Max(Completed To Date)

Summary

I hope you found the presentation useful and if you are currently using SharePoint for tracking user stories please comment on your experiences.

As a software developer I never thought I'd be saying this (I suppose eight years of working for the company that invented this technique might bias me), but you can not underestimate the value of business process modeling when starting a new project. This is especially true if it's a small project and you don't have the benefit of a dedicated requirements analyst.

As a consultant (or as a person who is learning someone else's business) your job is to understand your customers existing business better than they do themselves. And you need to be on the same page with others regarding how you are going to change their day to day functions. And that's what business process modeling is for.

You may ignore this document after the first week on the project, in fact I would encourage you to. But even if you throw it away immediately after creating it, the process of developing the document will still:

• Flush out important questions
• Show your customer you understand their world
• Help document the project to other developers
• Facilitate communication (especially with the people who pay the bills)
• Generate user stories (requirements)
• Identify the entities that can feed into an Entity Relationship Diagram (or database I suppose)
• Aid making good choices for the decisions it be hard to undo later (like whether to manually code workflows or use Windows Workflow Foundation)
• More clearly identify pain points and areas where your software can help end users; and
• Identify metrics that can help determine project success from an ROI and product owner's perspective

Convinced this is a tool you need in your toolbelt yet? As long as the answer isn't "I only write code leave me alone" then check out this quick how-to:

Start with the AS-IS diagram

As soon as possible on a new project you should plan to interview your future end users. This shouldn't be an unstructured Q&A session. You should start with a rough draft of what you think their business process is and ask questions that identify the places where you were wrong.

In order to start the diagram you need to identify both the end users and existing systems and put those in "swimlanes" down the left side of a diagram. Swimlanes are important because they help you be explicit about who is performing what activities. Then flush out activities in the happy path in appropriate swimlanes like so:

As soon as you talk to end users (like the clerk in the example above) you will discover unhappy paths. What if the warehouse is out of a product? What if the payment was declined? What happens if a customer needs to return something?

Obviously you document these using standard activity diagram notation:

Identifying Pain Points/Opportunities

The interesting part about unhappy paths is how often they occur, how they affect the customers of your customer, and ultimately how they affect your customer's bottom line. For instance, how often is payment declined in the example above? Fairly frequently? Can you get an actual percentage? If it turns out it's because the original application failed to validate credit cards, then a small amount of programming effort can provide a large benefit to length of time until order fulfillment. Sometimes you can make the process more efficient without writing a line of code.

And at the end of your project if you can show that you've decreased the average length of time it takes to go through the process, ideally by showing less time was spent in unhappy paths, you will likely have made yourself one happy customer. This can be especially effective if your new system can show statistics (like percent of orders that are declined) and compare those to the old process.

Developing the TO-BE diagram

AS-IS process diagrams are completely useless unless you can effectively describe what the process will look like after you implement your new system. The process of developing these documents is pretty similar to the AS-IS diagram except of course you're trying to fix the pain points.

One technique I like for generating a backlog of user stories (tasks) is to put unique numbers with each step in the TO-BE process, then ensure there is at least one user story per step in the process. You may be surprised at how easily activities in your new project's swimlane map to user stories.

Summary

I'm a fan of writing code as soon as possible on a project, but if you fail to understand an end user's business process you may be writing the wrong code. Business process modeling can ensure you're writing the right code to solve the most important problems first. And that may be the difference between project success and project failure.

I suppose I'm a pretty hard core Microsoft developer. I've been doing Microsoft .Net C# development since the .Net framework was first released in 2002. Before that, starting in 1997 about when the technology was first released, I developed in Visual Basic with classic ASP (Active Server Pages). So that's twelve years of Microsoft development all told. Which is why the Ruby on Rails class I've been taking this week is so interesting. Ruby represents a significant branching out for me. So these are my initial impressions. I am a complete Ruby neophyte, so please keep all flames at least friendly in tone :).

Dynamic Typing

Let's get to the heart of this Ruby stuff. I thought I would passionately hate dynamic typing. I was getting ready to fill pages ranting about its evils. But that moment never came. Don't get me wrong the Visual Studio strongly typed intellisense no-reading-documentation-needed experience is far nicer than the NetBeans look-it-up-in-irb-with-.methods experience. But the feeling was more like mild to moderate annoyance rather than unbridled hatred. Like when I mistyped a variable and Ruby decided it was a new variable initialized as nil. Annoying, and hard to track down, but hey I'm a Ruby newbie, experts probably don't make these mistakes, right? Anyway, I would be interested to hear how non-trivial, well tested applications perform with refactoring (e.g. variables/fields in views?) and how bad things get during O&M.

Speed of Development

I figured speed of development would be Ruby's saving grace. It was that and more. The Ctrl-S-Alt-Tab-Ctrl-R-zero-delay development cycle is frickin' amazing! It made development feel more fun than it has in a long time. Part of that may be the slower-than-molasses-in-December speed of development I experience today on my (not 2010) SharePoint project. Regardless, even if I were working in an ASP.Net MVC project I suspect the extra speed of development in Rails by skipping the main compilation and JIT compilation steps would increase the enjoyment of a Ruby project enough to equal out the lack of strong typing.

Active Record vs LINQ

Perhaps I'm just not experienced enough with active record, but to me the syntax feels contrived, non-intuitive, and just kind of agitating. I mean it's cool that you can write

p = Person.find_all_by_first_name_and_last_name("Lee", "Richardson")

Ugly as sin, but cool. The alternative doesn't feel much better:

p = Person.all(:conditions =>
    { :first_name => "Lee", :last_name => "Richardson" })

Uch. Of course I am biased. I'm passionate about LINQ. I love LINQ more than any single feature in Microsoft development. I think LINQ may be the most brilliant stroke of genius Microsoft (Anders Hejlsberg) has ever had. And I don't think I realized how passionately I felt about the technology until I didn't have it. So for me I'll stick with:

Person p = ctx.People.Where(p =>
    p.FirstName == "Lee" && p.LastName == "Richardson");

Duck Typing

My mind was blown when the instructor showed us polymorphism without inheritance. I can't argue about how incredibly powerful it is. I just cringe to think of the potential misuse. But without any real Ruby experience I'll have to just leave it at "Wow!"

Mocking

It's pretty scary that you can override the functionality of any method anywhere in Ruby. But when the instructor showed us overriding DateTime.now for the purposes of mocking I had an "ah ha!" moment. Mocking DateTime.Now in C# is an awful experience that involves an intermediate class with a virtual "Now()" method. Ruby sure got C# on that one.

YAML

It's no secret that I passionately hate XML. And it's no secret that Microsoft passionately loves XML. Pity about the mismatch. Ruby on Rails really endeared itself to me when its designers recognized that XML is a travesty against humanity and used YAML Ain't Markup Language instead. Nice!

Interactive Ruby Console

The Interactive Ruby Console (IRB) is just wonderful. Now I can at least get that functionality today in C# with Joe Ferner's excellent Developer's Toolbox. What I can't get is the Interactive Rails Console (ruby script/console). Now that is awesome. I can't wait for C# 4.0 which I suspect will have this. For now RoR++, C#-- .

Tooling

I'm not sure how related this is to dynamic typing, but surprisingly I really, really missed Visual Studio. Perhaps RubyMine is better that NetBeans. I sure wasn't impressed with Komodo. I just felt like constantly switch between my IDE and various console windows, and a database viewer felt clumsy yet somehow necessary with Rails. It just felt so 1999.

Summary

There's no doubt about it, I could be very happy on a Ruby project. The no XML thing is just a perk, the fast development cycles and bringing fun back to coding again is truly awesome. But am I ready to give up Microsoft development full time just yet? No way. LINQ, strong typing, and believe it or not Visual Studio tip the scales back to about equal. Now if only Microsoft could somehow make compilation instantaneous.

The current version of SharePoint and I have a love hate relationship. Since I want to be able to look forward to a more joyful and fulfilling relationship with the tool/platform, I made a list of my biggest frustrations before I left for the SharePoint 2010 conference (#spc09). My objective was to return with most of the items checked off as resolved and to have a bunch of new functionality that I hadn't realized I needed. SharePoint 2010 easily fulfils the second promise with stuff like Business Data Services (Awesome!) and Visio Data Services (Amazing!). But I don't want to try to recap the whole conference so here's just how it compares to my initial list:

Testability

SharePoint is notoriously hard to unit test. The good news is Microsoft provided a whole session devoted to software development best practices that included a good chunk on unit testing. It was wonderful to see!

The bad news: SharePoint 2010 will not have public constructors and classes will still be sealed (final). The good news to the bad news: After discussing this issue with Chris Keyser I discovered that Peli de Halleux of Microsoft Research has a free tool called Pex that solves the whole problem. It allows what it calls detours, which allow you to mock non-virtual methods. This stuff is amazing and I'll blog more about it later.

Summary: Testability is better even for existing SharePoint installs -- so between Sporm and Pex this is a non-issue -- I'm happy.

No Referential Integrity

It kills me that if you create an employee list item that references a company list item and you delete the company list item then nothing happens. No cascading delete. No error message. Just an orphan record. SharePoint 2010 now supports both true referential integrity through errors and cascading deletes!

Summary: Awesome!

Validation is Terrible

Ever tried to validate by pattern in SharePoint (e.g. social security number)? How about range validate (e.g. date of birth can't be after today)? Or maybe validate one field against another? The answer is you can't. Not easily or consistently. If you handle validation in an event receiver then the user doesn't get the error until after a postback on a separate page. And custom field controls are a lot of work and they don't work in datasheet view.

SharePoint 2010 mostly solves these validation issues by allowing field level and list level validation. The error messages even show in datasheet view. The bad news is that pattern matching is not currently supported (e.g. you can't validate social security number).

Summary: Good stuff, need to see the final version to be extremely happy.

Poor Documentation

Ever noticed that blogs give you better details on SharePoint APIs than MSDN does? I don't even bother looking on MSDN for SharePoint content anymore. Since the hands on labs didn't contain any documentation I'll have to refer to what Steve Ballmer said during the Q&A session, which was that better documentation will be a priority.

Summary: high hopes, but TBD.

Views Don't Allow Precedence

If you've ever needed a view with A and (B or C) and ended up with (A and B) or C, you know where I'm coming from. As a developer you can implement precedence in CAML, but as an end user you're stuck. Sadly this issue is still not fixed in SharePoint 2010.

Summary: Fail.

Re-Deployment is Hard

Currently redeploying lists is hard; redeploying web parts without overwriting user settings is hard; and redeploying workflows is very hard.

Without documentation and intermittent Internet Joe Ferner and I had a hard time manually confirming that this is better. We also missed a key session on Thursday that talked about it. There were some tweets that looked promising, but I'll have to wait until Thursday's presentations are posted to be able to comment for sure.

Summary: Good I think, but still TBD.

Massive Duplication in CAML Instantiated Lists

Using CAML to instantiate a list is currently awful in SharePoint. It's like 5 pages of CDATA and HTML and XML with massive duplication. Sadly SharePoint 2010 doesn't solve the root problem: list instantiation looks exactly the same. The good news is that along with just about every other aspect of SharePoint 2010 development, Visual Studio makes this task mind numbingly easy to do. If you can just try not to look in that file that was auto-generated for you, then you should be fine.

Summary: Better.

Poor Usability

Once you get used to the current version of SharePoint the user interface is extremely consistent and many users really like it. But if you were to compare it to any modern Web 2.0 site it's a complete failure. SharePoint 2010 does an excellent job of implementing selective refresh/AJAX/Web 2.0. But to me it feels more complicated. Maybe this is because it introduces the ribbon, which I have never liked. Maybe it just is more complicated. In any event I'm not the best judge of usability, so I'll have to wait and see what others say.

Summary: Definitely better, but jury's still out.

Can't Reference a List AND Content Type

Suppose you have a Calendar list. The list supports two content types: Events, and Iterations (sprints). It's a nice architecture because you want to view iterations and events in the same calendar views. Now if you have a User Story list, wouldn't it be nice to have a lookup field that points only to Iterations? Sadly you couldn't do this before and you won't be able to for the foreseeable future.

Summary: Fail.

CAML for Queries

Technically this wasn't on my list because I use Sporm, but on behalf of my non-spormified software development brethren let me say that writing queries in CAML is awful.

SharePoint 2010 makes enormous strides in this area. I won't go into great detail, but you can now use LINQ on the server side and on the client side!

Summary: Awesome!

Summary

Lots of TBD, lots of awesomeness, still some fail. Worth upgrading? Absolutely. Will it still be a love hate relationship? Probably, but at the moment it's looking pretty darn good.

In my last post I described how a new open source tool called sporm significantly simplifies unit testing SharePoint. Making SharePoint unit testable is my absolute favorite feature of sporm because SharePoint is notoriously hard to unit test. But sporm provides other benefits as well and its ability to pull us out of the depths of verbose loosely typed XML hell and into LINQ excellence is next on my list of favorite features. So in this post I'll describe the pre-sporm technique of querying with CAML, how to query data using sporm, and finally how sporm supports SharePoint's unique architecture of allowing multiple content types per list and what that means to you.

Caml's Are Ugly

Warning: if you're new to SharePoint then what you're about to see may shock and upset you. If, like me, you hate both XML and loose typing then you will agree that CAML is awful, but bear with me I promise sporm will make it better. Much better.

CAML or Collaborative Application Markup Language is how one queries for data in SharePoint. A simple query might look like this:

<Query>
  <Where>
    <And>
      <And>
        <Eq>
          <FieldRef Name='First_Name' />
          <Value Type='Text'>Lee</Value>
        </Eq>
        <BeginsWith>
          <FieldRef Name='Last_Name' />
          <Value Type='Text'>Rich</Value>
        </BeginsWith>
      </And>
      <Leq>
        <FieldRef Name='Dob' />
        <Value Type='DateTime'>2009-01-01T00:00:00Z</Value>
      </Leq>
    </And>
  </Where>
</Query>

Simple right? ;) In case you didn't catch the meaning from the slightly, uh verbose, query this asks for records with a First_Name of "Lee", a Last_Name starting with "Rich" and a "Dob" less than or equal January 1 2009.

There are a couple of things to note about this query:

• Field names are loosly typed. If Dob were ever renamed to DateOfBirth the query would fail at runtime (probably during a demo to a customer, or if you're lucky during integration tests), but certainly not at compile time.
• And is a binary operator. This forces explicit precedence and removes the need for parenthesis, but at the cost of readability.
• Types must be explicitly defined. I guess this is necessary since it's XML, but somehow I just don't feel like this should be necessary.
• It's XML. Ok obviously, but the point is you have to type everything twice. <BeginsWith> </BeginsWith>. Ouch, so verbose, so angley, I so hate XML.

Now, there are tools that make this better. U2U's free CAML Query Builder tool significantly improves the experience of querying SharePoint data.

But it makes you wonder if something is wrong when you need a tool to retrieve data from your data store. Do you typically use tools to assist when you're writing SQL? Probably not. But like I said hang in there, sporm make things better.

Unlinq my Caml

If you were to write the same query as above using sporm it would look like this:

IQueryable<Employee> employees = GetEmployees().Where(e =>
      e.FirstName == "Lee"
      && e.LastName.StartsWith("Rich")
      && e.Dob <= new DateTime(2009, 1, 1));

Just a little easier to read than CAML, right? A couple of things to note:

• Fields are strongly typed. If you were to rename FirstName the compiler would catch every single instance at design time.
• Operators are standard C# operators. &&, .StartsWith(), and <= are all familiar and concise and use a standard, known precedence.
• Types are standard C# types. You never have to explicitly say that "Rich" is a string, it just is.
• Your query is actual C# code. The lambda (closure) and the fact that it uses deferred execution might throw off a junior developer in some scenarios, but the query is readable and works exactly the same as if you were querying in memory objects or querying a database with LINQ to SQL or the Entity Framework.

Nice! What's beautiful about this is that sporm converts your C# directly into CAML using C# 3.0's expression trees feature. What sporm can't convert to CAML it executes in memory transparently to you. Sporm uses log4net and outputs its CAML queries to the console by default, so it is a good idea to watch the output if you're concerned about performance.

Now the following isn't relevant to the comparison with CAML, but I would be negligent if I didn't explain how the GetEmployees() function works.

SP != DB B/C of Content Types

First of all GetEmployees looks like this:

private IQueryable<Employee> GetEmployees() {
      return MySPDataContext
            .GetCurrent()
            .GetList<Employees>()
            .OfType<Employee>();
}

How it works is that the static GetCurrent() method retrieves sporm's context object, which knows how to query a SharePoint site, from either the web context or thread local storage; next the GetList() method tells sporm which list you want to query; and the OfType() method tells sporm which content type within the list you want to query. This last part is important because sporm supports SharePoint's ability to have multiple content types per list, which other LINQ providers like LINQ to SharePoint do not. But what are content types and why should you care?

SharePoint's List/Content Type architecture seems odd at first, but it allows interesting scenarios not available in a traditional database. For instance you might have a calendar list that contains multiple types of records (list items) in the same list. Your calendar list might contain some combination of the following three record types: meetings with unique fields like Organizer (a person); iterations with unique fields like DeployedToProduction (a Boolean); and actions with unique fields like RelatedEmployee (a reference to another list). This architecture allows SharePoint to view all three types of records in a single view: like a per month calendar view. The data might look like this:

Sporm's unique architecture supports this scenario by allowing you to retrieve iterations like this:

return MySPDataContext
      .GetCurrent()
      .GetList<Calendar>()
      .OfType<Iteration>();

Or get meetings from the same list like this:

return MySPDataContext
      .GetCurrent()
      .GetList<Calendar>()
      .OfType<Meeting>();

While you may only use multiple content types per list occasionally you can feel comfortable knowing that sporm will support you when you need it. The rest of the time you can arrange your architecture to accommodate your 90% scenario of one content type per list. I'll discuss the architecture I'm using on my current project in my next post.

For now I hope this has clarified some of the benefits of using sporm, and I hope that you'll consider using it on your next SharePoint project.