Professional Team Foundation Server 2013 (2013)

What's in this chapter?

· Learning about the changes and new features

· Understanding the Team Foundation Server data warehouse

· Understanding the tools available to create and manage reports

· Using the new Work Item Charting in Web Access

· Creating and customizing reports using Excel

· Extending and customizing the data warehouse and dashboards

One of the key value propositions for Team Foundation Server has always been the reporting features that it provides. When you have your source control, work-item tracking, and build and test case management systems all integrated in a system like Team Foundation Server, the reporting can provide powerful insight into the status of your projects. The data collected and the reports provided by Team Foundation Server gives your projects a level of transparency that allows you to react and adjust to changing conditions.

In this chapter, you will first learn about the Work Item Charting tools that are new in Team Foundation Server 2013 Web Access. Then you will see changes that were first introduced in Team Foundation Server 2010 and the minor changes since. These changes are designed to support multiple team project collections on a single server and, thus, improve reporting capabilities. You will then learn about the three data stores in the Team Foundation Server data warehouse. This chapter also provides an overview of how to set up and configure the integration with SharePoint, and how to take advantage of the excellent reporting features. Finally, you will learn how to customize project portals and warehouse adapters.

What's New in Team Foundation Server 2013?

Team Foundation Server 2010 included a significant investment in the reporting infrastructure and capabilities. This prior investment means that very little has changed in the reporting features of the product in the 2012 and 2013 releases. However, Microsoft has begun a significant push to give you the ability to report against more current data by introducing Work Item Charting, which allows you to visualize the results of your work item queries. They are continually improving the feature set of Team Foundation Server through roughly quarterly updates. Work Item Charting will continue to be improved in these updates.

For customers who are upgrading from Team Foundation Server 2010, this news will bring some comfort. There were almost zero schema changes to the relational warehouse database and the Analysis Services cube, which means all your custom reports and dashboards should continue to work without modification. Contrast this to upgrading from the 2008 version to the 2010 version where the entire structure changed and almost all reports needed to be rewritten from scratch.

Following are the biggest changes for reporting and SharePoint integration since Team Foundation Server 2008:

· Work Item Charting has been added to Web Access.

· Cross-collection reporting is now supported.

· A relational warehouse schema is now supported.

· The Analysis Services cube schema is more usable.

· An optional, but richer, SharePoint integration is now supported.

· Excel-based reporting features have been added.

Work Item Charting in Web Access

Work Item Charting is a new feature in Team Foundation Server 2013 that allows you to visualize the results of work item queries in a myriad of formats including pie, bar, column, and stacked bar charts, as well as showing the data in a Pivot table format. Figure 15.1 shows the results of the My Work Items query in each of these formats on a single dashboard.

Figure 15.1 My Work Items query in multiple chart formats

The Work Item Charting capabilities are included in the on-premises version of Team Foundation Server as well as Visual Studio Online. To create a chart, open the Team Foundation Server Web Access portal and navigate to your team's home page. Click on the View queries link in the Activities section. As you can see in Figure 15.2, there is an All Bugs query that returns all of the Bug work items for the Mobile System team.

Figure 15.2 All Bugs query results

Looking just below the All Bugs query results title, you can see three links, the standard Results and Editor links and the new Charts link. Clicking the Charts link brings up the Charts page for the All Bugs query, as shown in Figure 15.3. Each query now has its own page for charts of the results of that query. Each page can hold multiple charts and is visible to the entire team.

Figure 15.3 Empty Charts page for the All Bugs query

To create a new chart for this query, simply click the New chart link in the toolbar. This will bring up the Configure Chart dialog box, as shown in Figure 15.4. We have filled in the values in Figure 15.4 to create a pie chart that shows the query results by Assigned To field value. We have renamed the chart to All Bugs By Assignment. Notice that the Configure Chart dialog box shows an example of the chart that changes as we change the criteria.

Figure 15.4 Configure Chart dialog box

When the chart has been configured, you can click OK and the chart will be saved to the Charts page for the All Bugs query, as shown in Figure 15.5. It will now update as the underlying query results change.

Figure 15.5 All Bugs query single chart

The Charts page can hold multiple views of your data. We have created two additional charts for the All Bugs query, as shown in Figure 15.6. If you need to change the criteria for a chart, you can simply click the pencil icon on that chart to open the Configure Chart dialog box.

Figure 15.6 All Bugs query multiple charts

Different queries will provide different fields to Group By. For example, my All Bugs Query's pie chart could only Group Work Item Type, Assigned To, and State, whereas the Assigned To Me query has the ability to be grouped by Work Item Type, State, Area Path, and Iteration Path. The fields available are dependent on the types of work items returned in the query results.

Note

This section has shown the charting functionality in Team Foundation Server 2013 RTM. As of this writing, Visual Studio Online has already provided the ability to Pin charts to the team home page. This feature will be provided to on-premises Team Foundation Server installation in one of the Team Foundation Server 2013 Updates.

Cross-Collection Reporting Support

Team Foundation Server 2008 allowed a single relational warehouse and cube per server. Ironically, organizations that were large enough to need multiple Team Foundation Servers were the same organizations that most needed aggregated reporting across their entire organizations.

Team Foundation Server 2010 allowed organizations like this to consolidate their multiple, separate servers into a single logical server. Now that they have a single logical server, they also have a single data warehouse across which they can do reporting.

Team project names are unique within a team project collection. Because of this, the data warehouse schema was modified to support a hierarchy of collections and projects.

None of the reports included with the out-of-box process templates are configured for cross-project or cross-collection reporting. However, it is possible to modify the Team Project filter on the reports to select multiple projects.

Changes to the Relational Warehouse

Before Team Foundation Server 2010, customer feedback reflected that the latency for reporting from the cube was too high. Customers wanted their work-item updates to be available in reports almost immediately.

A common example was a daily stand-up meeting, whereby the team would be looking at the Remaining Work report and question why the report showed that an individual or team hadn't made any progress. Often, it turned out that they had, in fact, updated their work items, but those updates hadn't been processed in the cube before the report was rendered.

Note

One of the useful features that arrived in Team Foundation Server 2012 was the task boards. At the top of each task board page is a miniature burndown chart, updated automatically every time you update any work item in the current iteration. This is also true for the velocity graph at the top of the product backlog screen and the capacity graph at the right of the sprint backlog screen.

Because this graph is not using the data warehouse functionality, it is always up to date and does not incur any delays in updating. This makes it an ideal candidate for pasting into, say, a project status e-mail.

For more information, see Chapter 14.

Until the 2010 release, reporting against the relational warehouse was not supported. Since the 2010 release, that is no longer the case. There are now several views on top of the warehouse to support reporting. These views make it easier to query for data and keep compatibility with future versions. Additionally, the naming conventions have been standardized to help differentiate fact tables and dimension tables. For example, dbo.Work Item is now called dbo.DimWorkItem, which identifies it as a dimension table.

Note

A more detailed discussion about fact tables and dimensions is provided later in this chapter.

Along with supporting queries against the relational warehouse, the work-item tracking warehouse adapters were updated for improved performance. The new adapters are now capable of moving data from the operational store to the relational warehouse much faster than in previous releases. The goal for the adapters was to keep the latency for work-item tracking less than five minutes during normal operations.

In the 2012 release, the only schema changes to the relational warehouse were:

· The addition of the Start Date and End Date to the iterations

· The removal of some of the fields that were used internally in the 2010 release for configuration of the warehouse

Changes to the Analysis Services Cube

Although the cube in Team Foundation Server 2005 and 2008 provided useful data and was reasonably well-used by customers, there was room for improvement. Along with supporting the architecture improvements, the changes in Team Foundation Server 2010 improved usability, query performance, and processing performance.

The main changes to the cube schema starting with Team Foundation Server 2010 include the following:

· The Current Work Item and Work Item History measure groups were combined into the Work Item measure group. Now you just include the Date measure to show historical trends.

· Area and iteration dimensions have been folded into the Work Item dimension as true hierarchies.

· Some dimension names have been updated to make them more meaningful and provide context, especially when looking at the entire list. For example, Platform is now Build Platform.

· Dimensions starting with Related have been moved to the Linked Work Item dimension.

A more detailed discussion of measures is presented later in this chapter.

The main additions to the cube schema starting with Team Foundation Server 2010 include the following:

· Work-item hierarchy and linking are now supported in the cube through the Linked Work Item and Work Item Tree dimensions.

· Work-item types can now be grouped into categories. For example, the Bug category can group Bug and Defect work-item types together. This is useful if you have different terminology across your team projects and need a meaningful report across all of them.

· Area Path and Iteration Path are now available as attributes on the Work Item dimension. This allows you to show a flat string (rather than a hierarchy) on your reports.

· As shown in Figure 15.7, display folders have been added to the Work Item dimension to make it easier to group fields, rather than display one long list.

Figure 15.7 Display folders on the Work Item dimension

Note

For more information on the data warehouse changes and the reasons behind them, refer to John Socha-Leialoha's three-part blog post titled “Upgrading Team Foundation Server 2008 Reports to 2010” (Part I at http://aka.ms/UpgradeTfsReports1; Part II at http://aka.ms/UpgradeTfsReports2; and Part III at http://aka.ms/UpgradeTfsReports3). Also see the official MSDN documentation, “Changes and Additions to the Schema for the Analysis Services Cube,” at http://aka.ms/TfsCubeChanges.

Optional and Richer SharePoint Integration

Integration with SharePoint is an important feature for Team Foundation Server. However, the installation and configuration of the integration was a significant source of problems. Along with the other architectural changes designed to support different server topologies, SharePoint integration is optional.

If you want to use the SharePoint integration features, you can configure SharePoint at install time or at a later time. The configuration options are designed to be flexible.

Team Foundation Server 2013 integration is available with both SharePoint 2010 and SharePoint 2013.

Warning

The reporting and SharePoint integration features are not available when Team Foundation Server is configured in the Basic configuration, when Team Foundation Server Express is used, or when installed on a client operating system (such as Windows 8). If you have any of these configurations and would like to enable the reporting features, you must use a Standard or Advanced configuration on a server operating system.

Team Foundation Server Data Warehouse

The Team Foundation Server reporting and data warehouse features comprise three data stores, as shown in Table 15.1.

Table 15.1 Team Foundation Server Reporting Data Stores

Along with these three data stores is a set of scheduled jobs that move data between the stores:

· Warehouse adapter jobs (sometimes called sync jobs) periodically copy changes from the operational store to the relational database.

· Analysis processing jobs instruct the cube to begin either an incremental or full process.

Figure 15.8 shows a high-level representation of this data movement and processing.

Figure 15.8 High-level architecture of the Team Foundation Server data warehouse

Operational Stores

The operational stores in Team Foundation Server are nothing more than the databases that support the normal day-to-day operations of the server. In previous versions of Team Foundation Server, there were different databases for the different feature areas (for example, the TfsWorkItemTracking and TfsVersionControl databases). In Team Foundation Server 2010, the contents of these databases were merged into a single Tfs_Collection database for each team project collection.

The schema of these databases is optimized for Team Foundation Server commands, rather than reporting. The data in these databases is changing all the time and does not lend itself to historical reporting and analysis.

The operational stores should not be accessed directly. The only supported interface for accessing them is through the Team Foundation Server object model.

Relational Warehouse Database and Warehouse Adapters

Each component in Team Foundation Server has different requirements for storing data in the relational warehouse database. The warehouse adapters for each operational store are responsible for transferring data to the data warehouse for their store.

Although the warehouse adapters are set to run on a schedule, they are also triggered and run on-demand when data changes. This keeps the latency in the relational warehouse low.

The warehouse adapters are also responsible for making schema changes in the relational warehouse and cube. For example, when you add a new field to a work-item type and mark it as reportable, the warehouse adapter will perform a schema change and add a new column to the relational warehouse, as well as make changes to the cube definition.

The dynamic nature of these data adapters allows the structure and mechanics of the data warehouse to be hidden from project administrators. This is one of the unique benefits of reporting in Team Foundation Server. You can define your work item types in a single place using relatively straightforward schema and tools. You then automatically get access to rich reporting features based on these customizations. You never have to deal with database schema changes or updating cube structures.

The downside of this, however, is that if you want to make customizations to either the relational warehouse or cube, you must deploy them as a custom warehouse adapter. If you don't, your customizations will be lost when the warehouse is rebuilt.

The relational warehouse database stores data in a set of tables organized in a star schema. The central table of the star schema is called the fact table, and the related tables represent dimensions. For example, the dbo.FactCurrentWorkItem table has one row for every work item stored in the work-item tracking operational store. A dimension table stores the set of values that exist for a given dimension. For example, a Person dimension is referenced by the Work Items fact table for the Assigned To and Closed By properties. You'll learn more about fact tables and dimensions later in this chapter.

Querying the Relational Warehouse Database

In Team Foundation Server 2010, writing reports against the relational warehouse database using Transact-SQL (TSQL) queries became officially supported. As a rule of thumb, you'll generally want to use the cube for historical reports, or reports that require a lot of slicing and dicing using parameters or aggregate data. The cube is preaggregated and indexed, and is ideal for this sort of reporting.

The relational warehouse, on the other hand, allows you to create reports that pull loosely related data together in ways not possible with the cube.

Following are the nine views against which you can query and write reports with some level of assurance that they will work when the server is upgraded to a future version of Team Foundation Server:

· CurrentWorkItemView

· WorkItemHistoryView

· BuildChangesetView

· BuildCoverageView

· BuildDetailsView

· BuildProjectView

· CodeChurnView

· RunCoverageView

· TestResultView

The other views that begin with v and end with Overlay are used for processing the cube, and, as such, aren't meant for use in your own reports.

The relational warehouse is an ideal store to use if you require reports with a lower latency than the cube can provide.

Note

As with previous versions of Team Foundation Server, fields that have the Html data type are not stored in the relational warehouse. Therefore, they are not available in the cube. For reporting against those fields, you must use the Work Item Tracking object model to query and retrieve them.

Querying the Current Work-Item View

Using the CurrentWorkItemView, you can query the relational warehouse and retrieve a list of work items without using the Work Item Tracking object model. For example, following is a work item query (WIQ) that returns all non-closed bugs assigned to John Smith in the Contoso project:

SELECT

[System.Id],

[Microsoft.VSTS.Common.StackRank],

[Microsoft.VSTS.Common.Priority],

[Microsoft.VSTS.Common.Severity],

[System.State], [System.Title]

FROM WorkItems

WHERE [System.TeamProject] = ‘Contoso'

AND [System.AssignedTo] = ‘John Smith'

AND [System.WorkItemType] = ‘Bug'

AND [System.State] <> ‘Closed'

ORDER BY

[System.State],

[Microsoft.VSTS.Common.StackRank],

[Microsoft.VSTS.Common.Priority],

[Microsoft.VSTS.Common.Severity],

[System.Id]

And here's an equivalent query that retrieves the same data from the relational warehouse:

SELECT

[System_Id],

[Microsoft_VSTS_Common_StackRank],

[Microsoft_VSTS_Common_Priority],

[Microsoft_VSTS_Common_Severity],

[System_State],

[System_Title]

FROM CurrentWorkItemView

WHERE

[ProjectPath] = ‘\ContosoCollection\Contoso'

AND [System_AssignedTo] = ‘John Smith'

AND [System_WorkItemType] = ‘Bug'

AND [System_State] <> ‘Closed'

ORDER BY

[System_State],

[Microsoft_VSTS_Common_StackRank],

[Microsoft_VSTS_Common_Priority],

[Microsoft_VSTS_Common_Severity],

[System_Id]

Dynamically Retrieving the Web Access Address

One of the things that people want to do is provide a hyperlink from a work item in a report to the Web Access view of that work item. This is useful, because it allows others to interact with the work item without needing Visual Studio installed.

To be able to create the hyperlink, you need to know the address of the server. Additionally, instead of surfacing the URL as a report parameter, or hardcoding it, it is ideal to somehow retrieve it from the database. But how, you might ask?

From within a SQL query of the relational data warehouse, this can appear to be difficult at first. However, with the ToolArtifactDisplayUrl field in the DimToolArtifactDisplayUrl table, you can easily find it.

The following SQL query essentially takes the first artifact display URL and retrieves the first half of the string before /CollectionName/WorkItemTracking/WorkItem.aspx?artifactMoniker=. This means that it will continue to work regardless of whether the server is configured with a virtual directory (/tfs/) or not, as well as HTTPS, custom port numbers, and so on.

SELECT TOP 1

SUBSTRING(

ToolArtifactDisplayUrl,

0,

PATINDEX(

‘%/’ + ProjectNodeName + ‘%',

ToolArtifactDisplayUrl

)

+ 1

) + ‘web/’ as WebAccesBaseUrl

FROM DimToolArtifactDisplayUrl

INNER JOIN DimTeamProject tp

ON

tp.ParentNodeSK = DimToolArtifactDisplayUrl.TeamProjectCollectionSK

WHERE ToolType = ‘WorkItemTracking/Workitem'

AND ProjectNodeTypeName = ‘Team Project Collection'

Querying the Work-Item History View

You can construct an “as of” query that returns only the last records for each work item that was modified before a certain date. For example, the following “as of” query returns the remaining work as of the end of December 2011:

SELECT System_Id, Microsoft_VSTS_Scheduling_RemainingWork

FROM WorkItemHistoryView WHERE System_ChangedDate < ‘1/1/2012'

AND System_RevisedDate >= ‘1/1/2012'

AND RecordCount > 0

AND ProjectPath = ‘\ContosoCollection\Contoso'

Other Considerations for Querying the Relational Warehouse

The relational warehouse is not suitable for all queries and, therefore, some will be faster using the Work Item Tracking object model that uses the operational store. Team Foundation Server 2010 introduced multiple project collections, and these collections share the same relational warehouse and cube.

The following are important considerations to keep in mind when writing queries against the views:

· Use ProjectPath or ProjectNodeGUID as the filter. A team project's name is not necessarily unique across multiple collections on the same logical server, whereas the project's path is fully qualified with the collection name and a project's GUID is also unique.

· Use unique keys for joins. For example, a work item ID is no longer guaranteed to be unique within the warehouse, because the same work item ID could exist in different team project collections.

· Be aware of compensating records. Whenever a work item is updated, a pair of records is added to the warehouse. The first record negates the previous record. This makes querying the relational warehouse faster for some types of queries.

Note

Note For more information on compensating records, see “Compensating Records” on MSDN at http://aka.ms/TfsCompensatingRecords, and “Work Item Tracking Compensating Records” at http://aka.ms/TfsCompensatingBlog.

Analysis Services Cube

The fact tables in the relational warehouse are suitable for reporting on current information. However, reporting on historical trends of data over time requires duplicating the data for every time interval that you want to report on.

Each time the cube is processed, the relational warehouse data is aggregated, summarized, and stored. The cube is a single central store to report against without having to aggregate across the different operational stores.

The cube contains dimensions, facts, attributes, and measures. Table 15.2 and Figure 15.9 show the definitions and the relationships of these items, respectively.

Note

For more information, see “Perspectives and Measure Groups Provided in the Analysis Services Cube” on MSDN at http://aka.ms/Tfs2013Cube.

Table 15.2 Cube Terminology

Figure 15.9 Relationships of objects in the cube

Cube Perspectives

In the cube, perspectives are groups of related dimensions and measure groups. A perspective is a subset of the features and objects of a cube. They are useful because you don't have to scroll through the whole Team System cube to get to where you need to be.

Perspectives are available only when you are using the Enterprise edition of SQL Server Analysis Services. A license for only the Standard edition is included with Team Foundation Server, so you'll need to license the other edition separately if you want to use cube perspectives.

Cube Processing

The Analysis Services cube is processed periodically on a schedule. The processing is triggered by two built-in Team Foundation Server jobs that correspond to the two different processing types:

· Full Process—Re-creates the cube from its definition, and processes every object in the cube. The default processing interval is every day at 2 a.m. for a full process.

· Incremental Process—Processes only objects that have changes since the last full or incremental process. The default processing interval is every two hours for an incremental process.

If the previous cube process failed, or the cube schema has changed, the next process is upgraded from an incremental process to a full process.

Note

If you would like to change the processing interval, see the article “Change a Process Control Setting for the Data Warehouse or Analysis Services Cube” on MSDN at http://aka.ms/Tfs2013CubeConfig.

Data Warehouse Permissions

Users within Team Foundation Server are not automatically granted access to the relational warehouse or cube. They must be explicitly granted access. The reason for this is that there are no fine-grained permissions provided or security trimming performed in the warehouse. When users have permission to view the warehouse, they have full access to the warehouse data for all team projects in all team project collections.

In some organizations, it is perfectly acceptable to allow any individual with work-item access in a particular project to have access to the whole data warehouse. However, in other more regulated industries and organizations, these permissions are reserved for a smaller subset of users.

To grant access, the TfsWarehouseDataReader role exists in both the Tfs_Warehouse relational database and the Tfs_Analysis cube. Users and groups can be added to these roles to allow them access to the resources.

Note

For more information, see the article “Grant Access to the Databases of the Data Warehouse for Visual Studio ALM” at http://aka.ms/Tfs2013CubeAccess.

SharePoint Integration

Once you have the standard reporting features working correctly, you can optionally configure integration with SharePoint. SharePoint integration is comprised of the following parts:

· Team Foundation Server Extensions for SharePoint

· Excel Services and dashboard compatibility

SharePoint Extensions

In order for a team project to have SharePoint integration, Team Foundation Server must have an association with a SharePoint web application. In order for this association to be configured, the SharePoint server must have the Team Foundation Server Extensions for SharePoint Products installed and configured.

There is no requirement that SharePoint be installed on the same server as Team Foundation Server, or even managed by the same people. Many organizations already have an existing SharePoint farm, and Team Foundation Server can integrate with the farm, as long as the Extensions are installed and configured.

The Extensions include site templates, web parts, and SharePoint timer jobs that maintain the associations between team projects and project portals, among other things.

Note

For more information, see “Extensions for SharePoint Products” at http://aka.ms/TFS2013SPExt.

Excel Services and Dashboard Compatibility

Excel Services is a feature of the Enterprise edition of SharePoint. It allows an Excel workbook to be rendered on the SharePoint server and presented to the user as a web page. This is incredibly useful because of the following:

· For report producers, pivot tables and pivot charts can easily be created in Excel.

· For report consumers, no extra software is required. The reports are simply web pages.

Note

For detailed instructions on manually integrating Team Foundation Server and SharePoint, you should consult some articles on MSDN. See “How to: Set up remote SharePoint Products Team Foundation Server” at http://aka.ms/TFS2013SetupSP, and “Configure Team Foundation Server Extensions for SharePoint Products” at http://aka.ms/TFS2013SPExt.

Adding a Project Portal and Reports to an Existing Team Project

For a number of reasons, you might not have a project portal or reports associated with your team project, such as in the following scenarios:

· The server or team project collection may not have had reporting or SharePoint integration configured when the team project was created.

· The process template used to create the team project may not have included the reporting or SharePoint tasks.

· Creating a SharePoint site might have been skipped during the project creation wizard.

· The connection between the team project and project portal may have been removed, or been invalid, before an upgrade.

During a TFS 2005/2008 to 2013 upgrade, the TFS installation was first upgraded to TFS 2010 before upgrading to TFS 2013. If the 2005 or 2008 server was imported to 2010 rather than upgraded, the project portal and reporting settings would have been lost.

Fortunately, with the help of the Team Foundation Server Power Tools, it's easy enough to add either a project portal or the default reports from a process template after the fact. From a Visual Studio command prompt, you can use the following commands:

· tfpt addprojectportal—Create a project portal for an existing team project that doesn't currently have one.

· tfpt addprojectreports—Create (or overwrite) the reports for an existing team project.

Additionally, you can use Visual Studio and navigate to the Team menu bar, Team Project Settings, and then Portal Settings to modify the association of a team project with a SharePoint site at any time, as shown in Figure 15.10.

Figure 15.10 Portal Settings dialog box

Creating Reports

Reporting is a powerful feature in Team Foundation Server. It breaks down the usual barrier within teams that is often caused by a lack of information. Team Foundation Server provides a powerful set of reports in the box, and provides the capability to add additional reports based on your needs.

Tools

Because reporting in Team Foundation Server is based upon SQL Server, any tool that can produce reports from SQL Server can be used. Following are the main tools that Team Foundation Server is designed to work with:

· Excel for pivot tables, pivot charts, and dashboards

· SQL Server Report Builder

· SQL Server Business Intelligence Development Studio (BIDS)

· SQL Server Data Tools

Each of these tools has different capabilities, as well as an associated learning curve. Figure 15.11 shows this comparison.

Figure 15.11 Comparison of report authoring tools

Excel Reporting from a Work-Item Query

Creating reports with Excel has the lowest barrier to entry. It's powerful enough for most purposes, and leverages a tool that most people are already familiar with. Perhaps the most impressive new reporting feature, originally introduced in Team Foundation Server 2010, is the capability to create reports from work item queries.

Although many people working with previous versions of the product used Excel to create reports based on the cube, it was still not approachable for many. You first had to be given access to the cube, be told the server name, and then wade through all the dimensions to find the ones you wanted in your report.

Starting with Team Foundation Server 2010, you can go from a Work Item Query to a pivot chart report in as little as two steps. No special knowledge is required. To do this, open Team Explorer, select the Work Items link, and start by expanding either the Shared Queries or My Queries folder. Then, right-click one of the queries and select Create Report in Microsoft Excel, as shown in Figure 15.12.

Figure 15.12 Selecting the Create Report in Microsoft Excel option

The first thing that happens is that Excel translates the Work Item Query into a query for the Analysis Services cube. After that, it presents a New Work Item Report dialog box, as shown in Figure 15.13. From this dialog box, you select which fields that you would like to pivot by, as well as the type of reports to generate.

Warning

Sometimes translating the Work Item Query can take longer than expected. The more columns that you have in your query, the longer the translation will take. It's a good idea to have only the columns that you want to pivot on in your query before you try generating a report.

Figure 15.13 New Work Item Report dialog box

There are two different types of reports:

· Current reports—These reports show the current state of the work items, represented as pie charts, as shown in Figure 15.14.

Figure 15.14 Current Report pivoted by State

· Trend reports—These reports show the historical trend of the work items, represented as area charts, as shown in Figure 15.15.

Figure 15.15 Trend Report pivoted by State

Once the reports are generated, you have a workbook prepopulated with the Analysis Services database connection. You can further filter and customize the automatically generated reports, or create entirely new reports.

Note

For more information, see “Creating Reports in Microsoft Excel by Using Work Item Queries” at http://aka.ms/Tfs2013ExcelReports.

SQL Server Reporting Services Reports

SQL Server Reporting Services provides a powerful reporting platform. Along with allowing you to run rich reports, Reporting Services also provides the following features:

· Subscriptions—Reports can be executed on a regular schedule, and the results can be e-mailed to the team (for example, a weekly progress report).

· Data-driven subscriptions—Reports can be executed and the parameters or delivery schedule can be dynamically changed based upon the results of a database query. For example, you could send a daily e-mail to team members who have high-priority bugs open.

· Caching and snapshots—If a report is particularly complex, or is refreshed regularly, you can configure caching and snapshots to improve performance.

· Linked reports—By using linked reports, you can create multiple reports with different parameters off a single base report (for example, a remaining work report with different area and iteration parameters for different teams within a project).

These Reporting Services reports are also the most accessible. For example, they are available from the following:

· Directly from the Report Manager website

· Integrated in Visual Studio Team Explorer

· As web parts on the SharePoint project portal

Permissions

Before you can access SQL Server Report Builder from the Report Manager website, you must be granted the appropriate permission. In addition to this permission, if you want to publish your report for others to use, you will need that permission as well. The Team Foundation Content Manager role is created as part of the Team Foundation Server configuration and includes both of these permissions.

Note

For more information, see “SQL Server Reporting Services Roles” at http://aka.ms/Tfs2013SSRSRoles.

SQL Server Report Builder

Report Builder provides a Microsoft Office–like report authoring environment. Using the tool, you can create and edit reports directly from the Reporting Services server.

You can download and install SQL Server 2012 Report Builder from http://aka.ms/sql2012rb. Once it is installed, you can access Report Builder from the Start menu under the Microsoft SQL Server 2012 Report Builder 3.0 folder.

To build a simple report, open Report Builder and select the Chart wizard icon on the design surface. When the New Chart Wizard appears, click the Create a dataset radio button and then click Next. Then, on the Data Source Connections screen, select Browse. When the Select Data Source screen appears, select the Tfs2010OlapReportDS shared data source from your Reporting Services Server, as shown in Figure 15.16. Continue through the wizard. When prompted for Data Source credentials, select Use the current Windows user.

Figure 15.16 Select Data Source screen

On the Design a query screen shown in Figure 15.17, drag the Work Item.Area Path dimension attribute and the Work Item Count measure onto the query pane and click Next.

Figure 15.17 Design a query screen

On the Choose a chart type screen, select a Column or Bar chart and click Next. On the Arrange chart fields screen shown in Figure 15.18, drag Area_Path from the available fields list to the Categories list. Then drag Work_Item_Count to the Values list and click Next.

Figure 15.18 The Arrange chart fields screen

Select a chart style and, when the wizard completes, select Run from the Ribbon (or press F5). The report should be rendered, and you should see something similar to Figure 15.19.

Figure 15.19 Example report created with Report Builder

When you are finished with your report, you can save it to your Reporting Services server and share it with other team members.

Note

For more information, see “Getting Started with Report Builder” at http://aka.ms/RBGettingStarted.

Note Both SQL Server 2008 R2 and SQL Server 2012 include Report Builder 3.0. This version includes new wizards and many other improvements over the previous versions that make it a compelling choice for report authors.

SQL Server Business Intelligence Development Studio and SQL Server Data Tools

SQL Server provides an integrated environment for developing cubes, data sources, and reports. This tool has a different name depending on the version of SQL Server you have installed. In SQL Server 2008 and 2008 R2, this tool is called Business Intelligence Development Studio (BIDS). In SQL Server 2012, this tool is called SQL Server Data Tools (SSDT). To install BIDS, run the Setup program for SQL Server 2008 or 2008 R2 and select the Client Components check box when you specify the components to install. In SQL Server 2012, you will select SQL Server Data Tools instead. Because BIDS and SSDT are add-ins to Visual Studio, they will install the Visual Studio shell if you don't already have it installed. BIDS and SSDT are usually installed with an older-than-current Visual Studio Shell, so BIDS 2008 R2 runs inside Visual Studio Shell 2008, and SSDT 2012 runs inside the Visual Studio 2010 shell. These tools can be installed side by side on a computer with newer versions of Visual Studio.

If you need to create complex and rich reports like the ones that are included with the product, you should refer to the white paper by John Socha-Leialoha. The paper is called “Creating Reports for Team Foundation Server 2010,” and it's available athttp://aka.ms/Tfs2010Reports.

Setting Default Report Parameters with Linked Reports

The reports that are included in the Scrum, Agile, CMMI process templates are very powerful while, at the same time, very generic. (These process templates are examined in more detail in Chapter 12.)

For them to be generic, a lot of their behavior is driven through parameters. For example, there are parameters for areas, iterations, and work-item types. Without any customization, each time users open the report, they must select the correct parameters before the report is meaningful to them.

If you have multiple teams using a team project, and they are using different area paths to keep their work items separate, the default parameter settings of the reports can be frustrating. Even if you're the only team working in a team project, you might want quick access to reports with preconfigured iteration parameters.

With the use of linked reports, you can predefine a set of parameters for a report and have it appear as a new report without creating an actual copy of the original report.

For more information, see the following blog posts:

· “Customizing Report Parameters—Cube Reports” at http://aka.ms/TfsReportParams1

· “Customizing Report Parameters—SQL Reports” at http://aka.ms/TfsReportParams2

SharePoint Dashboards

SharePoint dashboards are a feature made possible through the integration between Team Foundation Server 2013 and SharePoint 2010 or 2013. Each dashboard is made up of three different types of web parts:

· Team Foundation Server web parts—These access the operational store, and show the current data in the system. They are interactive and can be used to update work items.

· Page Viewer web parts—These display SQL Server Reporting Services reports. They pass through parameters and cache results.

· Excel Services web parts—These render charts from Excel workbooks stored in a SharePoint document library. They use the Single Sign-On (SSO) or Secure Store Service (SSS) to authenticate to the cube server.

When Team Foundation Server is integrated with the Enterprise edition of SharePoint (which includes Excel Services), the dashboards will display Excel Services web parts. For servers that don't have Excel Services available, the dashboards will use Page Viewer web parts and display Reporting Services reports.

Both the Agile and the CMMI process templates come with the following dashboards. However, only the first two dashboards are available on a server without Excel Services:

· My Dashboard—Quickly access work items assigned to you.

· Project Dashboard—Review progress with the team. Shows the Task Burn Down and Burn Rate reports.

· Progress Dashboard—Track progress toward completing an iteration.

· Bugs Dashboard—Monitor bug activity.

· Build Dashboard—Monitor code coverage, code churn, and build activity.

· Quality Dashboard—Troubleshoot software quality issues with the team.

· Test Dashboard—Monitor test progress and find gaps in test coverage.

Note

For more information, including detailed descriptions and samples of each of the dashboards, see “Dashboards (Agile)” at http://aka.ms/Tfs2013AgileDash and “Dashboards (CMMI)” at http://aka.ms/Tfs2013CMMIDash.

Accessing Dashboards

The easiest way to access dashboards for a team project is to select the Documents link in Team Explorer, and then select the Show Project Portal link, as shown in Figure 15.20. This will then open the default web browser and navigate to the SharePoint site associated with that team project.

Figure 15.20 Show Project Portal link in Team Explorer

If there is no project portal associated with the team project, then the documents link will not be available on the home screen.

Customizing a Dashboard

The default dashboards have no filters applied and, therefore, the first customization you'll want to make is to scope them to the area and iteration that your team is currently using.

To create a customized dashboard for your team, follow these steps:

1. Browse to an existing dashboard on your project portal site.

2. Select the Copy Dashboard button in the site toolbar, as shown in Figure 15.21.

Figure 15.21 Toolbar showing Copy Dashboard button

3. On the Copy Dashboard Page screen shown in Figure 15.22, enter a Dashboard File Name and Title for the new dashboard. Then, click the Copy Dashboard button.

Figure 15.22 Copy Dashboard Page screen

Now you can modify the web parts on the dashboard to show details specific to your team.

Advanced Customization

A few advanced customization topics to briefly look at include:

· Customizing project portals

· Customizing warehouse adapters

· TfsRedirect.aspx

Customizing Project Portals

Project portals are designed to be customized to the team or the organization's needs. Beyond the simple customization available within SharePoint, you can modify the process template to change project portals created in the future. Following are a few scenarios that you might want to do this for:

· Your organization has an existing SharePoint site template, and you want to modify it to include the Team Foundation Server dashboards.

· You want to modify the existing Team Foundation Server site templates to include your customizations for future project portals.

· You want to change the visual appearance of the portal site.

Note

For more information, refer to the white paper, “Customizing Team Foundation Server Project Portals,” by Phil Hodgson at http://aka.ms/TfsProjPortals.

Customizing Warehouse Adapters

As discussed earlier, if you want to make customizations to either the relational warehouse or cube that are beyond simple field changes, you must deploy them as a custom warehouse adapter. If you don't deploy the changes as an adapter, your customizations will be lost when the warehouse is rebuilt.

A custom adapter must know how to do the following:

· Create the schema in the relational warehouse.

· Retrieve and transform data from the operational store, and load it into the relational warehouse.

· Create the schema in the analysis database.

· Create a warehouse adapter sync job and schedule it.

Note

Note For more information, refer to the Team Foundation Server 2010 sample warehouse adapter from Nick Ericson in the MSDN Code Gallery at http://aka.ms/Tfs2010SampleAdapter. Even though it is for Team Foundation Server 2010, it will work with Team Foundation Server 2013 once you update the project references.

TfsRedirect.aspx

If you look at the Link property for the Page Viewer web parts on the dashboards, you'll see that they're set to a value like the following:

/sites/DefaultCollection/FabrikamFiber/_layouts/TfsRedirect.aspx?tf:type=Report&tf:

ReportName=Dashboards/Burndown&tf:ShowToolbar=0&Width=381pt&Height=180pt

TfsRedirect.aspx is a special piece of glue that helps SharePoint, Reporting Services, and Team Foundation Server work together. For example, several items on a project portal that point to other related resources are:

· Team Web Access

· Process Guidance

· Reports on the dashboard pages

Because these settings are stored only in Team Foundation Server and can be changed at any time, SharePoint uses the TfsRedirect.aspx page to retrieve them.

By specifying the tf:Test parameter, you can see the underlying settings, which may be useful in debugging project portal configuration problems. For any existing project portal, simply append the following to the site URL:

/_layouts/TfsRedirect.aspx?tf:type=ReportList&tf:Test=1&tf:clearcache=1

Note

For more information, see “Using TfsRedirect to Display Reports in TFS 2010 Dashboards” at http://aka.ms/TfsRedirect.

Summary

In this chapter, you learned about the compelling new Work Item Charting features that were introduced in Team Foundation Server 2013. You also learned about the reporting features introduced in Team Foundation Server 2010, as well as the changes incorporated into the 2012 release. In addition, you learned about the various data stores in the system and how data flows between them.

This chapter covered the two main reporting technologies (Reporting Services and Excel Services), along with the tools to create and customize the reports. This chapter also described how to quickly and easily create a report from a simple Work Item Query. Finally, this chapter looked briefly at some advanced customization topics.

Chapter 16 takes a look at how you can integrate Team Foundation Server with Microsoft Project Server and receive up-to-date project status and resource availability reports across multiple teams.