Using Dynamic Prompt does not pass the filter to the database

[damzI]

Hi All,

My fact table needs to filter on the most recent date from the date dimension. So I am using one query to filter the most recent date from the date dimension; and another query with the fact information to filter from the previous filter for the most recent date. The problem is that the fact query fetches ALL the data and then applies the filter. I have set the processing of both queries at the database level. Is there a way to create the above dynamic filter so it passes the filter to the database - without pulling all the data ?

I have also tried a hard coded filter in the fact and that works like a charm. The problem is that my filter needs to be dynamic.

Thanks ! - D

[bdbits]

If you are using two seperate queries, it is doing exactly what you told it to do - run this query, run the other query, and join them together.

I cannot think of a single reason you would not be able to do this in a single query with a filter. You've not indicated anything about how your data is structured, so I cannot begin to guess at the syntax of the expression you need.

[damzI]

Thanks for you fast response. As suggested I am now using one query instead of 2. So I have been able to get the where clause. But it does not factor in the actual value (max-date), instead uses a placeholder in the query. Is there any way I can get the query to pick the actual values without hard-coding it ?

we are using a hadoop cluster with one star schema.

Thanks, D

[bdbits]

I have no access to and never used a hadoop cluster with Cognos, but I am guessing you are using one of the SQL-on-Hadoop front ends. Are you looking at the "Cognos SQL" or "Native"? A bit of a guess here, as I said never used Hadoop on Cognos, but it may be showing you a placeholder that it actually does populate when you run the report. Does it appear to be running too long? Is anyone able to capture what is received at the Hadoop side? Those are things I would be looking at for any data source.