BI Thoughts and Theories

I’ll be presenting a couple of sessions at the Carolina Code Camp on Saturday, May 15th. This will be my second time presenting at the Carolina Code Camp, and I’m looking forward to it. Last time, I had great audience participation, and the event was well organized and fun.

I’ll be presenting on “Processing Flat Files with SSIS” at 8:45 AM and “Unit Testing SSIS Packages” at 12:30 PM. Looking forward to both – it should be fun.

If you are in the area, feel free to drop in – there is no charge for the Code Camp.

I’ve been meaning to post about this ever since I saw Jamie’s post on “The SQL Developer Gap”. I couldn’t agree more with what Jamie expressed. Prior to getting into serious BI development, I was primarily an application developer. This was at a time when agile development was becoming very popular. As part of that, refactoring, test driven development, continuous integration, and automated unit testing were becoming accepted as good software engineering practices, and tool support was coming along very quickly.

Then I switched over to BI development in the SQL Server 7 time frame. In a lot of ways, it was like going back to the dark ages. No refactoring support, no automated testing, no concept of builds. Nothing significant changed until SQL Server 2005, when tools like SSIS and SSAS took their first steps toward becoming more “developer friendly” by leveraging Visual Studio to easily integrate into source control and the beginnings of multi-developer support. However, there haven’t really been any improvements in this since 2005. Refactoring, automated testing, automated builds, etc., can all be done, but they are painful and time consuming to set up, and require a fair amount of specialized knowledge to do correctly. In addition, these are all skills that the average BI developer usually doesn’t posses.

To join in Jamie’s rant, this is something that has aggravated me increasingly over the last few years. In many ways, BI is ideally suited to an agile approach and developer tools that increase productivity – requirements shift on the whim of the business, you need to deliver quickly and often, and you need easy mechanisms to confirm that what you are delivering provides the correct results. There are many tasks in developing BI solutions that are repetitive and could be easily automated, if only the tools provided better support for it. And developer productivity using the SQL Server BI tools hasn’t seen a significant increase since 2005.

I’m spending a fair amount of my time these days working in Visual Studio, where I have the luxury of a built in unit testing tool, the capability to switch between visual editing and text editing depending on which makes the most sense, the ability to easily do a diff between two versions in source control, a full undo-redo stack, etc. And I get to use add-ins like ReSharper (a fantastic tool that I can’t recommend enough). It really highlights the difference between developing traditional applications and BI applications these days.

That’s part of the reason I joined Varigence, where I have the opportunity to actually help developers deliver BI solutions faster and better. Our approach makes it much easier to support the same features that you see in traditional application development tools. I’ve been pretty pleased to see how easy it is for us to add productivity features to our tools – honestly, it makes me wonder why BI developers had to wait this long for these features to be available in the tools we use on a daily basis.

I’m going to be presenting two sessions at the SQL Server Best Practices Conference on August 24-26 in Washington, DC. This conference has a different focus than many of the technical conferences that I’ve presented at in the past. Instead of technical deep dives, the focus is on providing attendees with information on the decision points they are likely to encounter in their projects, and guidance on how to choose the best course of action at those points. It promises to be a very interesting conference.

I’ll be presenting on two topics. The first, Unit Testing SSIS Packages, is a topic I’ve presented on before, but not with this focus. I’m a big fan of unit testing and the test driven development (TDD) model, but I’ve found that there are numerous challenges in applying this approach to SSIS. So my session will focus on the best practices you can use to make this successful.

My other topic, SQL Server BI in the Cloud, is a new one for me (and for pretty much everyone, considering that SQL Azure is still pre-CTP). However, I’ve been doing some work with it and other cloud based models for BI, and there are some clear key decision points that you need to consider when embarking on this type of project. I’m really looking forward to this one, as it’s a new area, and that always prompts good discussions.

If you are attending, please consider dropping by for my sessions. If you’re not registered for the conference, there’s still time, and there’s a great lineup of speakers.

David Reed, who manages the SQL Server community samples on CodePlex, has been running a SQL Heroes contest. It’s for open source applications that help improve the SQL Server experience. You can read more about it on the SQL Heroes blog.

I find myself in the interesting position of having two horses in the race: BIDS Helper (with Greg Galloway and Darren Gosbell) and ssisUnit. If you use either (or both) of these, and like them, please vote in the Finalists Survey.

If you don’t use them, why not give them a try?

While at TechEd this summer, I participated in a panel discussion on testing the database and related technologies. The panel also included Gert Drapers and Jamie Laflen of the Visual Studio Team System – Database Edition team, David Reed (who manages the SQL Server community samples on CodePlex), and Adam Machanic as the moderator. The discussion covered some of the pros and cons of testing data, and some of the common issues encountered. It was also a chance to talk about testing related areas (like SSIS packages), of which I am a big fan. If you are interested in seeing it, it was just put online.

If you are interested in unit testing SSIS packages, keep an eye on www.codeplex.com/ssisunit. I should have a new release going up soon, which adds some much needed features, and a much more extensible framework for adding new functionality.

I’m going to be presenting a few sessions at the upcoming SSWUG Virtual Business Intelligence Conference. It’s occurring from September 24-26, 2008. It has a very impressive list of speakers (I’m really not sure how I made it on the list – it’s quite an honor). I’ll be presenting on configurations in SSIS, unit testing SSIS (one of my favorite topics), and the new Report Builder functionality in SQL Server 2008.

I’m looking forward to it, but it will be a new experience for me. The sessions will be recorded in advance, and then the speakers will be online during their presentation time slot to answer questions from the audience. It sounds interesting, and since it’s being broadcast online, there’s potential for a much wider range of attendees.

If you’re interested in the conference, please check out the web site.

I’ve been a bit lax posting on my blogs and the MSDN forums recently. Fortunately, I have a good reason (at least I think it’s a good one). My employer, Mariner, has graciously given me permission to open source a unit testing framework for SSIS packages. Preparing it for release has taken a bit more time than I expected, as I wanted to polish up a few items, and that led to a few more changes, etc. The framework, as we were using it, was definitely functional, but I wanted to make a few changes for ease of use. Now I have those changes in, and an alpha (but functional) version is available on Codeplex, under the ssisUnit project.

I’ve posted previously about unit testing for SSIS, and how I really missed the automated unit testing capability that I’d taken for granted in more traditional application development. Since the team currently has no plans for unit testing for SSIS, it seemed like a good time to get this out and available to the public. There are currently methods of testing SSIS, but most of them involve testing the package as a whole. One of our goals for unit testing SSIS, though, was to enable testing at a more granular level. ssisUnit enables testing down to the individual task level in the control flow. In future iterations, I’d like to expand the functionality to include testing individual components in the data flow.

We have additional plans for ssisUnit in the future, including Visual Studio integration, additional command capabilities, and a GUI for creating the test cases. The current version is v0.50, and I hope to have another release by mid-April. Please download it, give it a whirl, and provide feedback and suggestions for improvement. If you’re interested in contributing to the project, please leave a comment on this post, or email me at john.welch@mariner-usa.com.

I’d put in a request for additional support for unit testing in Integration Services on Connect (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=276292). Unfortunately, it doesn’t look like it will make the cut for SQL Server 2008.

I used automated unit testing extensively when I was more focused on application development, and I really miss having it available in SSIS. In my experience, it made a significant difference in both quality and speed of development. Having a suite of test cases gave developers a safety net and being able to quickly test small units of work made progress much more tangible. (For more on test-driven development and some of the benefits, see http://en.wikipedia.org/wiki/Test-driven_development.)

Given that, maybe it would be worth consider incorporating some unit testing capabilities into the BIDSHelper project (http://www.codeplex.com/bidshelper) or starting another community project to add those capabilities. Any thoughts on what would be good features to include? My list includes:

• Ability to test tasks individually
• Ideally, ability to test pipeline components individually
• Standard xUnit functionality (the ability to set up and tear down a test)
• Some support for setting the database to a known state prior to the test would be valuable

Any other thoughts would be welcome.