Cannot operate on a set with more than 4,294,967,296 tuples.
Below is an error that I run into often. Can someone tell me what is causing this and how to resolve it?
Executing the query ...
The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.
Execution complete
Here is the MDX that caused the error:
With
Member [Measures].[Total Count] as
'[Measures].[Crash Count]'
Member [Measures].[Total Fatal] as
'[Measures].[Fatal Crashes]'
Member [Measures].[Total Injury] as
'[Measures].[Injury Crashes]'
Member [Measures].[Total A Injury] as
'[Measures].[A Injury Crashes]'
Member [Measures].[Total B Injury] as
'[Measures].[B Injury Crashes]'
Member [Measures].[Total C Injury] as
'[Measures].[C Injury Crashes]'
Member [Measures].[Total 0 Property Damage] as
'[Measures].[0 Property Damage Crashes]'
SELECT
NON EMPTY { [Measures].[Total Count],
[Measures].[Total Fatal],
[Measures].[Total Injury],
[Measures].[Total A Injury],
[Measures].[Total B Injury],
[Measures].[Total C Injury],
[Measures].[Total 0 Property Damage],
[Measures].[Vehicle Count],
[Measures].[Person Count],
[Measures].[Fatalities],
[Measures].[0 Injuries],
[Measures].[A Injuries],
[Measures].[B Injuries],
[Measures].[C Injuries],
[Measures].[Injuries] }
ON COLUMNS,
NON EMPTY {[Crash].[Case Nbr].[Case Nbr] *
[Date].[Calendar Date].[Date] *
[Crash].[Weather].[Weather] *
[Crash].[Road Defects].[Road Defects] *
[Crash].[Collision Type].[Collision Type] *
[Crash].[Light].[Light] }
DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS FROM
(select strtoset('{[Crash].[County].&[Morgan] }')
on columns From
(SELECT STRTOMEMBER('[Crash].[TS Crash Mile Station].[5.28]') :
STRTOMEMBER('[Crash].[TS Crash Mile Station].[5.77]')
on columns From
(select {descendants([Crash].[Case Nbr].Currentmember)}
on Columns from
[SDM_Reports] ))) where ([Reportability].[Is Reportable].&[
April 4th, 2007 7:22pm
Quick Update on this issue,
If I remove the [Crash].[Case Nbr].[Case Nbr] from Rows, then the error goes away.
Free Windows Admin Tool Kit Click here and download it now
April 4th, 2007 7:34pm
I am having the same problem..Can somebody help....
I want to know what causes this problem..
June 7th, 2007 10:53am
http://www.sqljunkies.com/WebLog/mosha/archive/2007/1/28.aspx
Free Windows Admin Tool Kit Click here and download it now
June 7th, 2007 11:08am
Hi,
In one of my projects we faced a similar problem. The solution was to combine related attributes next to each other. In above MDX,
Instead of this
Crash].[Case Nbr].[Case Nbr] *
[Date].[Calendar Date].[Date] *
[Crash].[Weather].[Weather] *
[Crash].[Road Defects].[Road Defects] *
[Crash].[Collision Type].[Collision Type] *
[Crash].[Light].[Light] },
try
[Date].[Calendar Date].[Date] *
[Crash].[Case Nbr].[Case Nbr] *
[Crash].[Weather].[Weather] *
[Crash].[Road Defects].[Road Defects] *
[Crash].[Collision Type].[Collision Type] *
[Crash].[Light].[Light] }
Here I have moved all members from Crash Dimensions together. This helps in bringing in Auto-Exists behaviour and avoids the problem of bigger cellset.
SQL Server BI Consultant
June 30th, 2011 5:05pm