Power BI file too slow

[mork]

We have a pbix file with a size of about 10MB. This is the file that we use for reporting and as a matter of fact this is our only file. After months of tweaking and adding stuff we ended up with a version that works for us and we have a complete and complex dataset. The thing is though that during the past months using the file has become really frustrating since it has become really slow and every time we go into the query editor we need to wait upwards of 2 hours to load all the tables. 

The laptop running the file is not really that fast (Centrino 2, 4GBs of ram) but still our tables aren't that big either.

With the task manager open I can see the following metrics while running Power BI.

CPU: 30-60%         Memory: 70-80%        Disk: 95-100%

We have about 15 tables. 6 of them from excel files and the rest from an OData connection with Project Online.

The excel tables are really small (less than 100 lines). Our biggest tables are most of those from the OData connection. Those are less than 25k lines but have about 10-15 columns each. (conditional columns mostly). 

Each of the big table is constructed with about 20-30 steps, including merging tables, conditional columns, removing/replacing values etc.

In my opinion the tables aren't that big but they are a bit complex. Not enough though to justify the waiting hours.

How can we check if there is something wrong with the model or if it only has to do with the laptop's power?

Also any tips for making Power BI datasets faster to process? 

PS: when uploaded to Power BI Service our dataset works fine and we don't see any delays. But in Power BI Desktop everytime we need to change something, even a small thing we have to wait for the query editor several hours to load the tables and also making the changes and applying them takes a lot of time.

[Yaman]

My guess is that a faster PC with bigger memory is needed.

I'm using PBI Desktop on a new I7 with 16GB ram and it slows my PC considerably when refreshing the calculations in large data sets.

It also depends a lot on the calculcations you're performing on the query level.

[KHorseman]

I've noticed a major slowdown just in adding new measures, even before I actually use them anywhere. The visuals all load very quickly, so the calculations are efficient enough, but once I have a lot of measures in any file, I can't even hit the New Measure button without going for a coffee break. Five minutes of "Working On It" before I can even type anything in, and then another five minutes once I hit enter. I don't understand what's causing that slowdown. The file itself runs quickly, all the measures calculate quickly, there's no lag in adding visuals, but God help me if I want to hit that New Measure button.

[Sean]

I think PBI Desktop runs better on Windows 10 Pro which is what I'm running. 

So don't only look at the general specs of the machine you are using? (which I'm sure also makes a difference of course)

EDIT: well centirno with 4GB may actually be kind of low (I only scanned through the top post)

@KHorseman what are you running?

BTW this explains a lot - we can tell by your posts when you've started a New Measure

[mork]

@Sean I know it's ridiculously outdated specs. But I only work as an intern here and at the time it was their only spare computer. I put an IT request though and got a much more recent one now and everything works much faster and smoother.

[KHorseman]

@Sean I have to build all my reports on a server outside our network that I have to remote into. I don't know its specs but they are definitely not great.

That said, I still don't understand why adding measures is what slows it down. I would think actually calculating the results of those measures would be the slow part, but it's not. I can add put one of my crazy nested double-SUMX measures into a visual and it loads up in two seconds at worst. But when I hit New Measure, before I even type a single character of the actual formula, I have to wait five minutes between hitting that button and being able to start typing. Then when I'm done I have another five mintue wait.

I don't get what it's doing in that time. It's not calculating anything, because I haven't even given it a formula to calculate yet. It's not rendering any visual. I could maybe understand a slight delay after entering the formula while it checks to make sure it's valid and contains the right context transformations and such, but before there's even a formula to check? That makes no sense. From seeing this kind of behavior in other programs, this really seems like some kind of memory leak bug to me.

[mork]

Bump

[Vicky_Song]

@mork, so many calculation and steps in your .pbix file will cause the performance issue. Instead of execute these steps directly in the .pbix file, is it possible for you to do them on the data source level? 

[mork]

A faster computer definitely helped. I put an IT request and recieved a much better laptop and now every procedure inside Power BI desktop is much faster.

[mork]

Unfortunately no. Would a faster computer help? Any tips on making lighter .pbix files?

[Vicky_Song]

@mork, one faster computer may help, however, I wonder whether it really helps a lot. Per my experience that the heavy data model design make the .pbix file heavy. So as I mentioned above, it worth for you to consider doing data calculation before the data is imported the .pbix file, it will help to make .pbix file run faster. 

[mork]

@Vicky_Song Unless I'm mistaken, I don't think it's possible to make any calculations inside MS Project Online before importing to Power BI Desktop. I'look more into it though. I might try to rebuild the dataset too and try to ommit any unneccessary steps.