filters applied outside query

[tupac_rd]

I have 3 query subjects joined, of which 2 are dimensions and one a fact table. The fact table is a straight pull from the database (select * from Fact). But the 2 dimensions are actually SQL statements of a combination of tables...
So Dim1 and Dim2 are like
(select distinct tab1.col1,
                      tab2.*
                      tab3.col1
  from
          tab1, tab2, tab3
  where tab1.col2 = tab3.col2 and tab2.col3 = tab3.col3)

The 3 tables (Dim1, Dim2 and Fact) are under a namespace. When we query using these and add a filter, for eg. Dim1.col1 = ' ABCD' the queries are very slow. I looked at the underlying SQL, and the filter is being applied outside of the query after joining the Dim1,Dim2 and Fact. Is there a way to push the filter to the SQL statement of Dim1 instead.

The only way I figured out was adding a prompt macro in the Dim1 SQL, but we don't want to add prompt macros to all the columns in the Dim1 SQL....

Is there a better way of doing it, so that any filters using Dim1 SQL are pushed to Dim1 SQL and any filters using Dim2 SQL are pushed to Dim2 SQL ....

Thanks
dd

[blom0344]

Explicitly coding distinct WITHIN a model is a VERY risky thing. Big chance that the database optimizer migrates the distinct to act upon the entire set. A distinct means sorting data en removing duplicates with possible horrendous effect.
Can you post the entire SQL as it is generated. We may have a better clue then!!

[tupac_rd]

you are correct, we are not explicitly coding distinct in the query in FM for Dim1 and Dim2, but when Cognos sends the query to Oracle, its adding the distinct.......

[blom0344]

Which is really normal behavior if you do NOT fetch fact (aggregated) data, but should not be applied with a regular data query

[tupac_rd]

Can you post the entire SQL as it is generated. We may have a better clue then!!

Here is the SQL

Code Select Expand


SELECT DISTINCT DIM1.COLUMN2,
        DIM2.COLUMN2,
        VIEW1.COLUMN5,
        TABLE7.COLUMN4
FROM


(SELECT DISTINCT TABLE1.COLUMN1 COLUMN1,
        TABLE1.COLUMN2 COLUMN2,
        TABLE2.COLUMN1 COLUMN3,
        TABLE3.COLUMN1 COLUMN4,
        TABLE3.COLUMN2 COLUMN5,
        TABLE4.COLUMN1 COLUMN6,
        TABLE4.COLUMN2 COLUMN7,
        TABLE4.COLUMN3 COLUMN8,
        TABLE4.COLUMN4 COLUMN9,
        TABLE4.COLUMN5 COLUMN10,
        TABLE4.COLUMN6 COLUMN11,
        TABLE4.COLUMN7 COLUMN12
    FROM   
        SCHEMA.TABLE1 TABLE1,
        SCHEMA.TABLE2 TABLE2,
        SCHEMA.TABLE3 TABLE3,
        SCHEMA.TABLE4 TABLE4
    WHERE
        TABLE3.COLUMN2 = TABLE1.COLUMN3 AND
        TABLE3.COLUMN2 = TABLE2.COLUMN3 AND
        TABLE3.COLUMN2 = TABLE4.COLUMN3 ) DIM1,

(SELECT     TABLE4.COLUMN1 COLUMN1,
        TABLE4.COLUMN2 COLUMN2,
        TABLE5.COLUMN1 COLUMN3,
        TABLE5.COLUMN2 COLUMN4       
    FROM   
        SCHEMA.TABLE4 TABLE4,
        SCHEMA.TABLE5 TABLE5,
        SCHEMA.TABLE6 TABLE6
       
    WHERE
        TABLE5.COLUMN2 = TABLE4.COLUMN3 AND
        TABLE5.COLUMN2 = TABLE6.COLUMN3 ) DIM2,

SCHEMA.VIEW1 VIEW1,
SCHEMA.TABLE7 TABLE7

WHERE DIM1.COLUMN3 = '201002' AND
    DIM1.COLUMN5 = 'a' AND
    DIM 2.COLUMN4= 'H' AND
    VIEW1.COLUMN4= '2007'
    TABLE7.COLUMN4= 'PAID' AND
    VIEW1.COLUMN3 = DIM1.COLUMN4 AND
    VIEW1.COLUMN4 = DIM2.COLUMN3 AND
    VIEW1.COLUMN5 = TABLE7.COLUMN4

What I am trying to do is get the filter DIM1.COLUMN3 = '201002' right into the SQL statement of DIM1...without adding a prompt macro in the SQL ......

[blom0344]

No, if you use a report detail filter, the statement is always added to the outer where clause. But I suspect that the real performance killer is the distinct. A large enough dataset would be killing..
If you query a fact with dimensions why is the aggregate missing? Are you not fetching a measure from the fact?
And why are you using a distinct for one of the dimension SQL definitions?