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.

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

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

Scripting Out the Logins, Server Role Assignments, and Server Permissions

Imagine there are over one hundred logins in the source server and you need to migrate them all over to the destination server. Wouldn’t it be awesome if we could automate the process by generating the scripts for the required tasks?

JP Chen | October 1, 2015

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