Author Topic: Nested loops when recommending aggregates  (Read 338 times)

Offline patton

  • Full Member
  • ***
  • Join Date: Dec 2016
  • Posts: 32
  • Forum Citizenship: +0/-0
Nested loops when recommending aggregates
« 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?

« Last Edit: 25 Jan 2018 07:10:30 am by patton »

Offline CognoidJay

  • Statesman
  • ******
  • Join Date: Jun 2016
  • Posts: 395
  • Forum Citizenship: +42/-1
    • Cognos With Jay
Re: Nested loops when recommending aggregates
« Reply #1 on: 16 Feb 2018 06:42:46 am »
Hi,

Have you looked into command blocks?

Jay

Offline patton

  • Full Member
  • ***
  • Join Date: Dec 2016
  • Posts: 32
  • Forum Citizenship: +0/-0
Re: Nested loops when recommending aggregates
« Reply #2 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?

 


       
Twittear