cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
brodtgaard
Frequent Visitor

Bad Enterprise Gateway Performance SSAS

Is anyone else experiencing bad performance with Enterprise Gateway and Analysis Services (SSAS)? I have a setup with an onprem SQL 2014 with tabular cubes and the new Enterprise Gateway installed as described by MS.


The gateway works but I am confused regards to performance. I have a simple PBI desktop report (.pbix) with live connection to SSAS. In PBI desktop the report reacts instantly to any filtration. When uploaded to PowerBI.com the first filtration (on the same report) takes 10 seconds for the objects to respond. The subsequent filtrations are as fast as the desktop version.


When doing more complicated reports (slicing and dicing into details on more dimensions) it is getting really bad. A report, where a specific filtration is refreshed in milliseconds in PBI desktop, will in the browser take up to 5 minutes to do the first rendering of the objects. Again, the subsequent filtrations are really fast.


It seems like the gateway uploads/caches a lot more data to PowerBI.com that the actual filtration needs – perhaps for all combinations of possible filtrations in the report. Could that really be true?

9 REPLIES 9
hisham02
Regular Visitor

I have this exact same issue! Any help?

powerBI-makyb
New Member

We are facing the same issues currently. Now its taking SSAS service down and we have to restart SSAS Service.

 

Any help?

We are still having this problem - I seems like using PowerBI as an enterprise portal for MOLAP cubes is a wrong decision after all. It is not unusual to have large dimensions (e.g. customers) in modern BI solutions. But all dimension members should not be sent to the frontend unless the dimension is being used.
Could the PowerBI team please look into this?

@brodtgaard I won't downplay the issue, but there will be at least some difference as the Desktop is all within your network and the service is making calls from Azure. You can see if the queries being sent back and forth are different by using SQL Profiler to watch the incoming calls. Test against Desktop and the Service for the same report pages. At minimum, it should give you an idea of where things are slow.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Thank you!

I've been analyzing the SQL Server Profiler output.

 

1)

I've noticed that it sometimes it can take minutes from filtering in the browser until something happens in the profiler. How to troubleshoot that?

 

2)

More interesting when filtering from the browser SSAS gets queries like:

 

EVALUATE
  FILTER(
    KEEPFILTERS(VALUES('Customer'[CustomerId])),
    NOT(ISBLANK('Customer'[CustomerId]))
  )

ORDER BY 'Customer'[CustomerId]

 

This query when run in Management Studio takes 35 seconds and returns all the customers from the customer dimension (about 3 million entries). My reports does not use the customer dimension!!!

It should definitely not be necessary to send these customerid’s to the front end, as the report only displays data on a much more aggregated level. That is one of the reasons to use cubes.

Why does this happen !!!?

bump

Having exactly the same problem, come on guys sort it out

brodtgaard
Frequent Visitor

bump!

Bump!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors