QA for OBI (Part 1 – Planning)

It seems that every client and every project (I think I’m in the 40’s now) has a different way of QA’ing their OBI system.  This holds true for whether you are doing a BI Apps deployment or simply a new deployment of a custom solution.  This post will set the foundation for the discussion of what needs to be QA’d and how to break the multi-layered solution into more manageable units.

I get the feeling sometimes that my customers are expecting QA to be something perfectly pre-canned that I can simply show up with, fill in the cells in a spreadsheet, and off we go.  The reality is that each project by nature is different, starting from a different place, executed in a different manner, and with different people involved.  It follows that some of the specifics of the QA cycle will therefore be different as well.

Why So Different?

Let’s explore a bit more as to what the variables are and why they make one QA plan look different from another.

Legacy Systems?

Many BI projects replace existing BI or reporting systems, while many others are brand new.  In some cases you’ll get a hybrid, where there are so many changes from the old system as to render it different enough to be considered new as far as QA goes.

The key difference between a replacement project vs. a new system is that with a replacement you have something to compare directly to.  Typically this means you compare to a legacy Cognos or BO report.  If the reports are the same at the end, then you’ve pretty much confirmed that everything else along the way is the same.

In some cases you may be able to leverage only a piece of the new system or perhaps only a single metric.  If this is the case, and you feel it is doable in your project then by all means do so.

The implied assumption whenever comparing to a legacy system is that people trust that the legacy system is accurate; comparing to an unreliable system is of course a waste of time.

Lacking any sort of easy comparison, you will have to roll up your sleeves and get your hands a little dirtier by being much more methodical and thorough along each of the layers of your system.  The 2nd part of this post will discuss what you will need to do in more depth.

Project Methodology and Deliverables

Not all projects are executed the same way.  There are a variety of approaches, deliverable formats, methodologies, and levels of thoroughness that exist across projects.  One thing we’re looking for from a QA perspective is what do I have to compare my results to?  If you have a legacy system then you are in good shape.  But if you don’t, you are going to have to fall back on your requirements and design documents.

The question then becomes: How good are those documents?  What if we’ve chosen to go light on documentation to increase our speed (via Agile say)?  Are they thorough enough for someone to interpret them and convert them into test cases?  Who has the skillset to do that?  That leads us to:

Skill Sets

One thing that is always different at each client is who will actually be doing the QA work.  I’ve seen cases where the consultants are responsible for System test, where the IT team is responsible for it, and where a 3rd party internal QA team is responsible.  What each of these groups knows and does not know, their skill sets, experience, capacity and availability all play a role in how QA will work.  For example, if an internal QA team is brought in, usually with no understanding of the meaning of the reports and data set, and with little experience in testing dashboards and ad-hoc queries, they will only be able to play a small role in the overall QA effort, focusing on things they are capable of doing.


I’d like to mention a few examples so you get the idea about why QA is totally different for every project:

1 – A small project, lead by one business/systems analyst:  She was responsible for defining the requirements, understood the data and what the end goals were.  She was able to write test cases and execute them herself, including some free-form unscripted testing as a cross check.

2 – A large project where the consulting firm was responsible for application QA and the customer’s internal QA team handled infrastructure and integration testing, and a 2nd customer internal team handled final application and functional testing.  The consultants generated extensive QA scripts for the data layers and the reports ensuring data was accurate.  The customer came on top of the consultants with some sanity checks by people knowledgeable in the system design and data set.  Requirements and designs were thoroughly written down and QA scripts were mostly written by the QA consultants actively involved with designs, definitions and specs.

3 – A large BI Apps project where a combination of IT, an internal QA team and select members from the business were involved in QA.  The internal QA team was not knowledgeable on the data, BI systems, or the desired reports.  The reports were designed through an iterative process, leaving the QA team with little time to create scripts as there was nothing hard and fast to go off of from a design document perspective. The responsibility of data testing fell to IT, which had good skillsets in SQL, understood the data to some degree and understood the outputs to some degree.

Different Layers/Areas for QA

With all of the variability out there, it is foolish to propose a one-size-fits-all approach to QA.  However, I do want to expose the different pieces of any OBI solution and the kinds of tests you should be thinking about.  Understanding how all the pieces fit together will help you come up with your own thorough and efficient QA plan tailored to your ability to execute on it.

Note this picture is not overloaded with everything in the entire QA world; I just want to highlight the major areas to focus on.  Keep in mind in this post I am not getting into the details of each layer; that will happen in the second part.

The layers and the key tasks to verify:

  1. Extracts: QA the extracts from your source system to make sure you have the proper data set whether it is a full load or incremental.  Depending upon how much logic is put into your extracts, this may be an easy or simple task.  When using a technology that is new to your group such as Oracle Golden Gate or Informatica CDC, expend a bit more energy making sure this code is working as expected to build up that comfort level, even within IT.
  2. Loads: Make sure data sets are applied properly.  This is where you confirm that if there is $1,200 in the source then the target data model should also say $1,200.  Much more in the 2nd post.
  3. OBI Model:  This is an area which I feel is frequently ignored and certainly not well understood.  Ultimately, all reports are built on the ad-hoc OBI model; you must therefore make sure the ad-hoc model works as it should.  This should be done regardless of whether or not you are deploying Answers to users or not.  Think of this as you OBI foundational layer.
  4. Security: Clearly you will need to expend some time deriving test cases and confirming them.  Test cases are best identified during requirements and security design, and they should be straightforward to come up with.  Generally speaking, the test cases here fall into two categories:  UI capabilities (visibility, privileges, etc.) and Data Visibility.
  5. Reports:  Reports are built on top of everything else so far.  If you feel confident that your model is correct, your metrics are correct, and that your security model is working, then you need to ensure that the developer built the report correctly and it actually represents what the business wants.  Additionally, this is the point where you will do some end-to-end testing to make sure all of the pieces work together seamlessly.  In the end, this is what your business users will be reviewing as part of their QA process.
  6. User interface:  Within your Dashboarding environment, make sure your drill downs work, your navigations, column selectors, master-details, conditional formatting, prompts, etc. all function properly.  Make sure things are labeled correctly (spelling!).  Make sure any options are as designed (such as Download link), and that any UI standards are followed.  Review any personalization configuration as well.
  7. Alerts:  Ensure any alerting or delivers technology is configured correctly.
  8. Infrastructure: Ensure that you have configured the environment properly.  This ranges from easier items such as caching, config files, custom messages, etc.  There are more technical aspects as well, including OBI clustering and fail over, database backups, and overall system ETL and user query performance.

Skill Sets

Earlier on I mentioned how the team you have available dictates how the QA process will be executed and by whom.  With that in mind, what skill sets do you need to have in order to properly QA your OBI system?

ETL QA Roles
Strong SQL, source system knowledge, source system access for entering records, knowledge of business rules and transformations for desired metric definitions Systems Analysts, QA Team, possibly IT
OBI Model
OBI Answers, knowledge of data model, source system access for validations, knowledge of derived metric definitions (not necessarily a base metric.  More later.) Systems Analysts, QA Team
Ability to independently confirm data from the source if possible, general analyst, business user. Systems Analysts, Business Analysts, End Users, QA Team
General Analyst Systems Analysts, Business Analysts, End Users, QA Team
Deep technical skills, typically those who set up the infrastructure Infrastructure Admins

Phases and Planning

There are a variety of QA phases that exist out there; different companies use different sets of these phases.  In some cases a buddy test or a Conference Room Pilot (CRP) may be used.  I’m going to focus on the most common ones when describing what should ideally be done during each section.

Unit Test

This is a classic developer test, performed by a developer as they are working on the code.  Typically they run through a variety of unscripted, lightly documented QA as they see fit.  When they feel the code or report is ready, it gets promoted to the ‘master’ code base for the next level of QA.  It is best to keep unit test light, meaning no involvement from any QA or other IT group.  This will simply slow development times down.


SIT is where the system comes together for the first time, both the data layers and the OBI toolset. Here is where the vast majority of your effort will be spent.  However, SIT is a very difficult time on a project, requiring the longest hours and the most stress.

1)      Full Load The first objective of SIT is to complete a full load of all new and modified ETL code.  Typically this will take several iterations of a full load to complete successfully.  Plan on the performance of the load to be poor, perhaps even several days in length.  Tuning activities will have to wait until the code is confirmed. Until the time that a full load happens, there will need to be significant babysitting of the loads to find problems and fix them.  Plan on several iterations of full loads in order to a) simply get them to run in an automated fashion, and b) for bugs fix cycles.  Depending on the size and complexity of the system, plan on 2-6 cycles of full loads in order to get the baseline build working.

2)      Incremental Load Once the Full Load has been confirmed to be in good condition (meaning few to no ETL defects), the process of running incremental loads can begin.  Make sure that multiple iterations of incremental loads are run, including back-to-back ones.  One of the key challenges in some systems is on the source side; providing a source system with decent transactions, or alternatively creating and managing source snapshots.  Plan on a decent amount of involvement from the DBA group to just manage snapshots and refreshes.  Try to use production snapshots of real, representative transactions – this will both give you confidence that the code works on real data but also a better understanding as to how much ETL tuning work remains.

3)      Formal QA This is the first time an official QA team is involved, armed with a set of scripts to validate all of the layers of the stack.  The workload will most likely be split among back-end and front-end teams.  A QA group, along with key business users will execute the majority of your OBI and security tests, along with some end to end validations.  With respect to business users, it is best to hold off on them until there is some solid level of confidence that what you show them is in good shape; bringing them in too early will leave a bad taste in their mouth.

4)      Its all about the builds.  SIT and UAT are all based on your build schedule.  With other kinds of systems, QA is focused on testing code.  The difference here is that you test the data, and it takes a long time to move that data around.  Thus, as you are planning your cycles, keep in mind the full builds, incremental loads, time needed for defect resolution, and then the next build.  I typically like to plan on a build per week or every other week for larger more complex systems.  If at all possible, try to overlap them slightly – you’ll need two database environments – but you can rebuild one while the other is still useful for QA testers.  Everything flows from those builds, so plan you QA resources accordingly.


UAT is where a larger group of business users come into the environment and start confirming the reports work as they should.  Of course the more you have them involved throughout design and development the less the risk that you’ve missed something.

Although there should be some portion of testing that is scripted, free-form testing is also a great way to help QA the system.  This kind of testing becomes more important when the QA scripts are either very light or are written by a team that is not experts on the delivered solution.

Similar to SIT, UAT is also structured according to system builds; perhaps every week or two depending on size and complexity.

Production Shakedown

There isn’t too much to discuss here; simply run a portion of your QA scripts after the production deployment as part of certifying it for go-live.  The development team can help to do this along with IT and Business Analysts.

Upgrades and Regression

When deploying your second iteration of your code base, a full blown regression test is a good idea.  When doing a second or third iteration, you very well may have to develop one-off deployment scripts that manually modify the data set and data model from 1.0 to 2.0.  Keep in mind you are not only testing the code but also the upgrade process. This process should be practiced to gain proficiency at it and to help ensure the actual after-hours production upgrade runs smoothly.  A few items to consider:

  • Start with two schemas: a 1.0 schema and a new 2.0 schema built from a fresh build.
  • Apply your upgrade scripts on the 1.0 schema to upgrade it to 2.0
  • The newly upgraded 1.0 schema should now be identical to the 2.0 schema- confirm this via regression tests.
  • For OBI, it is at this time where you may want to become more sophisticated in your webcatalog migration activities. For a 1.0 deployment, the entire web catalog is typically copied as-is into Prod.  However with an upgrade you should not do this, as production settings and user created content will be erased.  Practice the migration of new and changed objects from the 2.0 Web Catalog into the Production 1.0 catalog to ensure it is done correctly.
  • The great thing about regression testing OBI reports is that it is very easy (assuming no changes):  Simply have two OBI environments, open up each one in a different browser tab, find the same page and set options, run both, and Ctrl-Tab between both windows- any pixel that is different you eye will notice right away.  It’s a great way to have broad coverage with a very simple test.
  • Make sure of course that any upgrades and regression is done using a static data set; comparing apples to oranges simply won’t work.

Test Case Preparation

When we typically think of a test case, we think about checking whether or not the code built matches the design specification.  That is of course all well and good.

But how do you know your design captured the intent of the business correctly?  What if there is a design flaw?  That’s where Business Case test scripts come in to play.  For each piece of code written, whether an ETL mapping, a Metric Calculation, or a report, take a step back from the details of the design and identify what the purpose of the design is.  Ideally this is done by the designer or the business analyst, and serves as a check on the design not just the code. For example, a complex ETL mapping might have a few objectives they are trying to achieve:

  • All widgets should be assigned to a customer
  • If an owner doesn’t exist in System A, then it should use the record from system B.

Test cases ultimately should be done concurrently with code development, starting with the same design spec and ending with both code and scripts ready for QA:

If you are able to be fully prepared for QA your timelines will be greatly reduced in SIT.  As it is more common to have an iterative development cycle on Reports as opposed to ETL or OBI Metrics, preparing test scripts in line with code development is better suited to ETL coding and OBI metrics testing.

In fact, it will most likely be possible to execute some of your ETL test scripts (code and business cases) as soon as ETL code is done and partial builds are worked on as part of development.  Getting this early feedback during development will only make SIT that much easier.  Note if the resources responsible are unfamiliar with the design or the design is lightly documented you may not be able to gain this edge.  And remember, try not to bog development down by using your developers in a Knowledge Transfer role during development – that will really jeopardize your timelines.

In the next post on this topic I’ll get into much greater detail on the kinds of tests to consider performing at each layer.


Posted on October 31, 2012, in BI Theory and Best Practices, OBI EE Development and tagged , , , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: