Transcript SQL Server 2005 for Developers

SQL Server 2005 for
Developers Pt 2
Donald Farmer
Grou Program Manager
SQL Server
Agenda

SQL Server 2005 Business
Intelligence Overview

Reporting Services

Data Transformation Services
SQL Server 2005 Business Intelligence
Analysis Services
OLAP & Data Mining
Data Transformation
Services
ETL
SQL Server
Relational Engine
Management Tools
Development Tools
Reporting Services









High availability for enterprise
applications
Key security and performance features
Focus on self manageability and
optimization
Integration with Visual Studio and .NET
Native XML technology
Interoperability via Web Services
Real time decision making: reporting, data
mining
Rich client and SharePoint solutions
Comprehensive ETL solution: up to 600%
faster
Reporting Services
SQL Server 2005 Business Intelligence


Reporting Services
Analysis Services
OLAP & Data Mining
Data Transformation
Services
ETL
SQL Server
Relational Engine
Management Tools

Traditional and
interactive reports
Scalable, manageable
and embeddable
server infrastructure
Integration with
SharePoint Portal
Server, Office, browser
and other familiar
tools
Support for relational,
OLAP, XML and other
data sources
Development Tools

Programmatic Interfaces

Customizable XML report definition


Viewing Interfaces



URL Addressability
Web Service / SOAP
Management Interfaces



Open Schema
Web Service / SOAP
WMI Interface
Extension Interfaces

Data, Delivery, Rendering and Security
Generating Reports



Application specific report authoring
requirements
Reports are defined in Report
Definition Language (RDL), a
customizable XML format
Use .NET XML classes to generate
report definitions

Leverage RDL Schema Description
(XSD)
URL Addressability
Virtual Roots

Report Server
http://[servername]/ReportServer



Web Service Interface
Parameter directives
Report Server Web Application
http://[servername]/Reports


SSL Support
Fully localized

Based on language in HTTP header
Web Service Interfaces






Namespace
Management
Item Properties
Report Execution
Report Parameters
Report History
Data Source
Management





Scheduling
Subscriptions and
Delivery
Linked Reports
Job Management
Security
Management
Extensibility

Custom Report Assemblies


Data Extensions


Delivers reports over different protocols and to
different devices
Rendering Extensions


Communicates to data sources and returns
data
Delivery Extensions


Custom classes, functions used in report
expressions
Renders to specific formats and devices
Security Extensions

Authorizes users to custom authorization
schemes
Custom Report Assembly




Assemblies can be referenced by
report to provide user-defined
functions
Build code in any .NET Language
Expressions can refer to functions
Code can be debugged inside Report
Designer
Data Extensions

Data Processing Extensions





Input: connection information, a query
Output: return a result set
Optional: parameter values
Any ADO.Net managed provider will just work
Interfaces

Subset of the .NET Managed Data Provider
(System.Data)





See http://msdn.microsoft.com/library/enus/cpref/html/frlrfSystemData.asp
Required: IDbConnection, IDbCommand,
IDataParameter, IDataReader
Recommended: IDbConnectionExtension
Optional: extended Data Extension interfaces
Generic query designer in Report Designer
Rendering Extensions

Why write one:



Rendering format that we do not ship
(.XML binary format)
Support a specialized use of a format
(CHMTL)
Why not to write one:


“If only I could tweak how the HTML…”
“I need to apply this logic rendering
time…”
Demos



Building a report in the BI Dev
Studio
Creating a report in Delphi
Managing Reports
Reporting Services Momentum

Amazing Launch Event





Over 4,500 viewed the webcast live
Over 9,500 have registered and viewed the ondemand webcast
Over 100,000 downloaded since January
Over 14 partners immediately offering
complementary solutions
Customers deploying and getting business
value today with Reporting Services:

Menlo Worldwide, PREMIER Bankcard, Inc.,
Cox Communications, Coldwater Creek, ASB
Bank
Data Transformation
Services
DTS Themes





Transformation
Integration
Beyond traditional ETL
Management
Development
Transformation







N-source, N-destination pipeline
Many prebuilt transformations
Custom and scripted transformations
Complex data flow with splits and
merges
Aggregation and sorting in the ETL
process
Error-handling and fix up
SCD support, Pivoting, built in for BI
Integration








Web Service and XML support
Load UDMs directly from data flow
Reporting Services integration
Message Queue for inter-application
messaging
WMI for cross-server eventing
ADO.NET, OLEDB sources
SAP connector
Simplify custom Data Sources
Beyond Traditional ETL
Before
1.
2.
3.
4.
5.
Collect, integrate and transform data
Stage & load warehouse
Apply analytics for intelligence
Report on analytics
Maintain warehouse for history and ad-hoc
reporting
After
1.
2.
Collect, integrate, transform, apply analytics,
load warehouse and run reports in one
process.
Maintain warehouse for history and ad-hoc
reporting
Management




Restart and checkpointing
Certificate signing for security
Comprehensive Logging
Simplified deployment of complex
processes


DTS Service


Flexible configurations for changing
environments
Handles persistence and monitoring
Integrated management studio


For multiple DTS Services
For DTS and other servers
DTS Extensibility






Native or managed
code
Custom
connectivity,
Custom tasks
Custom
transformations
Highly extensible
objects
Fully embeddable
engine
Packages and Tasks

Package

Basic unit of execution









Smallest Unit of Work
Individually successful or unsuccessful
Disabled or enabled
Constraint relationships between tasks
Rich set of “out of the box” tasks


Consists of one or more tasks
Persisted as XML
Tasks


Immediate or scheduled (using SQL Agent)
Including File System, Web Service and XML
… (more later)
Script task for quick extensibility
Custom tasks for re-use
Loops and containers

Containers


Functional grouping of tasks
Loops and enumerators

For Loop


For Each Loop


Uses an expression
Uses an Enumerator – e.g., traversing an object
model
Sequences

Arbitrary grouping of tasks
Data Flow Task


The “pipeline”
A very special task




Provides high end transformation
Participates in overall task flow
Takes advantage of runtime
infrastructure
Extensible component architecture

Rich set of transformations out-of-thebox
DTS Data Flow

An arbitrary graph of components






Source-to-Destination
Multiple Sources
Multiple Transforms
Multiple Destinations
N-sources, N-transformations, N-destinations
Source-independent buffer architecture

Enables plugging in any rowset-based operations, even
on non-traditional source data



Data mining, cleansing and text mining
Web services and XML integration
Multiple transformations performed in-place,
rather than copy-transform-copy...

Execution plan maximizes concurrency

Error flows from pipeline components

High perf aggregation and sorting
Demos

Complex data flow

RSS feed text mining
Creating a package in code
using System;
using Microsoft.SqlServer.Dts.Runtime;
namespace Microsoft.SqlServer.Dts.Samples
{
class CreatingAPackage
{
Package package;
public CreatingAPackage()
{
package = new Package();
}
}
}
Adding connections in code
using System;
using Microsoft.SqlServer.Dts.Runtime;
namespace Microsoft.SqlServer.Dts.Samples
{
public class AddingConnections
{
private Package p;
private ConnectionManager ConMgr;
public AddingConnections()
{
p = new Package();
ConMgr = p.Connections.Add("OLEDB");
ConMgr.ConnectionString = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Initial Catalog=AdventureWorks;Data
Source=(local);";
ConMgr.Name = "OLEDBConnectionManager";
ConMgr.Description = "Connection to the AdventureWorks
database.";
}
}
}
Adding tasks in code
using System;
using Microsoft.SqlServer.Dts.Runtime;
namespace Microsoft.SqlServer.Dts.Samples
{
public class AddingTasks
{
Package p;
public AddingTasks()
{
p = new Package();
Executable efileTask1 = p.Executables.Add("STOCK:FileSystemTask");
TaskHost thFileTask1 = efileTask1 as TaskHost;
Executable efileTask2 = p.Executables.Add("STOCK:FileSystemTask");
TaskHost thFileTask2 = efileTask2 as TaskHost;
PrecedenceConstraint pcFileTasks =
p.PrecedenceConstraints.Add((Executable)thFileTask1,
(Executable)thFileTask2);
pcFileTasks.Value = DTSExecResult.Completion;
}
}
}
DTS Summary


Beyond traditional ETL tools
Scalable and complex data integration:

Take full advantage of mainstream hardware






Scale-up to enterprise requirements
“Scale-down” enterprise capabilities to SMBs
Smart data handling close to the source
Integrate emerging data needs with
traditional BI strengths
Enterprise capable


Fully leverage Windows platform
Rich task flow and job management infrastructure
Embeddable and extensible
DTS Themes





Transformation
Integration
Beyond traditional ETL
Management
Development
SQL Server Community sites
http://www.microsoft.com/sql/community/default.mspx
List of newsgroups
http://www.microsoft.com/sql/community/newsgroups/default.
mspx
Locate Local User Groups
http://www.microsoft.com/communities/usergroups/defa
ult.mspx
Attend a free chat or web cast
http://www.microsoft.com/communities/chats/default.mspx
http://www.microsoft.com/usa/webcasts/default.asp
Questions?
[email protected]
© 2003-2004 Microsoft Corporation. All rights reserved.
This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.
Further Information
[email protected]