Get'em While They're Hot

Last week Microsoft unofficially announced that it had released Visual Studio 2005 and SQL Server 2005 (http://blogs.msdn.com/somasegar/archive/2005/10/27/485665.aspx). At that point, we all got to see the scalability of the Microsoft Developer Network (MSDN) download site as tens of thousands of developers all sought to download the products. (As a quick aside, don't forget to also download your free refactoring tools for Visual Basic .NET 2005 at http://www.devexpress.com/vbrefactor. Although Biztalk Server 2006 is sharing the November 7 launch with Visual Studio 2005 and SQL Server 2005, Microsoft hasn't yet released Biztalk Server 2006 on MSDN.)

Then, on Tuesday, November 1, MSDN essentially went offline for many subscribers as Microsoft started the process of transitioning to the new subscription models. I'm sure many of you will be affected by the new subscription models. For those of you still trying to decide which Visual Studio 2005 Team Edition you want to transition to, I stand by the recommendations I made in my June 3 column, "Which Team Edition Is Right for You?" (http://www.windowsitpro.com/article/articleid/46594/46594.html).

The good news is that the various versions of Visual Studio 2005 are definitely release quality. Thus far, I installed the Professional version without any of the Team System features. One nice surprise for me in transitioning one of my Visual Studio .NET 2003 projects is that Microsoft has provided a conversion wizard for the project files so the transition to the new version was almost completely painless.

SQL Server 2005 has a similar story. In addition to the updates to the core database and the inclusion of the Common Language Runtime (CLR), many of the products that build on SQL Server are going through major enhancements with this release. One such product is SQL Server 2005 Analysis Services. Analysis Services started to be a viable tool with SQL Server 2000. The 2005 release is starting to make this product available to an even larger customer base.

The basic idea behind Analysis Services is that as transaction data accumulates, it's possible to query for summary data. In some cases, the query is fairly straightforward. For example, a query for the total value of all sales for each month for the past 6 months is straightforward. Other queries can be more complex. An example of a complex query would be asking how many customers who purchased books also purchased music. What these types of queries have in common is that they're either time-consuming or difficult to ask with traditional SQL.

This is where Analysis Services comes in. Analysis Services are built around data warehouses called cubes. The cube provides data that has already been aggregated and associated with the original data. You can set up a cube based on one or more criteria that reference your transactional data. That way, when a user asks for data based on the past 6 months, the cube can retrieve precompiled answers for the first 5 months and even information for the current month. This might fully answer the query or there might be more data that has been added in your transaction database that hasn't yet been compiled into the cube. However, as new sales data for the current month is entered, Analysis Services has the ability to retrieve the updated data from your transactional database on the fly. For more information about the concepts underlying Analysis Services, I suggest starting with this page: http://www.microsoft.com/sql/analysis/overview.mspx.

Because Analysis Services uses precompiled summary data to speed complex queries, you can move down through the top-most summary level to lower levels. Continuing with my original example of 6 months' data, you could start with a view that showed sales per quarter, delve into the performance on a monthly basis, and from there go directly to the performance at a weekly, daily, or even hourly level. Eventually you might even go to the discrete transaction level, but that might be more appropriate if you're using a cube that includes a sales-by-location dimension. In that case, you might look at the sales for store A versus store B for a given month.

This ability to delve down into your business data is referred to as business intelligence. BI has two roles in an organization. Its first role is to help people understand what happened in the past. Its second role is to help people prepare for the future (e.g., help people set expected future sales) based on the historical information gathered. The people who need to carry out these BI tasks aren't really developers. They're business analysts who in the past would use a series of printed reports, then analyze that data and ask for details. With the release of SQL Server 2005, what we are seeing are ongoing improvements in the automation of this business analysis. A good example is the Microsoft Office Business Scorecard Manager 2005, a product that the Microsoft Office team is creating to leverage the power of Analysis Services. Still in the prerelease phase, you can get more information about the Business Scorecard Manager at http://office.microsoft.com/en-us/assistance/ha012225141033.aspx.

The key takeaway here is don't be blinded by low-level tools changes. Yes, there are numerous new features in Visual Studio 2005 and SQL Server 2005, but keep in mind that these new features are also going to let you build new classes of applications. If you're a database developer, now is the time to start thinking beyond the transactional database. By the way, one final aside: When installing SQL Server 2005, remember to use the Advanced button during the feature selection process so that you can get access to the new Adventure Works sample database and Data Warehouse.