Bad Enterprise Gateway Performance SSAS

[brodtgaard]

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?

[hisham02]

I have this exact same issue! Any help?

[powerBI-makyb]

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

Any help?

[JonasKjeldsenDK]

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?

[Seth_C_Bauer]

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

[brodtgaard]

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]

bump

[stevo]

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

[brodtgaard]

bump!

[brodtgaard]

Bump!