Tuesday, March 16, 2010

This blog has moved

This blog is now located at http://blogs.socha.com/. You will be automatically redirected in 30 seconds, or you may click here. For feed subscribers, please update your feed subscriptions to http://blogs.socha.com/feeds/posts/default.

Tuesday, March 02, 2010

Upgrading Team Foundation Server 2008 Reports to 2010, Part III

This is likely to be my final post on upgrading reports for Team Foundation Server 2010, and reflects one last change we made to the cube schema from the Release Candidate to the final version. This last change was to improve the performance of cube processing, and in particular, processing the Test Result dimension. We were able to significantly improve performance by removing the Area and Iteration Hierarchy attributes from the Test Result dimension.

So if you have any reports that use these hierarchies in the Test Result dimension, you’ll need to update your reports.

Sunder Raman has provided a higher-level overview of this change:
http://blogs.msdn.com/sunder/archive/2010/03/03/team-foundation-server-2010-cube-schema-changes-in-rtm.aspx

Area Hierarchy

You’ll want to use the Area Hierarchy from the Test Case dimension. The idea is that the test case defines the area that is being tested, so this is the appropriate test result to use. Your other option would be to use the Area Hierarchy from the Test Plan dimension. However, we don’t recommend this because test plans can and often do cover multiple areas.

One side effect of this change is that you’ll now see the area for some test results listed as Unknown. This means they don’t have any area, which will be the case when you publish unit test results that aren’t connected to a Test Case. Previously these test results appeared in the root area path for your Team Project, so it’s a minor change. But now you can at least tell which test results were explicitly connected to an area as a result of being run via a Test Case.

Iteration Hierarchy

You’ll want to use the Iteration Hierarchy from the Test Plan dimension. Test Plans are usually tied to a specific iteration. In Agile development, each new iteration introduces new functionality, and therefore a new test plan is required. Test Cases, on the other hand, don’t really live in any one iteration, so it wouldn’t make sense to use the iteration from the test case.

Thursday, October 22, 2009

Customizing Report Parameters for Team Foundation Server 2010

Very often you’ll want to change the default settings for parameters in a report, but Reporting Services doesn’t have a user interface that allows you to select parameters, and then save them for use again. There is a way to customize the set of parameter values without learning how to author reports, but it’s a little involved. In this post, I’ll cover the following:

  • Changing the default values that are used to render a report
  • Creating multiple views on the same report, each with different default parameters

About Report Parameters

First a little bit of background on report parameters. Many of the TFS 2010 reports read from the Analysis Services database (or cube) that, by default, is called Tfs_Analysis (it was called TfsWarehouse in previous releases). Since this is a cube, and not a relational warehouse, default parameter values will look a little odd. As an example, this is what the default value might look like when you want to select “Iteration 1” in the iteration parameter:

[Work Item].[Iteration Hierarchy].[Iteration1].&[10]&[11]

Yikes, you say, how are you supposed to figure that out?

I’m not going to get into the details of why this is so complicated. If you’re interested, I wrote an MSDN article for TFS 2008 that will give you some idea about how this works, although the details have changed since then.

But there is a tool you can use to get this information: SQL Server Management Studio, which you can install from the SQL Server DVD. Alternatively, you can use a free program called MDX Studio, which is what I’ll use here as an example.

Finding the Default Value

The first step is to launch MDX Studio and connect to the TFS cube. You might need to find out the server and database from your TFS administrator, and also have them grant you read access (they need to add you to the TfsWarehouseDataReader role).

Finding an Iteration Value

  1. Launch MDX Studio, type the name of the Analysis Services machine into the Connect to Analysis Services dialog box, and then click on the Connect button. You should see a display something like this:
    MDXStudio
  2. In the Metadata section, scroll down so you can see Work Item and expand that item.
  3. Expand the Work Item.Iteration Hierarchy node.
  4. Expand the (All) and then All nodes, and then the collection node that contains your team project.
  5. Expand the node that contains your team project. You should see something like this:
    Iteration1
  6. Finally, drag the iteration node (Iteration 1 in this example) into the Query area:
    IterationMDX

You can now copy this “ID” and use it in the following steps.

Setting Parameter Defaults

You’ll be using Report Manager to make these changes:

  1. Right click on the Reports node of your team project inside Visual Studio and select Show Report Site…:
    ShowReportSite 
  2. Click on the Properties tab at the top of the report:
     PropertiesButton
  3. Click on the Parameters link:
    ParametersLink

At this point you’ll see a list of parameters, which should look something like this:

Parameters

To change the default iteration, for example, you would paste the value you got from the previous step into the text box circled in red above, and then click on the Apply button.

Multiple Parameter Settings – Linked Reports

There are times where you’ll want to have several versions of the report, each with different parameter values. Reporting Services has a feature called Linked Reports that allows you to essentially create sets of parameters that will be applied to a single copy of the report, but appear as a new report.

Here is how you create a linked report:

  1. Click on the Properties tab for the report (just like in the previous section)
  2. Click on the Create Linked Report button on the Properties page
  3. Type in the name you want to use for this new linked report, and then click on the OK button
  4. Click on the Properties tab of this new linked report
  5. Click on the Parameters link on the left side to customize the parameter values that will be used for the linked report, and click on the Apply button to accept the new default values

Thursday, October 15, 2009

Upgrading Team Foundation Server 2008 Reports to 2010, Part II

My previous post, Upgrading Team Foundation Server 2008 Reports to 2010, provides a lot of information on how to upgrade reports to work with the new 2010 version of Team Foundation Server, and even though I wrote it for Beta 1, much of the information still stands. There are, however, changes that have been made since then, so in this post I’ll provide updated information that applies to Beta 2 (and hopefully the final version of 2010).

Cube Changes

There were a few breaking changes to the cube that affected our reports, which are described in the sections below. You can find more details on cube changes in Sunder’s blog post: Team Foundation Server 2010 - Relational Warehouse and Cube Schema Changes.

Measure Changes

In previous versions of the cube, there were two measure groups for work items: Work Item History and Current Work Item. Here is one example of a measure pair from those two groups:

[Measures].[Cumulative Count]
[Measures].[Current Work Item Count]
Having two measures was always a little confusing. Starting in Beta 2, there is just one measure and one measure group. The measure group is now called Work Item, and there is a single measure that is used for both current and historical:

[Measures].[Work Item Count]

Now this measure will show historical information if you have the Date dimension included in your query, otherwise it will show current information. Much simpler. The other work item measures also follow this new pattern, with a single rather than a pair of measures for each of the work item measures.

In general, any measure that had Cumulative at the start before, just drop the Cumulative. Likewise, if it had Current at the start of the name, just drop Current. The Count measure above is, of course, an exception. Here is another example of before and after:

Before: [Measures].[Cumulative Completed Work]
After:  [Measures].[Completed Work]

That being said, there are some cases where measures with Cumulative at the start now have Trend at the end of the name. For example, [Measures].[Result Count Trend]. In general, your best bet is to look at the cube and see what the measures are called now.

Area/Iteration Hierarchies

As I mentioned in the previous post, 2010 introduces team project collections (TPCs), which is a grouping of a set of team projects. Although TPCs were supported in beta 1, the TPC was not included in the area and iteration hierarchies. So if you had two projects with the same name, but in different TPCs, you couldn’t tell which areas/iterations belonged to a specific project.

To fix this, we added the TPC name as part of the area/iteration hierarchy between the root and the top-level node for the team project. As a result, we had to change all the queries that we use to get the area and iteration parameters.

In this process of doing this, however, we also realized we could switch to a single query for each parameter instead of the two queries we had before. These new queries look a lot more complicated, but they’re actually not that bad. A bit of of explanation is in order because we also change the default value that we’re using so we can hard-code it rather than using a query. Doing this allowed us to provide a better experience when the cube doesn’t contain any areas or iterations for the team project, which will happen, for example, with a new team project that has no work items.

Here are the new default values:

Parameter

Default Value

AreaParam

[Work Item].[Area Hierarchy].[All]

IterationParam

[Work Item].[Iteration Hierarchy].[All]

 
What we’re trying to do is return query results that look something like this:

ParameterCaption

ParameterValue

All (No Filter)

[Work Item].[Area Hierarchy].[All]

  (ProcessTest)

[Work Item].[Area Hierarchy].&[67]&[67]

  Iteration 1 [Work Item].[Area Hierarchy].&[67]&[78]

 
So how do we get this? Using a few tricks:

  • The All member is always returned. We use a Union function to ensure that we always return this member.
  • The NonEmpty/CrossJoin near the end of this query returns all the members at the Area1 level and below. Area0 is the level of the team project, and there is a level above this for the TPC. By going directly to Area1 and all children of that level, we’re getting all the children of the project. We also cross join with the team project to ensure we only get areas for the current team project.
  • The reason for the Except/Extract function is much less obvious, but it has to do with the outer Descendants function. Basically, we want to get all the first children of the project, and then all children of these members. In order for this to work when there are no matches (in which case the NonEmpty returns no members), we Union the results with the All member, but then we need to remove that member and also return only members in the Work Item dimension. That is what the combination of Extract and then Except accomplishes.
dsArea
WITH
    MEMBER [Measures].[Add Spaces] AS
        VBA.Space(([Work Item].[Area Hierarchy].CurrentMember.Level.Ordinal - 1) * 2)
    MEMBER [Measures].[ParameterCaption] AS
        IIF([Work Item].[Area Hierarchy].CurrentMember
               IS [Work Item].[Area Hierarchy].[All],
            @AllNoFilter,
            [Measures].[Add Spaces]
                + [Work Item].[Area Hierarchy].CurrentMember.Member_Caption
        )
    MEMBER [Measures].[ParameterValue]
        AS [Work Item].[Area Hierarchy].CurrentMember.UniqueName
    MEMBER [Measures].[ParameterLevel]
        AS [Work Item].[Area Hierarchy].CurrentMember.Level.Ordinal
SELECT
{
    [Measures].[ParameterCaption],
    [Measures].[ParameterValue],
    [Measures].[ParameterLevel]
} ON COLUMNS,
{
    Union(
        [Work Item].[Area Hierarchy].[All],
        Descendants(
            //
            // We use Except to remove the extra [All] member so we get the descendants
            // only of the team project.
            //
            Except(
                Extract(
                    Union(
                        //
                        // We add [All] so Extract always has at least one tuple to
                        // work with. Otherwise it produces an error when there are no
                        // tuples returned by the NonEmpty below.
                        //
                        CrossJoin(
                            [Team Project].[Project Node GUID].[All],
                            [Work Item].[Area Hierarchy].[All]
                        ),
                        NonEmpty(
                            CrossJoin(
                                StrToMember("[Team Project].[Project Node GUID].&[{"
                                    + @ProjectGuid + "}]"),
                                [Work Item].[Area Hierarchy].[Area1]
                            ),
                            [Measures].[Work Item Count]
                        )
                    ),
                    [Work Item].[Area Hierarchy]
                ),
                [Work Item].[Area Hierarchy].[All]
            )
        )
    )
} ON ROWS
FROM [Team System]
dsIteration
WITH
    MEMBER [Measures].[Add Spaces] AS
        VBA.Space(([Work Item].[Iteration Hierarchy].CurrentMember.Level.Ordinal - 1) * 2)
    MEMBER [Measures].[ParameterCaption] AS
        IIF([Work Item].[Iteration Hierarchy].CurrentMember
                IS [Work Item].[Iteration Hierarchy].[All],
            @AllNoFilter,
            [Measures].[Add Spaces]
                + [Work Item].[Iteration Hierarchy].CurrentMember.Member_Caption
        )
    MEMBER [Measures].[ParameterValue]
        AS [Work Item].[Iteration Hierarchy].CurrentMember.UniqueName
    MEMBER [Measures].[ParameterLevel]
        AS [Work Item].[Iteration Hierarchy].CurrentMember.Level.Ordinal
SELECT
{
    [Measures].[ParameterCaption],
    [Measures].[ParameterValue],
    [Measures].[ParameterLevel]
} ON COLUMNS,
{
    Union(
        [Work Item].[Iteration Hierarchy].[All],
        Descendants(
            //
            // We use Except to remove the extra [All] member so we get the descendants
            // only of the team project.
            //
            Except(
                Extract(
                    Union(
                        //
                        // We add [All] so Extract always has at least one tuple to
                        // work with. Otherwise it produces an error when there are no
                        // tuples returned by the NonEmpty below.
                        //
                        CrossJoin(
                            [Team Project].[Project Node GUID].[All],
                            [Work Item].[Iteration Hierarchy].[All]
                        ),
                        NonEmpty(
                            CrossJoin(
                                StrToMember("[Team Project].[Project Node GUID].&[{"
                                    + @ProjectGuid + "}]"),
                                [Work Item].[Iteration Hierarchy].[Iteration1]
                            ),
                            [Measures].[Work Item Count]
                        )
                    ),
                    [Work Item].[Iteration Hierarchy]
                ),
                [Work Item].[Iteration Hierarchy].[All]
            )
        )
    )
} ON ROWS
FROM [Team System]

Handling the Turkish “I”

It turns out that there are several different “i” letters in Turkish, and this creates problems with one of the queries that we had in Beta 1. You’ll need to change dsProjectGuid to use all caps for the GUID in column name:

SELECT ProjectNodeGUID FROM GetProjectNodeInfoFromReportFolder(@ReportPath)

This is to match the case of the actual column name as defined in the warehouse.

Monday, May 18, 2009

Upgrading Team Foundation Server 2008 Reports to 2010, Part I

There have been some major changes in the reporting infrastructure for the forthcoming 2010 release of Team Foundation Server. You can find a blog entry on these changes here, written by the Program Manager on the team that made these changes:

In this blog post I’m going to dive deep into the details on how to modify reports to work with the new schema. But first, some definitions and background.

Update: Although I originally wrote this post for Beta 1, most of the information still applies. Some things have changed, and you’ll find the details here: Upgrading Team Foundation Server 2008 Reports to 2010, Part II

Definitions

Term

Description

Dimension

The dimensions in a cube are at the heart of what makes a cube useful for slicing data in many ways. Data values are associated with a set of dimensions, allowing you to show aggregate results sliced using a specific set of dimension values.

Fact

Data that can be associated with multiple dimensions. This data may also be aggregated. Fact tables hold these values.

Attribute

Under each dimension you’ll find a set of attributes and possibly hierarchies (area and iteration are a hierarchies). Each attribute is connected to a column in the corresponding dimension table in the warehouse.

Measure

Measures are values that correspond to columns in the corresponding fact table.


StarDiagram

SQL Server 2008

The new version of Team Foundation Server requires SQL Server 2008, which brings a set of changes with it. I wrote a previous blog post on changes that might affect your existing queries:

One significant change is the tools you’ll be using. You’ll need to use the 2008 version of Report Designer, which has a number of UI changes that take a little getting used to. In particular, the entire experience with working with queries has changed dramatically. The 2005 version of Report Designer used three tabs for Data, Layout, and Preview:

Bids2005Tabs

The Data tab is now gone in the 2008 Report Designer, and has been replaced with the Report Data docking window:

Bids2008ReportData

You use this new window to edit and create data sources and report parameters. Editing of data sets is now done in a dialog box that you get to by double-clicking on a dataset, such as dsBugCount in the image above.

Many of the reports shipped with Team Foundation Server use hand-written MDX for their queries (the other reports use hand-written SQL). As such, if you need to change a query, you’ll be editing the actual query code. Double-clicking on a data set will produce the following dialog:

DatasetProperties

This dialog box is the new location for working with query parameters, fields, etc. To edit the query, click on the Query Designer button.

QueryDesigner

There is a limitation of this query designer that you’ll likely notice right away—whereas MDX in the 2005 Report Designer was formatted with syntax coloring, the new Query Designer has no formatting for queries, making it difficult to edit the queries.

I’ve found the easiest way for me to edit queries with syntax color support is to use this approach:

  • Copy the query from the Query section of the Dataset Properties window (which does have correct line breaks).
  • Paste the query into a session of SQL Server Management Studio.
  • Copy and paste the modified query into the Query Designer dialog box for testing and saving back to the report.

Updating Reports

Updating a Team Foundation Server 2008 report to work with the 2010 version requires making a number of changes. The details of these changes are described in the following sections.

MDX Changes

Here is a brief list for MDX reports against the cube:

  • Use the new data source names
  • Filter on Project GUID. This involves adding some new parameters and a new dataset
  • Update/replace queries use for area/iteration parameters
  • Replace WHERE clauses with sub-cube expressions
  • Rename dimensions/attributes whose names have changed

SQL Changes

If you’re working with the SQL warehouse, the changes are also significant. Here is a brief overview of the changes you’ll need to make:

  • Use the new data source names
  • Filter on Project GUID
  • Use the new public views when possible
  • Make sure your joins use unique keys. For example, work item IDs are no longer unique within the warehouse
  • Rename all table references because all table names have changed

Data Source Rename

You’ll find that the names of the Reporting Services data sources used by reports have changed:

2008 Name

2010 Name

TfsReportsDS

Tfs2010ReportsDS

TfsOlapReportsDS

Tfs2010OlapReportsDS

The new names exist to accommodate upgrading a server where you have an existing set of reports designed for Team Foundation Server 2005 or 2008. Installing the new version creates a new warehouse and cube that use a different, incompatible schema. The new data sources point to the new warehouse and cube, without overwriting the previous data sources.

ServerUpgrade

The old warehouse and cube are not removed or overwritten when you upgrade the server so that old reports will continue to work. However, no new data will be added to the pre-upgrade warehouse and cube. Using new data source names allows the old reports, which use the old data sources, to continue to render pre-upgrade data.

You’ll need to make sure reports that you upgrade use the new data source names so they’ll connect to the new cube.

Reports Location

The support for Team Project collections in Team Foundation Server 2010 required some changes to the default location of reports. There were two features driving this change:

  • There is now a hierarchy instead of a flat list. For example, you can have a Team Project collection that has some projects under it.
  • Team Project names are not server-unique as they were in 2008. A project name has to be unique only within a single collection. In other words, the same project name can exist in two different team project collections.

Because of these changes, reports have a different location on the Reporting Services server. The pattern looks like this:

/TfsReports/Collection0/MyProject

That also means you need to use a different mechanism to obtain the default project associated with a report. In previous version, you could get the default project name from the report’s path and just use that to filter query results.

But with team project collections and 2010, you need to use the project’s GUID instead of the name as a filter since the name by itself may not be unique.

In the warehouse you’ll find a stored procedure you can use to obtain the project’ GUID from the report path, using code like this:

  • Create a parameter called ReportPath and move it so it’s the second parameter, after ExplicitProject.
  • Add this expression as the default value for the ReportPath parameter:
    =IIF(LEN(Globals!ReportFolder) > 0,
        Globals!ReportFolder,
        Parameters!ExplicitProject.Value
    )
  • Create a SQL dataset called dsProjectGuid that has this query:
    SELECT ProjectNodeGuid FROM GetProjectNodeInfoFromReportFolder(@ReportPath)
  • Create a parameter called ProjectGuid just after the ReportPath parameter and set the default value to results of the dsProjectGuid dataset.

You will then need to modify your other queries to use the resulting GUID, which is covered below under Cube Improvements.

Warehouse Improvements

For the first time, writing reports against the warehouse using SQL is 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 of aggregate data. The cube is really good at this sort of work.

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

Views

There are now a number of public views to help you work with the warehouse. All of these public views have names that end with “View,” such as WorkItemHistoryView.

Views that begin with “v” and end with “Overlay” are used for processing the cube, and as such aren’t really meant for use in your reports.

Compensating Records

When you work with historical data in the warehouse, you’ll need to be aware of compensating records. You’ll find compensating records in tables/views that include historical information, such as WorkItemHistoryView. Whenever you modify a work item, a pair of records is added to the warehouse. One record “undoes” the most recent previous record, and the next record adds the new settings for that work item. The compensating records allow you use to aggregate functions like SUM in query results.

Also connected with compensating records are two columns: System_ChangedDate and System_RevisedDate. The first column, System_ChangedDate indicates when the change to the work item was made. The other date tells you when the work item was next changed. As an example, suppose that you created a new task at 10:53 on 5/15/2009, and then you modified that work item at 11:23 on the next day. You would then see records like this:

System_ChangedDate

System_RevisedDate

Remaining Work

RecordCount

10:53 5/15/2009

11:23 5/16/2009

20

1

10:53 5/15/2009

11:23 5/16/2009

-20

-1

11:23 5/16/2009

0:00 1/1/9999

10

1

The first record was the original record, and you can see that it was revised at 11:23. The next record “cancels out” the first record, and was added at the same time as the third record. Finally, the third record is the current record, as indicated by the System_RevisedDate set to the maximum value for a DATETIME column. As a result, you can write a query that will return the current remaining work correctly like this:

SELECT SUM(Microsoft_VSTS_Scheduling_RemainingWork)
  FROM WorkItemHistoryView WHERE System_Id = 108
   AND ProjectNodeGUID = 'A8657108-E085-4DE5-B14C-97DAA378D46E'
That being said, you’ll get better performance if you use an “As Of” query instead of using SUM.
As Of Queries

The query above returns the “current” results. But what if you want to obtain the results as of a specific day? Let’s say you want to find out the remaining work as of the end of 5/15/2009. Looking at the end of a given day will give you the same results as you’ll get from the cube. Here is what that query will return the remaining work as of the end of 5/16/2009:

SELECT System_Id, Microsoft_VSTS_Scheduling_RemainingWork
  FROM WorkItemHistoryView WHERE System_ChangedDate < '5/16/2009'
   AND System_RevisedDate >= '5/16/2009'
   AND RecordCount > 0
   AND ProjectNodeGUID = 'A8657108-E085-4DE5-B14C-97DAA378D46E'

Here’s how this works. We want to return only the last records for each work item that was modified before 5/16/2009. The System_RevisedDate clause ensures we retrieve only the last record that was modified before 5/16/2009 because it finds the one record that was both modified before and revised after this date (or not revised at all, in which case the date is in the year 9999).

Additionally, you’ll notice this query tests for a positive RecordCount. Compensating records that cancel out a previous record always have a RecordCount of -1.

Uniqueness of ID Values

One new feature of Team Foundation Server 2010 is the support for team project collections, which is a group of team projects. Entire team project collections can be moved from one server to another. As a result, the IDs of work items are unique only within a single team project collection, but they may not be unique on a server that contains records from more than one team project collection. That is why the query in the previous section includes a filter on the project GUID in addition to the work item ID.

Table Names

Most table names have been changed in Team Foundation Server 2010. In previous versions, many of the table names had space in them, which created problems with some reporting tools. In the new warehouse, names do not have spaces, and they also have a prefix to indicate their use:

Prefix

Description

Dim

The table contains dimension data, which means the data will appear in the dimensions part of the cube. There is usually one column for each attribute or hierarchy that belongs to the dimension.

Fact

The values of cells in the cube, which show up via measures. Fact tables have foreign keys to various dimension tables.


As an example, the FactWorkItemHistory table contains a column called StateChangeCount that appears in the cube under measures, in the Work Item History measure folder.

Additionally, the DimWorkItem table shows up in the dimension hierarchy under Work Item.

Renaming the tables this way makes it easier to see the connection between tables in the warehouse and their corresponding entities in the cube.

Cube Improvements

The team working on the warehouse and cube had several goals when they set out to change the cube design:

  • Make the cube more usable
  • Increase query performance
  • Improve translation and load performance

Previous versions of the cube had a lot of dimensions, a little over 60. The new cube has been restructured to use less than half the number of dimensions. Yet at the same time, there are more ways to slice than before. This “simplification” was achieved by changing what were dimensions in the old version to attributes on closely related dimensions in the new version. For example, the Activated Date dimension became the Activated Date attribute inside the Work Item dimension.

Additionally, because the number of attributes on a dimension can be quite high now, many of the attributes are grouped into attribute folders.

Area and Iteration

Area and iteration used to be dimensions. Now, however, you’ll find area and iteration hierarchies within other dimensions, such as Work Item and Test Result. You’ll need to use the appropriate hierarchy in your query. For example if you’re slicing on test results, you’ll need to use the area/iteration hierarchies within the Test Result dimension.

Because areas and iterations were changed to become attributes and the project name is no longer unique, the queries for the area and iteration parameters are very different from before.

New Parameter Queries

If your report has parameters for area and iteration, you’ll need to modify or replace your queries to use the new structure. In this section you’ll find queries we’ve created for area and iteration lists and default values.

The old structure used a parent/child hierarchy that allowed you to use the project name as a key, making it easy to get the list of available areas and iterations for a team project, as well as the default values.

In the new structure, team project names are not guaranteed to be unique, so using the project name as a key might give you results for more than one team project. Additionally, the hierarchy is now a natural hierarchy rather than a parent-child hierarchy, which means that you don’t have data members that represent a node without children. Instead, the new hierarchies have the equivalent built into the hierarchy so the queries don’t need to check for DATAMEMBER values and format them differently (by enclosing them in parenthesis).

The queries to get the default area/iteration return a single member based on the project GUID by looking only at the direct children of the root. Limiting the scope to just the children of the root keeps the query fast.

Update: The queries for area and iteration no longer work in Beta 2 or later. For more information, see Upgrading Team Foundation Server 2008 Reports to 2010, Part II

dsAreaDefault
WITH
    MEMBER [Measures].[ParameterCaption] AS
        [Work Item].[Area Hierarchy].CurrentMember.Member_Caption
    MEMBER [Measures].[ParameterValue] AS
        [Work Item].[Area Hierarchy].CurrentMember.UniqueName
SELECT
{
     [Measures].[ParameterCaption],
     [Measures].[ParameterValue]} ON COLUMNS,
{
    NonEmpty(
        CrossJoin(
            StrToMember("[Team Project].[Project Node GUID].&[{" + @ProjectGuid + "}]"),
            Descendants([Work Item].[Area Hierarchy].[All].Children, 0)
        ),
        [Measures].[Cumulative Count]
    )
} ON ROWS
FROM [Team System]
dsArea

This data set uses as input the results from the dsAreaDefault query so it will scan only areas in the current project. Limiting the scope to just the children of the default area significantly improves performance of this query.

WITH
    MEMBER [Measures].[Add Spaces] AS
        VBA.Space([Work Item].[Area Hierarchy].CurrentMember.Level.Ordinal * 2)
    MEMBER [Measures].[ParameterCaption] AS
        [Measures].[Add Spaces] + [Work Item].[Area Hierarchy].CurrentMember.Member_Caption
    MEMBER [Measures].[ParameterValue] AS
        [Work Item].[Area Hierarchy].CurrentMember.UniqueName
SELECT
{
     [Measures].[ParameterCaption],
     [Measures].[ParameterValue]} ON COLUMNS,
{
    Descendants(StrToSet(@AreaDefault))
} ON ROWS
FROM [Team System]
dsIterationDefault
WITH
    MEMBER [Measures].[ParameterCaption] AS
        [Work Item].[Iteration Hierarchy].CurrentMember.Member_Caption
MEMBER [Measures].[ParameterValue] AS [Work Item].[Iteration Hierarchy].CurrentMember.UniqueName SELECT { [Measures].[ParameterCaption], [Measures].[ParameterValue]} ON COLUMNS, { NonEmpty( CrossJoin( StrToMember("[Team Project].[Project Node GUID].&[{" + @ProjectGuid + "}]"), Descendants([Work Item].[Iteration Hierarchy].[All].Children, 0) ), [Measures].[Cumulative Count] ) } ON ROWS FROM [Team System]
dsIteration

This data set uses as input the results from the dsIterationDefault query.

WITH
    MEMBER [Measures].[ParameterCaption] AS
        [Work Item].[Iteration Hierarchy].CurrentMember.Member_Caption
    MEMBER [Measures].[ParameterValue] AS
        [Work Item].[Iteration Hierarchy].CurrentMember.UniqueName
SELECT
{
     [Measures].[ParameterCaption],
     [Measures].[ParameterValue]} ON COLUMNS,
{
    NonEmpty(
        CrossJoin(
            StrToMember("[Team Project].[Project Node GUID].&[{" + @ProjectGuid + "}]"),
            Descendants([Work Item].[Iteration Hierarchy].[All].Children, 0)
        ),
        [Measures].[Cumulative Count]
    )
} ON ROWS
FROM [Team System]

Other Modifications

Project Filter

You’ll need to modify all uses of the project name to use the GUID instead. The old version is on the top, and the new version is on the bottom, with the changes highlighted in yellow.

STRTOMEMBER("[Team Project].[Team Project].[" + @Project + "]"),
STRTOMEMBER("[Team Project].[Project Node GUID].[{" + @ProjectGuid + "}]"),

WHERE Clause

Many of the reports used the MDX WHERE clause to slice the data on team project, as well as other dimensions. However, this doesn’t always work with SQL Analysis Services 2008 because of changes in how queries are processed. Additionally, it’s not as fast as it could be because the slicing is done after the query body has been processed.

A much better approach is to replace the WHERE clause with a sub-cube expression because a sub-cube expression is applied before processing the rest of the query. Here is the before and after:

Before
FROM [Team System]
WHERE
(
STRTOMEMBER("[Team Project].[Project Node GUID].[{" + @ProjectGuid + "}]"),
STRTOSET(@IterationParam),
STRTOSET(@AreaParam),
StrToMember("[Work Item].[System_WorkItemType].[" + @Bug + "]")
)
Improved
FROM
(
SELECT
CrossJoin(
STRTOMEMBER("[Team Project].[Project Node GUID].[{" + @ProjectGuid + "}]"),
STRTOSET(@IterationParam),
STRTOSET(@AreaParam),
StrToMember("[Work Item].[System_WorkItemType].[" + @Bug + "]")
) ON COLUMNS
FROM [Team System]
)

Complement Changes

Some of the reports used the complement operator (“-“), which performs much the same functionality as the EXCEPT function in MDX. However, these queries used the complement operator inside a WHERE clause in such a way that it produces an error in SQL Analysis Services 2008:

,STRTOMEMBER("[Team Project].[Team Project].[" + @Project + "]")
,-{STRTOMEMBER("[Work Item].[System_State].[System_State].[+@Proposed+]")}

The problem with this statement is that there is no mention of the Work Item dimension elsewhere inside the WHERE clause. What you need instead is exclude a value from a list of values, like this:

EXCEPT(

Descendants([Work Item].[System_State].[System_State])
,STRTOMEMBER("[Work Item].[System_State].[System_State].[+@Proposed+]")
)

Tuesday, March 10, 2009

SharePoint Access Denied on SPWebApplication

I've been working on a SharePoint solution that needed to write properties to the current web application. Everything worked just fine on my development machine. But on some of our test machines, I found the following message in the SharePoint logs:

The SPPersistedObject, SPWebApplication Name=Default Web Site Parent=SPWebService, could not be updated because the current user is not a Farm Administrator.

System.Security.SecurityException: Access denied.

This turns out to be correct behavior, but it took a while to understand what was going on and how to setup my development machine to reproduce the problem.

In my code to set the web application property value, I was using RunWithElevatedPrivileges to ensure I had rights. However, what I didn't realize is that elevating privileges gives you full access to the content database, but not to the configuration database. SPWebApplication's property bag is stored in the configuration database, not the content database. That was one piece to the puzzle.

The other piece to the puzzle was how I had my development machine setup. I was using the same account on both the Central Administration application pool and the application pool used to run regular the web applications. So in essence, I was implicitly granting my code rights to write to the configuration database.

When I modified the application pools to use two different accounts, I was able to reproduce this problem on my development machine.

Here is a summary:

  • RunWithElevatedPrivileges only provides full access to the content database for the current web application. In other words, you can modify read-only lists, write SPWeb properties, etc. But you can't make changes to the SPWebApplication because it is persisted to the configuration database.
  • Always make sure your application pools for normal web applications are using a different account than the Central Administration application pool. This is recommended practice for production machines, but you should also setup your development and test machines this way to catch problems early.

Thursday, March 05, 2009

TFS Report Issues with SQL Server 2008

Some of the reports that shipped with Team Foundation Server 2005 and 2008 run into problems when you attempt to run them on SQL Server 2008 Reporting Services. Here is an overview of of the problems and fixes for those problems. Most of these problems are a result of changes in how Analysis Services processes queries in 2008 vs 2005.

Complement and Except Errors

Several of the TFS reports show one of these errors when running under SQL 2008:

The set must have a single hierarchy to be used with the complement operator.

Two sets specified in the EXCEPT function have different dimensionality.

The reports that are affected are Scenario Details and Unplanned Work.

What does this mean? Basically, it means that the WHERE clause in the query is using the "-" operator to exclude a specific attribute from the query. For example, the Scenario Details reports includes the following inside the WHERE clause:

,STRTOSET( @AreaParam )

,STRTOSET( @IterationParam )

,STRTOMEMBER("[Team Project].[Team Project].[" + @Project + "]")

,-{STRTOMEMBER("[Work Item].[System_State].[System_State].[+@Proposed+]")}

 

The minus sign in front of the last line indicates that we want to filter out work items that are in the Proposed state. However, this doesn't work in SQL 2008. You'll notice that we're trying to exclude an a specific value without mentioning the other values.

The solution is to remove the exclusion from the WHERE clause and put it into the body of the query:

,STRTOSET( @AreaParam )

,STRTOSET( @IterationParam )

,EXCEPT(

    Descendants([Work Item].[System_State].[System_State])

    ,STRTOMEMBER("[Work Item].[System_State].[System_State].[+@Proposed+]")

)

 

The key here is that we need to include all states except for the Proposed state, which is what the Except function above does.

WHERE vs Sub Cube

Many of the reports were written using the WHERE clause to filter the results. For example, so the results would be filtered on a single team project instead of the entire cube. However, the behavior of the WHERE clause changed somewhat in SQL Server Analysis Services 2008. The simplest solution is to rewrite the WHERE clause as a sub-cube expression instead. Not only will this fix some of the problems, but it should also make reports run faster.

Old Version:

FROM [Team System]

WHERE

(

  STRTOMEMBER("[Team Project].[Team Project].[" + @Project + "]"),

  STRTOMEMBER("[Work Item].[System_WorkItemType].[" + @Bug + "]")

)

New Version:

FROM

(

  SELECT

    CrossJoin(

      STRTOMEMBER("[Team Project].[Team Project].[" + @Project + "]"),

      STRTOMEMBER("[Work Item].[System_WorkItemType].[" + @Bug + "]")

    ) ON COLUMNS

  FROM [Team System]

)

 

The WHERE clause above is applied after the rest of the MDX expression has been evaluated. But the second expression defines a subset of the cube that will be used to evaluate the rest of the expression. In other words, using a sub cube, you're restricting the set of cells to a smaller set before evaluating the query.

Extract Problems

Here is the error message you might see:

The Extract function was called with hierarchy 'System_Id' that does not exist in the set.

That's certainly not a very helpful error message. This message appears in the report called Bugs Found Without Corresponding Tests. The solution, as it turns out, is to use a sub-cube instead of a WHERE clause (described above).

Tuple Problem

In several of the reports, selecting all of the iterations produces the following error message:

The function expects a tuple expression for the argument. A tuple set expression was used.

More than 4,294,967,296 tuples

This error occurs with the following reports:

  • Bugs by Priority
  • Bugs Found Without Corresponding Tests
  • Issues and Blocked Work Items
  • Related Work Items
  • Requirement Details
  • Requirements Test History and Overview
  • Scenario Details
  • Triage
  • Unplanned Work

The solution to this problem is also to replace the WHERE clause at the end of the MDX query with a sub-cube expression, as described above.

SQL Reports and the IN Clause

The Load Test Summary report is a SQL report, and it contains an IN clause in the WHERE statement. This is used to allow multiple values to be selected and passed to the query. However, a change in behavior in SQL 2008 Reporting Services means that you'll see the following message when you have no values:

Incorrect syntax near ')'.

Here is why you see this message. The WHERE clause in the Load Test Summary report looks like this:

WHERE (Result.__ID IN (@ResultParam))
AND ([Test Result].[Result Record Count] = 1)

When there are no results passed into this query, Reporting Services 2008 simply removes @ResultParam, so you get something like "IN ()", and you get a syntax error, preventing this query from running.

The solution is to add an expression to the @ResultParam query parameter. This expression is evaluated in order to determine what is passed to the query. Here is what works:

=IIF(Parameters!ResultParam.Count > 0, Parameters!ResultParam.Value, "")