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
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.
- Anonymous thanks again.
How many users do you have interacting with the dash daily? Do you have any idea of the peak usageI don't know! The Azure metrics don't tell us. My colleague says he can count the 'connections' (outside of Power BI Embedded) but he hasn't had time yet to do so.
The Azure metrics, while welcome, don't have enough detail to monitor usage. I would want to be able to look at the usage per connection and map that back to the token. Maybe it's in development.... :smileyhappy:
We have about 250-300 individuals who can connect, but I would imagine that 50-100 might connect during any particular day - but as I said, we don't actually know.
Peak usage? We're running 2-2.5GB, having brought that down from 2.8-3.2GB when we were forced onto A1. Although I see a gradual increase in cacheing, it's not as steep as yours.