Turning Data into Smart Decisions:
Building Business Intelligence Solutions with SQL Server 2005 - George Wesolowski

“How you gather, manage, and use information will determine whether you win or lose.”
- Bill Gates

Introduction – The BI Vision

The primary goal of SQL Server 2005 Business Intelligence is to improve your organization by providing relevant information from disparate data sources in a timely manner. This allows you to make better, faster, and more informed decisions. It also allows you to analyze the affects of these decisions based on information rather that instinct.

Business Intelligence with SQL Server 2005

SQL Server 2005, currently in its second beta version and scheduled for release late this year, provides an integrated environment for designing, developing, deploying, and managing Business Intelligence applications. This environment includes two new tools, the SQL Server Management Studio, and the Business Intelligence Development Studio.

SQL Server Management Studio

Consider the SQL Server Management Studio as a replacement for both the SQL Server Enterprise Manager and Query Analyzer that you’re familiar with in SQL Server 2000. While Enterprise Manager provided an administrator’s view of all the SQL Servers and databases that are registered in the environment, SQL Server Management Studio provides an integrated view of all the SQL Servers, Analysis Services, Integration Services, Reporting Services, and SQL Mobile databases that are registered in the environment. In addition to providing this administration view of interest to your database administrators, it also allows your database developers to create, analyze, execute, and save SQL queries as well as MDX queries within the same Visual Studio-like environment.

Business Intelligence Development Studio

While there are parallels between SQL Server Management Studio and Enterprise Manager, the Business Intelligence Development Studio is an entirely new tool for SQL Server 2005. This developer tool, based on the Visual Studio IDE, provides for the seamless integration of SQL Analysis Services, Integration Services, and Reporting Services. Using this tool, you can create, debug, and deploy Analysis Services projects, Integration Services projects, and Reporting Services projects. As in the Visual Studio environment, multiple projects may be combined into a single solution that may be integrated with SourceSafe.

Analysis Services Projects

Analysis Services projects allow the developer to define and create Analysis Services databases. Here, the developer defines the dimensions, measures, fact tables, and cubes to construct an Analysis Services database based on an existing relational database using new and improved graphical designers. In addition, the developer can choose from seven new data mining models that are used to find patterns and perform predictive analysis on your Analysis Services database. These models include:

Decision Trees

Naļve Bayes

Cluster Analysis

Sequence Clustering

Association Rules

Time Series

Neural Networks

Integration Services Projects

Integration Services can be thought of as a more powerful replacement for Data Transformation Services (DTS) in SQL Server 2000. An Integration Service project is developed using the Business Intelligence Development Studio which consists of one or more Integration Services packages. These packages consist of control flow and data flow structures which are stored as XML and may be integrated with SourceSafe. Examples of control flow tasks include data import and table creation from disparate data sources such as text files, SQL Server tasks such as executing queries and scripts, database maintenance tasks, Analysis Services tasks, and event watcher tasks. These packages are designed and debugged using new and improved workflow designers and are programmable using a .NET object model.

Reporting Services Projects

Reporting Services projects allow the report developer to define and debug Reporting Services reports. These reports may be deployed to the Reporting Services Web Service where they may be delivered to clients using an on-demand (“pull”) or event-based (“push”) delivery mechanism. Also, these reports offer integration with the Microsoft Office System.

Conclusion

The tools that are bundled with SQL Server 2005, namely the SQL Server Management Studio and the Business Intelligence Development Studio, are not just for database administrators any more! They provide a completely integrated development environment for building, debugging, and deploying, and maintaining Business Intelligence solutions for your enterprise. These solutions will help turn your disparate corporate data into better, timelier, and more informed business decisions.