Hello,
​
Thanks!
------------------------------
Dale Simpson
------------------------------
​​
#CognosAnalyticswithWatson

Hello Dale,

I saw your previous post with the title "Invalid coercion from 'member' to 'string'" and concluded that you are using a dimensional package. A so called DMR package.
And if you are new to Cognos this is far more complex than a relational package. So my advice would be to use a relational package if available and you can use the statements in your post.
Or create a dataset from the package with all the data you need for your report and add this to a datamodule and use the datamodule as the source for your report.

But if that all is not possible, then keep in mind that you should only use dimensional functions in a dimensional package.
The second is that you should not use detail filters in a dimensional model. They are performance killers as you have experienced.

What you could do is:
Remove the detail filter and create several query items:
[Part/Concern] with the definition [Qls Rpr Part - Long Name] + [Qls Rpr Conc - Long Name]
[Concern] with the definition filter ( [Qls Rpr Conc]; [Qls Rpr Conc - Long Name] in ( ?ConcLongname? ) )
[Part] with the definition filter ( [Qls Rpr Part]; [Qls Rpr Part - Long Name] in ( ?PartLongname? ) )

Then add these in the list and run the report. it should be faster.

------------------------------
Thomas van der Meer
------------------------------

The most likely explanation for the performance degradation is that your database has no index reflecting your concatenated "column," and therefore has to perform a complete scan to honor your filter. You could work with your DBA to create a calculated index to match your concatenated column, but this may not be an option in your environment.

Likely the simplest approach to improving performance is to limit the use of your Part/Concern column to display, only, and break your filter into two parts: 

[Qls Rpr Part - Long Name] = ?Part_prompt?
AND
[Qls Rpr Conc - Long Name] = ?Concern_prompt?

Note: this will likely only address your performance issue if both of the ~Long Name columns already have existing indexes. If you don't have direct access to the underlying database (or the DBA) to check, you'll just have to test. (But ultimately, all query performance tuning is empirical, anyway.)

Hope that helps. Good luck.

------------------------------
Michael Walter
Business Analyst
TX HHSC
Austin
------------------------------

In general writing predicates (filters) along the lines of  the lines <expression> <comparison-operator> ... are more expensive for a system to resolve.

Reduces opportunity for system to locate/skip data, system has to evaluate the expression on a row-by-row basis etc.

Faster expressions would be those comparing the values to each column i.e. Qls Rpr Part in ('DOOR' , .... ) and  Qls Rpr Conc in  ('Dent', ...) etc.

See also. Chap 6. http://www.redbooks.ibm.com/redbooks/pdfs/sg248121.pdf

------------------------------
NIGEL CAMPBELL
------------------------------

​Thank you all for the great feedback. I resolved the issue using parts of each of your suggestions

Issue resolved by:

• Eliminating the use of concatenated field. This was originally needed due to the User's not having the separated fields to start with. The User's would run a prior report (from a separate software) to get the starting info used in this report. We revisited and modified the prior report so the user would receive the separated fields instead of the concatenated one. We ended up changing the data source for the first report from a separate software/application to the Cognos driven website as well.  After that we were able to use the separated fields to filter this report much faster.
• In order for the first report to work, we worked with our IT team to add indexes to these fields in the database.

Thanks again!

------------------------------
Dale Simpson
------------------------------