Thats a different way of implementing. We have partition for each month. So how many LTS we will create.
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?
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.