Unifying Data Storage and Access
Most organizations have multiple business systems, each
with its own dedicated data store. Although you can often generate reports from
individual applications and perform analysis on the data they contain, you can
only achieve complete and consistent insight into the business by consolidating
disparate data throughout the enterprise to create a central source of business
data for reporting and analysis.
Consolidating Corporate Data for Analysis and Reporting
SQL Server 2008 supports two common approaches to unifying
business data for analysis and reporting:
· Data warehouse. A data warehouse is a dedicated data store for enterprise-wide
data, which is populated and synchronized with business data from disparate
data sources throughout the enterprise. The key advantage of this approach is
that you can design the data warehouse for optimal analytical and reporting
performance with no impact on the performance of the line-of-business
applications from which the data originates. Another advantage is that you can
clean and consolidate data that comes from multiple sources into a single
consistent version of the truth.
Data source abstraction. SQL Server 2008 Analysis Services enables the creation of data source views to provide an abstraction layer over one or more data sources. You can then use
the data source view as a single source for Analysis Services, Integration
Services, and Reporting Services. With a data source view, the data is
retrieved from the underlying source systems when analysis occurs or a report
is generated. This enables real-time analysis of the data in your business
applications. Additionally, the data source view through its added layer of
abstraction can be used to create friendly names to replace long or cryptic
table names.
These approaches are shown in the following illustration. With SQL Server 2008, you can use either one or a combination of the two.
Work with All of Your Data the Way You Want to
Regardless of which approach you take to unify your business data, SQL Server 2008 builds on its strong legacy of support for both relational and non-relational data by providing data types that enable developers and administrators to efficiently store and manage unstructured data
such as documents and images, so you can store, manage, and analyze data in the
format that best suits your business.
SQL Server 2008 includes enhanced support for XML data storage as well as a FILESTREAM data type that enables large binary data to bestored in the file system yet remain an integral part of the database with
transactional consistency. Additionally, restrictions on the size of user-defined data types have been removed, which makes it possible to exceed the 8-KB limit imposed in earlier releases of SQL Server. Support for spatial and location data types makes it possible to store and analyze
geographical data that conforms to industry standards.
Integrate All Enterprise
SQL Server 2008 data source views enable integration of data and access to data that originates from heterogeneous enterprise-wide data stores, such as SQL Server, Oracle, DB2, and Teradata। These views also provide an OLAP store of enterprise scale; the breadth of support for diverse data sources in SQL Server Integration Services means that you can extract
data from all kinds of existing business applications. Therefore, unifying the
data in all of your enterprise data sources is easy to accomplish, regardless
of whether you want to build an abstraction layer through a data source view or
use an ETL process to synchronize a dedicated data warehouse for analysis and
reporting.Additionally, through support for Web services and the
Microsoft .NET Framework, SQL Server 2008 supports interoperability with
multiple platforms, applications, and programming languages, so you can
maximize your investment in new and existing systems by integrating and
connecting your disparate data sources. Support for existing and emerging open
standards, such as HTTP, XML, SOAP, XQuery, and XSD, further facilitate
communication across your extended enterprise systems.
Data Warehouse Performance
SQL Server 2008 provides a comprehensive and scalable data
warehouse platform that enables your organization to integrate data into the
data warehouse faster so you can scale and manage growing volumes of data and
users.
Optimize ETL Processes
Data warehouses are usually populated and updated with
data from source systems through an ETL process. After the initial load of data
into the data warehouse, periodic refreshes of new and changed data from the
source systems are performed to ensure that the data warehouse is up-to-date.
SQL Server Integration Services provides a comprehensive
platform that you can use to extract data from diverse source systems, make any
required transformations to the data or its structure and format, and then load
the transformed data into the data warehouse. Performing lookups to match
records from source systems to existing records in the data warehouse is a
common operation in ETL processes, and in SQL Server 2008 the performance
of lookups has been significantly improved so that these operations scale to
extremely large tables.
To help track data changes and ensure data warehouse
consistency, SQL Server 2008 introduces change data capture functionality to log updates in change tables,
which makes it easy to identify rows that have been modified and to determine
the details of the modification and its cause.
Manage Hardware Resources Efficiently
Through support for data compression, SQL Server 2008
enables you to store your data more effectively and reduce storage costs. In
addition, the compression significantly improves performance for large
input/output bound workloads. SQL Server 2008 also provides native support
out-of-the-box for backup compression. Additionally, the VARDECIMAL data type
and new sparse columns in SQL Server 2008 help you get the most efficiency
from your disk storage resources by reducing the amount of space that is
required for the large tables that contain the predominantly numeric or NULL
values that are often found in data warehouses.
In addition to efficiencies in data storage, SQL
Server 2008 supports dynamic allocation of Address Windowing Extensions
(AWE) mapped memory that supports up to 64 gigabytes of memory with
Windows Server® 2003, Datacenter Edition, and 2 terabytes with the 64-bit edition, to support
large data warehouse environments.
SQL Server 2008 also introduces Resource Governor,
which enables administrators to define resource limits and priorities for
different workloads, so that concurrent workloads can provide consistent
performance and make it possible to manage resource contention issues
proactively.
Optimize Database Performance
SQL Server 2008 includes a high-performance relational
database engine that enables you to build highly effective data warehouse
solutions. Innovations, such as query optimizations for star schemas and tools
to help you tune indexes and data structures, make SQL Server a natural
choice for a heavily queried data warehouse. With the introduction of the MERGE
Transact-SQL statement in SQL Server 2008, developers can more effectively
handle common data warehousing scenarios, such as checking whether a row exists
and then executing inserts or updates. Additionally, an extension to the GROUP
BY clause enables users to define multiple grouping in the same query. Grouping
Sets produce a single result set that is equivalent to a UNION ALL of
differently grouped rows, which makes aggregation querying and reporting easier
and faster.
SQL Server 2008 supports partitioned tables to help you
optimize the performance and management of large tables, and with new support
for partitioned table parallelism, you can significantly optimize a data
warehouse by using partitioned tables.
Building and Managing Sophisticated BI Solutions
As organizations demand ever increasingly complex
analytics, the need to be able to build and deliver effective BI solutions
quickly and to reduce the management overhead of your BI infrastructure has
become a major consideration. SQL Server 2008 includes innovative tools
that increase developer productivity and manageability, which enables faster
capitalization on new analysis and reporting capabilities while incurring
reduced administrative overhead.
Developer Productivity
SQL Server 2008 simplifies the development of business
intelligence solutions. BI developers benefit from easy-to-use utilities and
tools that increase control and automate routine, time-consuming tasks, and can
use the productivity features of SQL Server 2008 to create effective analysis
and reporting solutions more quickly than ever; so your organization can take
advantage of them sooner.
Use a Rich, Modern Programming Environment.
Through tight interoperability with Microsoft Visual
Studio®, developers can easily build and maintain robust, secure, scalable BI applications.
SQL Server Business Intelligence Development Studio offers a single tool
that covers multiple types of BI solution and provides a single, consistent
environment for developing ETL, analysis and reporting solutions. Intuitive BI
wizards that are delivered as part of the Business Intelligence Development
Studio make it easy for even novice developers to build advanced Business
Intelligence models and projects.
By embedding the common language runtime (CLR) in the database
engine, SQL Server 2008 enables developers to choose from a variety of
languages to develop applications, including Transact-SQL, Microsoft Visual
Basic®, and C#®. This flexible environment
enables developers to use their existing skills to efficiently develop database
applications.
When integrated with Visual Studio, the development
experience across all of the SQL Server 2008 BI technologies is optimized for
providing a true application development environment that supports the full
project development life cycle (develop, test, deploy, modify, and test).
Implement Best Practice Solutions
Enabling developers to build solutions more quickly is
only effective if those solutions are optimally designed. To help ensure the
best possible performance and correct functionality, SQL Server 2008 includes
the following development environment features that promote best practices and
help developers create effective analysis solutions:
· A consistent development environment for all BI
solutions, including Analysis Services, OLAP, and data mining applications.
· Built-in support for the full development
lifecycle, including design, build, debug, and deploy operations; and support
for team-based development through integrated support for source control.
· A number of intuitive designers and wizards that
make it easy to create Analysis Services solutions quickly.
· An attribute relationship designer that includes
built-in validations to help in creating optimal dimension designs.
· A dimension editor that has been slimmed down to
provide better productivity and the presence of parent child relationships is
automatically detected.
· A cube designer that has been streamlined and
improved to provide better detection and classification of attributes along
with identification of member properties.
· Aggregation of individual partitions, which
enables you to optimize measures from different periods or areas.
· The aggregation designer has a new algorithm to
help create initial aggregations. The aggregation designer is optimized to work
with usage driven aggregations. You can now look at the aggregations that have
been created and add to those aggregations or remove them. Intelligent support
is provided to help with merging existing and new aggregation designs.
Additionally, SQL Server 2008 provides
AMO warnings to alert developers when their design breaks one of over
40 best practices. These warnings are integrated into real-time designer
checks, and provide a non-intrusive way for developers to detect potential
problems with their design.
Increase Reporting Flexibility
Reporting is a significant element of any BI solution, and
business users are demanding increasingly complex reports. SQL Server Reporting
Services provides the following features to make it easy to build reporting
solutions:
· A Visual Studio-based report development
interface in Business Intelligence Development Studio that developers can use
to build, debug, and deploy reports.
· A business-focused report development tool named
Report Builder that business users can use to create and deploy reports.
· A wide range of data display structures, including tables, matrices, lists, and charts.
Additionally, SQL Server 2008
includes extensive enhancements to Reporting Services that improve reporting
performance and provide increased flexibility for formatting and publishing
reports. One of the enhancements made to Reporting Services in SQL
Server 2008 is support for a new layout structure that combines table and
matrix data regions into a new Tablix data region. Tablix enables developers to
generate reports that combine fixed and dynamic rows. Previously, layouts of
this kind had to be developed by using multiple matrix data regions and
shrinking row headers. Support for Tablix data regions simplifies the inclusion
of combined static and dynamic data in reports, and extends the formatting and
layout capabilities of Reporting Services significantly.
Manageability
Through innovations such as a unified management tool,
enhanced self-tuning capabilities, and a powerful management programming model,
SQL Server 2008 extends the SQL Server ease-of-use leadership and
increases the productivity of database administrators (DBAs). These
enhancements enable DBAs to focus on high value tasks, like database
architecture, while spending less time on routine maintenance, configuration,
and tuning.
Use a Single, Unified Tool
SQL Server 2008 provides DBAs with SQL Server Management Studio; a single, unified management tool that provides integrated management of Analysis Services, Reporting Services, Integration Services, and multiple versions of SQL Server, from the same interface for increased DBA productivity, flexibility, and manageability across extended SQL Server
implementations.
Monitor Data Warehouse Resources
SQL Server 2008 includes performance data collection and
warehousing, which enables monitoring and reporting of resources across your
data services solution.
Enterprise
Scalability
A key factor related to the successful delivery of truly
sophisticated BI solutions requires sustained developer productivity and
manageability even through large-scale implementations. Furthermore, the
infrastructure must provide performance-related scalability, which in SQL
Server 2008 has achieved a wide variety of investments that are focused on
ensuring this enterprise-scale sustainability even through the largest scale of
BI implementation.
Scalable Analytics
The premise of Online Analytical Processing (OLAP) is that
instant access to accurate information enables end users to answer even the
most complex questions at the speed of thought. Thus, the aim to continuously
excel in providing even faster query times and data refresh rates is a priority
during the development process of any SQL Server Analysis Services
release, an aim that naturally also has been driving the release of SQL
Server 2008 Analysis Services.
SQL Server 2008 includes Analysis Services that enable you
to drive broader analysis with enhanced capabilities, including complex
computations and aggregations. Analysis Service provides enterprise-scale performance
through:
· A flexible caching model. With Analysis Services, you can control how data
and aggregations are cached to optimize query performance while maintaining an
acceptable level of latency between the cache and its underlying data store.
· Declarative attribute relationships. In an Analysis Services dimension, you can
explicitly declare relationships between attributes in a hierarchy. This
enables Analysis Services to pre-generate aggregations when a cube or dimension
is processed, which improves runtime query performance.
· Block computation. Block computation eliminates unnecessary aggregation
calculations (for example, when the values to be aggregated are NULL) and
provides a significant improvement in analysis cube performance, which enables
users to increase the depth of their hierarchies and complexity of
computations.
· Write-back to MOLAP. Analysis Services 2008 removes the requirement to query
ROLAP partitions when performing write-backs, which results in huge performance
gains.
· Scale-out Analysis Services. A single read-only copy of an Analysis Services database
can be shared between many Analysis Servers through a virtual IP address. This
creates a highly scalable deployment option for an Analysis Services solution.
· Execution
plan persistence. SQL Server 2008 provides functionality to lock down
query plans so that, to the maximum extent possible for correctness, the query
plans survive server restart, server upgrade, and production deployments. This
ensures consistent optimal performance of queries against SQL Server data.
Scalable रिपोर्टिंग
For many organizations, getting the right information to
the right people at the right time is a significant challenge. SQL Server 2008
provides a high-performance reporting engine for processing and formatting
reports along with a complete set of tools for creating, managing, and viewing
reports. An extensible architecture and open interfaces enable easy integration
of reporting solutions in diverse IT environments.
You can generate reports from multiple diverse data
sources, including SQL Server, DB2, and Oracle, without first building a
centralized data warehouse. You can deliver reports throughout the organization
both internally and externally through the simple deployment and configuration
capabilities that are provided by Reporting Services. This enables users to
easily create and share reports of any size or complexity. You can also deliver
reports to customers and suppliers easily by deploying reports over the
Internet.
Reporting Service provides support and the ability to
control server behavior with memory management, infrastructure consolidation,
and straightforward configuration through a centralized store and an API for
all configuration settings.
Extending the Reach of Your BI Solution
In the past, BI solutions were used by a small group of
business analysts. Now, more and more organizations are realizing the benefits
of extending the insights that are available through BI to all employees and
embedding those insights into the day-to-day operations of the business.
SQL Server 2008 enables you to create a BI solution that
can scale to thousands of users and deliver a rich user experience to everyone
through an extensible, open, and embeddable architecture that is optimized for
interoperability with Microsoft Office.
Extending Business Insight to Everyone Through Familiar Tools
Microsoft Office is a ubiquitous productivity suite that
most information workers in organizations throughout the world use to perform
their daily tasks. Through close integration with Office, SQL Server 2008
enables you to empower your employees with critical, timely business
information that is tailored to their specific information needs.
Tight integration with the 2007 Microsoft Office
System and PerformancePoint Server 2007 enables organizations to save time
and money by using technology that works well together. The integration also
provides a quicker return on investment in terms of end-user scale by
delivering business insight to everyone through familiar tools that are already
installed on every desktop computer.
Extend Your Reporting Solution with Microsoft Office
Reporting Services 2008 supports rendering to both Excel
and Word formats. Rendered reports are fully editable in the appropriate Office
application, which extends your reporting solution so that users can create
custom documents based on business reports.
Use Excel for Analysis
Excel is the tool of choice for many financial and
business analysis tasks. By combining Excel with SQL Server Analysis
Services, you can bring the full power of your OLAP solution to business users
through PivotTable dynamic views and Microsoft PivotChart® dynamic views in an Excel spreadsheet.
You can use the SQL Server Data Mining Add-Ins for
Excel 2007 to enable business analysts and executives to go through the
entire life cycle of a data mining project, including preparing data, building,
evaluating, and managing mining models, and predicting results by using either
spreadsheet data or external data that is accessible through your Analysis
Services database.
Publish Business Insights Throughout the Enterprise
SQL Server 2008 integrates tightly with SharePoint
Services to make it easy to publish and manage reports centrally in a
SharePoint site and to build user-specific dashboards that provide customizable
views of relevant reports.Additionally, PerformancePoint Server provides a central
interface for data analysis that is built on SQL Server Analysis Services
and enables customers to monitor, analyze, and plan their business, as well as
drives alignment and provides actionable insight across the entire
organization.
Empowering End Users Through Flexible Reporting
SQL Server 2008 provides a number of reporting enhancements that enable you to
quickly and easily generate the reports that your organization needs, in the
format that you want, and in a layout that makes sense of the data they
contain.
Create Ad Hoc Reports with Report Builder
Report Builder has been enhanced extensively in SQL Server 2008
to enable users to easily build ad hoc reports with any structure. The
intuitive design interface makes it easy for non-developers to create business
documents, such as purchase orders, invoices, and contracts, based on report
data.
Include Richly Formatted Data
Rich formatting can make business documents and reports
more intuitive and significantly easier to understand. The rich text component
of SQL Server 2008 enables mixed-formatting text boxes and importing of
marked up text strings, and supports the new chart formats and the Tablix data
region so that users can generate reports with high standards of visual design
to convey business information clearly and logically.
Benefit from Increased Responsiveness
As reports become more central to the way organizations
distribute business information, ensuring the highest levels of performance and
scalability of your reporting solution has become more critical. The Reporting
Services engine in SQL Server 2008 has been significantly re-engineered to
resolve current limitations, and now includes on-demand processing and
instance-based rendering to provide the best possible reporting performance.
Deploy Reports Securely Over the Internet
SQL Server 2008 enables you to extend the reach of your
Reporting Services solution to external users, such as customers and suppliers,
by publishing reports securely over the Internet.
