Forum Discussion
powerbi gateway - sizing
Anonymous,
I think the issue is with system specs. 2 vCPUs and 8GB of RAM is very, very low when dealing with large datasets. Instead of table row size, how much total data are you dealing with in terms of GBs?
We have clients with 50GB+ datasets, and we're using Azure Databricks with 15 vCPUs and 56GB of RAM.
i had a support ticket in with microsoft about this, but there solutions didnt work.
in the end i increased the memory on the server to 16gb and it works fine now - peaks at around 8.3gb
we are now using t2.xlarge.
Interesting observations about the gateway whilst debugging the problem.
i would say the process is split into 3
a) get the data via the odbc connector
b) mashup the data using the mashup container
c) send the data to powerbi service
In our case step b) is the memory intensive part and doesnt handle running out of memory very elegantly
Also I would recommend putting the gateway as near to the data source as possible, to avoid network issues as the volume of data received (step a) is far greater than the volume of data sent (step c)
HI everyone, had time to sleep on this, and have reached a different solution.
Our data is in redshift, and we started out using the redshift connector (no PBI gateway required). However there are limitations with how you can use this connector, i.e. it only connects to a whole table.
Yes you can apply basic filters afterwards and it seems to take these into account on the data refresh.
Anyway we need more control than this, i.e. the ability to join tables in our data request.
So we moved to ODBC and reluctantly set up a powerbi gateway.
Great this all works, except for the performance on large datasets.
So I have now tried using a hybrid approach. Those tables where we are taking all or almost all the data, we can take using the redshift connector, and those tables where we need more control - use the odbc connector. This takes a lot of strain off the gateway.
We will probably still resize the gateway to 16gb, given others comments above, and I have a support ticket in to consider if there are some bugs. I do believe there are because for instance it is passing the same queries to redshift twice.