Power BI Premium limitations and refresh scheduling best practices

[D_Lav]

Hi,

Long post I know. But I hope I can start some discussions around this:) My heading might be off but couldn’t think of a better one.

----------------

I have thought about this for a while but as I can’t seem to find any good resources I start to wonder if I don’t understand this correctly.

I am Power BI Service Admin and capacity admin for a P1 capacity in my company. We have a couple of hundred users and around 25-50 workspaces that would need to reside in the Premium capacity. Since it is a P1 we only have 25GB of RAM and 8 cores.

it is a big company and they have been using Power BI pro only until recently for sharing and using Power BI which has ended in now when we are moving to premium. Workspaces that I mentioned above is created earlier and now needs to be moved in to the P1.

My problem is that I don’t know anything about the underlying datasets such as data model type (import, live, direct, size etc.) for these workspaces or how often they are refreshing. Are there any best practices or how should I go about figuring this out to best optimize when and how the different workspaces should be refreshed (such as in timeslots) or does any work need to be done with underlying datasets? I feel very uncomfortable to bulk add everything due to the limitations of this capacity and don’t feel that this is an option.

therefore, I need to prep.

My first idea is to create a schedule in excel and after contact and research of each workspace, Document size, refresh counts etc. and give the workspace a timeslot. So, it wouldn’t fail if there are multiple of import models that would try to refresh at the same time on the premium capacity.

In a best world scenario everything would start from scratch but now we are migrating, and I need to document, list and set up best practices which is fun but a huge task:) Is there any functionality to figure this out or preferred way of work?

I wish there could be a way to multi schedule different workspaces refresh as the capacity admin also.  I guess I need to be added as a gateway admin to all our companies gateways also to be able to see what’s going on if I don’t want to rely on communication to workspace admins for setting correct schedule refresh window?

[MariusT]

Hi @D_Lav ,

Great and fun post. There are several aspects to consider here.

Since you did not mentioned it specifically, I would start with asking if your are familiar with the Premium Capacity Metrics App?

The app will be a great way to analyze your environment, but, at least in my opinion, it is also important to talk to workspace/dataset owners, if you really want to dig into this task. I will say that I don't have a ton of experience, but some, on this matter, and depending on which side you are on, it is sometime easy to forget the other parties opinions and remarks. What I aim at here is the juntion where the technical people meet the business people. 

But, as a start I would install the Premium Capacity Metrics App, to analyze the environment. Once you have the app in place, it can be helpful to cellect all the data and create your own datamodel. By doing this you can take a more analytical approach when suggesting changes, either based on CPU usage at different hours, or number of evictions, peak hours for refresh, dataset sizes, and so on. 

Although this is not an exact science, you will at elast end up with the biggest contributors to resource use and refresh wait/duration, so you can start working your way down the list. Part of this process would be to identify the different reports, and rank them in terms of criticallity.

After you have done your analysis and categorized your reports based on how business critical they are, I would say the fun starts. You might ending talking to the different workspace/dataset owners, all claiming that their reports are the most critical. If you run into scheduling issues at this point, and don't mind spending more money, you can always scale up to a P2, and then split it into two P1 capacities. But, based on the amount of users and workspaces you mention I guess this will be a bit overkill, although what really count is the number of reports, and how they are set up.

When it comes to scheduling refreshes, I would suggest looking into customizing the schedules. In Power BI Service, as you sure know, the available hours is every half and whole hour. If you manage refreshes using PowerShell, you can schedule refreshes at different times than those available in Power BI Service, and since you have a Premium license they will not be counted from the 48 available scheduled refreshes.

There are several other aspects to consider, but let's start with this and see waht the discussion brings.

[D_Lav]

Thanks for your contribution @MariusT!

Very good input and thats excatly what i have done:) i have started to monitor the capacity metrix app and also the "Health" tab in premium. But as you can tell my organisation is huge and i just want to know that im not doing anything wrong here with my thinking:)

we have around 2000 workspaces and 100 apps so im shooting for the app workspaces at the moment.

I am deciding at the moment if im going to set the refreshes up by using PS or if i should use the cmdlets and ad the refresh parts to my SSIS packages. Anyway i am sketching on something like a Ganttchart to be able to easily visualise my refreshes.

Just to be clear! this is for optimization purposes around 1 P1 capacity. I understand fully that i could scale up the enviroment to a p2,p3 - multiple P1s etc. But at the moment (budget purpose mainly) i am going to see if i could admin and optimize the sh*t out of this P1 for large scale purpose!