Slow Rank function

[Robert Kostecki]

I am working on migrating a set of reports from Business Objects (Deski 6.5) to Cognos 10.1. on Oracle 11g EE

There seems to be a challenge with Rank function in Cognos when used with "at" clause i.e. rank([YTD RC] at [UBR_Product], [Client] for [UBR_Product]). The query is being pushed from database to Cognos local processing causing extremely slow (hours vs minutes) execution over a set of 25k members.

I tried to set the local processing to database only with no success. If anybody has a quick tip how to resolve without rewriting ranks in SQL I would appreciate.

[charon]

Hi,

in case you use a crosstab, you might wanne try the dimensional function "topcount".
The expression editor will show you the syntax of that function..

gl & regards
charon

[bberryhill]

From the syntax of your statement I think you are reporting over a relational model.  If by chance you are using a dimensional data source the rank syntax is different.

rank (numeric_expression [ASC|DESC] [tuple member_expression { , member_expression }] within set set_expression)

Here's an expression from one of my reports: rank( [Revenue] within set [TopCustomers]

[Robert Kostecki]

Thanks, unfortunately none of the suggestions resolves the issue i.e. pushing rank processing back to the database.

As noted report was written in "relational reporting style" with derived query subjects (hence Oracle mentioned in the initial post).

In order to address the issue I added derived Query Subject with only columns required for the rank and thus avoiding "at" clause. However I have found that the derived query does not roll up within only its own query items but also adds items from the base/parent query subject.

E.g. in example above if parent query subject had "Sales Country" Data Item then this item would be implicitly added to the final derived query (even though it was not used in it), thus splitting records and distorting ranking figures.

Derived Q (Column 1, 2, Rank() ) <--- Base Q (Column 1, 2, 3)  => SELECT Column 1, 2, 3, Rank()....

The only work around I could find was to build rank specific Query Subjects containing only columns required for the Rank (thus removing "at") and then joining them back to the main query. Alternatively using free-hand SQL with custom built ranks.

Hope this helps.

[navissar]

Yep, was able to recreate this. Actually, any rank function isn't sent to the DB on my test environment. I see three options:
1. Switch to DQM. It'll change the way queries are processed and will make local processing, in as much as there is, faster.
2. Write down the queries' SQL. Sucks, I know.
3. Contact IBM, maybe they have something.