cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
brodtgaard Frequent Visitor
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
Super User
Super User

Re: Bad Enterprise Gateway Performance SSAS

@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.


Near SE WI? Join our PUG Milwaukee Brew City PUG
brodtgaard Frequent Visitor
Frequent Visitor

Re: Bad Enterprise Gateway Performance SSAS

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 !!!?

brodtgaard Frequent Visitor
Frequent Visitor

Re: Bad Enterprise Gateway Performance SSAS

bump

stevo Frequent Visitor
Frequent Visitor

Re: Bad Enterprise Gateway Performance SSAS

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

brodtgaard Frequent Visitor
Frequent Visitor

Re: Bad Enterprise Gateway Performance SSAS

bump!

brodtgaard Frequent Visitor
Frequent Visitor

Re: Bad Enterprise Gateway Performance SSAS

Bump!

Highlighted
powerBI-makyb Occasional Visitor
Occasional Visitor

Re: Bad Enterprise Gateway Performance SSAS

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

 

Any help?

JonasKjeldsenDK Frequent Visitor
Frequent Visitor

Re: Bad Enterprise Gateway Performance SSAS

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?

hisham02 Frequent Visitor
Frequent Visitor

Re: Bad Enterprise Gateway Performance SSAS

I have this exact same issue! Any help?

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Top Kudoed Authors
Users Online
Currently online: 373 members 3,856 guests
Please welcome our newest community members: