Sql server 2008 r2 data warehousing
Download
1 / 55

SQL Server 2008 R2 Data Warehousing - PowerPoint PPT Presentation


  • 131 Views
  • Uploaded on

SQL Server 2008 R2 Data Warehousing. Ciprian Jichici General Manager Genisoft Microsoft Regional Director Romania. Agenda. Review: Key S+S Data Platform Requirements SQL Server 2008 R2: The Data Platform for S+S Warehousing – A Classic Approach Introducing StreamInsight

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'SQL Server 2008 R2 Data Warehousing' - tejano


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Sql server 2008 r2 data warehousing

SQL Server 2008 R2 Data Warehousing

Ciprian Jichici

General Manager Genisoft Microsoft

Regional Director Romania


Agenda
Agenda

  • Review: Key S+S Data Platform Requirements

  • SQL Server 2008 R2: The Data Platform for S+S

  • Warehousing – A Classic Approach

  • Introducing StreamInsight

    • Choosing a Development Model

    • Choosing a Deployment Model

  • Master Data Services




Warehousing
Warehousing

  • Fundamentals

  • SQL Server Scalability and High Performance for Warehousing

  • Clustering

  • Backup and Restore

  • Database Mirroring

  • Database Snapshots




Introducing streaminsight
Introducing StreamInsight

  • Concept of Complex Event Processing

  • Introduction to StreamInsight and Overall Architecture

  • Choosing a Development Model

  • Choosing a Deployment Model


Complex event processing
Complex Event Processing

  • Processing and querying of event data streams

  • Data queried while “in flight”

  • May involve multiple concurrent event sources

  • Works with high data rates

  • Aims for near-zero latency


Isn t this just a database application
Isn’t This Just A Database Application?

Event

request

output stream

input stream

response


Aims of complex event processing
Aims of Complex Event Processing

  • Identify from seemingly unrelated events

    • Patterns

    • Relationships

    • Gaps (Expected events that did not occur)

    • Abstractions

  • Trigger immediate response actions


Sweet spot for complex event processing
“Sweet-spot” For Complex Event Processing

Relational Database Applications

CEP Target Scenarios

Operational Analytics Applications, e.g., Logistics, etc.

Data Warehousing Applications

Web Analytics Applications

Manufacturing Applications

Financial Trading Applications

Monitoring Applications

Aggregate Data Rate (Events/sec)


Usage example capital markets
Usage Example: Capital Markets

  • Algorithmic trading

  • Smart order routing

  • Real-time profit and loss

  • Rapid analysis of transactional cost

  • Fraud detection

  • Risk management


Usage example click stream analysis
Usage Example: Click-stream Analysis

  • Often 100,000 events per second

  • Automate

    • Page Layout

    • Navigation

    • Presentation

    • Targeted Advertising


Usage example communications
Usage Example: Communications

  • Real-time network monitoring

  • Quality of service monitoring

  • Provide location-based services

  • Fraud detection

  • Intrusion detection


Usage example command intelligence
Usage Example: Command Intelligence

  • Battlefield control

  • Monitoring of resource locations

  • Intrusion detection

  • Network traffic analysis

    • Emails

    • Network traffic

    • Watch-lists

    • Financial movements


Usage example manufacturing
Usage Example: Manufacturing

  • Asset monitoring

  • Aggregation of machine-based sensor data

  • Generation of alerts in error conditions

  • Identifying the “golden batch”


Usage example casino monitoring
Usage Example: Casino Monitoring

  • Gaming machine event analysis

  • Card table analysis

    • Fraud detection

    • Profit and loss in real-time

  • Targeted advertising

    • Player behavior

    • Loyalty system implementation


Usage example mpg and virtual worlds
Usage Example: MPG and Virtual Worlds

  • Real-time monitoring

  • Managing player interest

  • Web-site traffic analysis

  • Detecting and eliminating undesired behaviors

  • Understanding behavioral patterns


Usage example public health
Usage Example: Public Health

  • Patient management

  • Outbreak management

  • Trend detection

  • Insurance risk analysis


Usage example logistics
Usage Example: Logistics

  • Vehicle management

  • Supply chain forecasting and tracking

  • Maritime logistics

  • GPS tracking


Usage example energy management
Usage Example: Energy Management

  • Monitoring

    • Consumption

    • Variations

  • Detecting outages

  • Smart grid management

  • Aggregating data across the grid


Competitive landscape
Competitive Landscape

Industry Forum: http://complexevents.com


Introducing streaminsight1
Introducing StreamInsight

  • Concept of Complex Event Processing

  • Introduction to StreamInsight and Overall Architecture

  • Choosing a Development Model

  • Choosing a Deployment Model


Microsoft streaminsight
Microsoft StreamInsight

  • Platform for development and deployment of CEP applications

  • High-throughput stream processing architecture

  • .NET based development environment


Streaminsight aims
StreamInsight Aims

  • Monitor data from multiple sources and detect

    • Meaningful patterns

    • Trends

    • Exceptions

    • Opportunities

  • Analyze data without storing it first

  • Low latency

  • Trigger response actions

  • Mine events for new business KPIs


Benefit net development environment
Benefit: .NET Development Environment

  • Use .NET languages such as C#

  • Query using LINQ

  • Take advantage of developer familiarity with .NET

  • Reduces development times (and costs)

  • Extend StreamInsight with .NET code


Benefit performance and data throughput
Benefit: Performance and Data Throughput

  • Highly parallel execution platform

  • In-memory caches

  • Incremental result computation

  • All processing triggered by incoming events

    • Avoids polling overhead

  • Can handle out-of-order events

  • Can incorporate static reference or historical data


Benefit deployment and management
Benefit: Deployment and Management

  • Multiple deployment scenarios

    • Fully integrated via embedded DLL

    • Stand-alone server (multiple apps and users)

  • Monitoring and management built-in

    • Management interface

    • Diagnostic views

    • Manageability framework allows for remote monitoring

    • Stand-alone event flow debugger


Streaminsight event data flow
StreamInsight Event Data Flow

Data Sources, Operations, Assets, Feeds, Sensors, Devices

Input Data Streams

OutputData Streams

Input Data Streams

CEP Engine

Monitor

& Record

Mine

& Design

Manage

&

Benefit

f(x)

f'(x)

g(y)

h(x,y)

Results

Deploy

History

Operational Data Store & Archive

CEP Engine

f(x)

g(y)

f'(x)

h(x,y)



Streaminsight deployment alternatives
StreamInsight Deployment Alternatives

CEP

CEP for lightweight processing and filtering

CEP

CEP for aggregation and correlation of in-flight events

CEP

CEP for complex analytics including historical data

Web servers

Sensors

CEP

CEP

Feeds

Devices

CEP

CEP

CEP

CEP

CEP

CEP

CEP

Complex Analytics &

Mining



Demo scenario highway monitor
Demo Scenario: Highway Monitor

  • Major highway

  • 8 Lanes

  • 2 Directions

  • 6 Toll-Points

  • Vehicle Types

    • Car

    • Bus

    • Truck

    • Ambulance

    • Taxi


Toll point timings
Toll-Point Timings

  • Vehicles are measured multiple times

  • Electronic tag captured

  • Speed is measured

  • Vehicle type is determined


Toll point measurements
Toll-Point Measurements

  • EventID (guid)

  • TollPointId (which toll-point) – 0 to 5

  • DirectionId (which direction) – 0 (North) or 1 (South)

  • Lane (which lane) – 0 to 7

  • VehicleTypeId – Emuneration

    • (0-car, 1-bus, 2-truck, 3-taxi, 4-ambulance)

  • TagId – vehicle’s individual tag

  • EnterGate – datetime when vehicle entered gate

  • MillisecondsToPassSpeedCheckPoint

    • How long did vehicle take to travel 10m

  • ExitGate – datetime when vehicle exited gate


Agenda1
Agenda

  • Concept of Complex Event Processing

  • Introduction to StreamInsight and Overall Architecture

  • Choosing a Development Model

  • Choosing a Deployment Model


Streaminsight development models
StreamInsight Development Models

  • Three development models

    • Implicit Server

    • Explicit Server

    • IObservable/IObserver


Implicit server development model
Implicit Server Development Model

  • Easiest development model to use

  • Hides most complexity

  • StreamInsight server acts as an implicit host

    • Creates and registers required objects

  • Allows developers to focus on query logic

    • Good test environment for query developers


Implicit server process
Implicit Server: Process

  • Server created implicitly for each query

  • Query is automatically hosted

    • Stored in memory, not on stable storage (disk)

  • Developer provides:

    • Input/output adapter bindings

    • Query logic and binding

  • Server manages objects

    • Creates event type (accesses it via reflection)

    • Registers input/output adapters

    • Registers query template and query

    • Creates objects when query started

    • Disposes objects when query is disposed


Implicit server query example
Implicit Server: Query Example

  • varinputConf = newTextFileInputConfig("MyData.csv");

  • varoutputConf = newTextFileOutputConfig("Result.csv");

  • varinputStream = CepStream<PayloadType>.Create("inputStream",

  • typeof(TextFileInputFactory),inputConf,

  • EventShape.Point);

  • CepStream<PayloadType> filter = from e ininputStream

  • wheree.value > 10

  • select e;

  • Query query = filter.ToQuery(typeof(TextFileOutputFactory),

  • outputConf,

  • EventShape.Interval,

  • StreamEventOrder.FullyOrdered);

  • query.Start();

  • // perform work or wait for output adapter to signal complete

  • query.Stop();


Explicit server development model
Explicit Server Development Model

  • Developer explicitly creates and registers all objects

  • Provides complete control of

    • StreamInsight application

    • Development environment (via client-side object model API)

  • Allows for reuse of

    • Queries

    • Adapters

    • Event Types

    • 3rd party Query Templates


Explicit server process
Explicit Server: Process

  • Code creates or connects to server

  • Code creates all objects and registers them into server

    • Query Template

    • Query Instance

    • Event Types

    • Input/Output Adapters

  • Server can store metadata in memory or persist to disk

  • Server can run locally or remotely via web service


Explicit server query example 1 of 2
Explicit Server: Query Example 1 of 2

Server server = Server.Create();

Applicationapplication = server.CreateApplication("app1");

InputAdapteria =

application.CreateInputAdapter<MyInputAdapterFactory>(

"InputAdapterName", "Some description");

OutputAdapteroa =

application.CreateOutputAdapter<MyOutputAdapterFactory>(

"OutputAdapterName", "Some other description");

CepStream<PayloadType> inputStream

= CepStream<PayloadType>.Create("inputStream");

varhighWaterMarkAlert = from e ininputStream

wheree.value > 99

select e;

Server server = Server.Create();

Applicationapplication = server.CreateApplication("app1");

InputAdapteria =

application.CreateInputAdapter<MyInputAdapterFactory>(

"InputAdapterName", "Some description");

OutputAdapteroa =

application.CreateOutputAdapter<MyOutputAdapterFactory>(

"OutputAdapterName", "Some other description");

CepStream<PayloadType> inputStream

= CepStream<PayloadType>.Create("inputStream");

varhighWaterMarkAlert = from e ininputStream

wheree.value > 99

select e;

Server server = Server.Create();

Applicationapplication = server.CreateApplication("app1");

InputAdapteria =

application.CreateInputAdapter<MyInputAdapterFactory>(

"InputAdapterName", "Some description");

OutputAdapteroa =

application.CreateOutputAdapter<MyOutputAdapterFactory>(

"OutputAdapterName", "Some other description");

CepStream<PayloadType> inputStream

= CepStream<PayloadType>.Create("inputStream");

varhighWaterMarkAlert = from e ininputStream

wheree.value > 99

select e;


Explicit server query example 2 of 2
Explicit Server: Query Example 2 of 2

QueryTemplate qt1 = application.CreateQueryTemplate(

"highWaterMarkAlert", highWaterMarkAlert);

QueryBinderqb = newQueryBinder(qt1);

qb.BindProducer("inputStream", ia, newMyInputAdapterConfig(),

EventShape.Interval);

qb.AddConsumer("queryresult", oa, newMyOutputAdapterConfig(),

EventShape.Point, StreamEventOrder.FullyOrdered);

Query q1 = application.CreateQuery(

"query1", qb, "Query Description");

q1.Start();


Explicit server connect to existing server
Explicit Server: Connect To Existing Server

// Connect to a remote CEP server.

Server server = Server.Connect(

newSystem.ServiceModel.EndpointAddress(

@"http://localhost:12345/CEP"));

// Retrieve the application app1 from the server.

Applicationapplication = server.Applications["app1"];

// Retrieve existing query template, qt1, from server.

QueryTemplate qt1 = application.QueryTemplates["qt1"]; 


Iobservable iobserver development model
IObservable/IObserver Development Model

  • Alternate to using Input/Output adapters

  • Observer is an object that wishes to be notified when another object’s state changes

  • Observable is an object whose state changes can be requested via registration

  • Avoids the need to define temporal properties of events

  • Works well with existing event-driven applications

    • Use StreamInsight to provide real-time analysis and insight


Iobservable iobserver process
IObservable/IObserver: Process

  • Application must implement

    • IObservable class for event producer

    • IObserver class for event consumer

  • StreamInsight server is configured to use these classes

  • Queries are created and bound


Iobservable iobserver example code
IObservable/IObserver: Example Code

// Connect to a remote CEP server.

Server server = Server.Connect(

newSystem.ServiceModel.EndpointAddress(

@"http://localhost:12345/CEP"));

// Retrieve the application app1 from the server.

Applicationapplication = server.Applications["app1"];

// Retrieve existing query template, qt1, from server.

QueryTemplate qt1 = application.QueryTemplates["qt1"]; 


Agenda2
Agenda

  • Concept of Complex Event Processing

  • Introduction to StreamInsight and Overall Architecture

  • Choosing a Development Model

  • Choosing a Deployment Model


Streaminsight deployment scenarios
StreamInsight Deployment Scenarios

  • Full integration into host application as embedded DLL

  • Stand-alone server

    • Multiple applications

    • Multiple users

    • Can be packaged as a Windows Service


Deployment hosted dll
Deployment: Hosted DLL

  • Choose this option for any of these scenarios:

    • Using Implicit Server development model

    • Relying on IObservable/IObserver programming paradigm

    • Need to minimize memory footprint

    • Wish to use a single process for both application and CEP server

    • Wish to tightly control access to the CEP server

  • Do not choose this option if:

    • You require shared access to the metadata

    • You require shared access to the streaming event data


Deployment stand alone server
Deployment: Stand-alone Server

  • Choose this option for any of these scenarios:

    • Metadata objects need to be shared between applications

      • Event Types

      • Adapter Types

      • Query Templates

    • A data source registered with the server provides an event stream for another existing application

  • StreamInsightHost.exe can be the server host


Deployment other potential scenarios
Deployment: Other Potential Scenarios

  • StreamInsight-enabled device

  • Building operational intelligence