Business
Intelligence with Microsoft SQL Server 2008
By
Yeung Man
|
|
|
Business Intelligence
With the advance of technology, organizational data is often
already collected in computer database through the use of database
management system such as Access, Oracle, SQL Server … etc. Data
has to be gathered, modeled and transformed before it can be used
to troubleshoot problems, extract useful information, suggest
solutions, support decision-making and improve business insight.
Furthermore, with an explosion of data stored in computer systems
and the demands of an ever-changing world, we need larger, faster
solutions that can be created and developed quickly and effectively.
This led to a new technology – Business Intelligence (BI which
includes reporting, online analytical processing (OLAP), data
mining, business performance management, benchmarking and predictive
analytics.
Delivery of BI Solution
Some organizations develop sophisticated BI systems to handle
their data analysis needs. Business Activity Monitoring (refers
to the aggregation, analysis, and presentation of real time information
about the activities within the organization) and Competitive
Analysis (allows a company to assess the strengths and weaknesses
or their competitors) are two of the tools often included in a
BI system. Another approach of delivering BI solutions is to integrate
BI data into the users’ familiar tools (like Microsoft Office).
This provides a familiar interface and an open, embeddable architecture
to allow users to integrate the data.
Microsoft SQL Server BI Solution
The BI solution offered by SQL Server consists of three components:
Users can use Business Intelligence Development Studio (an IDE
which is based on Microsoft Visual Studio development environment)
to do data analysis and develop BI solutions by utilizing the
Microsoft SQL Server Analysis Services, Reporting Services and
Integration Services.
What’s New in SQL Server 2008 in BI
SQL Server 2008 provides a comprehensive and scalable data
warehouse platform that enables powerful analyses with a single
analytical store. Following are some of the advances in SQL Server
2008 in data warehousing:
Data compression
Data compression built into SQL Server 2008 enables organizations
to store data more efficiently, while also improving performance
due to lower I/O demands.
Backup compression
With SQL Server 2008 backup compression, less storage is required
to keep backups online, and backups run significantly faster since
less disk I/O is required.
Resource Governor
Resource Governor allows organizations to define resource limits
and priorities for different workloads, which enables concurrent
workloads to provide consistent performance.
SQL Server 2008 enables organizations to efficiently deliver
reports to users according to their business needs.
New Report Designer
The Report Designer is optimized for creating reports, enabling
organizations to accommodate reporting needs. Furthermore, it
enables users to edit or update existing reports within an environment
optimized for Microsoft Office.
Visual Components
SQL Server 2008 extends the visual components available within
reports. Visualization tools such as maps, gauges, and charts
make reports more accessible and understandable.
Optimized Office Interoperability
Microsoft Office system provides optimized interoperability with
SQL Server 2008 Analysis Services. For example, Excel is a fully
functional Analysis Services client; Reporting Services can create
reports from Analysis Services data and render them as Microsoft
Office Word documents and Microsoft Office Visio can be used to
annotate, enhance, and present data mining graphical views.
Microsoft SharePoint Integration
SQL Server 2008 Reporting Services offers integration with Microsoft
Office SharePoint Server 2007 and Microsoft SharePoint Services,
providing central delivery and management of enterprise reports
and other business insights.
Microsoft Office PerformancePoint Server
An integrated performance management application to monitor, analyze,
and plan business activities based on the data provided by SQL
Server 2008 Analysis Services. Office PerformancePoint Server
2007 provides scorecards, dashboards, management reporting, analytics,
planning, budgeting, forecasting, and consolidation functionality
to provide extensive performance management capabilities.
SQL Server 2008 builds on the strong OLAP capabilities of
SQL Server 2005 by delivering faster query times. SQL Server Analysis
Services provides the following analytical advances:
Design to scale
SQL Server 2008 drives broader analysis with enhanced analytical
capabilities and with more complex computations and aggregations.
New cube design tools help users streamline the development of
the analysis infrastructure, enabling them to build solutions
for optimized performance. Best Practice Design Alerts have been
built into the design, enabling developers to integrate real-time
alerts at design time, which optimizes design.
Resource Monitor
A new Resource Monitor in SQL Server 2008 provides detailed insight
into resource utilization. With this Resource Monitor, the database
administrator can monitor and control the analysis workload to
optimize server utilization.
Predictive Analysis
An enhanced Time Series algorithm extends forecasting capabilities
in terms of accuracy, stability and prediction flexibility.
Analytical data from Analysis Services can be made available
to users by
