Answered by:
Error message "Infinite recursion detected. The loop of dependencies is: ." but I have no recursion?

Hi,
I have a query like
WITH MEMBER [Product].[AttribE].[grp6] AS Aggregate( { [Product].[AttribE].&[a], [Product].[AttribE].&[b], [Product].[AttribE].&[c], [Product].[AttribE].&[d], [Product].[AttribE].&[e] } ) ,Caption = 'Some text' MEMBER [Product].[AttribE].[grp0] AS Aggregate(( [Product].[AttribE].[AttribE].Members  { [Product].[AttribE].&[a], [Product].[AttribE].&[b], [Product].[AttribE].&[c], [Product].[AttribE].&[d], [Product].[AttribE].&[e] } )) ,Caption = 'All Others' SELECT { ([Measures].[m], [Period].[YearSixQtr].[Qtr].&[200903], [Statistic].[Statistic].&[none]), ([Measures].[m], [Period].[YearSixQtr].[Qtr].&[200906], [Statistic].[Statistic].&[none]), ([Measures].[m], [Period].[YearSixQtr].[Qtr].&[200909], [Statistic].[Statistic].&[none]), ([Measures].[m], [Period].[YearSixQtr].[Qtr].&[200912], [Statistic].[Statistic].&[none]), ([Measures].[m], [Period].[YearSixQtr].[Qtr].&[201003], [Statistic].[Statistic].&[none]), ([Measures].[m], [Period].[YearSixQtr].[Qtr].&[201006], [Statistic].[Statistic].&[none]) } ON COLUMNS, NON EMPTY ({ ([Product].[AttribA].&[A], [Product].[AttribB].&[B1], [Product].[AttribC].&[C], [Product].[AttribD].&[D1]) } * ( { [Product].[AttribE].[grp6], [Product].[AttribE].[grp0] } )) + ({ ([Product].[AttribA].&[A], [Product].[AttribB].&[B1], [Product].[AttribC].&[C], [Product].[AttribD].&[D2]) } * ( { [Product].[AttribE].[grp6], [Product].[AttribE].[grp0] } )) + ({ ([Product].[AttribA].&[A], [Product].[AttribB].&[B2], [Product].[AttribC].&[C], [Product].[AttribD].&[D3]) } * ( { [Product].[AttribE].[grp6], [Product].[AttribE].[grp0] } )) + ... (about 1100 more lines like those above) ON ROWS FROM [MyCube]
When I run it, I get an error message "Infinite recursion detected. The loop of dependencies is: .".
But I am not aware of any recursion involved. I am also a bit confused by the dot, and not some clear member name in the "loop of dependencies" part of the message. Can it be that AS has some other issue than an infinite recursion?
When I remove some rows, the query runs fine, but at slightly less than 1000 rows, the error message appears. Adding or removing relationships between the attributes does not change this behavior at all. Statistic is a shell dimension with the [none] element being the element linked to the fact table. the [m] measure is not calculated in any way, so the structure of the query seems rather simple, apart from the long list of items. I had similar queries running with 50000 rows without problems.
Any ideas what I could do?
Thanks
Frank
Question
Answers

Hi,
As some of my observations pointed me to assume an issue in the AS statement parser, I just tried to resolve the many crossjoins in the statement myself and not putting them in the statement. This will make parsing for AS a little bit simpler. Restating the statement as follows resolved the issue:
WITH
MEMBER [Product].[AttribE].[grp6] AS
Aggregate( { [Product].[AttribE].&[a], [Product].[AttribE].&[b], [Product].[AttribE].&[c], [Product].[AttribE].&[d], [Product].[AttribE].&[e] } )
,Caption = 'Some text'
MEMBER [Product].[AttribE].[grp0] AS
Aggregate(( [Product].[AttribE].[AttribE].Members  { [Product].[AttribE].&[a], [Product].[AttribE].&[b], [Product].[AttribE].&[c], [Product].[AttribE].&[d], [Product].[AttribE].&[e] } ))
,Caption = 'All Others'
SELECT {
([Measures].[m], [Period].[YearSixQtr].[Qtr].&[200903], [Statistic].[Statistic].&[none]),
([Measures].[m], [Period].[YearSixQtr].[Qtr].&[200906], [Statistic].[Statistic].&[none]),
([Measures].[m], [Period].[YearSixQtr].[Qtr].&[200909], [Statistic].[Statistic].&[none]),
([Measures].[m], [Period].[YearSixQtr].[Qtr].&[200912], [Statistic].[Statistic].&[none]),
([Measures].[m], [Period].[YearSixQtr].[Qtr].&[201003], [Statistic].[Statistic].&[none]),
([Measures].[m], [Period].[YearSixQtr].[Qtr].&[201006], [Statistic].[Statistic].&[none])
}
ON COLUMNS,
NON EMPTY
{ ([Product].[AttribA].&[A], [Product].[AttribB].&[B1], [Product].[AttribC].&[C], [Product].[AttribD].&[D1], [Product].[AttribE].[grp6]),
([Product].[AttribA].&[A], [Product].[AttribB].&[B1], [Product].[AttribC].&[C], [Product].[AttribD].&[D1], [Product].[AttribE].[grp0]),
([Product].[AttribA].&[A], [Product].[AttribB].&[B1], [Product].[AttribC].&[C], [Product].[AttribD].&[D2], [Product].[AttribE].[grp6]),
([Product].[AttribA].&[A], [Product].[AttribB].&[B1], [Product].[AttribC].&[C], [Product].[AttribD].&[D2], [Product].[AttribE].[grp0]),
([Product].[AttribA].&[A], [Product].[AttribB].&[B2], [Product].[AttribC].&[C], [Product].[AttribD].&[D3], [Product].[AttribE].[grp6]),
([Product].[AttribA].&[A], [Product].[AttribB].&[B2], [Product].[AttribC].&[C], [Product].[AttribD].&[D3], [Product].[AttribE].[grp0])
... (about 2200 more lines like those above)
}
ON ROWS
FROM [MyCube]
Neverthless, the error message is a bit misleading, as there is no recursion involved from cube configuration or MDX statement side, and even if there might be some internal recursion in the AS statement parser, it definitely is not infinite.
Lesson learned: Do not trust that AS can parse long MDX statements with some complexity correctly, simplify them instead to make it more easy for AS to parse them.
Thank you Deepak for sharing your ideas.
Frank
 Marked as answer by FrankPl Thursday, November 25, 2010 10:58 AM
All replies

".. When I remove some rows, the query runs fine, but at slightly less than 1000 rows, the error message appears .."  is the error determined by the number of rows, or by the inclusion of specific rows? Also, what happens on a server with more memory  see this (rather dated) article by Mosha:
Recursion in MDX  How deep can it go
...
Basically, the stack size is made virtually infinite, i.e. limited only by the amount of memory in the machine. In Analysis Services 2005 the implementation is very similar, with the difference that instead of creating new threads it creates new fibers, which are lighter and should put less load on the Windows.
 Deepak 
Hi Deepak,
It seems to be the number of rows, as commenting out some rows (no matter which) lets the query return after a few seconds.
I am rather sure there is no recursion from my side involved, as the result is the same after commenting out the complete calc script except for the CALCULATE, and simplifying the columns of the query to "{[Measures].[m]} * [Period].[YearSixQtr].[Qtr].Members".
So there must be some AS internal reason  be it recursion or something different  causing the error message.
The cube is rather small: it has just three dimensions: 1650 products, time with quarterly granularity (34 quarters), and the shell dimension which has the value "none" for all fact records (the other members are assigned in the calc script if this is not commented out). The fact table has just 27000 records.
The server is a virtual server running on HyperV with 4GB RAM configured for this instance. I do not see a spike of memory usage of the msmdsrv.exe in perfmon in case the error appears, but maybe this happens too fast for the sampling rate of perfmon.
What is strange is that in SQL Profiler, I do not see a "Query Begin" event in case of this error, just an Error event for the infinite recursion. If the error does not appear due to reduced rows, I see the Query Begin and Query End events.
Do you have any idea which of the perfmon counters would show the stack memory, and  more importantly  which memory setting I would have to tweak to increase the stack  if that is possible at all loooking at Mosha's posting?
Frank


As it would have needed another virtual engine to shut down to get some headroom to increase memory, I just stopped some other services using some RAM, thus making about 1.5 GB free. This did not change the behavior at all.
Another strange observation: the error also appears when I am connected to a database that does not contain the cube in the from clause. Maybe this is connected to the observation that there is not even a "Query Begin" event in Profiler?

Hi,
As some of my observations pointed me to assume an issue in the AS statement parser, I just tried to resolve the many crossjoins in the statement myself and not putting them in the statement. This will make parsing for AS a little bit simpler. Restating the statement as follows resolved the issue:
WITH
MEMBER [Product].[AttribE].[grp6] AS
Aggregate( { [Product].[AttribE].&[a], [Product].[AttribE].&[b], [Product].[AttribE].&[c], [Product].[AttribE].&[d], [Product].[AttribE].&[e] } )
,Caption = 'Some text'
MEMBER [Product].[AttribE].[grp0] AS
Aggregate(( [Product].[AttribE].[AttribE].Members  { [Product].[AttribE].&[a], [Product].[AttribE].&[b], [Product].[AttribE].&[c], [Product].[AttribE].&[d], [Product].[AttribE].&[e] } ))
,Caption = 'All Others'
SELECT {
([Measures].[m], [Period].[YearSixQtr].[Qtr].&[200903], [Statistic].[Statistic].&[none]),
([Measures].[m], [Period].[YearSixQtr].[Qtr].&[200906], [Statistic].[Statistic].&[none]),
([Measures].[m], [Period].[YearSixQtr].[Qtr].&[200909], [Statistic].[Statistic].&[none]),
([Measures].[m], [Period].[YearSixQtr].[Qtr].&[200912], [Statistic].[Statistic].&[none]),
([Measures].[m], [Period].[YearSixQtr].[Qtr].&[201003], [Statistic].[Statistic].&[none]),
([Measures].[m], [Period].[YearSixQtr].[Qtr].&[201006], [Statistic].[Statistic].&[none])
}
ON COLUMNS,
NON EMPTY
{ ([Product].[AttribA].&[A], [Product].[AttribB].&[B1], [Product].[AttribC].&[C], [Product].[AttribD].&[D1], [Product].[AttribE].[grp6]),
([Product].[AttribA].&[A], [Product].[AttribB].&[B1], [Product].[AttribC].&[C], [Product].[AttribD].&[D1], [Product].[AttribE].[grp0]),
([Product].[AttribA].&[A], [Product].[AttribB].&[B1], [Product].[AttribC].&[C], [Product].[AttribD].&[D2], [Product].[AttribE].[grp6]),
([Product].[AttribA].&[A], [Product].[AttribB].&[B1], [Product].[AttribC].&[C], [Product].[AttribD].&[D2], [Product].[AttribE].[grp0]),
([Product].[AttribA].&[A], [Product].[AttribB].&[B2], [Product].[AttribC].&[C], [Product].[AttribD].&[D3], [Product].[AttribE].[grp6]),
([Product].[AttribA].&[A], [Product].[AttribB].&[B2], [Product].[AttribC].&[C], [Product].[AttribD].&[D3], [Product].[AttribE].[grp0])
... (about 2200 more lines like those above)
}
ON ROWS
FROM [MyCube]
Neverthless, the error message is a bit misleading, as there is no recursion involved from cube configuration or MDX statement side, and even if there might be some internal recursion in the AS statement parser, it definitely is not infinite.
Lesson learned: Do not trust that AS can parse long MDX statements with some complexity correctly, simplify them instead to make it more easy for AS to parse them.
Thank you Deepak for sharing your ideas.
Frank
 Marked as answer by FrankPl Thursday, November 25, 2010 10:58 AM