LogoASPAlliance: Articles, reviews, and samples for .NET Developers
Business Intelligence with Microsoft SQL Server 2005
by Rajdeep Mukherjee
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 20634/ 51


Microsoft SQL Server 2005 (sometimes known as Yukon) provides a great platform that presents the features and functionalities to build any complex analytical solutions. Today's software applications aim at extracting useful information from a collection of data. Microsoft SQL Server 2005 provides all the modern analytical technologies, starting from basic reporting, through OLAP systems, to data mining applications.  This article provides an introductory overview of the Business Intelligence (BI) competence of Microsoft SQL Server 2005.

Business Intelligence and Databases

Let us first take a look into the incorporation of Business Intelligence in databases. As we all know, that information is stored in databases in the form of useful data. When this data quantity is too high (for example, greater than 10 GB), Data Warehousing concept comes in and data is normally organized and stored in a data warehouse. Any such data warehouse supports multiple databases of same or different nature. For example, a data warehouse can store in structured format the mixture of several databases that are specific to SQL Server, Oracle, and DB2, etc. Database applications that work with backend databases normally go for various role based logins for its users having specific roles and corresponding privileges within the application. For example, there can be role based logins for administrators, directors, managers, sales reps, etc. and each of these groups can have specific defined set of privileges within the application.

The behavior of the application is a summation of the activities of all of these user roles. These database applications, however, do not possess management skills, but are primarily intended for providing useful data that helps the management users to make faster and important business decisions. One primary point to be noted here is that the data stored for this purpose should be accurate and plenty. This will help to make correct business decisions and make accurate business forecasts.       

Business Intelligence in SQL Server 2005

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.


SQL Server 2005 Business Intelligence provides a single, tightly integrated, extensive platform for data integration, rich analytics, and flexible reporting, supported by a professionally conceived integrated development environment. It also offers a cost-effective and simpler BI solution as compared to the other BI solutions available in the market that are costly and have long development cycles. It shows great promise and will transform the thoughts of the industry regarding integration, analysis and reporting.

The article aimed at providing its readers with an introductory overview of the BI technologies in Microsoft SQL Server 2005. I would like to request that the readers go through the useful links provided in the previous section and obtain profound understanding on how to get started with the BI technologies in SQL Server 2005.

©Copyright 1998-2022  |  Page Processed at 2022-08-15 9:53:31 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search