SQL Server Analysis Services Design Decisions in Team System

  • Article

When I saw this post [VSTS and SSAS 2005: another example of bad design?], I passed the link to the PM that’s working on this for Team System, Tom Patton. He put together this response:

The cube structure for RTM has been re-designed to address many of these concerns. For RTM, we are using a single cube and leveraging the new features of Analysis Services including:

  • Multiple Measure Groups: The new cube structure contains 15 measure groups from across all components. Common dimensions of Time, Person, Area, Iteration, Project, and Build can be used to correlate facts from all of the measure groups.
  • Many-to-Many Relationships: Elements that cross boundaries and have m:n relationships are modeled using the new many-to-many relationships in AS2K5. For instance, work item relationships, work item relationships to tests, builds related to changesets, work items related to check-ins.
  • Perspectives (SQL Server 2005 Enterprise Edition Only) : When using the SQL Enterprise Edition server, each fact area has a perspective associated with it that allows the ad-hoc user to see a limited set of the metadata. We have perspectives for Testing, Build, Code Churn, Work Items, Work Item History, Related Work Items, and Test/Work Item relationships.
  • Translations (SQL Server 2005 Enterprise Edition Only) : When using SQL Enterprise Edition, we localize the names of cube metadata to provide friendlier, localized experience for ad-hoc browsers and query designers.
  • Report Builder Model: We set elements in the cube definition to allow the cube to be used with Report Builder, and update the report builder model as the schema changes. This is the OLAP model only.
  • Incremental Processing (SQL Server 2005 Enterprise Edition Only) : We are now using the parallel incremental processing technique for SQL Enterprise Edition.
  • Reworked Date Dimensions: Date dimensions have friendly names, multiple hierarchies, including Week. 

These enhancements more fully exploit the new capabilities of Analysis Services, and lay a better foundation for getting information correlated across the various components of Team System. I hope these changes address your concerns.