Scale your Power BI Capacity Up/Down

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.

  1. Create a manual trigger that will scale up the capacity when it is under high load.
  2. 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
    image
  • 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”.

image

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

Cost Analysis

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).

image

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.

Share?

Comments

  1. Thanks Matt, Good article as usual. I had no idea that you could rent capacity for only the hours it was required as I always assumed it was a 24/7 model. Although the charges are still to steep for smaller companies (15 – 20 users of which 3 are developers ) the spreadsheet gives a better idea on where the break-even point is.

  2. Thanks Matt. We have been working hard since Embedded began to keep our solution within A1. Other than the obvious points of slimming down data and pushing Power Query code out to Azure SQL to reduce refresh memory spikes, we also use the Pause/Unpause facility. By pausing the capacity, you essentially flush out much of the cache. This can free up around 0.5-1GB of memory. This works for us without any negative performance impacts as we have around 300 users using 100 different RLS connections on a ‘light usage’ basis. Our guess is that long term cacheing doesn’t have much performance value — when a user logs on, it’s unlikely their set of data will be there, and indeed our subjective experience is that pausing/unpausing doesn’t seem to make any difference.
    Previously a user could trigger a refresh during the day which led to a memory spike. When that spike hit 3GB the refresh failed. Since we have been using pause/unpause, we have avoided breaching 3GB and all is fine.
    We’ve been doing this manually for a few months now (it only takes a minute or so) every morning, but we shall automate as soon as we get time, either via Microsoft Flow per your advice or programmatically.
    Just one point: in order to provide a good user experience, after pausing/unpausing I have to log on as a user to wake up the PBIX which can take 30-60 seconds to get going.

    • Thanks for sharing those excellent insights. I wonder if you could use flow to launch a webpage with a report embedded to wake it up. The AAD App has approval, so I assume it can work.

Leave a Reply