Effective Regression Testing for OBIEE Applications

It's a dark and stormy Friday night, and as you drift off into slumberland in your warm, dry and cozy bed, a thought pops up in your mind: "If a butterfly flaps its wings in Costa Rica tonight, will the CFO's Executive Financial Summary dashboard still work?" Suddenly you are wide awake and feeling a bit of paranoia. You jump out of bed, fire up your laptop, log into the corporate VPN and then into OBIEE...

This sounds like the beginning of a bad BI horror story -- and hopefully for all of you a scenario like this truly is just that - a story. But we've all experienced some variation of this scene. The OBIEE equivalent of butterflies do flap their wings in Costa Rica, and the result does sometimes alter the OBIEE equivalent of weather patterns in Japan. Change the name of a presentation column... or the mapping rules of a logical column... or the aggregation rules of a logical table... or a minor extraction rule in the warehouse ETL logic... or even simply merge your local RPD work into a MUD repository... and you risk causing an error in a report that might not be noticed until days after the change -- usually when the report is most needed.

The real truth is that an OBIEE system just like any other software implementation has some inherent fragility that requires vigorous regression testing strategies to keep it running smoothly. But while most "traditional" software systems have very clearly defined behaviors that lend themselves well to control by means of various specific testing strategies, a Business Intelligence application by nature does not always necessarily have completely predictable outcomes - especially true in OBIEE given the complexity of the BI Server and, for example, how it constructs queries against the underlying data.

Given the complexity of the OBIEE architecture and the fleeting nature of the data, establishing an adequate regression testing strategy is a challenge.

One excellent approach is to identify commonly executed queries with Usage Tracking (or just identify a specific request's logical query), then execute the corresponding logical sql using nqcmd.exe. This is an excellent approach but can respond to only two results: success or failure. In most cases this is entirely adequate.

Another approach favored of course by Oracle is to implement their Application Testing Suite, which looks ike a promising tool but frankly I have not heard of any usage in the real world -- if anyone has direct experience with this tool, feel free to respond. Moreover it goes without saying that license fees would be a significant factor in evaluating its total ROI.

A third way to address regression testing in OBIEE is to set up a series of webcat (HTTP) requests that can be executed on a scheduled basis by an automated web testing utility. Several such utilities exist - LoadRunner being the most prevalent - but as with Oracle's offering, most require licensing.

One popular web testing utility distributed as open-source software and therefore free of licensing costs has gained some following among the developer community: a Firefox plugin called Selenium (the IDE flavor, to be precise), which can execute a wide range of web page interactions and combine them into test scripts. These test scripts can then be assembled into test suites and exported to a full-fledged program in a variety of commonly used languages/frameworks (C#, Java, Perl, PHP, Python, Ruby), at which point this program can be scheduled for execution just like any other.

While Selenium is highly flexible, fairly easy to use and very cheap (at least in terms of licensing costs), it also has some drawbacks, particularly when used to test an OBIEE system. In the hopes of blazing the "Selenium for OBIEE" trail, I have attempted my own small POC by writing some basic test scripts against the Paint webcat. In doing so I identified some gotchas and workarounds that will at the very least help you successfully construct OBIEE test scripts using this very capable tool. I will explore those workarounds and advanced configurations in later posts. But my overall opinion is that Selenium is a very capable testing tool which deserves serious consideration for use in an OBIEE environment.

Here are what I consider general strengths of Selenium:

Flexible

• Can execute a variety of actions: open urls, click links, interact with prompts, execute javascript ...
• Can be designed to respond to specified conditions either by failing OR by simply logging and continuing
• Can assemble multiple test scripts within a single test suite
• Can export scripts in a variety of languages / platforms - C#, Java, Perl, PHP, Python, Ruby
  

Straightforward to use

• Scripts can be written using a simple Firefox plugin
• Applies open standards: page elements are identified using Xpath
  

Economical

• Open Source therefore no licensing
• Fairly wide adoption therefore a decent user community
• Commercial organizations exist that specialize in Selenium development & support
  

And some general challenges:

Somewhat steep learning curve

• Though basic use is straightforwad, still takes a bit to get the hang of the tool
• Because it's based on open standards like XPath, the skills learned are not "siloed" but can be useful elsewhere
• Extending test suites for scheduled execution requires a leap of additional complexity
  

Open Source = No official support

• On the other hand, there are plenty of answers to common problems on the web
• Googling a solution is probably just as fast as relying on traditional paid support
  

OBIEE-Specific Challenges

• Problematic handling of daughter or "target" windows
• Default page element XPath queries cannot be used because they rely on IDs, which in OBIEE are dynamic
  

Integrating Testing in the System Development Life Cycle

After you have spent three to six months gathering requirements, designing, and building the OBIEE Application you start the User Acceptance Testing with the business users. When the business users start to do the acceptance testing many times we hear the following from the business users:

1.) The data on the Reports and Dashboards do not agree with the data on the in my Excel Spreadsheet
2.) The data on the Reports and Dashboards do not agree with the data in our source system
3.) It is difficult to navigate between the Dashboard and Reports to see the necessary information
4.) We do not like the gauges and look and feel of the Reports and Dashboards
5.) The Dashboard Prompts do not give us the selection criteria we need to see the reports
6.) We cannot see the information that we need on the Reports and Dashboards
7.) We have a need to look at data to follow up on the out of bounds metrics on the Reports and Dashboards

How many times have you encountered this in on a BI Application or OBIEE Project? If you have been on many of the projects that I have worked on it has occurred to frequently. Instead of going to the next project in the BI Program, you have to spend many hours trying to meet the user’s needs found during User Acceptance Testing.

So what has contributed to this situation? There are many items that have contributed to the above situations. Some of them are:

1.) Poor Requirements gathering
2.) Poor Design
3.) Not involving the user in all phases of the system development life cycle
4.) Poor or no Testing Processes

Of the above one of the biggest contributors to this situation are Poor or no Testing Processes. So where do we start the testing processes for a project. Bill Hetzel in his book “The Complete Guide to Software Testing” states that Testing Processes need to start with the Project Initiation Phase. He recommends the following steps for integration testing within the System Development Life Cycle:

Project Initiation:

Develop Broad Test Strategy
Establish the overall test approach and effort

Requirements:

Establish the testing requirements
Assign testing responsibilities
Design preliminary test procedures and requirements-based tests
Test and validate the requirements

Design:

Prepare preliminary system test plan and design specifications
Complete acceptance test plans and design specification
Complete design-based tests
Test and validate the design

Development:
Complete the system test plan
Finalize test procedures and any code-based tests
Complete module or unit test designs
Conduct the tests
Integrate and test subsystems
Conduct the system test

Implementation:
Conduct the acceptance test
Test changes and fixes
Evaluate testing effectiveness


As Bill Hetzel states above Testing needs to be involved in all phases of the System Development Life Cycle. Now one of the arguments against testing is that it takes a long time do and conduct. However, without following at least some of the major steps projects are prone to some of the same symptoms that we initially discussed. Also a fail or project that exceeds the cost and schedule and does not provide the client the information that he needs result in a failed project – this is the perception by the business users if the project does not meet their perceived needs and requirements.

It has been said the Projects are 80% communications and 20% technical. If we fail to communicate the testing needs and implement projects that the users perceive do not meet their needs and requirements then we are contributing to a failed project. Implementing testing into the System Development Life Cycle greatly improves the communication on a project, and results in users perceiving that the projects meet the needs and requirements. There is no major secret to integrating testing within the System Development Life Cycle, it just requires that it be included and the users be involved in all phases of the System Development Life Cycle. If the proper communication and testing processes are included in the System Development Life Cycle, User Acceptance Testing should only be a minor effort for the project because the users are only validating what already has been defined and tested.