Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
jengwt
Helper V
Helper V

Microsoft SQL Server Analysis Services process killing my computer's memory

And it has nothing to do with data pulls, so far as I can tell. This is occuring with a pulled dataset, and I'm simply messing with measures and visuals.

I've been working on a complex report (lots of complex Measures), and Microsoft SQL Server Analysis Services has been opening and eating up my computer's memory, often freezing Power BI.

It appears to start up, or to really get going, when I try to apply a "third tier" Measure (meaning that it is based off of a Measure which is based off of a Measure) to a visual.

MicrosoftSQLServerAnalysisServices.PNG

 

Here is the code for the "third tier" Measure:

Grd_Dyn = IF([Scr%_Dyn]>=.9
    , "A"
    , IF(AND([Scr%_Dyn]<.9,[Scr%_Dyn]>=.8)
        , "B"
        , IF(AND([Scr%_Dyn]<.8,[Scr%_Dyn]>=.7)
            , "C"
            , IF(AND([Scr%_Dyn]<.7,[Scr%_Dyn]>=.6)
                , "D"
                , "F"
                )
            )
        )
    )

*Update: I have since replaced nested ifs like this one with switches, which are far more efficient.

 

Pretty simple code. If you put a hard value in in place of "Scr%_Dyn", you would get an answer back probably in less than milliseconds. But when I try to add this Measure to any visual, the PBI loading wheel gets going, and PBI will often freeze.

 

Could it be related to the first and second tier Measure? I don't think so becasuse those two work fine and do not slow down my report when I add them to Visuals. 

The code for them is far to complex to post here (a total of about 400 lines of Dax code). Suffice to say that "Scr%_Dyn" computes a percentage based off several filters and the values yielded by "m_Dyn", which does some pretty simple math based off of several filters. Here is what one of the Visuals looks like:

TableRedacted.png

 

What's worse, this process keeps running even when you close PBI, "idling" at a fairly high memory use. I also noticed, when I re-opened PBI to pull that code, that there were now TWO of those processes running , as well as another one, CefSharp.BrowserSubprocess:

2MicrosoftSQLServerAnalysisServiceses.PNG

 

Guess where both of these processes live?

C:\Program Files\Microsoft Power BI Desktop\bin\msmdsrv.exe

C:\Program Files\Microsoft Power BI Desktop\bin\CefSharp.BrowserSubprocess.exe

 

What is the purpose of this process?

Why does it only seem to be a problem when I take the aforedescribed action?

How can there be multiple instances of it running if I only have a single Power BI report open?

I've made some pretty complex reports. Why has this only become an issue now?

 

I've also noticed that this tends to happen with the Microsoft.Mashup.Container process. I've seen as many as SEVEN mashups running on my machine at a time. I think this is related to data pulls.

11 REPLIES 11
RogerSteinberg
Post Patron
Post Patron

I had the exact same issue and my problem came from complex ranking calculated columns and measures. Once I removed them and applied the ranking formulae within my sql query the error disappeared. To be honest, this is really bad on Power BI ... Why would my entire CPU and memory be eaten up by ranking measures... I have 16gb of RAM btw

Same problem too! 😞

 

Microsoft is extremely disappointing and power BI is dying from its success.

 

This is a great pity.

 

Microsoft makes frequent updates without doing the necessary tests beforehand, with the result that what was working doesn't work anymore and all the reports of a company become unusable.

 

This is simply shameful for Microsoft!

 

I'm very angry because a company of this size cannot do that and implement updates without testing them.

The On-premises data gateway hasn't worked for several weeks now and the dates are pushed back from day to day... and this despite the latest version installed.

 

Shameful!

 

vmakhija
Post Prodigy
Post Prodigy

Hello

Did you get any solution to this?

I am facing almost same problem where I want to know what this SSAS process is doing.

I have a DAX code to create a table basically using the UNION function in DAX, but it complains and gives memory issues.

Any inputs please?

Regards

@vmakhija I've found joins (merges) and unions (appends) in PBI are significantly slower than just doing those actions in the queries themselves. I would recommend that you do as much of that type of work in your queries as possible, letting your server handle that, and let PBI do the dynamic aggregations, like measures.

Also, cut your data down to what you absolutely need; PBI, or the machine on which it's runinng, only has so much memory. That and make your measures as efficient as possible.

Hope this helps.

Hello,

 

I would consider watch this video by Marco Russo, maybe it can enlight you guys. 

Cheers

v-chuncz-msft
Community Support
Community Support

@jengwt,

 

DAX Studio can be used to capture queries, you may have a look.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-chuncz-msft I forgot to mention, now updated in the OP, that I haven't pulled the data for this report in some time; there should be nothing query-related going on. I'm merely messing with some measures and visuals.

Any update on this?

 

I'm getting memory error issues when trying to create new measures.

 

Under Power BI desktop SSAS is taking up nearly half of the memory, and there are also about 12 instances of Microsft Mashup Evaluation Container

No. I started over and used some other methods to create the report that I wanted. Still doesn't explain what was going on.

Still no update on this one? I am experiencing the same issue, but nothing seems to be an easily identifiable culprit.

@JRHans09 Nope. I've mentioned it to several Microsoft support folks. I don't think anyone there knows how those three processes work. Maybe the original devs.

The only solution that I know of is to write your measures smarter; use more efficient code. Learn to use variables, switch statements, the differnces between filtering tables and calculatetables.

 

The key thing is that EVERY TIME you name a measure, field, or table, PBI calls, and calculates, it again! A simplistic example would be if you wanted to cube a number:

Cube = [number] * [number] * [number]

This code calls [number] three times! This would be slower than:

Cube = VAR num = [number]
RETURN  num * num * num

Which only calls it once.

 

For more, check out my other thread: https://community.powerbi.com/t5/Desktop/Slow-Measures/m-p/440978

And there are a few others like this out there.

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.