Using a Sub-Analysis as a Base Filter for Main Analysis Request

Stepping away from the back-end of OBI 11g for a minute, I wanted to show one of the features of OBI that has just moved slightly to a different location that where it existed in 10g.  Seemingly in OBI 11g there a few features that look to be hidden just by the way the new prompts, wizards, or drop-downs are created.  Without looking deeply you just might miss them.  But this post isn’t about uncovering all the clandestine gems of OBI, it is simply to provide a tutorial for how to create an Analysis query filtered by a column on the results of an existing query.  I’ll let you determine for yourself where this may solve a use case and wield its power for your future development goals/requirements.

Get Started

This tutorial uses the SampleApp web catalog and metadata provided by Oracle which can be downloaded on Oracle’s BI portion of their website.

Here are the steps…

Tutorial Steps

1. Create a New Analysis
2. Select the Sample Sales subject area
3. Create a query with the following criteria
4. Add this simple filter on the Shipped Amount fact
5. Save the filter in a simple folder (somewhere in My Folders) called “Analysis A” or something similar
6. Create a New Analysis which will be our primary analysis that will filter off of “Analysis A”
7. Select the Sample Sales subject area again
8. Create a query with the following criteria
9. Add a new filter either by clicking on the filter icon and selecting “Product”.”P1 Product” or by expanding the column drop-down for the Product column and selecting Filter
10. In the Operator drop-down scroll all the way to the bottom of the list and select “is based on the results of another analysis”
11. When the prompt window changes, click the “Browse…” button.  Navigate to the folder where the “Analysis A” resides and select “Analysis A”.  Ensure the Relationship drop-down is set to “is equal to any”.
12. In the Use Values in Column drop-down select “P1 Product”
13. Click OK
14. Click the Results tab.
15. Notice that the query results are predicated on the Analysis A query that returns only the products with a Shipped Amount of less the 2,000,000 dollars.
16. Save the analysis as “Analysis B”
17. You are done.  Pat yourself on the shoulder.

Conclusion

This tut just gave you a look at a filter option that most people simply overlook in OBI 11g because it is tucked at the bottom of the drop-down.  In addition, you learned about one of the nifty features of OBI that has been around for a minute but still works and looks better in OBI 11g.  And, if you never know about it, now you do. Peace.