Analytics Software

Hi everyone,

The following issue is very strange.

We have a star schema and a fact table partitioned on a date column 'date_debut'. A biee model has been created on top of this star schema.

In order to not perform a full table scan on the facts, I have added the following code to the BI answers report filter (converted to sql):
"Faits Stock". *"Date Debut" >= EVALUATE* ('TO_DATE(%1,%2)', '20070101','YYYYMMDD') and "Faits Stock". *"Date Debut" < EVALUATE* ('TO_DATE(%1,%2)', '20100101','YYYYMMDD')

So far it works OK.
Then I've added the following filter statement
FAITS STOCK. Date Liquidation <= Evaluate ( TO_DATE(%1,%2),'20100304', 'YYYYMMDD')

When I run the report, the first part of the first statement ( "Faits Stock". *"Date Debut" >=* EVALUATE('TO_DATE(%1,%2)', '20070101','YYYYMMDD') ) is not anymore included in the fired sql .

See log (down on the page).

This leads to extremely poor performance (can't wait for the answer). Moreover we are wondering why some filters disappear form the query and thus possibly lead to wrong results.

If anyone's got an idea...

Thank you all

FYI: server version is 10.1.3.3.2.071217.1900

-------------------- SQL Request:
SET VARIABLE QUERY_SRC_CD='Report',SAW_SRC_PATH='/shared/P2 STOCK/Dépendance - Montants nets par code prestation sur base de P2STOCK';
SELECT "Dim Actes"."Code Prestation" saw_0, "Faits Stock"."Date Debut - Année" saw_1,
"Faits Stock"."Date Debut - Mois" saw_2, "Faits Stock".Net saw_3
FROM "P2 STOCK"
WHERE (("Faits Stock".Indicateur <> '7') OR ("Dim Actes".Acte LIKE 'RESTIT%'))
AND (("Faits Stock"."Indicateur Storno" = '0') OR ("Dim Actes".Acte LIKE 'RESTIT%'))
AND ("Faits Stock"."Date Debut" >= EVALUATE('TO_DATE(%1,%2)', '20070101','YYYYMMDD')
and "Faits Stock"."Date Debut" < EVALUATE('TO_DATE(%1,%2)', '20100101','YYYYMMDD'))
AND ("Dim Actes"."Code Prestation" BETWEEN '601' AND '650')
AND ("Faits Stock"."Date Liquidation" <= EVALUATE('TO_DATE(%1,%2)', '20100101','YYYYMMDD')) ORDER BY saw_0, saw_1, saw_2


+++Administrator:430000:430005:----2010/05/25 14:53:04

-------------------- General Query Info:
Repository: Star, Subject Area: P2STOCK, Presentation: P2 STOCK


+++Administrator:430000:430005:----2010/05/25 14:53:04

-------------------- Logical Request (before navigation):

RqList
DIM_ACTES.Code Prestation as c1 GB,
FAITS STOCK.Date Debut - Année as c2 GB,
FAITS STOCK.Date Debut - Mois as c3 GB,
Net:[DAggr(FAITS STOCK.Net by [ DIM_ACTES.Code Prestation, FAITS STOCK.Date Debut - Année, FAITS STOCK.Date Debut - Mois] )] as c4 GB
DetailFilter: FAITS STOCK.Date Liquidation <= Evaluate( TO_DATE(%1,%2),'20100101', 'YYYYMMDD')
and FAITS STOCK.Date Debut < Evaluate( TO_DATE(%1,%2),'20100101', 'YYYYMMDD')
and FAITS STOCK.Date Debut >= Evaluate( TO_DATE(%1,%2),'20070101', 'YYYYMMDD')
and (FAITS STOCK.Indicateur Storno = '0' or DIM_ACTES.Acte like 'RESTIT%')
and (FAITS STOCK.Indicateur <> '7' or DIM_ACTES.Acte like 'RESTIT%')
and DIM_ACTES.Code Prestation between '601' and '650'
OrderBy: c1 asc, c2 asc, c3 asc


+++Administrator:430000:430005:----2010/05/25 14:53:05

-------------------- Execution plan:

RqList <<110774>> [for database 3023:83993:P2stock,46]
DIM_ACTES.CODE_PRESTATION as c1 GB [for database 3023:83993,46],
extract(year from FAITS_STOCK.DATE_DEBUT) as c2 GB [for database 3023:83993,46],
extract(month from FAITS_STOCK.DATE_DEBUT) as c3 GB [for database 3023:83993,46],
sum(FAITS_STOCK.NET by [ DIM_ACTES.CODE_PRESTATION, extract(year from FAITS_STOCK.DATE_DEBUT), extract(month from FAITS_STOCK.DATE_DEBUT)] ) as c4 GB [for database 3023:83993,46]
Child Nodes (RqJoinSpec): <<110921>> [for database 3023:83993:P2stock,46]
DIM_ACTES T86662
FAITS_STOCK T86755
DetailFilter: DIM_ACTES.ACTE_ID = FAITS_STOCK.ACTE_ID and FAITS_STOCK.DATE_DEBUT < Evaluate( TO_DATE(%1,%2),'20100101', 'YYYYMMDD')
and not Evaluate( TO_DATE(%1,%2),'20100101', 'YYYYMMDD') < FAITS_STOCK.DATE_LIQUIDATION
and (FAITS_STOCK.INDICATEUR_STORNO = '0' or DIM_ACTES.ACTE like 'RESTIT%')
and (not FAITS_STOCK.INDICATEUR = '7' or DIM_ACTES.ACTE like 'RESTIT%') and not '650' < DIM_ACTES.CODE_PRESTATION [for database 0:0]
GroupBy: [ DIM_ACTES.CODE_PRESTATION, extract(year from FAITS_STOCK.DATE_DEBUT), extract(month from FAITS_STOCK.DATE_DEBUT)] [for database 3023:83993,46]
OrderBy: c1 asc, c2 asc, c3 asc [for database 3023:83993,46]


+++Administrator:430000:430005:----2010/05/25 14:53:05

-------------------- Sending query to database named P2stock (id: <<110774>>):

select T86662.CODE_PRESTATION as c1,
TO_NUMBER(TO_CHAR(T86755.DATE_DEBUT, 'yyyy'), '9999') as c2,
TO_NUMBER(TO_CHAR(T86755.DATE_DEBUT, 'MM'), '99') as c3,
sum(T86755.NET) as c4
from
DIM_ACTES T86662,
FAITS_STOCK T86755
where ( T86662.ACTE_ID = T86755.ACTE_ID
and T86755. DATE_DEBUT < TO_DATE('20100101','YYYYMMDD')
and TO_DATE('20100101','YYYYMMDD') >= T86755. DATE_LIQUIDATION
and (T86755.INDICATEUR_STORNO in ('0') or T86662.ACTE like 'RESTIT%')
and (T86755.INDICATEUR <> '7' or T86662.ACTE like 'RESTIT%')
and T86662.CODE_PRESTATION <= '650' )
group by T86662.CODE_PRESTATION, TO_NUMBER(TO_CHAR(T86755.DATE_DEBUT, 'yyyy'), '9999'), TO_NUMBER(TO_CHAR(T86755.DATE_DEBUT, 'MM'), '99')
order by c1, c2, c3