Adding a filter to execute on database side

[Poochi62]

I am relatively new to Cognos Report Studio but am having a hard time with reports that use a large dataset that then needs to be filtered within the report.  From what I understand, I am finding that the entire dataset gets served up to the content store and then your report filters are applied on that large dataset.  This makes my report have terribly slow response time.  I had my DBA trace the SQL only to find that the actual SQL statement was not utilizing my filter to append the where clause in the SQL statement and therefore not using the index on the table.  So the report was cranking hundreds of thousands of rows of data, then limiting that down to the 100 or so rows I need.  Very inefficient!

I have read that there is a way to pass a parameter or filter back to the database side to execute.  Then your report is only dealing with a subset of the data.  Not sure if this is done with a Prompt Macro or by using a SQL object??  Like I said, I am new to this.

I am currently using Framework Manager with a Pass-Through SQL statement to create my package.  I am using that Package in Report Studio to build my report.  I am really surprised that the filters applied in Report Studio do nothing to limit the data prior to rendering it.  This seems counter-productive.

[RubenvdLinden]

What kind of datasource are you using?

Normally, Cognos should not locally process your dataset unless you use functions that require local processing.

Please select your query in Report Studio and set the Processing property to 'Database Only'. If the query fails, it's probably because you use a function that specifically requires local processing.

[Poochi62]

My datasource is a Package I created within FM.  It is a Custom SQL (Pass-Through) from an Oracle database.

I tried using the 'Database Only' option on the query and it did nothing different.  It still cranks for a long time.

[RubenvdLinden]

The Pass-Through SQL is probably your bottleneck.

If you specify a query as Pass-Through, Framework Manager does not alter it at runtime.
Instead, it processes any alterations (such as filters) locally.

In Framework Manager, please set your query to run as 'Native' SQL Type instead of 'Pass-Through'.
This will allow Cognos 10 to add statements to your query and thus improve performance on the database side.

[Poochi62]

Wow!  Lightning fast!   :)

It can't be that easy??  When I first change that setting from 'Pass-Through' to 'Native SQL' and hit 'Test Sample', it returned a SQL statement though not one exactly like my original, but didn't return any test rows.  The 2nd time I tested it, that SQL was not displayed and instead the test rows came back.  What did it do?  Did it turn my normal SQL into Native Cognos SQL??

[RubenvdLinden]

If you only use ANSI SQL, you can use Cognos SQL which gives the best possible performance because it ignores unused table attributes.

Native SQL is native to the underlying datasource, which is Oracle in your situation. The advantage of Native SQL is that you can use database-specific SQL keywords that are not available in Cognos SQL (e.g. CONNECT BY). However, you cannot use database prompts or variables directly in your SQL.
Cognos will use your Native SQL in subqueries to optimize performance on the database side.

Pass-through is basically the same as Native SQL, however it will not use subqueries to optimize performance on the database side. It will literally execute your SQL, then process any filters or tranformations locally which is really bad for performance. You should only use Pass-through SQL in Cognos if your datasource does not support derived tables.

My best practice is to just import the required tables from your datasource into Framework Manager (select * from table) and then create the appropriate relationships between them. Let Cognos do the work for you.

[blom0344]

That is a very effective piece of information, Ruben.. 

[cognostechie]

Native SQL is native to the underlying datasource, which is Oracle in your situation. The advantage of Native SQL is that you can use database-specific SQL keywords that are not available in Cognos SQL (e.g. CONNECT BY). However, you cannot use database prompts or variables directly in your SQL.
Cognos will use your Native SQL in subqueries to optimize performance on the database side.

I beg to differ a little here. It is possible to use Prompts in Native SQL. Consider this :

Select
  Customer_ID,
  Customer_Name,
  DOB
from DatabaseName.dbo.Tablename
where DOB between #prompt('Pl. enter Starting Date (format: YYYY-MM-DD)','Date''2012-01-01''')#
and #prompt('Pl. enter Ending Date (format: YYYY-MM-DD)','Date''2012-01-01''')#

This will prompt you for a range of Dates and then retrieve only those Customers who were born during that date range.

[RubenvdLinden]

Great cognostechnie, I didn't know that Cognos macros work in Native SQL.

However, you can't use the native database prompts and variables, such as

SELECT Customer_ID, Customer_Name, DOB
FROM tablename
WHERE DOB BETWEEN '&begindate' AND '&enddate'

[cognostechie]

Sorry for misunderstanding your sentence. One reason for that would be that Native SQL is re-written by Cognos at the time of execution. This means that it validates the Native SQL when you write it and it can understand Cognos macros but may not be able to understand some database objects.