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.
• Single dimension to Multiple Fact Table – Pick all the dimension attributes for a dimension folder in combination with all the facts in a fact folder.
• 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.
