Forum Discussion
Power BI Embedded Capacity memory & CPU limits - refreshing Import method
Hi Anonymous,
I don't know if you have already seen these metris that are added in Azure for PowerBI Embedded.
https://azure.microsoft.com/en-us/updates/power-bi-embedded-azure-memory-metric-available/
https://azure.microsoft.com/en-us/updates/azure-alerts-now-availble-for-power-bi-embedded/
So now you can set some alerts when the memory or the query processing pass or reach some limits.
Hope it helps.
Thanos
We have the exact same problem
We hit the max on an A1 SKU in no time. We were forced to move to an A2 tier, which is DOUBLE the cost. So we switch our capacity to A1 in the evening, then the next morning switch back to A2, manually update the datasets which did not refresh overnight, and then run the day on A2. Come evening and we repeat the procedure.
Even on A2 we hit the maximum without any effort. In fact a single dashboard with not much data pushes us over the limit. And there is no warning - we get to know about it when customers phone in saying the visuals "show me a cross with a circle around it".
We use embedded.
What i see happening though is the memory usage does not come down. It steadily climbs during the day. Only if we pause and restart the server do we get the memory to drop. But then it starts climbing again... see the screen shot below. The Yellow circle is where we restart in the morning, and then it drops to about 500 MB but start to climb. The straight parts on the graphs is where there is NO activity... yet it consumes more than A1 SKU capacity. The spikes are where someone opens a single dashboard. You will see a single dashboard render (with really not much data) pushes it over the limit in no time - mostly because we start at such a high point even before it was rendered.
I have tried logging a call with MS TEchnical, only to find out i have to buy a service package, which I did, under protest... but then when i tried to log a support call it said I don't have the correct rights to log a service request... (I have the rights to buy it, but not use it??)
PBI is a great product, but MS is doing their best to make it unaffordable and unuseable at this point...
The reason that it grows is that it is cacheing the data, I understand. Your graph is very familiar to us! Having to manually switch tiers twice a day is a real pain, but definitely saves on the cost. We considered this but then tried other things first:
1. Avoid work in Power Query - push back changes into SQL Views or similar.
2. Logically break up the pbix so that the relevant data is only loaded when needed. Ok, we haven't done this but MS 'recommended' it. For example, keep data from different years in different pbixes, as most people look at the current year regularly but the previous years rarely.
3. Reduce the size of the pbix - remove columns from tables - especially fact tables, when they are not needed. This helped a lot.
4. Use Direct Query if possible. Haven't tried this as it means re-building a twenty page dashboard with a lot of complexity.
Your phrase " In fact a single dashboard with not much data pushes us over the limit. " is worrying. That shouldn't be happening - is your pbix size enormous? We are running about 55MB in A1 and it peaks at about 2-2.5GB, well within the 3GB limit.
roelf - let me know how you get on!
Thanks Anonymous for the feedback.
Some of our queries are definately not optimised - that's for sure.
We run quite a few files though - mainly because the RLS Dashboard we had could not update the dataset... So we had to split them into seperate files - one per customer... which is another pain in the neck.
A typical file for us is less than 5 MB. The datasets on PBI ranges between 1 MB and the biggest one is 19MB - which is not massive... You also say below you run a 20 page file - ours are 4 and some 5
We export the information to PBI.COM in JSON, and then parse and unpack the JSON in Power Query - so maybe we should reduce that ?
Your point on "Logicaly break up the pbix" - how did you manage that? Is the data only "pulled" when you say open a page? We can look at breaking up tables so that they are only called when someone opens the page/sheet
Remove columns - i assume this means don't import them in the data query ? Or are you saying import, but then delete them from the dataset in Power Query ?
Direct Query - we tried this, but the response is super slow... plus we encountered too many measures we wanted to use which are not available in DQ.
I have also been told that the Mem usage grows because of cacheing - but is there a way to clear cache ? That to me is quite a problem, cause that means everytime someone opens a report, there's not way of "un-doing" his usage?
Thanks again for the assistance.
Hi roelf
Your memory usage will depend on how many customers (and therefore how many pbixes are open at any one time) you have. It could be that it's all working fine but you just have too many pbixes....? How much memory is used by one or by two customers?
JSON unpacking - maybe. We get memory peaks when our pbix is refreshed - it is refreshed on completion of some uploading at various points during the day. For us, to reduce the peak, we needed to reduce the Power Query processing (which takes place on refresh), and to do that we needed to optimise query folding - which meant in turn to reduce as much M code as possible. The reasons for your max memory could be different for you?
Breaking up the pbix - oh, not rocket science - just remove columns and set row filters in Power Query which should then fold the query back to the source. There's stuff online about what M code will reduce query folding.
Remove columns - see above. By removing them in Power Query, all the steps are 'folded' into a single query - thus optimising the query against the original source.
Direct Query - yes, it sets restrictions on DAX usage. There's a pbix option to ignore the limitations but at the risk of the refresh timing out or taking too long.
Cacheing - alas, no. The closing down of an inactive pbix is managed somewhere in the background of the service. I don't believe there are any ways to manage this manually or programmatically, unfortunately.