Print Page - Nested loops when recommending aggregates

Title: Nested loops when recommending aggregates
Post by: patton on 25 Jan 2018 07:00:37 AM

Hi all

We have a Dynamic Cube with a defined in memory aggregate. Mostly small dimension, except for one which has 20k rows.
When we run aggregate advisor and select only in-memory aggregates to be recommended, it runs for hours without completing. It appears that the Aggregate Advisor sends a query that involves a loot of nested loops. When we hint that query, it executes fast.
This only happens in our preproduction database and not in our development server. Same model, same data.
Is there a way to avoid nested loops? Is there a way to give hints when executing aggregate advisor or in cubes?

Title: Re: Nested loops when recommending aggregates
Post by: AnalyticsWithJay on 16 Feb 2018 06:42:46 AM

Hi,

Have you looked into command blocks (https://www.ibm.com/support/knowledgecenter/en/SSEP7J_10.2.1/com.ibm.swg.ba.cognos.ug_cra.10.2.1.doc/c_usecomblocks.html)?

Jay

Title: Re: Nested loops when recommending aggregates
Post by: patton on 02 Mar 2018 06:58:43 AM

Thanks for the answer Jay.

In theory, I could use a command block to force the query to use hints or sth similar?

COGNOiSe.com - The IBM Cognos Community

IBM Cognos Analytics Platform => Cognos Analytics => Dynamic Cubes => Topic started by: patton on 25 Jan 2018 07:00:37 AM