EVENT DRIVEN CUBE MONITORING.

1. EVENT DRIVEN CUBE MONITORING. David Prime & David Elliott SQLBits 6

2. WHO WE ARE. Who we are • David Prime – Betfair Research • David Elliott – Information Management & Analytics Architect Betfair • Launched June 2000 around an exchange betting platform • You can bet that an outcome will happen (back) or that it won't happen (lay). • You can choose the odds at which you want to play. • You can bet whilst the game is in play. • You can play on a range of products and games other than sports wagering What this means in terms of data • Bets: >5 million bets daily • Latency: 99.9% bets processed <1 sec • More trades than all of the European stock exchanges combined

3. OUR OBJECTIVES. Background • Early look at StreamInsight in Deep-dive • Architectural direction: EDSOA • Real Time requirements: Anti-Fraud, Legislation, Exposure Monitoring • BI / OI • Analytics API / Continuous ETL Cube Monitoring • A good use-case and an opportunity to assess using SI with the rest of the BI stack • Provide light-weight aggregated usage information for the business Real-Time • Alerts: name and shame greedy users, discover broken code • Aggregate session data • Using time windows to run complex monitoring scenarios

4. COMPLEX EVENT PROCESSING. Betfair is awash with events Your online business probably is too

5. COMPLEX EVENT PROCESSING.

6. STREAMINSIGHT. • What is StreamInsight? • New • Integrated • Fast • Improving

7. OVERVIEW. StreamInsight Input adaptor CEP Server Cubes Output adaptors Trace & Real-Time ETL Trace events Alerting DB

8. WHAT DO WE WANT TO CAPTURE?

9. TRACE EVENTS. QUERY BEGIN QUERY SUBCUBE QUERY END • EXISTING SESSION • AUDIT • LOGIN • SESSION INTIALIZE • AUDIT LOGOUT ERROR

10. TRACING ANALYSIS SERVICES. String connString = "Provider=MSOLAP;Data Source=bigbox;InitialCatalog=AdventureWorksSample;Integrated Security=SSPI;"; // Create AS server object server = new Microsoft.AnalysisServices.Server(); // Connect server server.Connect(connString); Trace trace = server.Traces.Add(); TraceEventsessionInit = trace.Events.Add(TraceEventClass.SessionInitialize); sessionInit.Columns.Add(TraceColumn.TextData); sessionInit.Columns.Add(TraceColumn.ConnectionID); sessionInit.Columns.Add(TraceColumn.NTDomainName); sessionInit.Columns.Add(TraceColumn.NTUserName); sessionInit.Columns.Add(TraceColumn.ApplicationName); sessionInit.Columns.Add(TraceColumn.StartTime); sessionInit.Columns.Add(TraceColumn.CurrentTime); sessionInit.Columns.Add(TraceColumn.DatabaseName); etc... C#

11. TRACING ANALYSIS SERVICES. C# trace.Update(); //engage the traces TraceEventHandleronTraceEvent = new TraceEventHandler(OnTraceEvent); TraceStoppedEventHandleronTraceStopped = new TraceStoppedEventHandler(OnTraceStopped); trace.OnEvent += new TraceEventHandler(OnTraceEvent); trace.Stopped += new TraceStoppedEventHandler(OnTraceStopped); trace.Start();

12. TRACING ANALYSIS SERVICES. private void OnTraceEvent(object sender, TraceEventArgs e) { siAdapter.PutEvent(e); //send the event out to streaminsight dbwriter.putEvent(e); //the dbwriter constructs a load of inserts based on the shape of the event //and dumps to our DB for cube-ness switch (e.EventClass.ToString()) { case "SessionInitialize": break; case "ExistingSession": break; case "QueryEnd": break; case "QuerySubcube": decodeQuery(e, querySubCubeID); break; . . . C#

13. NOW WHERE?

14. STREAMINSIGHT. //filters out the events we want CepStream<QuerySumm> querySumm = from e in producer.AlterEventDuration(e => TimeSpan.FromMinutes(1)) where e.eventClass == "QueryEnd" select new QuerySumm { userName = e.userName, allTime = e.duration, cpuTime = e.cpuTime, startTime = e.startTime, endTime = e.endTime }; //detects slow queries so we can go and moan at the user CepStream<SlowAlert> slowProducer = from e in querySumm where e.allTime.Milliseconds > 1000 select new SlowAlert { userName = e.userName, allTime = e.allTime, cpuTime = e.cpuTime, startTime = e.startTime, endTime = e.endTime }; LINQ

15. STREAMINSIGHT. //filters out the events we want CepStream<QuerySumm> querySumm = from e in producer.AlterEventDuration(e => TimeSpan.FromMinutes(1)) where e.eventClass == "QueryEnd" select new QuerySumm { userName = e.userName, allTime = e.duration, cpuTime = e.cpuTime, startTime = e.startTime, endTime = e.endTime }; //detects slow queries so we can go and moan at the user CepStream<SlowAlert> slowProducer = from e in querySumm where e.allTime.Milliseconds > 1000 select new SlowAlert { userName = e.userName, allTime = e.allTime, cpuTime = e.cpuTime, startTime = e.startTime, endTime = e.endTime }; LINQ

16. AND THEN? • Output adaptors are nice • Nagios • Splunk • Homebrew • MORE CUBES :)

17. OUTPUT CUBE. How do we do this? • FACTS • Dimensions

18. DEMO.

19. NEXT STEPS: SECURITY. Security Monitoring / Auditing • Alerting on suspicious querying activity / disallowed querying • Alerting • Reporting • Analysis • Provide an audit trail of querying on sensitive attributes • Regulatory Reporting • Dynamic Security

20. NEXT STEPS: PERFORMANCE. Performance Recommendations • Provide data to enable assessment of ‘hot’ areas within the cubes • Alerting • Reporting • Analysis • Feed into third party monitoring tools • Identify heavy users • Identify poorly performing queries for tuning • Automatic aggregation generation

21. EXTENDING THE FRAMEWORK.

22. QUESTIONS.