Strange memory error only for one part of the pivot table using MDX calculated member

Apr 18, 2013 at 6:27 PM
Edited Apr 18, 2013 at 6:41 PM
Hi,
I am using your tool to add a MDX calculated member to the Pivot Table. My objective is to calculate IRR for a dynamic selection of projects. So, I have invoked the Excel IRR function and passed the parameters. What is strange is that it produces an IRR for the entire table if there are no filters or grouping. But if I apply a filter or grouping, I get a memory error. I have 4 sample projects in my data (P1 to P4). The results are returned for P1 but for P2 to P4, I get the following memory error.
ERROR - CALCULATION ABORTED: Query (1, 54) Memory error: Allocation failure . If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine.
I have tried it 2 different machines, but the same issue is repeating. Is there something that I am doing wrong?


Here is the MDX calculated member I have added:
[Measures].[IRR v1] = Excel!IRR
(
SetToArray
([Data].[Event].[All].Children,
[Measures].[Sum of Cashflow]
)
)
Here is the link to the file
https://www.dropbox.com/s/wp94mlsun5g69q8/IRR%20MDX%20Trial.xlsx
Coordinator
Apr 19, 2013 at 3:03 PM
I don't have any reason to believe OLAP PivotTable Extensions is misbehaving. I would recommend you pull the MDX query out then add your calculation into the WITH clause and try running the query in Management Studio. Let's isolate this from Excel and see if you can get your calculation to work.

I assume you will be able to replicate the error above inside Management Studio. If so, I would recommend you ask your question on the Analysis Services forum. I try to focus this forum just on OLAP PivotTable Extensions and not generic MDX questions.

Here's a link to the Analysis Services forum:
http://social.msdn.microsoft.com/forums/en-US/sqlanalysisservices/threads/
Apr 19, 2013 at 3:07 PM
Thanks a lot for the response. I understand your point. I tried to run the query produced in DAX studio but it still failed. I do not know how to connect the Management Studio to PowerPivot data source. Any pointers? I searched for it yesterday but could not find anything.
Coordinator
Apr 22, 2013 at 10:11 AM
Oh, if its PowerPivot then DAX Studio is your best bet.