In my excitement to present you all with my amazing sparkline discovery, I definitely jumped ahead in how everything in my tracker came together on my Scrum Board. Thus, this post backtracks a bit and will cover how exactly I pulled the TFS data into my Excel document (using Pivot Tables), so that I could create the Scrum Board and all the associated goodness.
As a consumer of team data, it’s very easy to rely on automated summaries to formulate your reports. However, the easy way isn’t necessarily the best way, or the way to ensure success for your teams.
Therefore before creating any summarization tools, it’s important to consider reviewing all of the day-to-day data that your team and their work produce. Even though you may rely on summaries once you have a tool in place, it’s always imperative to be able to access and review the ground level data.
As usual, I know that this topic sounds really complex and fancy, but I promise, it’s not. Here we go:
1. Get Familiar with your TFS Cube
Frankly, this can be a little tricky. I won’t pretend to be fluent on this particular topic since the complexities of the TFS Cube can be a bit elusive, so your best bet is to check out some more expert advice over at MSDN.
What I can tell you is that for me the most important factors, after simply learning which of the two cubes we have here contained the data I needed, was becoming familiar with the dimensions and measures. Particularly if you have any customizations in your build of TFS, it’s imperative to learn the nuances of what each measure and dimension refer to so that you can create the proper relationships to analyze later.
2. Inserting a Pivot Table
After opening your file in Excel (a new file or an existing file, either is fine), navigate to the “Insert” Tab on the Ribbon. With that tab active, select the “Pivot Table” option, and then again select the “Pivot Table” option from the dropdown menu.
3. Connecting to your TFS Cube (Creating Your Pivot Table)
Connecting to a TFS Cube is not as intimidating as it sounds, I swear. Once you complete step 2, you’ll get this popup menu:
You’ll want to select the “Use an External Data Source” radio button, and then hit the “Choose Connection” button. At this point you’ll get a second popup that will list all of the connections available to you. If you don’t see your TFS Cube, make sure that you have “All Connections” selected in the upper dropdown menu. Once you select your TFS Cube Connection, as is natural, hit “Open” and then “Ok”, respectively. If you did it right, you’ll get this awesome looking graphic on your Excel sheet telling you that you did a great job:
4. Organizing Your Pivot Table
Once you have your Pivot Table inserted into your spreadsheet, you’re all set to get into the fun part (yes, I’m aware I have a twisted view of fun, but that’s ok!). Just click into that lovely Pivot Table area you created in Step 3, and you’ll get a new “Pivot Table Field List” menu to the right within your Excel window. This is where knowing your measures and dimensions come into play.
As you navigate through the list of Fields, select the items that you’re looking to analyze in your table. As you select each one, your Pivot Table will begin to populate, and you can see your selections and their relationships each step of the way.
At this point, once you select all of the fields that you want to view in your table, you’re all done. Voila, you have a Pivot Table!
5. Using Pivot Table Data in Scrum Boards
Once you have your table created, it’s time to tweak the row labels and specify the exact portions of your data that’s of interest in your analysis. Once that’s set, you can either use the “Grand Total” rows at the end of your Pivot Table, or utilize your own formulas to manipulate the data in whatever way you see fit. You can then take those data points you’ve created and use them in a Scrum Board, a report, or any other system you might need.
6. Want More?
If you’re interested in where I went next with this data (hint, I mentioned Scrum Boards and Sparklines a LOT), why don’t you check out Part 1 of the series?
Questions/Comments?
Feel free to comment here on my blog, or find me on Twitter @DokiDara.
