Real-Time Business Intelligence with Microsoft SQL Server 2008 R2

1. Required Slide SESSION CODE: BIE403 Real-Time Business Intelligence with Microsoft SQL Server 2008 R2 Jesus Rodriguez Chief Architect, Tellago Microsoft Architect Advisor Microsoft MVP Oracle SOA ACE

2. About me… • Chief Architect, Tellago, Inc • Microsoft advisor • Microsoft MVP • Oracle SOA ACE • Speaker, author • http://weblogs.asp.net/gsusx

3. Agenda • The challenges of real time business intelligence • Introducing Microsoft StreamInsight • Real Time BI patterns • Demo, Demo, Demo, Demo, Demo

4. Real time business intelligence?

5. Real-time Business Intelligence systems are event driven, and use Event Stream Processing techniques to enable events to be analysed without being first transformed and stored in a database. These in- memory techniques have the advantage that high rates of events can be monitored, and since data does not have to be written into databases data latency can be reduced to milliseconds.

6. Capabilities?

7. Event Driven

8. Continuous queries

9. Time Sensitive

10. Low Latency

11. High Volume

12. Event processing is the foundation of real time BI…

13. Scenarios?

14. Financial Sector

15. Call Centers

16. RFID Systems

17. Web Analytics

18. What are the challenges of traditional (batch) BI in real time scenarios?

19. Query Overloading

20. Agility

21. ETL Dependencies Has the ETL run yet?

22. Report Abuse

23. Ok I got it…..How do I implement something like that on the Microsoft platform?

24. Real time event processing was the missing piece….

25. Introducing Microsoft StreamInsight

26. Microsoft StreamInsight is a platform that you use to develop and deploy complex event processing (CEP) applications. Its high-throughput stream processing architecture and the Microsoft .NET Framework-based development platform enable you to quickly implement robust and highly efficient event processing applications.

27. Microsoft StreamInsight Event Event Event Event Event Event Event Event Event .NET C# LINQ CEP Application Development IDE Event targets Event sources CEP Application at Runtime CEP 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 27

28. Developing Your First StreamInsight Application DEMO

29. Events • Events expose different temporal characteristics • Point in time events • Interval events with fixed duration • Interval events with initially unknown duration • Rich payloads capture all properties of an event d c e Payload/ value  b a t5 t3 t4 t1 t2 Time 

30. Event Types • Events in Microsoft’s CEP platform use the .NET type system • Events are structured and can have multiple fields • Fields are typed using the .NET framework types • CEP engine provisioned timestamp fields capture all the different temporal event characteristics • Event sources populate time stamp fields

31. Event Streams & Adapters • A stream is a possibly infinite sequence of events • Insertions of new events • Changes to event durations • Stream characteristics: • Event/data arrival patterns • Steady rate with end-of-stream indication • Intermittent, random, or in bursts • 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

32. 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) };

33. Time Windows Tumbling Window Time

34. Time Windows Hopping Window Time

35. StreamInsight Hosting Models Hosted server mode In-process server mode Application CEP Server CEP in-process server (explicit model) Application CEP explicit logic

36. Real Time BI Patterns with Microsoft StreamInsight

37. Real Time Distributed Event Processing • The event processing logic leaves on the StreamInsight Server • Clients are able to start and stop queries • Multiple clients subscribe to the same even types StreamInsight Server Start Query UI application Query Events Start Query Input Adapter Output Adapter Events UI application

38. Real Time Distributed Event Processing DEMO

39. Aggregations and Windowing • The aggregation formulas are calculated in the StreamInsight Server • Aggregations are processed using different time windows • The logic can be extended with custom aggregates StreamInsight Server Query Start Query UI application Events Input Adapter Output Adapter

40. Real Time Event Windows and Aggregations DEMO

41. Real Time Event Replay • The StreamInsight server tracks the events processed by the different input adapters • The client is able to replay events based on specific time windows • The StreamInsight server uses a replay adapter to reprocess specific events StreamInsight Server Query Replay UI application Events Input Adapter Output Adapter Event tracking

42. Real Time Event Replay DEMO

43. Self Service BI + Real Time Event Processing • The StreamInsight server tracks the events processed by the different input adapters • The PowerPivot UI polls the data into its own database • The user can use the data to calculate specific aggregations StreamInsight Server Query PowerPivot UI Input Adapter Output Adapter PowerPivot UI Event tracking

44. Self-Service Event Analysis with StreamInsight and PowerPivot DEMO

45. Conclusions • Event processing is the foundation of real time business intelligence • Microsoft StreamInsight enables complex event processing capabilities on the Microsoft platform • Take into account real time business intelligence patterns such as time windows aggregations, replays, event subscriptions, etc • Technologies like Microsoft PowerPivot can complement the capabilities of StreamInsight with self-service data mechanisms

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

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

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

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

50. Required Slide