Force OBIEE to hit partition table

Question

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

user8590399 · Answer

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.

rmoff · Answer

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 · Answer

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