This is another one of those articles that has been swirling in my head for a very long time, about 2 years actually. I have long been frustrated by the lack of a suitable scalable Power BI pricing model for small to medium sized enterprises (SMEs). You can read my rant about that in this blog article from 2 years ago when Premium was launched https://exceleratorbi.com.au/power-bi-licences-changes-good-bad/. Even before the announcement of Power BI Premium, I have been lobbying for an affordable option for SMEs, and 3 years down the track I don’t feel that much has changed. Premium is good for large enterprises, but what about smaller enterprises? There is good news though; there is one less obvious approach that may make Power BI Premium more workable and affordable for some companies, and that is what this article is about today. This is a big topic, so the article will be spread over a few weeks. This article explains the opportunity. My next blog will provide step by step instructions on how to configure the solution.
Auto Start and Stop Premium Capacity Over Night
This solution is in 2 parts.
- SMEs can purchase relatively cheap Azure embedded capacity, and deploy the reports via SharePoint Online (see video of this working below).
- In addition, the solution allows SMEs to assign this capacity to non-pro users during peak times of the day when needed, and then auto shut down the capacity in the evening when not needed. All this while still allowing pro users to access the content 24×7.
First a few acknowledgements of some people that have helped me piece together this solution. Firstly Adam Wilson (Microsoft) helped me understand the opportunity to use embedded premium capacity with SharePoint Online as a potential solution for SMEs. Then Gilbert Quevauvilliers (from Fourmoo) made me aware of the “A” range of SKUs (Azure Power BI premium capacity) that can also be used with SharePoint Online. There is another type of embedded capacity called the EM SKUs, but these have to be purchased via a reseller and the minimum purchase commitment is 12 months. The A SKUs do not have these limitations.
The killer feature I have had in my mind for some time is the desire to start and stop premium capacity automatically (think auto shut down over night to save money). I knew this could be done with the Power BI APIs (application programming interface) but I didn’t have the technical knowledge on how to do this myself. Last week when I was at the MVP Summit, I was chatting with a few of my more technical MVP buddies. A few offered to help me (we MVPs truly like helping other people) and as it turned out Rui Romano gave me a couple of lessons on how to do what I wanted. Rui gave me a crash lesson on Power Shell, Power BI APIs, Azure APIs and a few other cool things too. Rui is a really smart guy. You can read Rui’s blog here and also see what his company has to offer the Power BI community here including the ability to embed Power BI reports directly in Office documents.
Overview of The Solution to Affordable Power BI Premium for Small Business
This is quite an involved topic, so I have broken it down into three sections with increasing levels of detail. You are of course free to read in anyway you would like however I suggest just starting at the top and work your way down.
I will provide:
- A business overview of the problem and opportunity
- A technical overview of the solution
- The specific steps to execute the solution (I will provide this from next week).
Business Overview of the Problem and Opportunity
There are 2 primary ways for users to access shared content in the Power BI service (there are several other ways including Power BI Report Server and Power BI Embedded which I won’t cover here).
- You can assign a pro licence to each user.
- You can purchase a capacity licence. With a capacity licence you can have as many users as you like, but there is a fixed processing capacity. You need to purchase enough capacity to meet your user demand throughout the day.
End State With Premium Capacity
- A business will have a number of Power BI Pro users and many more Power BI free users that can access shared reports via premium capacity.
- Reports are published to PowerBI.com and then displayed for end user consumption to both groups of users via SharePoint Online (secure embed).
- You purchase a premium capacity (one of the A SKUs, starting at around US$700 per month).
- You automatically start the premium capacity each morning on weekdays (using Microsoft Flow), and then automatically assign the workspace to the capacity.
- You automatically un-assign the workspace from the premium capacity each evening on weekdays (using Microsoft Flow), and then suspend the capacity until it is needed again.
- While the premium capacity is in a suspended state, the Pro users can still access the reports but the non-pro users cannot.
Watch the End to End Process of Starting the Capacity
A few people made comments that they didn’t think the reports would work with SharePoint Online secure embedded. The documentation also suggests that it wont work. I decided to record a video showing you that it does work.
Pro licenses are needed for report publishers (even if you have a capacity licence) and generally speaking this licensing method is fine (very affordable) for smaller groups of people. You can use Pro licences for all users in your entire business if you choose, however it can become very expensive as you scale above around 1,000 users.
The premium capacity “P” SKUs start in price at around US$5,000 per month – this licence model is designed for large companies. It is worth noting that there are features that are available in Premium that are not available for pro licences (eg incremental refresh). Premium is generally not an option for SMEs due to the absolute price, per user price, and the lack of need for the additional premium features.
Premium capacity becomes cheaper than pro licensing when you exceed around 650 users, and becomes much cheaper in very large deployments (it can be as cheap as US$3.00 per user).
While Premium P capacity is not an option for SMEs, there are some cheaper “A” SKUs (embedded premium) that have much less capacity at a lower cost. Because these are embedded capacities, you can’t log on to PowerBI.com to use it, but instead have to embed the reports somewhere, such as SharePoint online. I will cover that part in my next article.
The price of the A SKUs is much cheaper than the P SKUs, with A1 starting at US$700 per month. While that is certainly more affordable, even that price can be expensive for smaller businesses. But one of the selling points of cloud computing is that you can scale up/down/off/on whenever you like. You can do this manually using the admin tools provided by Microsoft, however you can also do it automatically, either on a schedule or on demand. This article describes how to automatically start (spin up) a Power BI capacity in the morning so that users without a pro account can access Power BI via Sharepoint Online. At the end of the day the capacity will automatically be suspended, saving cost. Pro users can continue to access the reports via Sharepoint Online but non-pro users cannot. You only pay for what you use, so if you run it for 12 hours per day, that will be about US$350 per month for the A1 SKU.
Note: I am not saying that the A1 capacity will be of sufficient size for your business. It will depend on the number of concurrent users as well as the size and design quality of your reports.
Technical Overview of the Solution
While this is a technical overview, I have included explanations for non technical readers to help everyone understand.
You can resume or suspend the capacity yourself using the Azure Portal (as long as you have Admin privileges). Just log in with your username and password (aka credentials), go to the appropriate menus and perform the tasks required. But what I want to show you is that you can automate these tasks too. It is a bit involved, but the instructions will be provided in the next article.
Prerequisites: you will need to have admin privileges for portal.azure.com and also for the Power BI admin portal. It can be assumed that most business users will not have this access, so this is a job for your friendly IT Admin folks. If you are a business user, talk to IT nicely and get them on board to help test if this is a viable solution for your business.
Automation Using APIs
Many of tasks that can be executed by an admin user can also be executed by an API (application program interface). Each API is designed to do a particular task(s). The tricky thing about calling APIs is the need to manage authorities to complete each task. If APIs didn’t have authority management, anyone could start or stop your servers without approval – not what you want. APIs can be called using PowerShell and the authentication is handled as part of the process. In the solution I am discussing here today, the authentication is managed via an Azure App with service principles applied, and the API is triggered on a timed schedule using Microsoft Flow.
The Specific Steps (Overview)
Firstly you need to create an Azure App. An App is like an automated robot that can act on your behalf (eg to give approval) when you are not there. While setting up the app, you will create a “secret key” for the App – this is used to delegate your approval to the App. What happens (practically speaking) is when “you” add the secret key, you are delegating authority to the App to act on your behalf using your credentials as the delegated authority to do so. But instead of storing your user name and password, you generate a “secret key” (essentially a password, or token). The app hides the secret key (you only get 1 chance to copy it to a safe place) and the app then can use it to prove it has your authority to complete a task. You then share this secret key with another process (like an API trying to resume/suspend capacity). You include the secret key inside the API call. When the API call is made, it passes the secret key to the Azure App, the App checks that the secret key is correct, and the App then makes the change. Think of it like a password to unlock a door. The owner can share a password with whomever, and having the correct password is the “proof” that they are allowed to enter.
Once you have an App, you have to tell the premium capacity that this App is allowed to control the capacity (it is just like giving another user admin rights). You assign admin privileges to the App to work on the premium capacity (eg make changes such as resume/suspend). Actually, there is no reason why you couldn’t adapt this process to scale the premium capacity up during the morning peak rush for data and then scale it back down in the later morning/afternoon. You could even create a manual trigger to double the capacity if the server is flooded and then auto scale back after a defined period.
Next you need to give the App approval to work in the Power BI tenant too, but you can’t assign the App access to do this directly. So the next thing is to create a new security group and add the App to the security group. Security groups CAN be assigned to control the Power BI tenant.
The next thing is to go into the Power BI admin portal and add the group to the approved users of the Power BI APIs. An API is simply a programmatic way of executing a task that you normally can do manually yourself with a few button clicks. Note: it can take up to 15 mins for these privileges to flow through and become operational.
The last thing to do is create a Microsoft flow to call the API(s) to request a assign/un-assign of the Workspace to a capacity, and also to resume or suspend the premium capacity. Note you can use Microsoft Flow to call any of the other APIs too using this process, such as moving datasets to a different workspace etc. I may come back and discuss that in future articles if there is any interest.
Step by Step Instructions Next Week.
This is a lot to absorb in one article, so I am breaking it up over several weeks. Keep an eye out for next week’s article. In the mean time, let me know in the comments below if this sounds like something that could work for your business.