SSWUG Business Intelligence Virtual Conference

As readers of this blog know, I've been actively participating in the SQL Server Worldwide Users Group (SSWUG) virtual conferences on SQL Server. These conferences are proving to be a very nice way for people who wouldn't normally attend technical conferences to be able to get some pretty good education. SSWUG is extending their conferences for the fall, and will be holding a Business Intelligence conference in September (September 24-26). My friend and colleague Matthew Roche will be chairing the BI conference for SSWUG, and has assembled a pretty impressive list of speakers (How I got on this list I'll never know) for this event.

Matthew has also posted some interesting information about the conference here: http://bi-polar23.blogspot.com/2008/07/check-out-this-lineup.html

Clarification on Components needed for Reporting in TFS

It was brought to my attention that I left one pretty significant detail out of my previous post. You need to make sure that you have the appropriate licenses in place for SQL Server when you install the Management Studio Express Edition tools.

I certainly don't have enough knowledge around SQL Server licensing to be able to tell you what is right or what isn't, so just make sure you speak to whomever in your organization controls your product licenses to ensure that you are legal.

More information on SQL Server licensing can be found here: http://www.microsoft.com/sql/howtobuy/licensing.mspx

Customized VSTS/TFS Reporting Part I - Configuring the Development Environment

As I mentioned in my previous post, I've decided to spend a bit of time here discussing custom reporting for VSTS and TFS. (Please feel free to leave me a comment here if there's anything in particular that you'd like to see discussed)

This post will primarily cover the basics of what you need and how to set it all up. Keep in mind that there are many different paths to get to where you need to be to develop reports, but I am going to detail what I would call the "Happy Path" (in my opinion, the easiest way to get to where you need to be). There's a ton of information available out there on MSDN and other places that discuss this, but I'm not sure there's any one concise listing of what you really need.

So, let's get started. The first thing we need to do is discuss the components that we'll need. Since we will be working with both relational (SQL Engine) and OLAP (Analysis Services) databases, we'll need to ensure that we have client tools for both. Of course the easiest way to get these is to install a local copy of SQL Server, but we'll stick with the more common client side components. I am also going to assume that you have Visual Studio Team System available to you. In my case, I'll use Visual Studio 2005, since we'll be talking to an instance of SQL Server 2005 for the purposes of this discussion. So, with those disclaimers out of the way, here's a list of the components you need:

• SQL Server 2005 Client Tools - When you don't have a local copy of SQL Server installed, the easiest way to get these is through the SQL Server 2005 feature Pack, which can be downloaded here: http://www.microsoft.com/downloads/details.aspx?FamilyID=50B97994-8453-4998-8226-FA42EC403D17&displaylang=en (You'll need a few components from this list)
• SQL Server Native Client - Allows you to communicate with SQL Server Relational Databases
• SQL Server 2005 Analysis Services 9.0 OLEDB Provider - Allows you to communicate with SQL Server OLAP cubes
• Core XML Services (MSXML) 6.0 - Adds interfaces for various XML activities (Behind the scenes everything you do in SSRS will be XML)
• Business Intelligence Development Studio - The easiest way to get this is through the SQLExpress Toolkit, which can be downloaded here: http://www.microsoft.com/downloads/details.aspx?FamilyID=e8ad606a-0960-4efd-8bd7-b21370c7be2b&DisplayLang=en Even though BIDS is just a slimmed down version of Visual Studio (It's actual SKU is "Visual Studio Premium Partner Edition") you still need to install this, because you need the designers (Project templates) that are included.

One thing to note here, if you do not have a local instance of SQL Server, you may want to go ahead and download/install the Management Studio Express Edition that is packaged with the SQLExpress Toolkit.

• SQL Server Books Online - You don't *really* need this, but I think you'll be happier if you have the documentation handy while  you're working on this. BOL can be downloaded here: http://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00df-4220-b133-29c1e0b6585f&DisplayLang=en 

Another disclaimer here. The links I'm posting are always subject to change without notice, and more importantly, the components themselves tend to go through revisions on a regular basis. It might be worth your while to search download.microsoft.com for the items in question before simply relying on my links.

Ok, so once we have the components downloaded and available, you'll want to install things in the following order (I'm assuming that you already have Visual Studio loaded on your system.. By the way, you can accomplish everything I am doing in this series of posts without the full blown Visual Studio, but I imagine that doesn't really apply here since we are talking about Visual Studio Team System customized reporting):

1. Core XML Services 6.0
2. SQL Native Client (Install the appropriate version for your platform, x86 or x64)
3. SQL 2005 Analysis Services 9.0 OLEDB Provider
4. Business Intelligence Development Studio (BIDS)
5. SQL Server Management Studio (SSMS)

Once you get everything installed, you'll know that you have success when you start Visual Studio and see the additional "Business Intelligence" project types as shown here:

Another way you can make sure you have the correct connectivity is to start SQL Server Management Studio and connect both the database engine and Analysis Services to the machine containing your TFS Database as shown below:

Once we have connectivity verified, we can get down to the business of creating a simple report.

I'll detail that in the next article in this series. Again, please feel free to post a comment here if there's anything in particular that you'd like to see discussed.

VSTS, TFS and Custom Reporting

One topic of conversation that seems to come up on a regular basis both around the office and around the 'net is how to make "better" the reports that ship with Visual Studio Team System and Team Foundation Server. I always take pause when I hear this, because I've never really considered it the job of the software manufacturer to provide reports for every conceivable use-case, and when you're talking about a product as vast and all-encompassing as VSTS/TFS is, I can't even imagine how many reports that would take. Then I stop and wonder if by "better" people really mean, "show the data that I want to see".

Team Foundation Server ships with a very well thought out (at least in my opinion) reporting infrastructure;  a data warehouse and an OLAP cube. These databases are designed with the idea that customers will use the data contained therein to create custom reports that represent the data elements they want to see (Rumor has it that the next VSTS release, "Rosario" will have even more to offer here, but I wouldn't know anything about that). The schema for both is simple enough that you don't need to be a TSQL or MDX genius in order to figure out how to develop compelling reports. For example, here's a subset of the WorkItem history schema in the TFS Warehouse database (this picture stolen from MSDN Patterns and Practices team by the way):

 

The logical representation above is implemented in a series of dimension tables and a fact table. The fact table schema looks like this:

As you can see, you can look at this schema and pretty easily tell how you can slice and dice data here to generate customized reports.

Given all this good information, I thought it would be a good idea to write a series of mini-articles on how to develop customized reports in VSTS/TFS. Over the next week I'll post the following here on this blog:

1. Getting started with VSTS/TFS customized reporting - What you need and how to configure the report development environment.
2. Creating a simple report using Visual Studio - How to use the wizard to create simple reports from both the relational and OLAP databases.
3. Deep dive into the TFS Warehouse schema - What data is available and what you can do with it
4. Deep dive into the TFS Warehouse OLAP cube - What data does the cube provide and how the built-in metrics help you.
5. Putting it together - developing specific reports to answer specific business requirements

If you do read this blog (and I know there's at least 1 of you out there - Hi Julian!) and would like to see something specific on this topic, please don't hesitate to let me know. (comment here so others can see)

Microsoft MVP Award

I received the following email this morning:

Dear Ted Malone,

Congratulations! We are pleased to present you with the 2008 Microsoft® MVP Award! The MVP Award is our way to say thank you for promoting the spirit of community and improving people’s lives and the industry’s success every day. We appreciate your extraordinary efforts in Team System technical communities during the past year....

So I guess this means I need to spend a bit more time actually earning the award.. :)

I have been privileged to work with some of the brightest and most interesting people you can imagine over the last 18 years, and many of them have been and are MVPs, so I am truly honored to be counted among their ranks today.

Check out my public MVP profile here, and while you're there, check out the rest of the crew....

SQL Server "Panic" Backup

One of the new features of SQL Server 2008 is it's integration with Powershell, and the ability to script just about any task.

Buck Woody has put together a pretty interesting "Panic Backup" script that will iterate through an XML file containing server and database names and back them all up. You can read about this here: http://blogs.msdn.com/buckwoody/archive/2008/06/30/sql-server-2008-improvements-practicals-powershell-run-a-panic-backup.aspx

Radio TFS

I don't think I've actually mentioned this resource in my blog before, but if you're into technical podcasts (for me, my Zune is usually playing Heavy Metal or some other really loud music, but hey, to each their own) there's a very good one on Team Foundation Server here at : http://www.radiotfs.com/

Radio TFS is the brainchild of Paul Hacker and Mickey Gousset. Their latest episode is about Team Test, which has some pretty interesting capabilities and features.

Check out the show here, and subscribe to the RSS feed here.