Select Page

Art of BI: Using a Sub-Analysis as a Base Filter for Main Analysis Request

Christian Screen | | April 6, 2011

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
    [simage=535,400,y,center]
  2. Select the Sample Sales subject area
    [simage=536,400,y,center]
  3. Create a query with the following criteria
    [simage=532,400,y,center]
  4. Add this simple filter on the Shipped Amount fact
    [simage=533,400,y,center]
  5. Save the filter in a simple folder (somewhere in My Folders) called “Analysis A” or something similar
    [simage=537,400,y,center]
  6. Create a New Analysis which will be our primary analysis that will filter off of “Analysis A”
    [simage=538,400,y,center]
  7. Select the Sample Sales subject area again
    [simage=536,400,y,center]
  8. Create a query with the following criteria
    [simage=531,400,y,center]
  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
    [simage=534,400,y,center]
  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”
    [simage=540,400,y,center]
  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”.
    [simage=541,400,y,center]
  12. In the Use Values in Column drop-down select “P1 Product”
  13. Click OK
  14. Click the Results tab.
    [simage=543,400,y,center]
  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.

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

Hyperion Myth #9: SOX Audit Requests Are Time-consuming

With serious financial penalties, SOX audits can be intimidating — but they don’t have to be. Find out how you can use Datavail’s software to automatically prove SOX compliance.

Jonathan Berry | March 13, 2018

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS