External Data

[Bonediggler]

Experts -

I am running a simple query off our data warehouse that includes an external file containing about 500 claim numbers.  I wrote the query by putting [Warehouse.Claim_Number] IN ([External_File.Claim_Number]) in the filter area. 

The problem is the report takes about 30 minutes to run.  If I write the report without the external data and rather put [Warehouse.Claim_Number] IN ('123','456','789' etc) the report will complete in about 2 seconds. 

Clearly using an external file is slowing things down but any idea why and/or what the solution is to improve performance?

Thanks in advance!

[bdbits]

This is somewhat dependent on data sources and various settings, but in general I think this is what is happening.

When you use the external file, Cognos is reading the data from the database as if your filter did not exist and stores it locally to the server (probably on disk, maybe in memory). Then it reads the external file, and applies the values as specified in your filter to the locally cached database result set. This is called "local processing". When you put the values in the filter itself, they are being passed as part of the SQL sent to the database. As you have seen, this is much, much more efficient.

The solution would be to ETL your external file into the database and do a join in your FM model (package), and use that instead of an external file.

[Bonediggler]

Thanks bdbits - getting the concepts behind what is happening is very helpful.

Unfortunately I cannot implement the ETL idea, but will work on filtering the warehouse data further so what is going into memory is hopefully a smaller data set.