expression contains a function that cannot operate on a set

Hello,

When using this expression

right(Measures.CurrentMember.Name,5) = "Units" or (instr(", CZ, DE", right([ProducingPlant].CurrentMember.Name,4))>0 or instr(", CZ, DE", right([SellingPlant].CurrentMember.Name,4))>0)

gives me the following error

The expression contains a function that cannot operate on a set with more than 4,2924,967,296 tuples

Any help would be greatly appreciated.

Here are two examples of how you might use parsing functions in an MDX statement. Hopefully this can help indicate the problem.

B.

Code Snippet

with member [Measures].[x] as

RIGHT([Product].[Category].CurrentMember.Name,5)

select

x on 0,

[Product].[Category].Members on 1

from [Adventure Works];

Code Snippet

select

[Measures].[Reseller Sales Amount] on 0,

FILTER(

Product.Category.Members,

LEFT(Product.Category.CurrentMember.Name="C"

) on 1

from [Adventure Works];

|||

I doubt whether this expression itself is causing the error, since there's no set involved. Is this perhaps the condition within a Filter(), where the set to be filtered might be a crossjoined set exceeding 4 billion tuples?

|||

Expression works fine for limited number of hierarchies..I get this error, only when it exceeds certain number.

|||

Thanks for the code snippet..

I tried to split my expressions & tested by having only: right(Measures.CurrentMember.Name,5) = "Units"

When trying to add the 4th dimension with out any measure, started receiving the error.

The idea behind this statement is that we have 'n' number of measures with volume & price measures. We need to display all the volume measures & restrict price measures based on the values in the table. Since, we follow a similar naming convention, I am using the above statement to compare the last 5 characters.

Any suggestions to fix this error?

|||

Well, could you share what those hierarchies/dimensions and the overall MDX look like, beyond this expression?

|||

The overall MDX Expression is defined for a particular Role "Europe Users" under "Enable Read Permissions"

right(Measures.CurrentMember.Name,5) = "Units" or (instr(", CZ, DE", right([ProducingPlant].CurrentMember.Name,4))>0 or instr(", CZ, DE", right([SellingPlant].CurrentMember.Name,4))>0)

I created an Excel pivot table using "Administrator" Role

- 3 dimensions with 2 property fields each

- 5 measures for 2 years

- 5 Page Fields

- All dimensions expanded

- All all these total Rows in Excel is only 250 max.

Now, I login as one of the "Europe Users" & try to open the excel file & click "refresh data".. I get this error message. It seem that the expression is not able to handle this many number of tuples.. Interestingly, it was working fine in SQL2000, but when I migrated to SQL 2005, I get this error message.

Thanks for the continued support & looking for your response.

|||

Since this worked in AS 2000, I can only guess that the internal implementation of cell security has changed, such that the size of the query cell space becomes an issue. Maybe someone from Microsoft will be able to shed more light on this scenario - the only idea I can suggest (since this cube is migrated from AS 2000) is to look at whether some of the original AS 2000 dimensions can be combined. Since dimensions in AS 2000 were strictly hierarchical, additional dimensions were sometimes added to hold additional attributes - these could be accommodated in a single dimension in AS 2005. In that case, "auto-exists" will reduce the number of tuples generated when stacking attributes in a pivot table. Could you describe the applicable dimensions and attributes in the Excel pivot table?

|||

Based on the KB article, http://support.microsoft.com/default.aspx?scid=kb;EN-US;940019, it looks like the problem is inevitable for complex queries that is operating on cell measure. Again, it boils down to the fact that how would this work in SQL 2000.

To answer your question, dimensions are customer, engine, parts & its related attributes. measures are units (direct) & price (calculated)

Any suggestions would be greatly appreciated

Logesh

|||

"To answer your question, dimensions are customer, engine, parts & its related attributes." - In that case, it's not clear which dimension(s) correspond to ProducingPlant and SellingPlant in the expression. Which are the largest dimensions/attributes; and is there any relation between the dimensions (like between engine and part)?

|||

Most of these dimensions are directly linked to the fact table

Customer -> Fact Table

Engines -> Fact Table

Parts -> Fact Table

ProducingPlant -> Fact Table

SellingPlant - > Fact Table

Except, Engine & Customer are related. In the engine dimension, one of the attribute is customer name

fact table - 330,000 records

customer- 120, 3 attributes

Engines - 2000, 5 attributes

parts - 10000,2 attributes

producing plant / selling plant - 100 records

Please let me know,

|||

"Engine & Customer are related. In the engine dimension, one of the attribute is customer name" - in that case, would it be acceptable to add the 3 Customer attributes to the Engine dimension (you could use a Named Query to join the dimension tables for the source)? Not an elegant solution, but if the scenario with errors involves stacking Engine and Customer attributes, the row count would be reduced from 240,000 to 2,000.

|||

Thanks again for your time.

For testing purpose, I removed the customer attribute from the Engine dimension. All have independant dimension directly related to the fact table. But, still the same issue...

My requirement is to show all the measures with units data & restrict price measures. He should be able to see the price only for his region.

- Units measures are pre-fixed by "Units"

- All Price measures are pre-fixed by "USD" & "Price"

Do you suggest possible ways to handle this security? So that I can change the way it is implemented currently. Because, I get this issue, only when I include the cell level security.

|||

Did you try removing the Customer dimension itself - what I suggested was to incorporate Customer attributes in the Engine dimension, rather than removing the Customer attribute from Engine?