Smarter Regression Testing for OBIEE

Do better OBIEE development and deliver it more rapidly! Just because OBIEE is presented to users through a web browser that doesn't mean the regression testing has to take place there too. This presentation will demonstrate how you can efficiently and effectively test the bulk of changes made to your system without going anywhere near the front end. With hands-on examples of the tools involved -- including the Baseline Validation Tool that ships with OBIEE 12c -- this will be a practical presentation giving anyone interested in the topic a headstart in implementation.

Transcript

1. info@rittmanmead.com www.rittmanmead.com @rittmanmead Robin Moffatt, Principal Consultant, Rittman Mead | December 2015 Smarter Regression Testing for OBIEE 1
2. info@rittmanmead.com www.rittmanmead.com @rittmanmead About Me 2 •Principal Consultant with Rittman Mead ‣ OBIEE & ODI ‣ SysAdmin ‣ Performance •Previously … • OBIEE/DW developer at large UK retailer • SQL Server DBA, Business Objects, DB2, COBOL….
 •Oracle ACE
 •Frequent blogger for Rittman Mead : http://ritt.md/rmoff •Twitter: @rmoff •IRC: rmoff / #obihackers / freenode
3. info@rittmanmead.com www.rittmanmead.com @rittmanmead About Rittman Mead 3 •World’s leading specialist partner for technical excellence, solutions delivery and innovation in Oracle Data Integration, Business Intelligence, Analytics and Big Data •Providing our customers targeted expertise; we are a company that doesn’t try to do everything… only what we excel at •70+ consultants worldwide including 1 Oracle ACE Director and 2 Oracle ACEs •Founded on the values of collaboration, learning, integrity and getting things done Optimizing your investment in Oracle Business Analytics •Comprehensive service portfolio designed to support the full lifecycle of any analytics solution
4. info@rittmanmead.com www.rittmanmead.com @rittmanmead Regression Testing 4 •Why •How •including BVT!
5. info@rittmanmead.com www.rittmanmead.com @rittmanmead Smart Regression Testing 5 Better OBIEE development 
 delivered more rapidly and 
 with greater quality
6. info@rittmanmead.com www.rittmanmead.com @rittmanmead Why Regression Test? 6 Confidence
7. info@rittmanmead.com www.rittmanmead.com @rittmanmead Why? 7 Confidence (A|a)gility in development Happier users
8. info@rittmanmead.com www.rittmanmead.com @rittmanmead What? 8 • Regression Testing is not just a prolonged exercise on a Gantt chart, just before go live • Smart Regression Testing is be run on demand, targeting the change made and assuring quality during development
9. info@rittmanmead.com www.rittmanmead.com @rittmanmead Detect Problems Early 9 •Fresh in the developer’s mind - Easier to fix •Fewer surprises during big test cycles - More honest & realistic project status reports •Better overall QA for the system - Happier users
10. info@rittmanmead.com www.rittmanmead.com @rittmanmead Save Time 10 • Manually checking for regressions • slow & error-prone • limited test coverage • boring! • Automated regression testing • fast & reliable • much greater test coverage
11. info@rittmanmead.com www.rittmanmead.com @rittmanmead What Could Possibly Go Wrong? 11 • Data - Are the numbers still correct?
 • Functionality - Do dashboards still run without error? - Do dashboards still render in the same way?
 • Security - Data - Visible Dashboards
12. info@rittmanmead.com www.rittmanmead.com @rittmanmead OBIEE Conceptual Overview 12 Analysis Analysis Data Sources Dashboard RPD BI Server -------------------- Sending query to database named orcl (id: <<19108>>), 
 connection pool named Connection Pool 01 select sum(T117.FCAST_COST_AMT) as c1, T107.PROD_TYPE_DESC as c2, T127.MONTH_YYYYMM as c4 from GCBC_SALES.TIMES T127 /* Dim_TIMES */ , […]
13. info@rittmanmead.com www.rittmanmead.com @rittmanmead Whoops… I broke it! 13 • RPD may still pass consistency check - but do you know if you broke existing reports and dashboards? •Cleanup / Refactoring •Developments •Bugfixes 1. The RPD
14. info@rittmanmead.com www.rittmanmead.com @rittmanmead Whoops… I broke it! 14 2. OBIEE front end •Cleanup / Refactored calculations •Presentation Catalog changes •Application Role changes
15. info@rittmanmead.com www.rittmanmead.com @rittmanmead Whoops… I broke it! 15 3. Database •Refactored designs • View -> Table • Table -> MView •Indexes •Partitioning
16. info@rittmanmead.com www.rittmanmead.com @rittmanmead Whoops… I broke it … 16 •Software upgrades •Environment changes
17. info@rittmanmead.com www.rittmanmead.com @rittmanmead Scope 17 • Don’t waste time testing what’s not used • Enable Usage Tracking NOW!
18. info@rittmanmead.com www.rittmanmead.com @rittmanmead What Makes a Good Regression Test? 18 Easy Better tests Fast Flexible Scalable
19. info@rittmanmead.com www.rittmanmead.com @rittmanmead Where Can We Detect Problems? 19 •Logical
 
 
 •Physical
 
 
 •Front End 4 1 3 5 2 User WebLogic Server OBIEE system components Data Source(s) BI Server BI Server Presentation Services Managed Server Managed Server JavaHost Presentation Services Web Browser Web Browser BI Plug-in BI Plug-in Analysis request Logical SQL Physical Query x n Logical Resultset Rendered Analysis RPD RPD DWH [ ... ] [ ... ] DWH [ ... ] [ ... ] 2 3 4 5 1 Query results x n
20. info@rittmanmead.com www.rittmanmead.com @rittmanmead Regression Testing Opportunities 20 •OBIEE Logical model • Does the RPD still work and return the same results for a given logical query?
 
 •Physical Database • Does the Physical SQL that OBIEE generates still run? 
 
 •OBIEE front end • From the point of view of an end user, does it look right?
21. info@rittmanmead.com www.rittmanmead.com @rittmanmead Baseline Validation Tool 21 • New in OBIEE 12c - Available for use with 11g too • Modular - run just the plugins you need for the test - Data - Logical query - Visual - BI catalog
22. info@rittmanmead.com www.rittmanmead.com @rittmanmead Baseline Validation Tool 22 • Distributed only as part of the OBIEE 12c Windows Client Tools • Hidden away …
 
 
 
 
 
 • Windows or Linux • Check doc folder in the zip for full instructions
23. info@rittmanmead.com www.rittmanmead.com @rittmanmead Baseline Validation Tool 23 • Set up an XML file pointing to your server(s) • Run BVT initial • Run BVT secondary • Compare results
24. info@rittmanmead.com www.rittmanmead.com @rittmanmead Regression Testing Database Changes 24 User WebLogic Server OBIEE system components Data Source(s) BI Server BI Server Presentation Services Managed Server Managed Server JavaHost Presentation Services Web Browser Web Browser BI Plug-in BI Plug-in RPD RPD DWH [ ... ] [ ... ] DWH [ ... ] [ ... ] Physical SQL Dataset
25. info@rittmanmead.com www.rittmanmead.com @rittmanmead Regression Testing Database Changes 25 • If only the DB has changed, then just test the DB. nqquery.log / Usage Tracking SQL*Plus
26. info@rittmanmead.com www.rittmanmead.com @rittmanmead Logical OBIEE Regression Testing 26 User WebLogic Server OBIEE system components Data Source(s) BI Server BI Server Presentation Services Managed Server Managed Server JavaHost Presentation Services Web Browser Web Browser BI Plug-in BI Plug-in RPD RPD DWH [ ... ] [ ... ] DWH [ ... ] [ ... ] Logical SQL Dataset
27. info@rittmanmead.com www.rittmanmead.com @rittmanmead Logical SQL in OBIEE 27 Analysis Logical SQL Resultset via nqcmd Analysis results BI Server RPD
28. info@rittmanmead.com www.rittmanmead.com @rittmanmead Baseline Validation Tool 28 • Use the report and logicalquery test plugins • Connects to the server and runs each analysis • Stores the results as CSV • Stores the Logical SQL
29. info@rittmanmead.com www.rittmanmead.com @rittmanmead Baseline Validation Tool 29 • Make a change to your system - or point BVT at a different environment • Run the same test again, specifying the second deployment in the XML Baseline Deployment/ Environment Secondary Deployment/ Environment ./bin/obibvt -deployment AfterChanges -config testconfig.xml
30. info@rittmanmead.com www.rittmanmead.com @rittmanmead Baseline Validation Tool 30 • Once you’ve got your two samples, run BVT again to compare them • It compares the CSV and Logical SQL on disk and reports any differences • Writes one HTML file per test plugin ./bin/obibvt -compareresults Results/Baseline Results/AfterChange -config testconfig.xml
31. info@rittmanmead.com www.rittmanmead.com @rittmanmead Baseline Validation Tool 31 • Links to view report on each environment and files being compared • If you want to know the actual difference you’ll need to run your own diff tool (eg WinMerge)
32. info@rittmanmead.com www.rittmanmead.com @rittmanmead Baseline Validation Tool 32
33. info@rittmanmead.com www.rittmanmead.com @rittmanmead Baseline Validation Tool 33 • Be aware of the config option “fudge factor” (DiffTolerance) necessary to deal with handling of floating point numbers across platforms • Watch out for false negatives with Logical SQL from expected variance in FETCH FIRST xxxx ROWS etc
34. info@rittmanmead.com www.rittmanmead.com @rittmanmead Regression Testing the OBIEE Front End 34 HTML, JS, CSS, PNG User WebLogic Server OBIEE system components Data Source(s) BI Server BI Server Presentation Services Managed Server Managed Server JavaHost Presentation Services Web Browser Web Browser BI Plug-in BI Plug-in RPD RPD DWH [ ... ] [ ... ] DWH [ ... ] [ ... ] HTML, JS, CSS, PNG HTML, JS, CSS, PNG HTML, JS, CSS, PNG
35. info@rittmanmead.com www.rittmanmead.com @rittmanmead Regression Testing the OBIEE Front End 35 • Front End testing is complex! • Two options to make sense of HTML, JavaScript, CSS, images: - Programatically - Parse server response - Visually - Render server response
36. info@rittmanmead.com www.rittmanmead.com @rittmanmead Front End test methods: Parsing 36 - Makes pass/fail tests easier • A string is present, or it is not - Higher rate of false negatives • It’s easier to miss problems - High development & maintenance 
 cost • Need to reverse-engineer DOM • Often relies on non-deterministic internal IDs to 
 accurately pinpoint assertions
37. info@rittmanmead.com www.rittmanmead.com @rittmanmead Front End test methods: Rendering 37 • More accurate - Can detect differences not expected or explicitly coded for • More prone to false positives - Lots of things change on a page for valid reasons (dates, alerts, etc) • Still requires DOM knowledge but less reliance on internal IDs Before After Test Result: Fail
38. info@rittmanmead.com www.rittmanmead.com @rittmanmead Visual Regression Testing 38 • Toolset - CasperJS - PhantomCSS
 • Server based, no dependency on 
 desktop or web browser - Uses PhantomJS, WebKit-based 
 “headless” browser
39. info@rittmanmead.com www.rittmanmead.com @rittmanmead Visual Regression Testing In Action 39 1. Tool takes automatic snapshot of dashboard pages. This is the baseline.
40. info@rittmanmead.com www.rittmanmead.com @rittmanmead 40 2. Make Analysis/RPD changes Visual Regression Testing In Action
41. info@rittmanmead.com www.rittmanmead.com @rittmanmead 41 3. Tool captures new screenshots and compares against baseline. Any differences are reported. Before After Test Output Visual Regression Testing In Action
42. info@rittmanmead.com www.rittmanmead.com @rittmanmead Baseline Validation Tool 42 • Uses custom code based around Selenium to automagically screenshot each report and dashboard • Compares resulting images before/after, based on a threshold that can 
 be altered
43. info@rittmanmead.com www.rittmanmead.com @rittmanmead Baseline Validation Tool 43
44. info@rittmanmead.com www.rittmanmead.com @rittmanmead Baseline Validation Tool 44
45. info@rittmanmead.com www.rittmanmead.com @rittmanmead Working smarter - summary 45 Test Physical Logical Front End What Changed? Report / Dashboard No No Yes RPD No Yes Yes Database Yes Yes Yes
46. info@rittmanmead.com www.rittmanmead.com @rittmanmead #EOF 46 email
 robin@rittmanmead.com web
 http://ritt.md/rmoff twitter
 @rmoff irc
 rmoff @ #obihackers Rittman Mead User Engagement Service http://ritt.md/ue Rittman Mead
 Performance Analytics Service http://ritt.md/pa