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.
