Tuesday, July 31, 2007

Creating SQL 2005 Data Warehouse Metadata

One of my favorite tools to use when designing a data warehouse is the Dimensional Modeling Spreadsheet created by Joy Mundy and Warren Thornthwaite of the Kimball Group in conjunction with their must-have book, The Microsoft Data Warehouse Toolkit.  Not only does it allow you to fully build out your fact/dimension design, ETL logic, source-to-target mapping and more, it also generates a script that will create the first iteration of your data model once you have everything in place.  I always try to fully flesh out this spreadsheet before I create the first SQL table, it really helps get things off to a good start.  As an added bonus, Joy and Warren have a whole site full of useful tools and utilities for building a Data Warehouse.

Mark Garner has just released Beta 1 of a tool called Metashare.  He appears to have based it on the Kimball Group spreadsheet I mentioned above, and pulls the same data back out of your data model for the purposes of documentation.  I will look forward to checking out Metashare with the next DW that I build.

Microsoft SQL Server BI in Formula 1 - Case Study

I love F1 racing. It the perfect showcase for technology, and I posted a while ago that Microsoft had been selected, along with McLaren to provide BI for F1. It looks like the first case study is now available as of a week ago. Here is a white paper and a video for you to look over.

According to the white paper, each Formula One car will typically generate 1–2 gigabytes of data in a race for 100 different sensors on the car. Each team runs two cars, and there will be 17 races next season. As far as I am concerned, this is one of the coolest applications of BI that I have heard of yet. Additionally, this is all being analyzed in real-time. I hope that we get some more case studies, but I'm not counting on anything.

Monday, July 30, 2007

How to configure SQL Server Integration Services (SSIS) 2005 for Load Balancing

I should begin this post by mentioning that currently SQL Server 2005 Integration Services does not support Network Load Balancing (NLB) in the traditional sense.  You can't just string together a series of SSIS servers, point them at a list of jobs and expect them to chew through them dependent upon available processing time and bandwidth.

Instead Microsoft recommends a "manual procedure" to perform SSIS load balancing.  I am posting this for my own reference in the future, but it might come in handy to others searching for the same information.  Here is a link to the info on MSDN.

SSIS Project Icon Broken in Vista?

I just opened an SSIS project for the first time in Vista using SQL SP2 and noticed that my SSIS project used the same icon as my SSAS project. I opened an older project just to make sure, deleted, recreated, re-added, etc. and still incorrect icon. Not a big deal, but still an annoying little glitch. . .

Blog III

This is my third blog.  My first blog is at the official ASP.NET blog site, and I haven't posted there in almost a year.  Also, I don't do as much straight .NET coding as I did years ago, so my posts wouldn't be very relevant in that forum.

My second blog was at a company that I worked for.  I no longer work there, and they seem to have taken all of the blogs down as the company goes through a downsizing period.

This represents my third blog.  I am planning to post interesting things related to the work that I currently do (mostly Data Warehousing and BI, and mostly Microsoft) to things that I think are cool.  I am using Windows Live Writer for posting, and it is a slick (free) tool.  I used Beta 1, and Beta 2 seems improved.  I am just waiting on them to include native image uploading for Blogger, but will just use Picasa until then.