Power BI Capacity Planning

What’s the right way to scale and plan for Power BI Capacity?

For many organizations, Power BI starts off as a proof-of-concept, with only a few handful of people using it. Most people start using PBI because it’s part of Office 365, so why not give it a try? Eventually adoption increases and people see shiny new Power BI reports, and more teams want access. Eventually management sees the benefit to start consolidating other BI tools and excel spreadsheet repositories all into Power BI. Then comes the question, how do I plan for Power BI Capacity?

 

 

When to Upgrade from Power BI Pro to Power BI Premium

There are several factors to consider when upgrading from Power BI Pro to Power BI premium.

1. 1. Cost: When you’re on the Power BI pro plan, every report viewer and every report developer need a license. On a premium capacity, only report developers need a license. Report viewers are free. Power BI pro licenses cost $10/person per month. That means if you have more than 500 users in Power BI, it will be more economical to go with Power BI premium. A P1 Power BI premium capacity costs about $4,995/mo
      
      
   2. Dataset sizes: Power BI Pro has a data model limit of 1 GB. If you need larger datasets than that, you will need to go Power BI premium.
      
      
   3. Refresh frequency: How often do you need to refresh your reports? If you need to refresh a report once every hour or more, you’ll need Power BI premium. Power BI pro supports 8 refreshes a day. Power BI premium lets you schedule up to 48 per day (and then you can kick off more than that using XMLA endpoint or API).
      
      
   4. Dataflows and Datamarts: Power BI premium allows users to load data into Power BI using some self-service ETL tools like dataflows and data marts. This allows you to bring data into Power BI, store it, transform it and then build multiple reports off of the data. This feature can be handy for organizations, and is only available in Power BI premium.
      
      
   5. Self-host Power BI report server: Do you need to host an on-premise version of Power BI Report Server? Then you’ll need Power BI premium.

When to upgrade from a P1 to a P2 premium capacity

One of the first questions that will arise when doing Power BI capacity planning is should we make the upgrade from a P1 to a P2? Usually organizations will get a single Power BI P1 instance for starters. Eventually as growth continues, they start to consider getting a Power BI P2 capacity. But since the cost is double that of a P1, there’s usually a little hesitation.

Things to consider when upgrading from a P1 to a P2:

1. 1. Have you done any optimizations on the content already on your premium capacity? A lot of times there are opportunities to improve the usage of your existing capacity. Examples include:
      
      
      
      • Implement Incremental Refresh – A lot of datasets take a really long time, because they’re pulling in a lot of the same data over and over again. However, you can take advantage of Microsoft Power BI’s incremental refresh feature to eliminate some of the overhead.
      • Use shared data models – If you have lots of reports pulling similar data, perhaps you should create one “shared data model” and connect multiple reports with a Live Connection to eliminate refreshes. With proper data modeling, you’ll be amazed how many reports you can consolidate.
      • Scale your gateways first – A lot of times organizations will see slowness in refresh times or performance with DirectQuery because they haven’t upgraded or scaled their gateways. It’s usually more cost effective to add some more gateways, add them to a cluster, or increase their memory. It can improve refresh times and DirectQuery Performance
      • Optimize DAX, impose timeouts – Interactive operations or DAX queries can really take a toll on premium capacities. View the Premium Capacity Usage metrics report to find some frequent offenders. Download the reports and see if you can optimize some of them. Use the Performance Analyzer in Power BI desktop to see if you can simplify or reduce the DAX calculations. Possibly consider adding DAX memory limits or query timeouts at the tenant level or workspace level.
   2. You can try autoscaling – You can try to enable autoscaling to see if it will give you the extra performance you need to refresh your reports. However, autoscaling is enabled for a minimum of 24 hours. Usually if you have runaway DAX queries or a rush-hour everyday in the morning, autoscaling might be enabled more often than not, and it would be cheaper to try to optimize your existing capacity . Then when those options are exhausted, upgrade to a P2.

2. How big are your data models?   P2 capacity allows a single data model to be 50 GB, while a P1 only allows for 25 GB.  If you have a large data model you need to implement, you may need to use a P2 capacity.
   
   
3.  How many datasets do you have refreshing at once?    A P2 capacity allows for up to 80 concurrent refreshes.  A P1 allows for 40.  If you hitting 40 datasets at the same time, consider improving your gateway performance first.  Move them closer to the datasets, add more memory and add them to a cluster.
   
   
4. Exporting Concurrency with Power Automate: A lot of organizations use Power Automate to distribute reports.  If you are exporting more than 55 pages at once, you’ll need to consider upgrading to a P2 that supports 95.

Capacity Size	CPU v-Cores	Dataset Size	DirectQuery Simultaneous Connections	Max memory per query	# of dataset refreshes at once	Pages exported with Export API
P1	8	25 GB	30	6 GB	40	55
P2	16	50 GB	60	6 GB	80	95

Source: Microsoft – What is Power BI Premium Gen2?

Power BI Capacity Planning: Should I get multiple Power BI premium capacities or just one?

Should you invest in multiple Power BI premium capacities?  It depends on your requirements.  Here are some good reasons to consider getting multiple capacities.

1. Separating Development and Production – Many organizations will get two capacities to separate development from production.  However, with Power BI premium per user, others are giving their developers Power BI premium per user licenses, and keeping Development within the shared capacity, and production in Power BI premium 
   
   
2. Separating for data residency requirements/compliance – Due to regulations and data privacy, sometimes data needs to reside within a country.  You can get a Power BI premium capacity in a different geolocation, and still have it be part of your tenant.  Some companies may have a smaller 
3. Dividing self-service and adhoc reporting from scheduled and centralized analytics – To prevent noisy neighbors, it’s good practice to separate DirectQuery connections, self-service/adhoc reporting from scheduled, centralized analytics.

There’s a lot of thought and consideration when doing Power BI Capacity planning, if you need assistance with it, don’t hesitate to reach out.  Our Power BI experts can help.