Affordable Power BI Premium for Small Business (Part 2) - Excelerator BI

Affordable Power BI Premium for Small Business (Part 2)

Level: Advanced

This is the second in my series of articles explaining how to use Microsoft Flow, the Azure APIs, the Power BI APIs and SharePoint Online to help make Power BI Premium more affordable for small to medium sized enterprises (SMEs).  You can read the first article in this series from the following link: Affordable Power BI Premium for Small Businesses

Step By Step Solution

This is an involved topic with lots of moving parts.  You need to correctly configure every part of the solution to make it work.  While the steps will be unfamiliar to many, you can do it if you follow my instructions.  Once again, I am indebted to Rui Romano from Power BI Tiles for his patient assistance to help me get this working. Believe me: no Rui = big issues = no blog post.  While the solution is quite straight forward in that there a few clearly defined steps, each step has its own specific configuration requirements and you must get everything 100% correct for it to work.

You can expect all the steps that follow will take about an hour to configure. Maybe less if you are an Azure/API pro, maybe more if you make mistakes, so take it slowly and get each step correct before moving on.

Note: This process only works with the new V2.0 (preview) Workspace experience, so make sure you have one of those before you begin.

Process Steps to Configure All This

These are the steps below.  You will need admin rights to Azure and Power BI within your tenant to proceed.

  1. Create a premium capacity (if you don’t have one)
  2. Create an Azure AD App with your delegated authority
  3. Create a security group and assign the app to the group
  4. Give the App approval to configure the premium capacity
  5. Go to Power BI and give tenant access to the security group
  6. Give the App approval in Power BI to administer the Workspace
  7. Write the flows to complete the process steps resume/assign/un-assign/suspend.
  8. Securely embed the Power BI reports in SharePoint Online

1. Create the Premium Capacity

If you already have a premium capacity that you want to use, you can skip this step. I have just included it for completeness.

  1. Login to portal.azure.com
  2. Click Create a Resource
    image
  3. Search for Power BI Embedded and select that option.
  4. Down the bottom of the screen, click Create.
    image
  5. There are a number of fields you must complete to create the capacity.
    1. Give it any suitable name so you can identify this premium capacity from any other you have in your tenant (this is company wide, not just you).
    2. You have to pay for it (sorry).  You will need to select a subscription from the list.  A subscription is simply a tool to manage the payment for what you use.  Think of it as an auto payment agreement.
    3. You must assign a resource group; you can create a new one or use an existing one.  Think of a resource group as a “tag” to associate your expenditure on this premium capacity with a project.  By placing your resources in resource groups you are able to more easily able to track the total expenditure from your subscription against individual sub projects (resource groups).
    4. Someone has to be the default administrator.  This will be you by default.
    5. Where do you want the resource to be located in the world?  Put it somewhere close to the users.  Best to place it in the same location as your Power BI tenant.
    6. Select a capacity tier.  The A1 option (SKU) is the cheapest but also has the lowest compute power.  You can start with A1 and change this later (scale up) if it is not powerful enough.  You can even change (scale up/down) the capacity intraday if needed, to give you more power when you need it and less when you don’t (not covered in this article).
    7. Click Create
      image

Azure will then weave its magic and build (spin up) the resource for you. You should see a notification in the top right hand corner of the portal (1 below).  If you click on the bell, you will see when the capacity is ready (2).  You can hyperlink directly to the capacity with the button (3).

image

OK, now you have your Power BI embedded capacity, which is an Azure A1 SKU in this case.  As I mentioned earlier, there are other types of Power BI embedded capacity that you can buy called EM SKUs, but these must be purchased via a reseller partner, and there is a minimum 12 month commitment.

The capacity will be running as soon as you create it.  You can tell because there is a pause button inside the administration dashboard for the capacity and the Status shows “Active”.  If you see as start button instead, then the capacity is not running (note you may have to refresh your browser to update the status). You pay per second that the capacity is running, and you pay nothing when it is paused.  The reason you pay nothing is because there is no data stored in the capacity, it is simply “compute power” that you are purchasing.

image

2. Create an Azure AD App

Before you start the rest of this process, open up a new Notepad.exe session and keep it open.  You will need somewhere to store various names and GUIDs during the process.  Each time you copy something to Notepad, make sure you clearly label each item.  It gets very confusing if you don’t do this.

  • Login to portal.azure.com (if you have not yet logged in).
  • Click Azure Active Directory (1 below).
  • Click on App registrations (1 below) and then on New application registration (2 below)
  • Give it a name (1 below), specify Application type (2 below) and then provide the Sign-On URL (3 below).  The sign on URL is not required for this App, so I have just used the generic localhost URL

This will create a new app.  Copy the name you gave to the App and save it in Notepad.  Make sure you label it as AAD App Name

  • Copy the Application ID (1 below) and save it in Notepad.  Make sure you clearly label it as AAD Application ID. All of the GUIDs you will copy look the same so you need to label them.  You will need this Application ID while creating the Flows in step 7.
  • Click on Settings (2 below) and then on Required permissions(3 below).
  • Click on Add (no image shown)
  • Then click “Select an API” (1 below).
  • Select Power BI Service (2 below) and then click Select (3 below).
  • This gives the App permission to use the Power BI Service APIs.
  • After clicking select above, you need to place a check mark in APPLICATION PERMISSIONS (1 below) and then click Select (2 below).
  • Click Grant Permissions\Yes (no image shown)
  • Click Done.
  • Click on Keys (1 below), give the key a suitable description (2 below) and then the expiry details (3 below). Then click Save (4 below).

This will create the secret key required to validate the API call from Microsoft Flow. Immediately copy the Key Description and Key Value and paste these to Notepad – you will not be able to retrieve the key later if you lose it and you will instead have to recreate the key.

This is the secret key that you use to delegate authority to the API to configure your tenant.

3. Create a Security Group and Assign the App to the Group

  • Click Azure Active Directory in the left panel (no image shown).
  • Then select Groups and then click on +New Group (no image shown).
  • Select Group type as Security (1 below), specify a group name (2 below) and description (3 below).
  • Select Membership type as Assigned (4 below) and then click the Members section (5 below).
  • Search for the AAD app in the search box then click on the App (6 below).
  • Click Select (7 below).
  • Click on Create (8 below).

Now the AAD app has been assigned to the security group created above.  Double check the AAD App has been added. When I was testing this it somehow didn’t get added.  It took me 15 minutes to work out what was wrong.  So check now before moving on.

Add the name of your new security group to Notepad, clearly labelling it as AAD Security Group.

4. Give the App Approval to Configure the Premium Capacity

  • Go to the Azure Dashboard and select the premium capacity that you want to control with the AAD App (no image shown).  This is the capacity you created in step 1, or any other capacity you want to control. Note, I am using a different capacity than I created above, called A1Demo.
  • Click on Access control IAM (1 below), then Add (2 below), Add role assignment (not in image).
  • In the Add roll assignment window (3 below), specify Owner as the Role (4 below) and then search for the name of the AAD Security Group in the search box (5 below – note in my case “blog” are the first characters of my AAD Security Group called BlogDemoGroup).  Make sure it is the AAD Security Group you add and not the AAD App.
  • The AAD Security Group will be displayed (6 above).  Click save (7 above).

5. Give Tenant Access to the Security Group

For this next step, you need to have admin rights in the Power BI tenant.

  • Go to Powerbi.com and log in.
  • Click on the cog on the top right corner and select admin portal.
  • In the Admin Portal, click on Tenant settings (1 below) and then scroll down to Developer settings (2 below).
  • This will allow service principals to use Power BI APIs (3 below) – set Enabled to On (4 below).
  • Then add the security group you created (5 below)
  • Select Apply (6 below).

The group now has the authority to make changes to the Power BI tenant.  Note:  There is a message displayed saying it can take up to 15 mins for these settings to be fully operational. Jot down the current time so you know how long to wait.  This will be helpful if you finish quickly and are not sure if the 15 mins has elapsed.

6. Give Workspace Access to the Azure App

  • While still signed into powerbi.com
  • Navigate to your Workspace – the one you want to assign to the premium capacity ( 1 below).
  • Copy the GUID of the Workspace from the Address bar in the browser and paste it in Notepad, clearly marking it as Workspace ID.
  • Click on Access (2 below)
    image
  • Search for the AAD App (1 below), Select the App name(2), change the access type to “Admin” and click Add (3), then close.  Make sure you have assigned Admin access to the App (the default is Member and this access will not work).
    image

This is required later to allow the Power BI API to assign and un-assign the workspace to the premium capacity.

7. Write the Flows

All the code you need is provided below. If you want to find the documentation for the Azure APIs to control Premium Capacities, you can find it here https://docs.microsoft.com/en-us/rest/api/power-bi-embedded/capacities

There are APIs for various operations (1 below). Of these, this article shows how to use Resume (2 below) and Suspend (3 below).

For reference, you can find the Power BI API code for assigning/un-assigning a Workspace to a capacity here https://docs.microsoft.com/en-us/rest/api/power-bi/capacities/groups_assigntocapacity

Note, you will not need this as I have provided the code below – this link above is just for reference.

Create the Resume Flow:

  • I copied the Resume API code from 2 above as follows:
https://management.azure.com/subscriptions/subscriptionId/resourceGroups/resourceGroupName/providers/Microsoft.PowerBIDedicated/capacities/dedicatedCapacityName/resume?api-version=2017-10-01
  • Copy the API code above and paste it in your Notepad file.
  • Go to the Azure Portal Dashboard, select the capacity you want to control, click overview and locate the subscriptionId (1 below), resourceGroupName (2 below) and dedicatedCapacityName (3 below).
  • Copy the three items needed by the API code into the appropriate place holders. The code in your Notepad file should now be ready to use in the next step.

Here is a sample of what it should look like using random GUIDs

https://management.azure.com/subscriptions/613192d7-503f-477a-9cfe-4efc3ee2bd60/resourceGroups/TestRG/providers/Microsoft.PowerBIDedicated/capacities/azsdktest/resume?api-version=2017-10-01

Now you are ready to create the Flows, starting with the Resume Flow.

  • Login to flow.microsoft.com
  • Select My Flows (1 below), +New (2), +Create from blank (3).
    image
  • Click Create from Blank
    image
  • Search for “Schedule” (1 below) and then select the Recurrence/Schedule trigger action (2 below).
    image
  • Select an interval that suits your needs. I have selected daily in the morning at 7 am.  You can just set 1 Month to start with if you like, and come back later and modify this step.
  • Then click “+New Step”
  • Search for HTTP and select that option (no image shown).
  • Select POST for the Method (1 below).
  • Copy the address from the API code you compiled above (from Notepad) into URI (2 below).
  • Click on Show advanced options.
  • Completed the details as shown below.
  • Tenant (4 below) is the domain for your company.  Make sure you use the *.onmicrosoft.com version.  It may work if you use your normal URL, but I can’t guarantee it.
  • Audience (5 below).  This is the code for the Audience for the Azure APIs.  You can cut and paste it from below.
    https://management.core.windows.net
  • Client ID (6 below) is the Application ID that you copied in step 2.
  • Secret (7 below) is the Secret key that you saved earlier.
  • Click on Save.

You can then test run the Flow to check if the Resume is working.

  • Click Test (no image shown)
  • I’ll perform the trigger (no image shown).
  • Run the Flow (no image shown).
  • See flow run activity (no image shown).
  • Assuming it works, rename the Flow to be “Start Capacity”.  If it fails, go back over the steps to work out what went wrong.

Before moving on to the next step, you need to get the GUID for the capacity and assign permissions to the AAD App to control the capacity (no images shown).

  • Navigate to PowerBI.com
  • Click on the Cog in the top right hand corner of the screen (as before)
  • Click on Admin Portal
  • Click on Capacity Settings
  • Click on Power BI Embedded (note it can take 15 seconds before this menu is visible – be patient, it will appear)
  • Click on the name of your capacity (note it can take 15 seconds before the capacity names to be visible)
  • Add the Security Group you created to the User Permissions, then click Apply
  • Copy the GUID from the Address bar in the browser (shown below) and save it in Notepad (clearly mark it as capacity ID)

Create the Suspend Capacity Flow:

The process steps to suspend the capacity is similar to above.  The easiest way to create the suspend Flow is to save the Flow you just created as a second copy and then edit this new copy.

Save the copy as shown below, giving it a suitable name:

After copying, you then need to click the ellipsis for the new Flow and select “Turn flow on” (no image shown).

Edit the new flow with the following variations.

  • Set the trigger to be in the evening when you are ready to shut down
  • Edit the URI (API call) and replace the word “resume” with the word “suspend”.

Save and test the flow.  Once done, the capacity should be stopped.

You should now have 2 flows. One to Start the capacity and one to Stop the capacity.

Assign Workspace to Capacity

Edit the first flow you created – the one to start the capacity (no images provided).

  • Add a new step below the last step
  • Choose the action HTTP
  • Method Post
  • Copy the following API code below to notepad
    https://api.powerbi.com/v1.0/myorg/groups/groupId/AssignToCapacity
  • Replace groupID with the workspace ID
  • Copy and paste the entire line of code into the URI box
  • Copy and paste the following code below to notepad
    {
    "capacityId": "enter capacity GUID here - keep the quotes"
    }
  • Add your Capacity ID inside the quotes (keeping the quotes)
  • Copy your modified code from Notepad
  • Paste the code into the body of the Flow step.
  • Click “Show advanced options” and add the necessary steps.
  • Here is the code for the Audience for the Power BI APIs.
    https://analysis.windows.net/powerbi/api
  • You should have something similar to shown below when finished.

    Note well. The Audience above is different to the first HTTP step, but the other items are the same.  Client ID is the Application ID from before, and the secret is the Key from before.
  • Test the flow works.
  • Rename each step in the flow to something more descriptive, like shown below, then save

Un-Assign Workspace from Capacity

It is easier to duplicate the Start Capacity Flow than it is to edit the previous Stop Capacity Flow.

  • Set the existing Stop Capacity flow to “Turn off flow”.  Better to do this rather than delete it for now.
  • Duplicate the updated Start Capacity Flow, call it New Stop Capacity, and set it to “Turn on Flow”.
  • Change the order of the 2 HTTP steps.  You can just drag the final step and drop it above the middle step.
  • Change the first step trigger time to (say) 7pm and rename as appropriate
  • In the second step, set the Capacity ID in the body to be as follows
    {
    "capacityId": "00000000-0000-0000-0000-000000000000"
    }
  • Rename the step to “Un-Assign workspace from capacity”
  • In the last step, edit the URI.  Replace “resume” with “suspend” and rename the step to “Stop the Capacity”
  • Save and test.

8. Embed in Sharepoint

When a free user is using premium capacity with a free user account, they can’t access the reports via PowerBI.com.  Instead they must access the reports another way – in this case via SharePoint Online.

  • Run the flow to restart the capacity and assign the workspace to the capacity.
  • Navigate to the report in PowerBI.com
  • Go to the file menu and create the SharePoint Embed code
  • Create a new SharePoint Page, edit the page and click to add a WebPart (1)
  • Search for Power BI (2) and select the Power BI WebPart (3)
  • Paste the embed link and publish the SharePoint Page.

You will need to give the users access to the SharePoint site/pages as required.  You will also have to add the free users to the PowerBI.com workspace so they can see the page.

Notes

  • This process only works with V2.0 Workspaces – it does not work with the old type of Workspace.
  • If you have issues, go back through each step, one at a time and make sure everything is configured as described.
  • There is no way of knowing which premium capacity tier is right for you. I have used the A1 capacity in this article – it is the cheapest but also the smallest capacity.  You will need to test out your capacity requirements to see what works for you.
  • Thanks to my mate Gilbert from Fourmoo for being a beta tester for me to make sure the steps all worked correctly.

What Do You Think?

Please post any comments you have about your experiences configuring this solution.

17 thoughts on “Affordable Power BI Premium for Small Business (Part 2)”

  1. Thought I’ll jump into this conversion regarding Power BI Premium given large dataset (10GB+) support is now in preview release.

    It seems that Premium is squarely aimed at the large business which is disappointing. Also, the direction of Azure Analysis Services may fold into Power BI Premium at some point down the path.

    The embedded PowerBI seems to be a good replacement for custom C# reporting solutions however it’s still not mature as a product compared with PowerBI Desktop/Web given it’s missing some basic functionality.

    Stick with Azure Analysis Services and shared PowerBI web model, there are limitations on using the free user (render requests).

  2. Matt, following your instructions we created an A1 embedded capacity and assigned the SharePoint Test workspace to it using the Microsoft Flow routines you articulated above. That part is working beautifully, thanks!

    But, we are encountering major issues with basic refresh scheduling and completion. Dataset and dataflow queries that refresh in minutes when published to a shared workspace are taking hours in the embedded workspace and often fail for no apparent reason. Plus, we are starting to see datasets fail because of memory errors saying we have exceeded allowed memory for out tier. We stepped up from A1 to A2 then A3 and now A4 in testing and we are still having issues with dataflows and datasets in the embedded capacity that are non-existent in the simple shared workspaces in Pro.

    Our GoldenDataset.pbix file is ~300 meg and it run fine outside of embedded but will not run within embedded. Have you faced issues like this? Any ideas on how to resolve them?

    1. This is a huge topic in its own right. The short version is that Microsoft throws (virtually) unlimited resources to the public cloud to support refreshes. So if you are not using premium, you basically get any “problems” solved for you bundled within the price. When you go to premium capacity, you have to buy the resources you need to make it work, or make the refresh process “lighter” so it doesn’t need so much resource. I can’t say if your models are well designed or not as I haven’t see them. One suggestion however – why not schedule them to refresh when the premium capacity is turned off? That way you will get unlimited resource.

  3. Hey Matt,
    Why do you need to always assign and unassign the capacity to the workspace?
    You can pause it when it is still assigned to the workspace right?

  4. Barney Lawrence

    Hi Matt, this is brilliant and I could see us using it as a way to trial Power BI in the cloud in advance of the move to Premium – we’re not small but our purse strings are very tightly knotted and we’d need to show the value of a widespread deployment first.
    However I see one possible hitch, am I right in saying that SharePoint Online will in itself come with a cost? My initial searches put it at about half the monthly cost per person of Power BI Pro so while I can see this working for anyone who is already paying for SharePoint, for those with no footprint in the cloud already it’s maybe just pushing the break even point between Pro and Premium from 600 up to around 1000 people.

    1. I hadn’t really considered this, but yes I guess you are right. Many companies these days are on O365 so there is no extra cost. You could try SharePoint on premise and see if it works – I don’t know. Post back with what you find if you test it.

  5. Fantastic ! I’ll try that to use calculated items with CDM / Dataflow.
    I’m used to share reports with users within Microsoft Teams. Do you think it works also ? Regards

  6. Thanks Matt – fantastic and detailed blog. I hope to use this approach for attempting to do some learning/testing with paginated reports – way too expensive to attempt otherwise with premium capacity.

      1. Matt, thank you for the very detailed article. We are considering Power BI embedded as a reporting solution in our own application, but also need to know how to deploy power bi reports to SharePoint. And your article helped a lot to bring some clarity to the conversation. I followed your instructions (except the Flow part, which I left out for now) and spun up a A1 SKU, but not sure how to deploy paginated reports to SharePoint using the same approach. How do we do this?

  7. Started read this post and quickly scrolled to the end, looking for the answer: will it launch a rocket to the Mars? 🙂
    Thank you, Matt, this is very helpful!
    Just wishing that Azure part will be not so overcomplicated

  8. Matt – thanks for sharing such detailed notes on a creative solution. For companies who have users within the same timezone this is starting to get to a price-point where it is bearable. For those with users across the world, the option to reduce costs by switching embedded capacity off is reduced/removed.
    Still, Embedded is at least a lower entry-point than Premium. It’s a shame about the peak renders per hour of 300 (entry level) though! It’s not good for the end-user experience for them to intermittently experience the system grinding slowly once the peak has been reached.
    I wish the Embedded was capacity based like Premium, rather than usage volume-based. Anyway…please keep up your lobbying! Thanks again.

    1. You make good points David. I guess MS has the render limits to prevent people opting for lower cost options. Ultimately MS needs to get paid for the value it adds to the customer, so generally I am OK with the render limits. It remains to be see if the 300 limit for A1 prevents it being useful for SMEs. I would be interested to hear from someone that is using it this way.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top