Can You Just Turn Answers ‘On’?

I have noticed over the years a common misperception that Oracle Answers is simply an option that you can turn on or off.  I’ve seen it from customers, sales reps (of course) and even experienced OBI consultants.  Nothing could be further from the truth.  This article explores why this is not the cases and what is necessary to deploying Oracle Answers for ad-hoc querying.  This post expands on the differences between Reporting and BI that I discussed in the Using Report Specs for Requirements post.

Recently an IT manger told his business customers that he would just turn Answers on and have it ready for the next weekly deployment.   Now the previous OBI releases were done to meet the needs of a single set of reports, even directing the consulting team to take shortcuts to deliver it as quickly as possible.  And shortcuts were in fact taken; our only goal was to deliver a set of reports that worked with their dashboard prompts.  When the customer asked what it would take to turn Answers on, he was not happy with my answer.

Report Centric

What do you end up with when you take the report centric approach that most projects take?  You will end up with fully tested set of dashboards and reports.  The reports will work as constructed, they will work with the prompts on the page, their drill-downs will work as designed, etc.  The items that you build will be added to your test plan and you will run through multiple iterations of testing what your users will see – dashboards, reports and their prompts.  That is what is visible to the outside world, and is what your user tests will contain.

Given A, Build A

What about under the covers though?  If my requirements are to make 10 reports work on some pages, I will design my OBI RPD accordingly.  If I have control over the data model, I will very likely make physical tables (along with their ETL) to optimally support those reports and only those reports.

Why would I do it like this?  (When I say “I” I mean “one”, not just me, Jeff).   First, I will always do the least amount of work necessary to complete a task.  That is just human nature and has nothing to do with OBI.  Particularly if you are using a 3rd party consulting firm – they want to do less work to reduce risk and to be more profitable.

Given Nothing, What Do I Build?

But the second reason is more interesting.  I don’t know what else to do beyond those reports you’ve asked me to build.  I have no ad-hoc Answers requirements to design to.  I don’t know how my data elements will be used aside from what I can gather from a few report specs.  If you want to go beyond those report specs then I will need to know more about it.  If a metric works differently when used in certain cases, I simply don’t know that.  I can’t make assumptions about what you want to do – that is a guaranteed failure.  Finally, I haven’t performed any testing on it, so how do I know it’s correct?  More on the testing aspect in a bit.

What is Oracle Answers and Ad-hoc?

Ad-hoc querying is performed with Oracle Answers within the OBI foundation, whether you have SE, SE One, EE, EE+, or any of the prebuilt applications.  However the architecture of OBI allows for other query tools aside from Answers; BI Publisher, MS Excel, Hyperion, or any ODBC/JDBC tool can access the BI server layer.  So when we say deploy Answers, what we are really saying is that the Subject Areas (Presentation Catalogs) contained in the BI server are ad-hoc capable and tested.

An ad-hoc capable Subject Area needs to support a variety of unknown queries, and do so accurately and perform well.  IT developed content has the luxury of going through weeks of system, integration and user testing before a locked-down system is deployed.  Ad-hoc analysis is the exact opposite of locked-down – it is almost entirely unknown.  The very first query a user creates must be perfectly accurate with no visibility or involvement from IT or any additional testing cycles.


What you are essentially deploying when you deploy a subject area for Answers/ad-hoc querying is a Model.   Thinking along the lines of a cube based system is really what you want to mimic here; forget the reports and dashboards and focus on the model.

  • Everything in the model must work together for scenarios that someone may need to know about (exceptions are allowed).
  • This means that every report a user or IT developer makes must be accurate. Incorrect or meaningless reports are excepted (you can’t prevent someone from making a dumb query).
  • Metrics must remain accurate regardless of granularity – levels or different dimensions.
  • Everything should perform reasonably well.
  • A single user should not be able to bring the system to a crawl with run-away queries.
  • Any exceptions (I call them land-mines) must be noted along with end-user Subject Area documentation.

Ok, So Turn Answers On

So what would happen if you simply took your existing Subject Area designed with a dashboard in mind, and allowed Answers access to it?

  1. Users would complain that certain items that should work together don’t, and Answers gives them error messages or junk data back.
  2. They reviewed the results and in a lot of cases they are wrong.
  3. Some of their queries take an hour an a half to run.
  4. They are making hundreds of queries, and you suspect that many of them are duplicated across users.
  5. The overall system begins to slow down as there are many run-away queries and the models are not designed properly.
  6. One of your problems begins to get better however – system performance begins to improve as users stop using the system.
  7. Eventually no one is using the system, and in fact even the dashboards are no longer used as users have lost faith in the accuracy of the overall system.  Performance gets even better as a result.  You just found a $20 bill in your pocket as your ship begins sinking.
  8. Soon you won’t have to worry about this because you will no longer be working there.  If this is a critical project with SOX implications, you may be competing against your boss for the next job.

So If I Can’t Just “Turn It On”, What Can I Do?

In this scenario, you’ve already deployed dashboards, and your backend system has been designed with only these report specs in mind.  What can you do to get some portion of your model(s) deployed for ad-hoc queries?  You’ll need a new development phase to bring the model to where it should have been in the first place.

Scope: First, identify a small piece of scope – a subset of your subject area that is most critical.  Get rid of all the superfluous stuff, as you job is going to be difficult enough as it is.  You may even wish to make a new Subject Area that is designed for ad-hoc with limited functionality.

You still need to know what items should work together, so review your reduced subject area with your users to determine what makes sense to go together and what doesn’t.

Gap Analysis & Re-Design: Verify and enhance the OBI Business Model to support data relationships that it doesn’t have built.  A highly likely example of this is a dimension works with one of the facts but not another, even though it should.  Update the RPD (and worst-case the physical data model & ETL) to make sure this dimension joins properly.

Identify for your more interesting (read: complex) metrics their logic holds when used with different dimensions and levels.  Does a metric have special logic when viewed along a certain dimension?  What about when there is a multi-variate dimension (e.g., a list of reason codes for a diagnosis record) added to the query – what should the metric show?

Example: A snapshot metric should not give you an error when you view it by Year or Quarter or Month; identity what the calculation or combinations should be.  If you view Monthly # by Quarter, what should happen vs. what will happen now?  If you have weekly snapshots but you subject area goes to day, then either:

  1. remove day from the subject area
  2. make a new subject area for this purpose without day
  3. force the snapshot to work with day, by perhaps using the last snapshot available (i.e., use Saturday’s data for Tuesday)
  4. Build a daily snapshot table

Fix the RPD: Once armed with logic upgrades, make the changes to the RPD & Physical data model as needed.  While developing, spend particular time on unit testing with SQL validation: Enhance a dimension, verify it works in isolation via physical SQL and then with all of the metrics it should work with.  If any poor SQL shows, fix it immediately.  Poor SQL during simple Unit tests will cause significant problems later on.

Perform Extensive Ad-hoc Testing: Regression testing your pre-built reports is the easy part here.  To test an ad-hoc model that can support nearly limitless combinations of queries, you will have to pick a point along the test vs. accuracy curve to achieve a comfort level.

A simple model, with one Logical Table source per logical table will require relatively few tests to validate the structure of the model along with the specific metrics.  A model with many LTSs, complex security rules, Level Based metrics and nested aggregations will require more testing to hit all of the different configuration points.   With ad-hoc testing you are primarily concerned with the skeleton of the RPD (meaning the Logical Table Sources).  If they work as desired, then you’ll just have to worry about individual metrics.  Ad-hoc testing is a large topic, so perhaps another post is due.

Identify possible performance areas and address: Small tables may be ignored, but focus on your larger, more heavily used fact tables.  What are the common combinations of dimensions and levels for these to possibly build aggregates for?  Interview who you suspect to be the main users of the system and identify what their common scenarios and queries typically are and you will begin to find some common combinations for aggregation.

Of course building aggregates is an expensive solution to fixing performance problems.  Make sure you’ve done your physical database setup properly.  With Oracle this means you should have partitioning and Star Queries setup and working well.  Finally, caching will be of little to no value to ad-hoc queries; if you’ve relied on caching for dashboard performance you will have a lot of work to do here.

Safeguard your Database from runaway queries: Typically this means setting up database query limits in terms of CPU % and total time, rows read, etc.  OBI has some features for this as well, but I personally have never seen them used as clients tend to rely on the database.

Give Users the Tools They Need to Succeed: Give them extensive training on Oracle Answers so that they know how to do things correctly. But even more importantly, train them on your model.  What does it look like (draw a picture for them), what do all of the fields/metrics mean, what works together and what doesn’t.  If you have to have special items in there for certain reports (land-mines), point them out.  I strongly recommend you produce Subject Area Documentation for each SA, understandable by users and IT developers (they need the same info as users do).

If your ad-hoc environment is a success, then you’ll run into a different problem.  How is all of this content managed and organized?  Consider putting in place a folder structure for different groups to share content.  I advise working with users to come up with an ‘Ad-hoc Publishing Guide’, where reports can be promoted from a single user to a shared area, possibly even a shared dashboard, possibly even to the official IT controlled dashboards.  Define folder structure, owners and permissions, procedures and processes.

How Do I Avoid This Rework (Or How Can I Plan For Ad-hoc From The Beginning)?

Take a read through the Using Report Specs for Requirements post where some of this is discussed.  The short answer is to gather Informational needs, not report specifications.  The grid below, called a Fact-Dimension Matrix (FDM) is the primary tool for identifying, per task or user group, what their informational needs are and the logic to compute their metrics.  This information comes directly from users and what they do on a day to day basis.  Care should be taken to ignore reports and focus only on information; report layouts will come later on, and they will use the items captured in this FDM.

I consider this kind of document to be the most important deliverable on a true BI project.  I have spent considerable time over the years enhancing this basic format in Excel to do some pretty cool stuff, and I use it as my primary requirements and design document.   From this matrix I can then draw a diagram like so:

I have just designed the perfect Logical Star Model for my requirements. From here I can then push down through OBI and into the database to come up with the perfect physical design and OBI design to support these informational needs.  With the Matrix and Logical Data Model in hand, I now know exactly what I have to test for as well – I’m testing that the picture works as I’ve drawn it, and I’m testing the metric calculations.  This is a larger topic best left for another time.

What About the BI Apps?

The BI Apps fall somewhere in between.  Out of the box they should pretty much work correctly.  Oracle has pretty much validated the skeleton of the Business Model, even if there are a few bugs here and there.  You’ll always have some configurations and different data fields however, so they will need to be tested as well.

However, customizations will need much more thorough ad-hoc testing.  Make sure your new tables, table sources and metrics work with the other items that they work within the Business Model (as opposed to the subset Subject Area).  After hooking up a GL Account to a new Fact table run through a series of join tests to make sure the GL Account table joins to the new fact tables properly, especially when there are aggregates involved.

The Best Solution To Any Problem

…is to eliminate the problem.  An option to consider is something called contained ad-hoc.  Others may reference it by other names, but essentially it is an advanced, highly interactive page which may eliminate much of the demand for Answers.  On it is a table or chart template with a great deal of prompts and column selectors.  This will allow a user to perform a great deal of analysis within the dashboard page without Answers.

Queries are still contained, but with a much larger scope.  Additionally you won’t need lots of expensive Answers licenses.  This may cut down some of your Answers needs into something more controllable and supportable. However, even within a contained ad-hoc page, testing the model still applies as there are many combinations available even with a relatively simple layout.


I hope that it is clear that just because there is this in OBI:

Doesn’t mean you flip it to

and it’s all good to go.  If you haven’t prepared for ad-hoc querying in requirements, design, development and especially testing then you are not ready to turn it on.  Like any new piece of functionality, you have to prepare for it and validate that you’ve done it correctly.

A word about durations.  Generally speaking, making Answers available on the BI Apps will be easier, as the skeleton has been in place and is reliable.  However, your customizations/extensions will have to be thoroughly vetted out with ad-hoc testing scripts and not report test scripts.  With minor changes to the OOB model, I would suspect the testing can be done in about 3 weeks or so, with the ad-hoc and user planning going on concurrently.

Durations for a custom environment can be drastically longer.  You are essentially doing a full release of the application as you will have to change the skeleton and its underpinnings, along with extensive testing.

I find this kind of rework to be a waste of time though.  Do it right from the beginning by applying a BI centric approach, which at its core produces a reliable and capable model as its primary product, with some UI screens on top of it.  Remember, building BI is about building Models.


Posted on January 7, 2010, in BI Theory and Best Practices, OBI EE Development and tagged , , . Bookmark the permalink. 5 Comments.

  1. Excellent post!

  2. Jeff,

    Great article, most of the client and developer do not understand how to use Answer for ad-hoc queries and end up killing system or project. Your suggestion is great…

    Keep it up Dude….


  3. Hi Jeff,

    Excellent post.

    I completely agree with the approach, and always try to focus in the model and not in the reports in the OBI projects I am part of.

    However, sometimes it is hard (for me!) to gain agreement upon what you refer as scenarios, when you say:

    “Everything in the model must work together for scenarios that someone may need to know about (exceptions are allowed).”

    To gain user sign-off when the solution is delivered, these scenarios must be very specific, or at least at a level of specificity that users are happy with. However, given the ad-hoc nature of the querying process, that is a difficult task. There will always be another combination of metric/dimension level that may fail if tested.

    In this cases, the project management team tends to embrace the reports list as an easy way of being able to agree upon the success of what is being delivered. As you explained in the article, this is far from being a good alternative, however, the ‘scenario-centric’ seems to require a highly mature organisation.

    I’ll give this more thought!


  4. Hari Babu Tellabati


    I would say ‘Awesome’ you explained a bigger problem in simpler terms.

    As a final step,we could as well delineate the ‘Presentation Layer’ just so
    the Users are aware,what would work together, when they slice and dice.
    I mean categorize the common Dimensions in to one section and the related Dimensions and Facts in another section and so on.


  5. Excellent Jef!! Great inputs. I am exactly looking for a documnet like FDM you described. Can you give me some more examples on your FDM. You can send that to me

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: