Force using Partition in a paritioned table

building reports webintelligence
1

I have a WEBI report on a customer dimension table that is partitioned by month joined with a fact table which is also partitioned by month. The reports are run by month. How do I make sure that my WEBI queries are explicitly hitting the partition that is desired? For example, to get sales by region by customer-type, if I were to write a plain Oracle SQL query it would be:

select g.region_name, c.cust_type, sum(f.gross_sales), sum(f.net_sales)
from
sales_fact f partition (P_JAN) f, customer partition (P_JAN) c,
geography g
where f.cust_id = c.cust_id
and f.geo_id = g.geo_id
group by g.region_name, c.cust_type

In WEBI, I would just drag/drop these dimensions and measures and put my month from time-dimension as a filter, it automatically generates an equivalent query. However, when I see the query that is run, I don’t see any partition references such as in my query above. It is also slower than plain SQL query. I suspect BO generated query is not using the partition. How do I force BO to use the partition as I would in a plain SQL query as above. Thanks in advance.

dave99 (BOB member since 2009-10-16)

2

Hi Dave!

Welcome to B :mrgreen: B!

The easiest way to ensure it is using the correct partion is in the SQL view of the ‘Edit Query’ panel. This will be verabtim of how exactly the SQL syntax will executed. You should be able to copy/paste that SQL syntax into your Oracle query tool and obtain the same result set. You can also use ‘Use Custom SQL’ option in the SQL view to make nessecary adjustments (although this is not always recommended.)

Good luck!

Ryan Bierschenk :us: (BOB member since 2009-01-26)