Steven Wright Blog

As I have mentioned in previous posts, I've been working on a data warehouse project. Within the project we decided to use SQL Server 2008 R2 Master Data Services (MDS) to store all the warehouse specific reference data. Here are some reasons why MDS was used: Allowed us to manage the reference data. Reference data could be loaded into the warehouse like a source system Would allow the client’s data governance team to easily update the reference data and keep the warehouse up-to-date For active development of entities and data loads we use a sandpit instance. When the ETL was ready to use the new entities or loaded data, a cut of the sandpit instance would be promoted to the development environment. We came across a problem when we needed to identify some changes which were accidentally made on the development instance. I came up with a method which helped to identify the changes fairly easily, below is the method I used. I will say that it’s not a perfect solution and migh...

Introduction I have, as mentioned in a previous blog , been working on a data warehouse project using CDC for extracting the source system data. I would like to share some of the experiences and evaluation criteria used for selecting a CDC tool for our project. The reason for using a specialist tool was as follows: Couldn't move all the source databases to SQL Server 2008 Needed the capture change data to be sent to another server instance A consistent management experience for CDC between all our source systems. The two database vendors the tool needed to support was SQL Server and IBM AS400 Timescales First we underestimated how long it would take to select a CDC tool. We plan for 1 month, for both evaluation criteria and running the tests, and it took us about 2/3 months. The time was taken in evaluating the tools against the criteria and gaining access to database systems from other projects. However our development of the ETL solution was able to continue, while t...

Introduction I have been working on a data warehouse project with a difference. The difference is that the ETL is not doing the classic extract of source system data, instead the source system is going to send its data to the data warehouse by using change data capture (CDC). The decision for using CDC was as follows: To have the ETL only process the data that has changed within the source system between each run of the ETL. This would help the ETL perform as it would only have to process the change data and not work out what the changes were first. Not to have the ETL processes impact the central source system database. The CDC would be responsible for delivering the changes of the source system to another database server. Then the ETL could be run at any time within the day and wouldn't be responsible for blocking the transactional source system. As there was plans to have the ETL run at the end of the day for two time zones e.g. US and UK. I would like to share some o...

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...