Transcript Microsoft MS. SQL Server 2008

Data Platform Vision
Vu Tuyet Trinh
[email protected]
Hanoi University of Technology
Outline




Overview of Microsoft Data Platform Vision
Analysis Services
Reporting Services
Integration Service
MS. SQL Server 2008
Microsoft
XML
e-mail
time/calendar
Types of
Data
file, document
geospatial
DATA
search
PLATFORM
query
VISION
Services to
interact
data analysis
reporting
data integration
MS. SQL Server 2008
Microsoft
robust
synchronization
• Microsoft Data Platform Vision
MS. SQL Server 2008
Microsoft
Improved Productivity

ADO.NET Entity Framework:
Provides a data programming interface that makes it:
 Easy to understand the conceptual data model
 Easy to design and develop applications
 Easy to maintain applications
MS. SQL Server 2008
Microsoft
Improved Productivity


LINQ

LINQ to SQL .

LINQ to Entities .

LINQ to DataSet .

LINQ to XML .

LINQ to Object .
Visual Studio
Providing features such as source code control, tracking, and
deployment tools .
MS. SQL Server 2008
Microsoft
Improved Productivity
Microsoft Office 2007
See Part 2-Analysis Services.

MS. SQL Server 2008
Microsoft
SERVICES
MS. SQL Server 2008
Microsoft
SERVICES
Analysis
Services
Reporting
Services
services
Data Platform
Intergration
Services
MS. SQL Server 2008
Microsoft
1. Analysis Services

Microsoft SQL Server 2008 Analysis Services builds on a strong
foundation of analytical tools to provide a truly enterprise scale
solution .

Analysis Services provides optimized Office interoperability to
provide a familiar interface and an open, embeddable architecture to
allow developers to integrate the data.
MS. SQL Server 2008
Microsoft
Analysis Services
Analysis
Services
Build EnterpriseScale Solutions
Extend Reach
with
Comprehensive
Analytics
Drive Actionable
Insight through
Familiar Tools
MS. SQL Server 2008
Microsoft
Build Enterprise-Scale Solutions

Microsoft SQL Server 2008 Analysis Services is designed to provide
exceptional performance and scales to support applications with millions of
records and thousands of users. Innovative, consolidated tools help improve
developer productivity and result in better design and faster implementation.
MS. SQL Server 2008
Microsoft
Build Enterprise-Scale Solutions
High
Developer
Productivity
Build
EnterpriseScale
Solutions
Scalable
Infrastructure
Superior
Performance
MS. SQL Server 2008
Microsoft
High Developer Productivity

SQL Server 2008 Analysis Services introduces a set of new,
innovative Best Practice Design Alerts that provide automatic
notification of potential design issues early in the development
process, which reduces wasted time caused by design mistakes and
facilitates a faster development process.
MS. SQL Server 2008
Microsoft
High Developer Productivity

Figure 1 shows an alert on the Time dimension and Calendar hierarchy.
MS. SQL Server 2008
Microsoft
Figure 1
High Developer Productivity

Figure 2 shows the current alerts on a design.
MS. SQL Server 2008
Microsoft
Figure 2
High
Productivity
SQLDeveloper
Server 2008 Analysis
Services further increases developer productivity

with new, enhanced cube, dimension, and attribute designers.
MS. SQL Server 2008
Microsoft
Figure 3
Scalable Infrastructure

Analysis Services can scale to support databases of many terabytes
in size with many thousands of users.

SQL Server 2008 Analysis Services provides Dynamic Management
Views similar to those available to the database engine.
MS. SQL Server 2008
Microsoft
Superior Performance

Analysis Services cubes are multidimensional structures and stores
bussiness data in a highly optimized and compressed format called
Multidimensional OLAP (MOLAP).

AS can improve query performance by orders of magnitude and
therefore allow a finer granularity of analysis.
MS. SQL Server 2008
Microsoft
Superior Performance

SQL Server provides attribute-based hierarchies that avoid the need
for any duplication and improve performance and scalability.

SQL Server 2008 Analysis Services allows writeback data to be
stored in MOLAP format resulting in significantly better performance
for query and writeback operations.

AS prevent users from overloading the relational database by
providing a high performance, transparent, synchronized aggregate
cache.
MS. SQL Server 2008
Microsoft
Extend Solutions with Comprehensive Analytics

Analysis Services takes the analytical platform to a new level offering more
advanced features than those traditionally related to OLAP. This enables
organizations to accommodate multiple analytical needs within one solution
offering so much more than a traditional OLAP platform. In this effort, the
Unified Dimensional Model (UDM) plays a central role, providing extensive
analytical capabilities.
MS. SQL Server 2008
Microsoft
Unified
Dimensional
Model
Extend Reach
with
Comprehensive
Analytics
Central
Manageability of
Key Enterprise
Metrics
Predictive
Analysis
MS. SQL Server 2008
Microsoft
Unified Dimensional Model

The UDM was a new concept for Analysis Services that was introduced with
the release of SQL Server 2005. The UDM provides an intermediate logical
layer between the physical relational database used as the data source and
the proprietary cube and dimension structures that are used to resolve user
queries.

In this way, you can think of the UDM as the centerpiece of the OLAP
solution.
MS. SQL Server 2008
Microsoft
Central Manageability of Key Enterprise Metrics

In SQL Server 2008 Analysis Services enterprise wide Key Performance
Indicators (KPI’s) can be centrally stored and managed.

This provides a central repository for users to access key enterprise metrics
through a variety of applications including Microsoft Office
PerformancePoint Server 2007, Microsoft Office Excel 2007, Microsoft
Office SharePoint Services 2007, and Microsoft SQL Server Reporting
Services.
MS. SQL Server 2008
Microsoft
Predictive Analysis

Traditional data analysis looks at historical data and quickly returns
results based on this data.

However, many questions asked by business users cannot be
answered by this sort of analysis as they are not looking for the
results of what has happened, but instead they are looking for
predictions of what might happen.
MS. SQL Server 2008
Microsoft
Predictive Analysis
Microsoft SQL Server Data Mining Add-Ins for Office 2007:
The Data Mining Add-Ins for Office 2007 empowers end users to perform
advanced analysis directly in Microsoft Excel and Microsoft Visio.
There are three individual components:
 Data Mining Client for Excel enables you to create and manage an entire
Analysis Services data mining project from within Excel 2007.
 Table Analysis Tools for Excel enables you to use the powerful Analysis
Services data mining capabilities to analyze data stored in Excel spreadsheets.
 Data Mining Templates for Visio enables you to render decision trees,
regression trees, cluster diagrams, and dependency nets in Visio diagrams.
MS. SQL Server 2008
Microsoft
Drive Actionable Insight through Familiar Tools
MSOffice Excel
Optimized
Office
Interoperability
Drive
Actionable
Insight
through
Familiar
Tools
MS. SQL Server 2008
Microsoft
MS Office Word
MS Office Visio
Rich Partner
Extensibility
Open
Embeddable
Architecture
MS Office Share
Point
MS Office
Performance Point
2. Reporting Service

Microsoft SQL Server 2008 Reporting Services provides a complete serverbased platform that is designed to support a wide variety of reporting needs
including managed enterprise reporting, ad-hoc reporting, embedded
reporting, and web based reporting to enable organizations to deliver
relevant information where needed across the entire enterprise.

Reporting Services 2008 provides the tools and features necessary to
author a variety of richly formatted reports from a wide range of data
sources and provides a comprehensive set of familiar tools used to manage
and secure an enterprise reporting solution.
MS. SQL Server 2008
Microsoft
Reporting
Services
Authoring Report
MS. SQL Server 2008
Microsoft
Managing
Reporting Services
Delivering Reports
Authoring Report
Using Report
Development
Tools
Authoring
Report
Accessing Data
Sources for
Report Creation
Charts
Tablix
Interactive
Features
Creating
Compelling
Reports
MS. SQL Server 2008
Microsoft
Managing Reporting Services
Extending Management
Capabilities
Managing
Reporting Services
Configuring a Reporting
Services Instance
MS Office SharePoint
Services Integration
Securing Reporting
Services
MS. SQL Server 2008
Microsoft
Delivering Reports
High Performance
Report Processing
Caching
Snapshots
Delivering Reports
Multiple File
Formats
Delivering Reports
through Subscriptions
MS. SQL Server 2008
Microsoft
Embedding Reports into
Business Applications
3. Intergration Services



SQL Server 2008 Integration Services (SSIS) helps Information
Technology departments to meet data integration requirements in
their enterprises.
SQL Server 2008 Integration Services meets the challenges of
cleansing, transforming, and mapping multiple data sources with
large volumes into a useful format.
New features improve its ability to scale up and improve
performance while speeding development and lowering the TCO.
MS. SQL Server 2008
Microsoft
3. Intergration Services
Technology
Challenges
SSIS
Architecture
Organizationa
l Challenges
Economic
Challenges
MS. SQL Server 2008
Microsoft
Intergration
Services
Integration
Scenarios
Technology
Challenges
•As illustrated in Figure , with
increased staging the time taken
to “close the loop,” (i.e., to
analyze, and take action on new
data) increases as well. These
traditional ELT architectures (as
opposed to value-added ETL
processes that occur prior to
loading) impose severe
restrictions on the ability of
systems to respond to emerging
MS.
Microsoft
SQL Server
business
needs.2008
SSIS
Architecture
Task flow and data
flow engine
Pipeline architecture
ADO.NET connectivity
Thread pooling
Persistent lookups
MS. SQL Server 2008
Microsoft
SSIS for data transfer
operations
Integration
Scenarios
SSIS for data warehouse
loading
SSIS and Data Quality
Application of SSIS
Beyond Traditional ETL
SSIS, the Integration
Platform
MS. SQL Server 2008
Microsoft
SSIS for data transfer operations

SQL Server 2008 Integration Services has an improved wizard that
uses ADO.NET, has an improved user interface,
performs automatic
data type conversions,
and is more scalable
than previous versions.
MS. SQL Server 2008
Microsoft
SSIS for data warehouse loading

SQL Server 2008 includes support for Change Data Capture (CDC).

SSIS can consume data from (and load data into) a variety of
sources including managed (ADO.NET), OLE DB, ODBC, flat file,
Microsoft Office Excel®, and XML by using a specialized set of
components called adapters.
MS. SQL Server 2008
Microsoft
Figure 3 shows an
example of such a flow.
MS. SQL Server 2008
Microsoft
Figure 4
shows a
page from
the SCD
Wizard.
MS. SQL Server 2008
Microsoft

Figure 5 shows
the data flow that
is generated by
this Wizard
MS. SQL Server 2008
Microsoft
SSIS and Data Quality

One of the key features of SSIS, as well as its ability to integrate data, is its ability
to integrate different technologies to manipulate the data. This has allowed SSIS
to include innovative “fuzzy logic”–based data cleansing components.

SSIS deeply integrates with the data mining functionality in Analysis Services.
Data mining abstracts out the patterns in a dataset and encapsulates them in a
mining model.

Support for complex data routing in SSIS helps you to not only identify
anomalous data, but also to automatically correct it and replace it with better
values. This enables “closed loop” cleansing scenarios
MS. SQL Server 2008
Microsoft

Figure 6 shows
an example of a
closed loop
cleansing data
flow.
MS. SQL Server 2008
Microsoft
Application of SSIS Beyond Traditional ETL
Service Oriented
Architecture
Application of SSIS
Beyond Traditional ETL
Data and text
mining
On-demand data
source
MS. SQL Server 2008
Microsoft
On-demand data source
Figure 7
shows a
SSIS
package that
sources data
from RSS
feeds over
the Internet,
integrates
with data
from a Web
service
MS. SQL Server 2008
Microsoft

Figure 8 shows
the use of the
SSIS package as
a data source in
the Report
Wizard.
MS. SQL Server 2008
Microsoft
SSIS, the Integration Platform

SSIS goes beyond being an ETL tool not only in terms of enabling
nontraditional scenarios, but also in being a true platform for data
integration. SSIS is part of the SQL Server Business Intelligence (BI)
platform that enables the development of end-to-end BI applications.
MS. SQL Server 2008
Microsoft
Integrated
development platform
SSIS, the Integration
Platform
Programmability
Scripting
MS. SQL Server 2008
Microsoft
Integrated development platform

SQL Server Integration Services, Analysis Services, and Reporting
Services all use a common Microsoft Visual Studio® based
development environment called the SQL Server Business
Intelligence (BI) Development Studio. BI Development Studio
provides an integrated development environment (IDE) for BI
application development.
MS. SQL Server 2008
Microsoft
Integrated development platform
Figure 9
shows a BI
Development
Studio
solution that
consists of
Integration,
Analysis, and
Reporting
projects.
MS. SQL Server 2008
Microsoft
Integrated development platform
Figure 10
shows an
example of
geographic
data
visualized
using a
scatter plot
and a text
grid.
MS. SQL Server 2008
Microsoft
Figure 11 shows
an example of a
script that checks
for the existence
of an Office Excel
file.
MS. SQL Server 2008
Microsoft
Making Data Integration Approachable

Figure 12, SSIS
eliminates (or at
least minimizes)
unnecessary
staging
MS. SQL Server 2008
Microsoft