Power BI which released at July 2015 targeted business analysts and power users. From that time Power BI team made massive enhancements, and with each enhancement the user market of Power BI expanded. Recently Power BI take a big step forward with bringing developers as users. Power BI Embedded is the Power BI reporting solution for application developers. In this post I’ll explain how to get started with Power BI Embedded. If you want to learn more about Power BI read Power BI online book; from Rookie to Rock Star.
What is Power BI Embedded?
Power BI Embedded is the reporting and analysis solution for mobile and web applications. Power BI Embedded is an Azure service that integrates Power BI solution into mobile and web applications. The report still has to be authored and created in Power BI Desktop. After creating the report it can be published into Power BI workspace in Azure, and using API Keys of Power BI workspace and embedding Power BI report frame into the web/mobile application it will be integrated into the application.
Key Advantages of Power BI Embedded
- Integrating Power BI into web and mobile applications
- Application users don’t need Power BI account to view the report
- Security integration with the application
There are other advantages of using Power BI embedded, but these three are the major two reasons why people will use Power BI embedded. one of them brings the Power of reporting and analysis of Power BI into an application. On the other hand if your application has some users, then they will be able to view Power BI reports without having Power BI account. Security then can be applied easily through workspaces in Power BI Embedded.
Prerequisites For the Sample Report
Power BI embedded is an Azure service, so the very first thing you need in Azure subscription. If you don’t have it you can apply for free trial version.
Also for this example I will show you a Power BI solution with connection to sample Azure SQL Database AdventureWorksLT. If you don’t know how to set it up, follow the instructions in this post to set up AdventureWorksLT Azure SQL DB.
Getting Started with Power BI Embedded
For this example I will use a Live/DirectQuery connection to Azure SQL DB AdventureWorksLT. The main reason is that Power BI embedded right now at the time of writing this post doesn’t support scheduled refresh, I believe this feature will be available very soon. So let’s create a sample Power BI report in Power BI Desktop. If you don’t know what Power BI Desktop is; read this blog post: Get Started with Power BI Desktop.
Open Power BI Desktop, Start with Get Data, and Choose SQL Server. Make sure you have checked Live Connection;
After entering credentials for connecting to Azure SQL DB you should be able to see list of tables. Note that you might need to set up firewall in Azure SQL server to allow your computer IP to pass, if you don’t know how to set it up. read my blog post about connecting to Azure SQL DB from Power BI. You only need to read the set up firewall section of it.
From list of tables, choose these: Customer, Product, ProductCategory, ProductModel, SalesOrderDetail, and SalesOrderHeader. and click on Load.
After load you will notice that there is no Data tab in Power BI, and the reason is that we’ve used DirectQuery/Live connection. with this option we won’t be able to use DAX or calculations from it in Data tab.
Now let’s create a very simple chart, the chart is a clustered column chart with Color (from Product table) as Axis, and SalesPerson (from Customer) as Legend, and OrderQty (from SalesOderDetail) as Value.
Now save the report as SampleReport, or any other names you like. We will publish this report into a Power BI workspace in next few steps.
Power BI Workspace Collection
Power BI reports can not be published from Power BI Desktop for using with Power BI Embedded. In fact for using Power BI Embedded, reports should be published through the application itself. and it would be published to a container or folder under your Azure subscription. This container or folder is Power BI Workspace Collection.
To create the workspace collection, go to Azure portal (https://portal.azure.com), and create a New Power BI Embedded under Data and Analytics section.
Now set a name for the Power BI workspace collection, and either use an existing resource group, or create a new one for it.
As you can see creating the workspace collection itself is simple and easy. After creation of Power BI embedded workspace collection, you will see the workspace collection blade opening as below;
Under each workspace collection there can be one or more workspaces. like folders that has sub folders. Power BI reports will be published to workspaces. I will explain in future posts how you can leverage using multiple workspaces, for now let’s keep it simple and work with only one workspace.
Sample .NET Projects
At this moment there is no graphical user interface option in Azure portal to create a workspace, so it needs to be created through an application. because this is the getting started sample and I don’t want to dive into coding here, I’ll be using sample Microsoft .NET projects from here.
Download the code above. Note that you need Visual Studio installed to open this project. There are two projects under Github all under same solution.
For creating a workspace use ProvisionSample project, and right click on it, and from Debug, choose start new instance
This project has the code for creating workspace, and also publishing the report.
** Note that this project is not a utility for deploying Power BI reports, this part can be embedded into your .NET application. This is just an example code for doing the deployment.
For creating a workspace choose option 5. it will asks some information about your existing Azure subscription key, and work space collection and will create the workspace for you. It will also asks for your Power BI workspace collection Access Keys, which you can find them under Power BI embedded workspace collection blade. Be careful that don’t share these keys to others. this would be the access to Power BI embedded.
After creating the workspace successfully you should be able to see it under workspace collections in Azure Power BI embedded blade.
To publish report again there is no graphical interface. and you can use same ProvisionSample projecct for publishing. (Number 6 in the list of options to choose);
For publishing the report you need to provide the access key and name of workspace collection, the id for workspace, then name for data set (the name that you want this to be stored), the full path of *.pbix file. Now you see that Power BI report is published.
Update Connection String of the Report
Because we used an Azure SQL DB as the source of connection, we have to update the connection string after deployment. Again you can use same ProvisionSample project with option 7 this time.
Testing the Embedded Report
After updating the connection information, now we can test the report. There is another project under this solution named EmbedSample. This project is an ASP.NET MVC solution which already has the Power BI embedded code in the application (I will discuss about details of that code in future posts). All you need to do is to set up the Web.Config of this project to use your workspace collection. Open Web.config and configure lines below;
After entering the information for Access Key and workspace collection and workspace id, then you can run this project and in the web application opened, under reports you will see the AdventureWorks Report which will show you the live data as result of DirectQuery connection.
As you can see the Power BI visualization here is exactly same as the visualization in Power BI service itself, and it supports the custom visuals as well. However users can’t edit the report at this stage. they can change the filtering if they want.
One of the big advantages of Power BI Embedded over Power BI publish to web is that you can set up a security in embedded, while the publish to web is available for everyone. Workspaces can be a way to set up the security. I will allocate a separate post to this, because this post is already more than long enough. You can choose which users of your application can access which report pages. right now in the sample application the user who logged in is an application user which doesn’t have any Power BI account. However the security for this user can be set specifically.
Because with Power BI Embedded users won’t have the Power BI account, so the pricing plan changed from user based to session based. there is a current pricing plan up to 1st of September based on renders, which will be changed to Per Session. free option allows up to 100 sessions per month, and with standard option each session costs only 5 cents. Each session will be the user/browser that has report opened in the application.
Limitations of Power BI Embedded
Power BI Embedded is a new feature, and as expected has some limitations for now. but most of these limitations will be removed in near future I believe. here are biggest limitations so far;
- Source is limited to Azure SQL DB and Azure SQL Data Warehouse through DirectQuery and Live Connection
- Import Data is not supporting scheduled Refresh; That means, you CAN connect to other data sources such as Excel and import the data into Power BI. build the report, publish it to Power BI embedded. you can see the report without any issue in the application. However there is no support for scheduled refresh yet.
- On premises data sources is not supported.
- Because of limitation to Live connection DAX and Data tab is not available.
Stay tuned for next posts about Power BI embedded where I will explain more about code behind of Power BI embedded, Row Level Security with Embedded, and using workspaces more in details.