Filter on one fact table not affecting second fact table in analysis? — Oracle Analytics

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Filter on one fact table not affecting second fact table in analysis?

Received Response
11
Views
4
Comments
2981486
2981486 Rank 3 - Community Apprentice
edited Aug 13, 2024 7:03PM in Oracle Analytics Cloud and Server

Hello,

I am creating an analysis with two fact tables (Sales and Inventory) and one conformed dimension (product).

In my analysis, I filtered the Sales fact table with sales_amount > 10. This filter displays only products with sales_amount > 10.

Now, I noticed that in the Grand Total of my table view, the Inventory measures are summing up for ALL products. I want the inventory measures to also be filtered to Products where sales_amount > 10.

I noticed that BI server is splitting up the query in 2. On query to the sales, the other to Inventory. How can I force the BI server to write a single query so that my filter is applied to the second fact table?

Thank you,

Answers

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    Hello,

    Try this:

    1. Create a UNION reports with oracle BI Answers

    OBIEE - Reporting on Multiple Subject Area (Advanced Logical SQL) [Gerardnico]

       Snap0.png

    2. Apply your filters  by each  "CRITERIA"

    3. You could use a pivot table / table / graph.

    Kind Regards,

  • 2981486
    2981486 Rank 3 - Community Apprentice

    Thanks, but there is no filter for my second Fact Table (inventory).

    The logic is:

    1. I filter my first fact table (sales amount > 10)

    2. My Dimension will automatically be filtered to only products that have sales > 10 through the join with the fact table.

    3. My second fact table should be filtered for only the products in my dimension through the join.

    The problem is, obiee writes 2 independent queries, so my second fact table is not filtered at all and is displaying measures for ALL products.

    Thanks,

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    Hello,

    1. Yes, but if you want do this, you have to create a join/complex join between two fact tables ( sales / inventory), if you dont have this relationship between your both fact, OBIEE dont know how to create the query to hit the database and dont take the filter (sales>10) that you want, for that reason, obiee right now, create two queries, check your model in your RPD.

    2. In other hand , Create your UNION criterias in BI Answers ,and make your filter based on  ADVANCED SQL FILTER do a LOGICAL QUERY to FILTER YOUR INVENTORY BASED in your SALES RESULTS

    11.png

    Kind Regards,

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    Selection steps with "Keep Only" on Product dimension where "Sales Amount > 10".