Successful Enterprise Roll-Out of Power BI (Part 3)

We should now have a strong understanding of how Power BI is an enterprise ready platform and the Power BI deployment approaches that assist us in the process of successfully rolling out Power BI at an enterprise level. If you haven’t already, be sure to read through the previous parts of this blog series to get all the helpful information for making a success of your Power BI roll-out:

In this final part of the blog series, we will be exploring the various questions we should be asking ourselves when choosing a Power BI Premium capacity.

Power BI Licensing

Before going into the details of how we can purchase the most appropriate Power BI Premium capacity for our organisation, it’s worth first understanding the three types of licensing that exist for Power BI, as well as some common terms used when discussing licensing.

Power BI Free: This offers all the data connectors available for ingesting data into Power BI, all the capabilities required for transforming our data, modelling our data and reporting. We can also publish our report to the Power BI Service (powerbi.com), within our personal workspace. However, the core limitation of Power BI Free is the lack of sharing and collaboration capabilities.

Power BI Pro (Per-User): This offers everything that Power BI Free offers, therefore all the capabilities required for data transformation, data modelling, and reporting. The core difference between Power BI Pro and Power BI Free is the ability to share all Power BI items with other users who also have a Power BI Pro license. So, Power BI Pro introduces all the sharing and collaboration capabilities that are missed from the Free licensing. Also, with a Power BI Pro license, we can store all our Power BI items such as datasets, reports, and dashboards in a shared capacity that is secure to our organization.

Power BI Premium (Per-Capacity): It is important to understand that even with Power BI Premium, some Power BI Pro licenses are still required in the organisation for report authors and dataset designers who will be developing and publishing reports and datasets to the Power BI Service. Power BI Premium resolves the challenges of the previous licensing plans: Power BI Free and Power BI Pro, by moving from per-user to per-capacity which reduces the price considerably when compared to having to purchase a single Pro license for every user, even for those only consuming the reports and not developing and publishing. Also, Power BI Premium offers additional capabilities that are Premium only such as XMLA endpoints and removes some restrictions that exist with Power BI Pro licenses, such as the capped model size and the number of concurrent refreshes. The core difference between Power BI Pro and Power BI Premium is that Premium offers a dedicated capacity, therefore CPU, memory and storage which is dedicated to your and only your organisation.

Power BI Capacities

Two types of capacities are available in Power BI for storing and hosting all Power BI items, such as datasets, reports, and dashboards, depending on which licensing model is purchased.

Shared Capacity: When we purchase a Power BI Free or Power BI Pro license and publish any of our Power BI items to the Power BI Service, we are publishing to a Shared Capacity, therefore storing and hosting all our Power BI items amongst other Microsoft Power BI customers. It is important to understand that this does not mean other organisations have access to our data. However, since these organisations are sharing the same capacity, some restrictions are put in place to ensure individual organisations do not monopolise on resources such as CPU, memory, and storage.

Dedicated Capacity: When we purchase a Power BI Premium SKU, we are purchasing a Dedicated Capacity, which is like purchasing our own personal server with its own CPU, memory, and storage. As all these resources are dedicated only to our organisation, all restrictions that exist for organisations in a Shared Capacity are removed. Also, as we are purchasing the resources in the capacity, we now have the added benefit of sharing all our Power BI items with an unlimited amount of report consumers in the organisation. Keep in mind, all items must be hosted in a workspace that has a premium capacity assigned against it.

Power BI Premium SKU’s

With the above information, we should have a stronger understanding of the Power BI Licensing available. Now, to go into the detail of what Power BI Premium SKU (Dedicated Capacity) is required for your organisation, we should also understand the three types of SKU’s that can be purchased. If looking into Premium licensing, you will most likely come across: P SKU’s, EM SKU’s and A SKU’s.

Keep in mind, that Premium SKU’s are only the P and EM SKU’s, whilst A SKU’s are Azure SKU’s and should not be classified as Premium. Furthermore, as we are focusing on rolling out Power BI to the enterprise, we will focus only on the P SKU’s as these are for organisations that want a complete BI solution.

The below highlights the resources that come with every P SKU:

Capacity Nodes (SKU) Total v-cores Back-end v-cores RAM (GB) Front-end v-cores DirectQuery/Live Connection (per sec) Concurrent Model Refresh
P1 8 4 25 4 30 6
P2 16 8 50 8 60 12
P3 32 16 100 16 120 24
P4 64 32 200 32 240 48
P5 128 64 400 64 480 96

The Four Questions: How to choose a Premium Capacity

It is important to highlight that there is no exact science to selecting the right Premium SKU, however, we should always aim to have enough resource to cover our peak usage and monitor the usage logs. To help us estimate, we can ask ourselves the following questions:

How big will our Power BI datasets be?

When working with Power BI, memory is one of the most important assets that we should look to preserve and use efficiently. After all, the underlying engine behind Power BI (and Analysis Services), is the VertiPaq engine (also known as xVelocity) which is an in-memory columnar database that compresses and stores data in memory.

When planning for the right amount of memory, we need to think beyond the memory required to host our dataset. The rule of thumb is to multiply our dataset size by 2.5 due to the memory being consumed in three areas:

  1. Hosting our dataset
  2. Refreshing our dataset
  3. Incoming queries to our dataset

Let’s say we have three Power BI Reports (a single dataset sits behind a report), as the below shows:

When working with Report C it will be hosted in memory, therefore as the below image shows we will have 3GB of memory being consumed. No surprises here!

However, when we schedule a refresh on the Power BI dataset, Power BI duplicates the dataset in memory, and performs the refresh on the duplicated dataset. It is very important to keep this in mind when purchasing a Premium SKU, as essentially we are doubling the memory requirement.

At first, you may be wondering why the refresh does not occur on the current dataset hosted in memory, hence less memory consumption. This is to ensure no downtime exists for the organisation in accessing reports. Once the duplicated dataset completes refreshing, it simply replaces the current dataset in memory, which is now outdated.

As we said before the general rule of thumb is to multiply our dataset by 2.5, and this is due to the potential queries being generated by users against the dataset for calculating measures.

So, even though Report C runs on a Dataset which is 3GB this does not mean we won’t have any other memory being consumed, hence we should take the memory consumed by a single dataset and multiple it: 3GB * 2.5 = 7.5GB

How many concurrent refreshes are required?

It is common to find many oversee the limit on the number of concurrent refreshes that can run at the same time. It is also very common to question this, after all when purchasing a Power BI Premium SKU we are purchasing our capacity so we should have the right to run as many concurrent refreshes as we want, right? Well, this would not be the best of ideas, hence restrictions exist. As we saw above, the refresh process is very memory hungry, so by having many refreshes running at the same time, we will be sure to increase the chances of exceeding our available resources.

Let’s say we are working with a P1 SKU, which offers a maximum of 6 concurrent refreshes and we currently have 6 datasets being refreshes concurrently, as the below image shows.

Now, if additional datasets have also been scheduled to refresh Power BI will take them into a waiting area. Below, we can see that we have dataset 7 and dataset 8 being put in the waiting area:

These will be placed in the waiting areas with no time limit. Power BI will wait until one of the refreshing datasets have completed and simply replace with a dataset queued for refresh, as the below shows:

It is important to understand how many datasets must be refreshed at the same time when purchasing a Power BI Premium SKU, to ensure minimal waiting time for reporting.

What Connectivity Type is going to be used?

Depending on the connectivity type that will be used, different considerations should be taken for selecting the correct Premium capacity. Power BI has multiple Connectivity Types which can be used for connecting to the underlying data:

  • Import
  • DirectQuery
  • LiveConnection
  • Composite

If we are using an Import, we should focus on ensuring we have enough memory and virtual cores in place for hosting and refreshing datasets, as well as processing incoming queries from report consumers interacting with reports. Whilst if we are using a LiveConnection or DirectQuery to either Analysis Services, SQL DB or any other relevant relational database, we should focus on ensuring the connection (queries per second) limit to these sources are sufficient, therefore reducing the chance of the limit being reached, resulting in wait time.

How many read-only users will exist?

The last question we should be asking ourselves when in the process of purchasing a Power BI Premium capacity, is how many read-only users will be accessing the Power BI content published in the Power BI Service.

By identifying the below breakdown, out of the total number of users:

  • Developers: Dataset Designers & Report Authors
  • Frequent Users: Users accessing report multiple times a week
  • Occasional Users: Users accessing reports once a week

We can get an understanding of the total cost associated with Pro licenses (£7.50 per user), but also we can get a rough estimate of what Premium capacity we require through the Power BI Premium Calculator. The purpose of entering the count of frequent and occasional users is to determine the possible load that will be placed on the resources, particularly the number of back-end virtual cores responsible for satisfying incoming user queries. Again, the Power BI Premium Calculator, which can be found here, only offers a rough estimate and we should ensure we keep all the above questions in mind when purchasing a Premium capacity.

Summary

Choosing the right Premium capacity can be a daunting process, but with the questions we explored in this blog we should now have a better understanding of the various factors we should be considering when purchasing a Premium capacity.

Sources:

Governance and deployment approaches. Available: https://docs.microsoft.com/en-us/power-platform-release-plan/2019wave2/business-intelligence/planned-features.

______________________________

Twitter: @Data_Laz

Email: Lazaros.viastikopoulos@adatis.co.uk