Microsoft SQL Server 2008 R2 StreamInsight : Come See the Value of Real-Time Data Management

1. Required Slide SESSION CODE: DAT204 Microsoft SQL Server 2008 R2 StreamInsight: Come See the Value of Real-Time Data Management Torsten Grabs Senior Program Manager Lead Microsoft Corporation

2. The Value of Timely Analytics $ value of analytics Web Analytics – Ad placement, Financial Services, Smart Grids, Monitoring – Systems mgmt, Health Care, Manufacturing, etc. Forecasting in Enterprises Historical Trend Analysis Time of interest Present

3. Current Products for Analytics Load barrier is dictated by current choices of the solution, e.g., loading into databases, persisting into files. This is intrinsic because in current approaches no processing can be done till the data is loaded. Facts/sec. Custom-built solutions that carry huge development and customization costs Active DW analytics Traditional DW Analytics Time of interest Present ET time in ETL Load time in ETL

4. Operational Intelligence Platform Visualization Distribution Processing Caching Data Bus Sources Refresh (Push) Microsoft StreamInsight Operational Analytics Cache Operational Dashboard (Ticking - Snapshot) Message Bus Devices, Sensors Reference Data Refresh(Push) Automated Decisions Reporting Dashboard (Refreshed) Web servers In-memory Database ETL Re-compute (Pull) Static Reports Intra-Day Cubes Stock tickers & News feeds ETL Service Broker Historic Cubes Mining, Validation, “What-If” Scenarios

5. The Need for an Event-Driven Platform Event Analytical results need to reflect important changes in business reality immediately and enable responses to them with minimal latency request output stream input stream response

6. Latency Scenarios for Event-Driven Applications 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.)

7. Overview: Microsoft StreamInsight Event Event Event Event Event Event Event Event Event .NET C# LINQ Application Development Event targets Event sources Application at Runtime StreamInsight Engine Devices, Sensors Pagers & Monitoring devices Standing Queries Output Adapters Input Adapters Web servers KPI Dashboards, SharePoint UI Trading stations Event stores & Databases Stock tickers & News feeds Event stores & Databases Static reference data

8. Virtuous Cycle: Monitor, Manage, Mine

9. Example Scenarios • Manufacturing: • Sensor on plant floor • React through device controllers • Aggregated data • 10,000 events/sec • Power, Utilities: • Energy consumption • Outages • Smart grids • 100,000 events/sec • Web Analytics: • Click-stream data • Online customer behavior • Page layout • 100,000 events /sec • Financial Services: • Stock & news feeds • Algorithmic trading • Patterns over time • Super-low latency • 100,000 events /sec Asset Instrumentation for Data Acquisition, Subscriptions to Data Feeds Data Stream Data Stream Visual trend-line and KPI monitoring Batch & product management Automated anomaly detection Real-time customer segmentation Algorithmic trading Proactive condition-based maintenance Stream Data Store & Archive Asset Specs & Parameters Event Processing Engine • Threshold queries • Event correlation from multiple sources • Pattern queries Lookup

10. Power Utilities • Scenario: Smart grid • Instrument households with smart power meters • Continuous, up-to-date insight into your grid, including generation, distribution, and demand • StreamInsight advantage • Scales to smart grids requirements • Scale to millions of meters • Hundreds of thousands of meter readings per second • Write validation, editing, estimation (VEE) rules declaratively in LINQ • Scale to the high data volumes expected in smart grids • React in almost real-time to changing grid conditions to avoid power outages

11. Financial Services • Scenario: Real-time RiskContinuous insight into market conditions and exposure • Continuous low-latency market monitoring • Manage risks across traders and per desk with aggregate and individual thresholds • StreamInsight advantage: • Implement risk monitoring declaratively in LINQ • Detect and notify in near real-time on risk • No change to models or LINQ code necessary for back-testing over historical data

12. Web Analytics • Scenario: Real-time Behavioral Targeting • Continuously analyze online behavior per user • Identify relevant content before the next click • Define content behind next click based on detected online behavior • StreamInsight advantage: • Scale to millions of concurrent online users • Immediate insight - real time analytics • Web logs no longer processed offline in batches • Correlate across your web farms and applications

13. Retail (Online and Traditional) • Scenario: Real-Time Coupon • Provide most relevant/appealing coupon • Maximize expected individual customer revenue • Correlate current sales transaction with customer purchase history • StreamInsight advantage • Track current market basket as a real-time stream • Use StreamInsight lookup pattern to correlate current market basket with purchase history • Easily scale to internet retail with millions of concurrent sessions

14. Event Types • StreamInsight events in use the .NET type system • Events are structured and can have multiple fields • Fields are typed using the .NET framework types • StreamInsight engine provisioned timestamp fields capture all the different temporal event characteristics • Event sources populate time stamp fields • All calculations done based on “business time”

15. Event Streams & Adapters • A stream is a sequence of events • Defined over a .NET type • Possibly infinite • Stream characteristics: • Event/data arrival patterns (steady, bursty) • Out of order events: Order of arrival of events does not match the order of their application timestamps • Adapters • Receive/get events from the data source • Enqueue events for processing in the engine • Insertions of new events • Changes to event durations

16. StreamInsight Query Features • Operators over streams • Calculations (PROJECT) • Correlation of streams from different data sources (JOIN) • Check for absence of activity with a data source (EXISTS) • Selection of events from streams (FILTER) • Stream partitioning (GROUP & APPLY) • Aggregation (SUM, COUNT, …) • Ranking and heavy hitters (TOP-K) • Temporal operations: hopping window, sliding window • Extensibility – to add new domain-specific operators

17. LINQ Query Examples LINQ Example – JOIN, PROJECT, FILTER: from e1 in MyStream1 join e2 in MyStream2 on e1.ID equals e2.ID where e1.f2 == “foo” select new { e1.f1, e2.f4 }; Filter Project &Aggregate Project Window Grouping Join LINQ Example – GROUP&APPLY, WINDOW: from e3 in MyStream3 group e3 by e3.i intoSubStream fromwin inSubStream.HoppingWindow( FiveMinutes,ThreeSeconds) selectnew { i = SubStream.Key, a = win.Avg(e => e.f) };

18. Real-time Data Management in Action DEMO

19. Extensibility SDK • Built-in operators do not cover all functionality • Need for domain-specific extensions • Integrate with functionality from existing libraries • Support for extensions in the CEP platform: • User-defined operators, functions, aggregates • Code written in .NET, deployed as .NET assembly • Query operators and LINQ can refer to functionality of the assembly • Temporal snap-shot operator framework • Interface to implement user-defined operators • Manages operator state and snapshot changes • Framework does the heavy lifting to deal with intricate temporal behavior such as out-of-order events

20. StreamInsight Deployment Alternatives Stream-Insight CEP for lightweight processing and filtering Stream-Insight CEP for aggregation and correlation of in-flight events Stream-Insight CEP for complex analytics including historical data • Event processing engines are deployed at multiple places on different scales • At the edge – close to the data source • In the mid-tier – consolidate related data sources, • In the data center – historical archive, mining, large scale correlation. Web servers Sensors Stream-Insight Stream-Insight Feeds Devices Stream-Insight Stream-Insight Stream-Insight Stream-Insight Stream-Insight Stream-Insight Complex Analytics & Mining

21. StreamInsight Deployment • Lightweight embedded engine • StreamInsight is available as a set of DLLs • StreamInsight can be included into your applications • Low footprint, small overhead • Facilitates deployments close to the data source • StreamInsight Windows service • Runs the engine as a Windows service • Applications can share incoming streams • Well-suited for more centralized deployments • Installation • Small, lightweight MSI • Installs in 2 minutes

22. SQL Server 2008 R2 Capabilities by Edition Parallel Data Warehouse Standard Datacenter Enterprise Workload

23. StreamInsight Solutions

24. Recap: Microsoft StreamInsight Event Event Event Event Event Event Event Event Event Development experience with .NET, C#, LINQ and Visual Studio 2008 CEP Application Development CEP platform from Microsoft to build event-driven applications Event targets Event sources Event-driven applications are fundamentally different from traditional database applications: queries are continuous, consume and produce streams, and compute results incrementally CEP Engine Standing Queries Output Adapters Input Adapters Flexible adapter SDK with high performance to connect to different event sources and sinks The CEP platform does the heavy lifting for you to deal with temporal characteristics of event stream data Static reference data

25. For More Information • StreamInsight main page & download : http://www.microsoft.com/sqlserver/2008/en/us/R2-complex-event.aspx • StreamInsight blog: http://blogs.msdn.com/streaminsight/ • StreamInsight MSDN documentation: http://msdn.microsoft.com/en-us/library/ee362541(SQL.105).aspx • StreamInsight E-clinics on Microsoft e-learning https://www.microsoftelearning.com/eLearning

26. Required Slide Speakers, please list the Breakout Sessions, Interactive Sessions, Labs and Demo Stations that are related to your session. Related Content • ASI07-INT | Real-Time Event Integration with Microsoft SQL Server 2008 R2 StreamInsight and Microsoft BizTalk Server • BIE202 | Data Integration at Microsoft: Technologies and Solution Patterns • BIP302 | Enabling Real-time Business Insight, Analytics and Reporting • DAT23-HOL | Querying Events in Microsoft SQL Server 2008 R2 StreamInsight Using LINQ • DAT20-HOL | Working with the Microsoft SQL Server 2008 R2 StreamInsight Event Flow Debugger • Microsoft SQL Server Mission Critical Booth

27. Required Slide Track PMs will supply the content for this slide, which will be inserted during the final scrub. DAT Track Scratch 2 Win • Find the DAT Track Surface Table in the Yellow Section of the TLC • Try your luck to win a Zune HD • Simply scratch the game pieces on the DAT Track Surface Table and Match 3 Zune HDs to win

28. Required Slide Resources Learning • Sessions On-Demand & Community • Microsoft Certification & Training Resources www.microsoft.com/teched www.microsoft.com/learning • Resources for IT Professionals • Resources for Developers • http://microsoft.com/technet • http://microsoft.com/msdn

29. Required Slide Complete an evaluation on CommNet and enter to win!

30. Sign up for Tech·Ed 2011 and save $500 starting June 8 – June 31st http://northamerica.msteched.com/registration You can also register at the North America 2011 kiosk located at registrationJoin us in Atlanta next year

31. © 2010 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

32. Required Slide