SSAS Tabular Cube - Error while pivoting the Tabular cube

Question

SSAS Tabular Cube - Error while pivoting the Tabular cube

Prabu Chandran 1

Hello All,

When I’m pivoting the Tabular cube in excel on my machine (RAM Size: 16 GB), I’m able to add 4 or 5 Dimensions at row-level and when I try to add more, I’m getting the below error.

"We couldn't get data from an external data source. Please try again later.
If the problem persists, please contact the administrator for the external source.
Here is the error message that was returned from the analysis server named servername"

Any suggestions.

Thanks in advance.

1 answer

Your answer

Answer 1

ZoeHui-MSFT 41,536

Hi @Prabu Chandran ,

For SQL Server Analysis Services, the DAX section of msmdsrv.ini contains settings used to control certain query behaviors in Analysis Services, such as the upper limit on the number of rows returned in a DAX query result set. For Azure Analysis Services, this property can be specified by using XMLA.

Based on the error message, you hit this row limit issue. please increase the upper limit, specify the MaxIntermediateRowSize configuration setting.

For more information, please follow the official document:

dax-properties

Regards，

Zoe

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Prabu Chandran 1 Reputation point

2022-05-27T02:33:49.36+00:00

Hi @ZoeHui-MSFT ,

Thank you for your reply.

We have set the MaxIntermediate property to 1000000, but the issue still persists. We couldn't pivot more than 4 to 5 dimensions at the row level, even if we took a higher granular level of data.

Is there anything to do with RAM increase on the user's machine or with any Excel version? I have gone through an article where it mentioned 64 bit Excel handles the memory issue efficiently (currently we are using 32 bit).

Any suggestions on this?
Prabu Chandran 1 Reputation point

2022-06-16T10:04:39.173+00:00
Good day, @ZoeHui-MSFT .

Thank you for sharing your ideas.

We tried 64-bit Excel as you indicated, but no improvement was apparent, and we were still unable to fetch more than 5 dimensions at the row level.

I've provided a screenshot of the memory setting. This is how our SSAS server is configured. Please double-check this value and let us know whether it's enough to gather massive datasets.

Is it possible to set up a conference call with our team to go through the adjustments while we work on a P1 production issue? If that's the case, please tell us how to set up a meeting.

Share via

SSAS Tabular Cube - Error while pivoting the Tabular cube

1 answer

Your answer