This is the third article in my series about how to make Power BI Premium more affordable for small to medium sized enterprises (SMEs). In my first article I explained the problem and the logic behind how to configure a workable solution. In my second article I provided step by step instructions on how to configure Flow to start/stop Power BI Premium capacities. In the article today I am covering a way to scale the capacity up/down either on demand, or on a timed schedule.
Use Cases for Scaling Capacity
I can think of at least 2 use cases for scaling your Power BI Premium capacity.
- Create a manual trigger that will scale up the capacity when it is under high load.
- Add different scaling choices at different times of the day as part of a standard routine.
- Start the capacity at the beginning of the day at 7am using A1
- Scale up to A2 at 8am when the load is greatest
- Scale back to A1 at 10am after the peak for the day has passed.
- Shut down the premium capacity at 6pm, still allowing Pro users to access the capacity
If you check my costings spreadsheet at the end of this article, you will see that this configuration will cost around US$65 per week. Of course you will need to work out the capacities that you need for your own situation – I have provided a calculation spreadsheet at the end of this article.
Create a Manual Trigger to Scale Capacity
I was readying a blog by Imke Feldman last week where Imke explained how to trigger a Flow from a URL. The Flow will allocate you a unique URL containing a GUID (secret key). When you launch that URL from any browser, it will trigger the Flow to execute.
Create a new Capacity
I will not go into the same level as detail on creating these flows as I did last time. If you want to learn how to do this step by step, you should follow the instructions I provided two articles ago.
The first thing to note here is that I first created a new capacity with the extra pricing tier I needed. As a result I have my original A1 capacity created in my article last time, plus a new A2 capacity. Follow the instructions from last time to set up this new capacity. Note well:
- After creating the capacity, you will need to copy the capacity GUID (refer to setting up a capacity from the last article).
- Go into Power BI\Admin Portal\Capacity Settings\Select New Capacity\User Permissions and add the security group to the capacity.
- Navigate to the workspace you want to control\Access and add the AAD App as an Admin.
- Navigate to portal.azure.com, select the new capacity\Access Control (AIM)\Role Assignments and add the security group as an Owner.
Steps to Create the Scale Up Manual Trigger
- Create a new Flow from blank.
- Search for “when a new http…”
- Select the trigger from the list
- Under Advanced Options (no image), select method = “GET”
- No other config is required here.
- When you save the Flow later, this step will generate the unique URL you will need to trigger the flow.
- Then follow the instructions from last week to.
- Create a new step to start the new capacity.
- Create a new step to assign the workspace to the new capacity
- Create a new step to stop the A1 capacity
- Save the flow.
Now the Flow has been saved, you can
- Go back to the first step
- Copy the URL
- Save the URL as a shortcut on your PC and/or phone.
From now on, when ever you launch this URL, the Flow will be triggered.
Steps to Create a Scale Down Manual Trigger
- Copy the flow above
- Make changes to complete the opposite steps using drag and drop, plus editing the code (Start A1, Assign A1, Stop A2)
When you save the changes, you will get a new URL trigger. Save this as another URL on your PC and or Phone.
Time Based Scaling
Another cool trick is to plan in some delays in your Flows prior to completing more steps. To do this, add a new step and search for “delay”.
There are 2 options as you can see above. You could put a 60 min Delay into your “Scale up to A2” flow so that it automatically scales down again after 1 hour. You could also set a specific time of the day to change the scaling of your capacity for your standard daily scheduled tasks
You will need to work out for yourself what capacity you need for your organisation. I have prepared a costing spreadsheet to run some numbers. You can download that here. Simply edit any of the green cells to see how it impacts the cost.
In the example below, I have assumed A1 capacity from 7am to 6pm (11 hours per day, 5 days per week) with an A2 running at peak times from 8am to 10am. The total cost per year for this is US$3,500 (less than US$300 per month, or about US$65 per week).
Please Share Your Experiences
I would love to hear from people that have tried some of these Flows. Let me know how well it works and/or any learnings you have gathered through the process.