To homepage To homepage
Solved

SQL Server Analysis Services Error

  • 25 January 2023
  • 5 replies
  • 186 views
M
Rank

Greetings,

I have an odd error and trying to eliminate where this issue is coming from.

I have a server running SQL server 2016 and TX is running on top of that. The version of TX I am running is 20.10.15.64 in Server 1.

The errors I am getting are attached. TX is running ok in Server 1 which is DB server.

There is Analysis Server 2 which seems to be an issue and is creating the errors above.

The attached error is coming from PowerBI Report Server interface. We are using Microsoft PowerBI tool for our reports. 

I have increased memory allocation in Server 1 where TX is running and issue seems to still be there.

Our products are all 64bit version.

Any Idea or view of what could be causing this.

 

 

icon

Best answer by harishkm 25 January 2023, 17:55

View original
2 Attachments

5 replies

Christian Hauggaard
Rank
Userlevel 6
Badge +5

Hi @malose.motlhasedi, is your analysis services model running in Azure or it SSAS? Can you please check that you have not added any DAX within the Power BI report on top on your SSAS model? All DAX should be in the analysis services model. If DAX has been added within Power BI it could explain the memory consumption.  

M
Rank

Hi Christian

 

Thanks for your valuable response. This is running in SSAS. I have DAX within PowerBI reports. This in PROD environment where this is failing, and memory is increased.

The same report is in our QA environment and is working without any issues and working smoothly with DAX within PowerBI Report.

However, following your suggestion, I have removed all DAX formulas on top PowerBI reports in PROD and still have the same issue but with this and get the following error.

 

Another strange behavior is that, after I have deployed the cube, when I access the PBI dashboard is shows data properly. but after couple of clicks and navigating more, then the display error happens again.

 

 

harishkm
Rank
Userlevel 2
Badge +1

Hi @malose.motlhasedi ,

From the problem description, understand that you have tried increasing the memory of server 1(TX server). you should be increasing the memory of server 2 (where SSAS server is installed)

Do you have other SSAS models on the same server?

The memory is shared on the server and even other models concurrent processing could end up having this issue on a totally unrelated model.

  • Try accessing the report when no other models are processing on the same server.
  • Verify VertiPaqPagingPolicy is set to 1. 

Below article is a good read on how we can manage memory efficiently using memory settings. 

Optimizing memory settings in Analysis Services - SQLBI

 

What is the size of the model? if its huge and powerbi capacity is fully utilized, you might get similar errors. Please check powerbi memory allocation as well. Each tier has different memory allocation.

Is the error from UI coming when accessing a particular report? Ideally, we should not be displaying very large tables in reports. try making such reports a “drill through” so that it doesn't have to load every time. 

Christian Hauggaard
Rank
Userlevel 6
Badge +5

Hi @malose.motlhasedi can you please help us by marking Harish’s answer as best answer if it resolved your problem?

If you have any follow up questions please let us know

M
Rank

Hi Christian

 

Apologies for taking long to reply. The answers provided gave other possible solution to my issue, but I picked up that the server in question did not have latest security patches from Microsoft and that solved the issue the patching was done. However, the provided solution also helped in realizing certain settings I did not do properly on my side and hence that also helped in avoiding future potential issue that might arise.

 

Regards
Malose

Reply


Terms and ConditionsCookie settings