Categories
CACHE query is not used when use Prompted Values
Answers
-
As mentioned before, remove all of the the WHERE clause from the cached query.
This is what you should be seeding:
SELECT a , b , c FROM (
SELECT
"xx"."xx"."Gls Nacionality" s_4,
.....
FROM "Proyecto X"
0 -
Removed all the filters of the firest query and yet the second query (filtered) does not hit the cache
Query Cached:
SELECT s_0, s_1, s_2, s_3, s_4 FROM (
SELECT
0 s_0,
DM.T1.F1 s_1,
DM.T2.F2 s_2,
DM.T3.F3 s_3,
REPORT_AGGREGATE(DM.T3.F3 BY ) s_4
FROM DM
) djm ORDER BY 1, 2 ASC NULLS LAST, 3 ASC NULLS LAST
FETCH FIRST 65001 ROWS ONLY
;
Filtered Query
SELECT s_0, s_1, s_2, s_3, s_4 FROM (
SELECT
0 s_0,
DM.T1.F1 s_1,
DM.T2.F2 s_2,
DM.T3.F3 s_3,
REPORT_AGGREGATE(DM.T3.F3 BY ) s_4
FROM DM
WHERE
(T2.F2 = 'F')
) djm ORDER BY 1, 2 ASC NULLS LAST, 3 ASC NULLS LAST
FETCH FIRST 65001 ROWS ONLY
;
0 -
Try caching the query without a filter and order by:
SELECT s_0, s_1, s_2, s_3, s_4 FROM (
SELECT
0 s_0,
DM.T1.F1 s_1,
DM.T2.F2 s_2,
DM.T3.F3 s_3,
REPORT_AGGREGATE(DM.T3.F3 BY ) s_4
0 -
Hello Joel
I have tested this using another fact column, which does not use REPORT_AGGREGATE, and it worked , apparently there is an issue with hit cache with REPORT_AGGREGATTE and filters
If the query does not have Report_agregates it works even when the query is a subset of the cached query
if the report has Report_agreggate and the SQL is exactly the same it works
However i haven't found documentation abut that incompatibilty, i base my conclusions only on my tests
Camilo
0 -
That was going to be my next suggestion - to remove the REPORT_AGGREGATE. If the Aggregation Rule of a measure is set to
Server Complex Aggregate then OBIEE will do a REPORT_AGGREGATE. Is this required on your report by the way?
0 -
Yes, It is required, that is the data that teh report needs to display
0 -
It is not a Server Copmplex Aggregate , only a Count (Distinct()) in the RPD, and Default(Sum) in the Presentation Server
0
