OBIEE by QuontraSolutions

1. Performance Testing and OBIEE By Quontra Solutions Email : info@quontrasolutions.co.ukContact : 020-3734-1498Website : www.quontrasolutions.co.uk

2. Introduction • Oracle BI specialist at Morrisons plc • Big IT development programme at its early stages implementing OBIEE, OBIA, ORDM, all on Oracle 11g & HP-UX

3. The aim of this presentation • A Performance Tuning Methodology • OBIEE techie stuff • Learn from my mistakes!

4. What is performance testing all about? • Response times • Report • ETL batch • OLTP transaction • System impact • Resource usage • Scalability

5. Why performance test?(Isn’t testing just for wimps?) • Check that your system performs • Are the users going to be happy? • Baseline • How fast is fast? • How slow is slow? • Validate system design • Do it right, first time • Capacity planning

6. Why performance test? • It’s never too late • “You’ll never catch all your problems in pre-production testing. That’s why you need a reliable and efficient method for solving the problems that leak through your pre-production testing processes.”

7. Why performance test? • Because it makes you better at your job • “At the very least, your performance test plan will make you a more competent diagnostician (and clearer thinker) when it comes time to fix the performance problems that will inevitably occur during production operation.”

8. Performance Testing – What & Why • Quantifying response times • System impact • User expectations • Problem diagnosis • Design validation

9. Performance Testing - How? Iterative approach Timebox! Do it right Don’t “fudge it” Evaluate design / config options Do more testing Redefine test Do more testing Be Methodical

10. Define & build your test • Define – what are you going to test • Aim of the test • Scope • Assumptions • Specifics • Data, environment, etc • Build – how are you going to test it • OBIEE specific • E.g. : • Check that the system performs • Baseline performance • Prove system capacity • Validate system design

11. Consider your test scope More components = more complex = more variables = larger margin of error Fewer components = easier to manage = more precise = more efficient

12. OBIEE stack Report / Dashboard Rendered report Presentation Services Logical SQL Physical SQL statement(s) BI Server Data set Database Data set(s) Excludes App/Web server & presentation services plug-in

13. Report / Dashboard OBIEE testing options Load Testing tool (eg. LoadRunner, OATS) User & Stopwatch Presentation Services Rendered report nqcmd LSQL SQL Client LSQL Physical SQL BI Server Physical SQL Data set Database Data set(s)

14. nqcmd Usage Tracking or NQQuery.log BI Server Logical SQL Data Logical SQL Logical SQL Test script nqcmd

15. nqcmd Master test script Test script nqcmd BI Server Test script Data nqcmd Logical SQL Test script nqcmd Test script nqcmd

16. LoadRunnera.k.a. HP Performance Centre • Simulates user interaction – HTTP traffic • Powerful, but can be difficult to set up • Ajax complicates things • Do you really need to use it? • Tools • Fiddler2 • FireBug • Reference: • My Oracle Support – Doc ID 496417.1 • http://rnm1978.wordpress.com/category/loadrunner

17. Defining your test - summary • Be very clear what the aim of your test is • You probably need to define multiple tests • Different points on the OBIEE stack to interface • Pick the most appropriate one • Write everything down!

18. Measure

19. OBIEE measuring & monitoring PerfMon (Windows) Enterprise Manager (Oracle) Server metrics e.g. : IO, CPU, Memory Web Server Apache log Oracle OS Watcher (unix) App Server OAS log Usage Tracking Presentation Services plug-in sawserver.log Analytics log Presentation Services NQServer.log Enterprise Manager NQQuery.log ASH, AWR, SQL Monitor BI Server Database PerfMon (windows only) systems management jConsole etc Enterprise Manager BI Management Pack Presentation services

20. Oracle SQL Monitor

21. Measure - summary • Lots of different ways to measure • Build measurement into your test plan • Automate where possible • Easier • Less error

22. Analyse

23. Analysing the data

24. Analysing the data

25. Analysing the data

26. Analysing the data

27. Analysing the data

28. Analysing the data

29. Recording data about the test

30. Extending Usage Tracking OBIEE_REPLAY_STATEMENTS qt_ora_hash query_text saw_path dashboard S_NQ_ACCT START_TS ROW_COUNT TOTAL_TIME_SEC NUM_DB_QUERY QUERY_TEXT QUERY_SRC_CD SAW_SRC_PATH SAW_DASHBOARD OBIEE_REPLAY_STATS testid testenv qt_ora_hash start_ts response_time row_count db_query_cnt

31. Analyse Iterative approach Do it right Don’t “fudge it” Evaluate design / config options Timebox!

32. Review Iterative approach Redefine test Continue testing Implement

33. Review

34. Implement Iterative approach

35. Lessons Learnt • You won’t get your testing right first time • There’s no shame in that • Don’t cook the books • Better to redefine your test than invalidate its results • Stick to the methodology • Don’t move the goalposts • Very tempting to pick off the “low-hanging fruit” • If you do, make sure you don’t get indigestion… • Timebox • Test your implementation!

36. Performance Testing OBIEE Iterative approach Do it right Don’t “fudge it” Evaluate design / config options Do more testing Redefine test Do more testing Be Methodical

37. Thank You!