Business Intelligence Redefined

Business intelligence refers to tools and techniques that support better decision making. By using these tools and techniques, you can turn raw data into insight. Data from possibly disparate source systems can be intelligently integrated to build a foundation for reporting, analysis, data mining, prediction, and expert systems.

SQL Server 7 first introduced OLAP Services, a data warehouse engine for online analytical processing. To continue the evolution of the business intelligence platform, SQL Server 2000 introduced several significant advancements to OLAP Services and renamed the technology Analysis Services.

Analysis Services 2000, in conjunction with data mining models and Data Transformation Services (DTS), which is Microsoft's premier Extract-Transform-Load (ETL) tool, was a platform of choice for building data warehousing and business intelligence solutions.

SQL Server 2005 revolutionizes and redefines the business intelligence platform by introducing completely redesigned ETL and integration platform known as SQL Server Integration Services (SSIS; formerly known as DTS), and it adds several new features to Analysis Services 2005 by introducing new data mining models, a Web-based reporting solution, and integrated management and development tools.

The main theme of business intelligence in SQL Server 2005 is integrate, analyze, and report: You use SSIS for integration, Analysis Services 2005 and Data Mining for analyzing, and Reporting Services and Report Builder for presentation and delivery of reports. Figure 4.6 shows the core components of the SQL Server 2005 business intelligence framework.

Figure 4.6. SQL Server 2005 provides new tools and technologies to build business intelligence solutions.

The business intelligence framework in SQL Server 2005 is based on a solid foundation of a relational engine, and it is supported by innovative development and management tools.

SSIS has been redesigned from scratch, and it goes a step beyond being just an ETL tool. The new SSIS is designed to be an ultimate solution to integrate data from various sources, massage it, and send it to one or more destinations. SSIS is discussed in more detail in Chapter 12, "SQL Server Integration Services Overview."

Microsoft really listened to the feedback it received on Analysis Services 2000, and it combined that with innovative techniques to make Analysis Services 2005 a highly scalable, available, and secure platform for building multidimensional solutions. Analysis Services 2000 is single-instance and cluster-unaware, whereas Analysis Services 2005 supports up to 50 instances and 8-node (32-bit) or 4-node (64-bit) failover clusters. Several enhancements have been made to the Analysis Services engine, cube and dimension handling, new data mining models, a new .NET Frameworkbased object model from programming, and native support for the XML for Analysis (XMLA) 1.0 specification. As you will see in Chapter 13, "What's New in SQL Server Analysis Services 2005," Analysis Services 2005 is loaded with new features as well as improvements to existing functionality.

In January 2004, Microsoft announced SQL Server 2000 Reporting Services, an enterprise reporting platform that supports a full reporting life cycle, including authoring, management, and delivery. Reporting Services can be used for both paper-oriented and interactive, Webbased reports. Reporting Services is now one of the core subsystems in SQL Server 2005. It incorporates feedback that Microsoft received on SQL Server 2000 reporting services, plus it has tighter integration with SQL Server 2005 and Analysis Services 2005. SQL Server Management Studio, Business Intelligence Development Studio, and other tools have been enhanced to support Reporting Services. SQL Server 2005 Reporting Services supports rich client printing, multivalued parameters, and interactive sorting, and it contains an enhanced expression editor.

SQL Server 2005 also introduces a new ad hoc report design tool targeted at business users. This tool, known as SQL Server 2005 Reporting Services Report Builder, is based on business intelligence technology acquired from ActiveViews Inc. in April 2004. Business users can very easily create reports based on data in a SQL Server relational database or Analysis Services OLAP cubes, without understanding or writing any T-SQL or MDX queries. The reports created using Report Builder can be published to Reporting Services or SharePoint Server.

Introducing Business Intelligence Development Studio

In much the same way as SQL Server Management Studio integrates Enterprise Manager and Query Analyzer into one shell, Business Intelligence Development Studio is an integrated environment for designing and building end-to-end business intelligence solutions. Business Intelligence Development Studio is a single tool that information workers can use to define new Analysis Services data sources, cubes, dimensions, and data mining models; design ETL packages; build reports; and deploy an entire solution to a test or production environment. Essentially, it combines Analysis Manager and DTS Designer, and it adds several new capabilities.

Like Management Studio, Business Intelligence Development Studio is also based on Visual Studio .NET, and it allows you to work with projects and solutions with complete SourceSafe integration. Figure 4.7 shows the Business Intelligence Development Studio environment, with an Analysis Services project being developed.

Figure 4.7. Business Intelligence Development Studio is an integrated IDE for building and deploying business intelligence solutions.

You might wonder when to use SQL Server Management Studio versus Business Intelligence Development Studio. If you are developing or maintaining business intelligence solutions, such as designing SSIS packages, reporting services reports, or Analysis Services objects, you should use Business Intelligence Development Studio. However, if you are responsible for developing and administrating relational database objects or administrating and configuring already deployed solutions that use business intelligence technologies, you should use SQL Server Management Studio.