SQL Server 2008 R2 Data Warehousing

1. SQL Server 2008 R2 Data Warehousing Ciprian Jichici General Manager Genisoft Microsoft Regional Director Romania

2. 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

3. Review: Key S+S Data Platform Requirements

4. SQL Server 2008 R2: The Data Platform for S+S * New offering

5. Warehousing • Fundamentals • SQL Server Scalability and High Performance for Warehousing • Clustering • Backup and Restore • Database Mirroring • Database Snapshots

6. Warehousing – A Classic Approach Demo

7. SQL Server 2008 R2: Storage and Search

8. Introducing StreamInsight • Concept of Complex Event Processing • Introduction to StreamInsight and Overall Architecture • Choosing a Development Model • Choosing a Deployment Model

9. 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

10. Isn’t This Just A Database Application? Event request output stream input stream response

11. Aims of Complex Event Processing • Identify from seemingly unrelated events • Patterns • Relationships • Gaps (Expected events that did not occur) • Abstractions • Trigger immediate response actions

12. “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)

13. Usage Example: Capital Markets • Algorithmic trading • Smart order routing • Real-time profit and loss • Rapid analysis of transactional cost • Fraud detection • Risk management

14. Usage Example: Click-stream Analysis • Often 100,000 events per second • Automate • Page Layout • Navigation • Presentation • Targeted Advertising

15. Usage Example: Communications • Real-time network monitoring • Quality of service monitoring • Provide location-based services • Fraud detection • Intrusion detection

16. Usage Example: Command Intelligence • Battlefield control • Monitoring of resource locations • Intrusion detection • Network traffic analysis • Emails • Network traffic • Watch-lists • Financial movements

17. Usage Example: Manufacturing • Asset monitoring • Aggregation of machine-based sensor data • Generation of alerts in error conditions • Identifying the “golden batch”

18. 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

19. Usage Example: MPG and Virtual Worlds • Real-time monitoring • Managing player interest • Web-site traffic analysis • Detecting and eliminating undesired behaviors • Understanding behavioral patterns

20. Usage Example: Public Health • Patient management • Outbreak management • Trend detection • Insurance risk analysis

21. Usage Example: Logistics • Vehicle management • Supply chain forecasting and tracking • Maritime logistics • GPS tracking

22. Usage Example: Energy Management • Monitoring • Consumption • Variations • Detecting outages • Smart grid management • Aggregating data across the grid

23. Competitive Landscape Industry Forum: http://complexevents.com

24. Introducing StreamInsight • Concept of Complex Event Processing • Introduction to StreamInsight and Overall Architecture • Choosing a Development Model • Choosing a Deployment Model

25. Microsoft StreamInsight • Platform for development and deployment of CEP applications • High-throughput stream processing architecture • .NET based development environment

26. 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

27. 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

28. 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

29. 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

30. 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)

31. StreamInsight Architecture

32. 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

33. End to End Solution Demo

34. Demo Scenario: Highway Monitor • Major highway • 8 Lanes • 2 Directions • 6 Toll-Points • Vehicle Types • Car • Bus • Truck • Ambulance • Taxi

35. Toll-Point Timings • Vehicles are measured multiple times • Electronic tag captured • Speed is measured • Vehicle type is determined

36. 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

37. Agenda • Concept of Complex Event Processing • Introduction to StreamInsight and Overall Architecture • Choosing a Development Model • Choosing a Deployment Model

38. StreamInsight Development Models • Three development models • Implicit Server • Explicit Server • IObservable/IObserver

39. 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

40. 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

41. 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();

42. 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

43. 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

44. 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;

45. 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();

46. 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"]; 

47. 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

48. 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

49. 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"]; 

50. Agenda • Concept of Complex Event Processing • Introduction to StreamInsight and Overall Architecture • Choosing a Development Model • Choosing a Deployment Model