Steven Wright Blog

I have been working with a couple of colleagues, James Dawson and Russell Seymour , on installing and configuring PowerPivot within a SharePoint 2010 beta 2 farm. We used the following instructions to install PowerPivot on one of the application tier server: http://msdn.microsoft.com/en-us/library/ee210616(SQL.105).aspx . After following the instructions we were able to verify the installation as we had a SharePoint site with PowerPivot gallery.  We managed to uploaded and open a couple of Excel workbooks, with PowerPivot embedded, within the web browser.  Also we were able to connect to the PowerPivot service and could see the PowerPivot data files loaded. When I tested the slicers I got greeted with the following error: We did some research and found the following blog post: http://powerpivotgeek.com/2009/12/11/excel-services-delegation/ .  I implemented the changes it recommended to our Excel workbooks and tried again this time getting the following error: ...

I recently had to investigated a client replication topology.  I needed to understand the publications, subscribers and the articles. The problems I was facing were as follows: The replication topology was only defined on the production infrastructure. There was no documentation about the replication topology. I had no way of being able re-create replication topology. Only short amount of time. I was able to script the replication topology from the SQL Server Management Studio.  This was partially helpful as I now had a script which could be used to review the replication topology.  However this end up being a fairly large file and would be time consuming to review all information.  So I deicide to write a PowerShell, SQL Server Powershell for the Invoke-Sqlcmd commandlet, that would do the following steps: Parse the file to find the lines containing the following store procedures: sp_addarticle. sp_addsubscription. ...

The Scrum for Team System process template includes reports to help manage the progress of project.  The onus is on the team to run - by opening them - and review them.  This is the default behaviour of the Team Foundation Server which our process template built against, however as the Team Foundation Server platform uses SQL Server Reporting Services for its reporting software, the reports can actually be pushed out to the teams via e-mail.  I have mentioned how to do this for version 2.x, and for version 1.x the same steps can be applied, with the following post:  How to E-mail the Sprint Burndown Chart to yourself or your team. For version 3.0 (For which beta 2 is now available for download from the following link: http://scrumforteamsystem.com/cs/forums/4554/ShowPost.aspx ) things are a little different. Firstly, if not already configured, the Reporting Services server will need to will need to be set up to point to an SMTP server. This can be done by usi...

In October 2009 I created 2 codeplex projects which I would to tell you about: SQL Server Reporting Services MSBuild Tasks (ssrsmsbuildtasks) A few years ago I created some tasks for MSBuild to help deploy reports for my project, details can be found here ,  since then my tasks have been re-used in a few other projects within my company.  However lately we have been doing a few projects which have made use of Reporting Services integrated mode with SharePoint, which meant that my tasks were unusable as they only work with a native mode report server. So I have been updating them to include support for an integrated mode report server.  Also before I added the integrated mode support I took some time to rewrite my original native mode tasks as well.  The re-writing of the tasks includes some improved understanding of MSBuild by making more use of Item Groups and metadata for setting of report server properties of the report items being deployed.  I plac...

Within any application lifetime management methodology, the reports are a useful tool to help see how well , or not , the project is progressing.  Within the Scrum methodology the reports which are used for tracking progress are the burndown reports.  In the Scrum for Team Systems process templates for Team Foundation Server these reports are available to help with managing the project.  With a manual run burndown report when an unexpected trend in the burndown happens, there is an option to manually add an annotation.  This would help during a retrospective when talking about why the trend oddity occurred.  Within our previous versions of the Scrum for Team System templates our burndown reports would only show the end of each days aggregated totals.  Any unexpected totals would have to investigated to understand the underlying cause.  Previously this would have involved either using Team Foundation Explorer to review the history and comments; Using ...

I have been working with Microsoft SQL Server Reporting Services, since its first release, within many of my projects.  One of the biggest tasks, that I find with writing a report is the testing the reports and making sure the data that is displayed is correct.  My method of developing and testing reports is as follows: Write the queries, outside Reporting Services, for creating the report data set(s) with the parameters defined which will be configured within the report. Define the parameters that report data set would create and assign a valid value. Execute the queries. Inspect the results to make sure that they meet the expected results. Repeat with a new valid value or fix any issues. After one or two or even three passes that have worked then I take the queries and put them into report data sets. Then I define the report layout. Then I preview the report make sure it’s still correct Then I release to our te...

As mentioned in my previous blog post, there are some new reports available in the Scrum for Team System V3 Beta 1 Template. I would like to give a high level overview of the new reports as there will be full report guidance available with the template explaining in more detail how to use the reports. The new Diagnostic report isn’t really a new report, more of a spin off from another report. Version 2.x included a Version report that provided details about the status of the Team Foundation Server warehouse process. This covered information like: When the process last ran When the process finished loading data into the warehouse How often the warehouse process would run When the last time the cube was changed Team Foundation Server 2010 provides more information about the Team Foundation Server warehouse process, including: Is the warehouse blocked from running Is the OLAP processing blocked from running The last full process started and ended time The last incremental pro...

For the last few months I, with the help of fellow work colleague Kate Begley , have been working on the writing the reports for the Scrum for Team System V3 process template, which Beta 1 has been released and details are available from http://blogs.conchango.com/sfts/archive/2009/07/28/scrum-for-team-system-v3-beta-programme.aspx . There are a number of changes, from a reporting aspect, that have been introduced into Team Foundation Server 2010 platform and the Scrum for Team System V3 process template like: Warehouse schema changes; only supporting Reporting Services 2008; a sophisticated multi-team support model and new QA model. As a result the reports have been completely rewritten so there is a limited number in this beta release. However I would like to highlight some of the smaller updates that been applied to the reports to help readability / usability of the reports which are as follows: A reporting site folder structure has been created to make browsing the report...

For the Scrum for Team System project I have been focusing on the reports; I came across an issue which was due to the way the Reporting Services 2008 "automatic upgrade" feature upgraded our reports. The Scrum for Team System template contains over 20 reports all written for Reporting Services 2005. This was done because Team Foundation Server 2008 only worked on SQL Server 2005 and Reporting Services 2005. Then in August of 2008 Team Foundation Server 2008 Services Pack 1 was released which added support for the Team Foundation Server 2008 is to run on SQL Server 2008 platform. With Reporting Services 2008 there is a new reporting definition language schema. However reporting services does have backward compatibility method as describe below: The report server will validate report definition file against the included reference to the RDL namespace which specifies the version of the report definition schema that is used. The report is automatically upgraded the first...

I have just finished a project were I made a lot of use of the SQL Spatial to do some processing and loading into the database. Here are some of lesson I learnt: The first method that needs to call after instantiating a SQLGeographybuilder object is: SetSrid() then the BeginGeography(), BeginPoint(). Before using sending a SQLGeometry object to the SQL Server use the IsVaild() function to ensure that Geometry object is valid. I create a console application which loaded line, which was using OS coordinates system, data from a flat file. Some of the line data within the file wasn’t as correct as I was led to believe. The application create the SQLGeometry object .NET but the Sql Server then rasied the error when it received and try to save it into the table. The STPointN() function is 1 base which is stated in the Books On-Line If your query needs to select some of the derive data from the functions you can help performance by using persisted compute columns on t...