4 Replies Latest reply on Feb 4, 2016 12:35 PM by user8590399

    Force OBIEE to hit partition table

    user8590399

      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

        • 1. Re: Force OBIEE to hit partition table
          SriniVEERAVALLI

          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

          • 2. Re: Force OBIEE to hit partition table
            user8590399

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

            • 3. Re: Force OBIEE to hit partition table
              rmoff

              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?

              • 4. Re: Force OBIEE to hit partition table
                user8590399

                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.