Force OBIEE to hit partition table — Oracle Analytics

Oracle Analytics Cloud and Server

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

Force OBIEE to hit partition table

Received Response
21
Views
4
Comments
user8590399
user8590399 Rank 3 - Community Apprentice

Hi Team,

I am currently using a fact table which is partitioned on YEARMO column at the database side. This is joined to WC_MONTH_D on YEARMO column.

So my join condition is fact.yearmo=month.yearmo. Now I am putting filter on CURR_FISC_YEARMO column of wc_month_d.

What I observed is it does not hit partition table unless I put filter on YEARMO of fact table. Is there any other way of implementing partition so that when I put filter on yearmo of month table it hits the partition.?

Or any other approach to improve the performance in this scenario?

Regards,

Akash

Answers

  • SriniVEERAVALLI
    SriniVEERAVALLI Rank 6 - Analytics Lead

    In BMM you need to use LTS and each table content tab should have as in attached.

    The condition and that check box checked.

    veera.PNG

  • user8590399
    user8590399 Rank 3 - Community Apprentice

    Thats a different way of implementing. We have partition for each month. So how many LTS we will create.

  • rmoff
    rmoff Rank 6 - Analytics Lead

    Does CURR_FISC_YEARMO join to YEARMO in the physical level? If that's not your join key then the query generated is not going to use it. If it is, then queries should be using it and so your DB

    But, re-reading your question you say :

    it does not hit partition table
    

    How do you mean? Do you mean OBIEE generates the physical query against a different physical table from the partitioned one? Do you have multiple LTS for that one fact table?

  • user8590399
    user8590399 Rank 3 - Community Apprentice

    Hi,

    No we do not have multiple LTS for the fact. It is database partitioned fact table. Ideally when we put filter on the partitioned column in the report, it internally hits the partition table and we can check that in explain plan.

    But here we don't have filter on partitioned column but corresponding CURR_FISC_YEARMO column of wc_month_d..

    Normally we have prompt coming from dimension table. so here fact is joined to month dimension and prompt is created on CURR_FISC_YEARMO column of wc_month_d..

    No we are not joining on CURR_FISC_YEARMO as we need join on some other columns like CYPM (i.e. Current year Prior Month), LYPM, CYCM etc columns of month dimension.