Subject Areas in Oracle OBI simplify ad-hoc analysis for business users, but that flexibility makes them notoriously hard to test — a Subject Area can span multiple dimension folders with hundreds of attributes tied to fact tables. This post explains the Subject Area Test Plan in BI Validator, which automatically generates logical queries across five combination types (dimension-only, fact-only, single dimension-to-fact, multi-dimension-to-single-fact, and multi-dimension-to-multi-fact) to catch ODBC errors and bad joins before business users hit them.
Key Takeaways
- Combinatorial complexity: Dimensions with up to 20 attributes, joined to facts via foreign keys, create near-limitless ad-hoc query combinations.
- Two recurring issues: attribute/fact pairs can trigger ODBC errors, and poorly modeled RPDs can silently insert “CAST AS NULL.”
- Five combination types tested: dimension-only, fact-only, single dim-to-fact, multi-dim-to-single fact, multi-dim-to-multi-fact.
- Single dim-to-fact = highest coverage: pairing every attribute with one fact at a time generates the most test volume.
Subject Areas in Oracle OBI
Subject Areas in Oracle OBI are great for business users. They hide the complexity involved in warehousing projects and present an easy to use mechanism to create adhoc analysis based on the business user’s need.
From an IT perspective, testing subject areas is fairly complex. Every Subject Area is really a grouping of Dimension Folders (with attributes) and Measures also known as Facts. The dimension folders may have 100s of attributes and connected to fact tables via foreign keys. Typically, this is referred to as the Star Schema in the industry. Engineers, Business Analysts, Quality Assurance teams struggle to ensure that business users do not encounter unpleasant surprises in the form SQL Errors when they try to create and execute analysis from the subject areas.
To create a new analysis, business users are empowered to pick any subject area, select random combination of dimension attributes and facts as part of the analysis.
Now, here is the challenge; assume that there 4 dimensions:
- Account
- Leads
- Opportunities
- Time
Also, assume each dimension has 20 attributes and that “Fact” has referential integrity with all the above dimensions. Now, to address a new Service Request from business, IT has to tweak a few joins. How can subject areas be tested so that enhancements/bug fixes/changes do not cause any regression?
From our experience, some of the common issues encountered while creating an analysis :
• A specific combination of attributes and facts may result in ODBC errors.
• Depending on how the RPD modeled, the physical query may contain ‘CAST AS NULL’ when the BI Server is not able to determine the right way to join tables.
To address the above issues/risks, we have introduced a new test plan “Subject Area Test Plan” in BI Validator. It drastically simplifies the testing of Subject Areas by automatically generating logical queries using various combinations of dimension attributes and facts as shown below:
• Dimension Attributes Only – Create one logical query per dimension folder by selecting all the attributes in that folder.
• Fact Measures Only – Create one logical query per fact folder by selecting all the measures in that folder.
• Single dimension to Single Fact Only – Pick all the dimension attributes for a dimension folder in combination with one fact or measure at a time. This category should produce the most number of logical queries.
• Multiple dimension to Single Fact Table – Pick one attribute from each of the dimension folders and one fact at a time.
• Multiple dimension to Multiple Fact Table – Pick one attribute from each of the dimension folders in combination with all the facts in a fact folder.
While these combinations of dimension attributes and facts do not cover all possible combinations, they represent a basic set of tests that can be used to validate the subject area with minimal manual effort.
We developed this test plan based on decades of experience in the BI space and are really excited about it. Test drive today.
Frequently Asked Questions: Testing Oracle OBI Subject Areas
1) Why is testing Oracle OBI Subject Areas so complex?
Each Subject Area groups multiple dimension folders — potentially with hundreds of attributes — connected to fact tables via foreign keys in a star schema, so testing every possible business-user query combination manually isn’t feasible.
2) What issues commonly occur when business users query Subject Areas?
Two recurring problems are ODBC errors triggered by specific attribute-and-fact combinations, and unintended “CAST AS NULL” values inserted when the RPD’s data model can’t determine the correct join path.
3) What is the Subject Area Test Plan in BI Validator?
It’s a test plan that automatically generates logical queries across five combination categories — dimension attributes only, fact measures only, single dimension-to-fact, multiple dimension-to-single fact, and multiple dimension-to-multiple fact — to validate a Subject Area with minimal manual effort.
4) Does the Subject Area Test Plan cover every possible query combination?
No — it covers a foundational set of combinations built from Datagaps’ BI testing experience, which validates the Subject Area’s design without requiring exhaustive manual testing of every possible attribute and fact pairing.
Rajesh Kumar A
Digital Marketing Manager, Datagaps
Digital Marketing Manager at Datagaps. Drives data-driven growth through content, performance campaigns, and marketing technology.
Subrahmanya Narayana Chirravuri
Senior Director, Technology, Datagaps
Senior Director of Technology at Datagaps. Leads engineering for the ETL, BI, and data-quality validation platforms.
