DAT301-LNC Building Operational Intelligence Solutions with Microsoft SQL Server and StreamInsight

2. DAT301-LNCBuilding Operational Intelligence Solutions with Microsoft SQL Server and StreamInsight Torsten Grabs Lead Program Manager Microsoft Corporation Craig Bauhaus Solution Architect Logica

3. Understanding Streaming Data (1) • Question: “how many red cars are in the parking lot”. • Answering with a relational database: • Walk out to the parking lot. • Count vehicles that are • Red • Cars SELECT COUNT(*) FROM ParkingLot WHERE type = ‘AUTO’ AND color = ‘RED’

4. Understanding Streaming Data (2) • What about: “How many red cars have passed the 40th street exit on the 520 in the last hour”? • Answering with a relational database: • Pull over and park all vehicles in a lot,keeping them there for an hour. • Count vehicles that are in the lot. Doesn’t seem like a great solution…

5. Understanding Streaming Data (3) • Different kinds of questions require different ways of answering them. • Answering the question with a streaming data processing engine: • Stand by the freeway, count red cars as they pass by. • Write down the answer, deliver the answer. This is the streaming data paradigm in a nutshell – ask questions about data in flight.

6. What is StreamInsight • Microsoft’s platform to build applications for streaming data • Continuous and incremental processing • High throughput, low latency • Event-driven computation • Declarative query language (LINQ) • Adapter model • Diagnostic interface • Extensibility model • Needs a SQL Server 2008 R2 License • Datacenter • Standard, Enterprise

7. 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 Re-compute (Pull) Static Reports Intra-Day Cubes Stock tickers & News feeds Historic Cubes Mining, Validation, “What-If” Scenarios

8. Latency Scenarios for Event-Driven and Streaming Applications Relational Database Applications 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.)

9. StreamInsight Platform StreamInsightApplication Development StreamInsight Application at Runtime Event sources Event targets Input Adapters Output Adapters StreamInsight Engine Devices, Sensors Pagers & Monitoring devices Standing Queries KPI Dashboards, SharePoint UI Web servers Query Logic Query Logic Trading stations Event stores & Databases Query Logic Event stores & Databases Stock ticker, news feeds

10. Virtuous Cycle: Monitor, Manage, Mine

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

12. Logica and StreamInsight Craig Bauhaus Solution Architect Logica partner

13. Logica • Logica is a business and technology service company, employing 39,000 people across 36 countries.  We deliver business consulting, systems integration and outsourcing across all industries and business functions • Logica is a leading SI for Microsoft StreamInsight and OSIsoft PI for StreamInsight • We are using this powerful pair to develop Innovative CEP solutions in our Innovation Centers • Just completed a successful Proof of Concept with Major Oil& Gas customer

14. Oil & Gas: PoC Functional Tests • Data Handling – High Frequency Data Collection • Buffer high-speed process data; detect an event and trigger the integration of high-speed data values into the historian. • Time Window - Continuous Time Window * • Detect pattern-based process data events (e.g. limit exceedance) in moving time windows and report the non-conformance immediately • Data Quality – Data Cleansing * • Analyze high speed process data and associated diagnostic information to identify suspect data; call routines to “cleanse” suspect data and output the processed data and diagnostic information; recognize critical events that need to be passed on immediately.

15. PI for StreamInsight • Enable data access to the PI System from Microsoft StreamInsight

16. PI for StreamInsight Version 1.0 • Support for • Read and write to PI points • Tag search • Snapshot input data

17. Time Window • Detect pattern-based process data events (e.g. limit exceedence) in moving time windows and report the non-conformance immediately

18. Time Window Query Design

19. Time Windows Performance • 50,000+ Windows • 15 min window w/ 5 sec updates • 24 hour window with 1 min updates • Ranging between 20k and 32k tags • Each tag updated once per second • Sustained average CPU Utilization was 70% • Real-time utilization burst and efficiently balanced

20. Data Quality Analyze high speed process data and associated diagnostic information to identify suspect data; call routines to “cleanse” suspect data and output the processed data and diagnostic information; recognize critical events that need to be passed on immediately

21. Data Quality Query

22. Data Quality PI Input Adapter Step 1 PI Input adapter feeds Process Values (data) and Quality Indicator (quality) tags into the piInput stream.

23. Data Quality Split Stream Step

24. Data Quality Turning a Quality Value into Stream

25. Data Quality Creation of New Streams

26. Data Quality Split Value goes to Secondary Process

27. Data Quality Data Cleansing Routines

28. Data Quality Cleansed Data Written to PI

29. Data Quality Alert Process

30. Data Quality Alarm Trigger

31. Data Quality Alert Trigger Code Sample // Join the alldataStream with the threshold reference stream// to create the alerting streamvaralertStream = from e inallDataStreamjointhinthresholdsStreamone.Pathequalsth.thresholdNamewheree.Value > th.valueselectnewPIEvent<Double>                {                    Annotation = e.Annotation,                    Id = e.Id,IsAnnotated = e.IsAnnotated,IsEdited = e.IsEdited,IsQuestionable = e.IsQuestionable,                    Path = e.Path.Replace(".PV", ".ALARMTRIG"),                    Status = e.Status,StatusText = e.StatusText,                    Timestamp = e.Timestamp,                    Value = e.Value                };

32. Performance

33. Oil and Gas Dashboard Demo demo

34. Summary • Operational Intelligence requires processing of streaming data • StreamInsight is Microsoft’s platform to build applications for processing streaming data • Operational intelligence has benefits in many different industries • Manufacturing, oil & gas, utilities • Financial services • Web analytics and clickstream processing • Telecommunications • Health care • Get started today with your operational intelligence solution on StreamInsight

35. Session Evaluations Tell us what you think, and you could win! All evaluations submitted are automatically entered into a daily prize draw*  Sign-in to the Schedule Builder at http://europe.msteched.com/topic/list/ * Details of prize draw rules can be obtained from the Information Desk.

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