Smarter Regression Testing for OBIEE

2bded62396ea66c84bd10e91c718dea9?s=47 Robin Moffatt
December 08, 2015

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.

2bded62396ea66c84bd10e91c718dea9?s=128

Robin Moffatt

December 08, 2015
Tweet

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