BI features and components available with the popular SQL Server 2005 editions (Express, Workgroup, Standard, and Enterprise) can be derived from the Microsoft SQL Server 2005 Features Comparison Matrix.    

We will discuss Business Intelligence in SQL Server 2005 under the following important components, SQL Server 2005 Analysis Services, SQL Server 2005 Integration Services, SQL Server 2005 Notification Services, SQL Server 2005 Data Mining Capabilities, and SQL Server 2005 Reporting Services. Let us unravel these BI components in the following sections.

SQL Server 2005 Analysis Services

Online Analytical Processing (OLAP) and Data Mining functions are provided by SQL Server 2005 Analysis Services for business intelligence applications. In regards to OLAP, SQL Server 2005 allows you to create, design and manage the multidimensional structures that have the data collected from various sources. In regards to Data Mining, SQL Server 2005 Analysis Services permits the users to design, develop and visualize data mining models that are derived from other data sources using various data mining algorithms. 

The SQL Server 2005 Analysis Services offers a business centric model known as Unified Dimensional Model (UDM) that describes business logic, business elements, computations and reporting. The UDM uses its robust data source view feature and gets mapped to various dissimilar backend data sources thereby presenting a fully integrated image of the business. It also provides proactive caching that helps in combining real time updates with Multidimensional Online Analytical Processing (MOLAP) class performance. SQL Server 2005 also provides KPI (Key Performance Indicator) framework that is a feature rich centralized storehouse. KPI framework helps in the construction of balanced scorecards and business performance centric applications.

SQL Server 2005 Integration Services

SQL Server Integration Services can be looked as the upgrade and substitute of Data Transformation Services (DTS). This provides a data integration platform from simple tasks and data transformation to a robust object model supporting creation of custom tasks and data transformations.

SQL Server 7.0 and 2000 earlier provided DTS that used to take care of extraction, transformation and loading of data. SQL Server 2005 provides a more architecturally enhanced tool known as SQL Server Integration Services (SSIS) that replaced DTS in its functions. SSIS helps transform company data into useful information that helps in meaningful analysis of corporate data in today’s business scenario. Although Data Integration looks apparently simple, it is practically cumbersome in a typical data warehousing scenario. The need for systems for the right technology calls for staging at every step in the data integration process. This is due to the existence of different sources of data that needs to be included in the ETL (Extract, Transform, & Load) process and the need for various complex operations to be performed on the data. Currently, value added ETL processes are available that reduces drastically the restrictions on these systems to cater to the business requirements. Effective business decisions are made out of effective data integration of data from various sources. SQL Server 2005 Integration Services provides such an effective and scalable architecture to enable data integration in a manner useful to effective business analysis.   

SQL Server 2005 Notification Services

SQL Server 2005 Notification Services allows developing applications that help generate and send notifications to interested subscribers or entities based on their interest. The notifications can be scheduled at specific times and can be generated and messages can be sent to a large number of subscribers. This can also deliver messages to a variety of devices. This is an addition to the SQL Server 2005 as compared to its earlier versions. Most of these notifications are based on addition or modification in data. The Notification Services examines the occurrence of any event on any specified data, whether any subscriber is available for getting notified for this event and if available, it sends the notification to the subscriber. Features like polling, scheduling, formatting and delivery of notifications are now available as built in features in the SQL Server Notification services for integration and development purposes.  

SQL Server 2005 Data Mining Capabilities

SQL Server 2005 Data Mining capabilities help in collecting useful data and derive data patterns and trends which help in describing mining models. Mining models are specific to each business state.

Available with SQL Server 2005 Standard and Enterprise editions, data mining algorithms are tools that provide the answers to various business queries relating to customers and business data. The tools also help in providing information regarding the futuristic business approaches that are derived from the collected data. It provides solutions to some very important business issues like success rate of any marketing campaign, website analysis, present and future market trends, customer retention policies, most saleable products, etc. Data mining features in the SQL Server 2005 are far more superior as compared to its predecessors. Using this tool effectively would need deep understanding of statistical theories and business processes. One of its major benefits is that this feature is tightly integrated with other components of SQL Server. Other salient strengths are its scalability, ease-of-use and extensive API. SQL Server 2005 Data Mining mainly revolves around integration of data from various sources, analysis of captured data, and reporting from the data collected.      

SQL Server 2005 Reporting Services

Reporting Services from SQL Server 2005 (commonly known as SSRS) provides a web based reporting mechanism that can be extensively used for creating reports from various data sources using different formats and centrally managed security and subscriptions.

SSRS was earlier available with the SQL Server version 2000 that provided enterprise level reporting capabilities. However, the SSRS has been more enhanced in the SQL Server version 2005 providing greater capabilities and also helping the business users to generate personalized business reports from scratch that can be shared among other users. Different users can interact with the reports through SSRS. SSRS caters to the enterprise-wide reporting needs from a single reporting platform.  

SQL Server Business Intelligence Development Studio, a Microsoft Visual Studio based Development environment, is a pre-requisite for developing reports using SSRS. Enterprise Reporting helps generate advanced enterprise level reports with rich designs. There are salient features like multiple data-sources support, cascading, end-user sorting, etc. that allow users to create great personalized reports encompassing enterprise-wide user access. Ad Hoc Reporting in SSRS provides an ad hoc reporting tool known as Report Builder that gives the business users (who do not possess much technical skills) the leverage to examine corporate data and build their personalized reports. Embedded Reporting in SSRS is operational through a set of redistributable Report Viewer controls (available with Visual Studio 2005 Reporting Services). These controls help in creating predefined and ad hoc reports and make these reports available to the end business users in specific formats that are relevant to them. The reporting platform can be well managed, centrally enhancing the processing and overall performance. It also helps in automated, real-time, enterprise-wide delivery of information that helps in taking faster business decisions. It also supports on-demand delivery of reports, and can implant reports in applications and can distribute reports based on events.