Overview: In this post we go through the intricacies of creating a SQL Azure storage, mapping it to a Power BI report, getting data into it, designing it, publishing it online and finally embedding to a website. These steps are generally required when starting to create a report and embedding it to a website
For creation of the Power BI report, first we have to select a Data Source for it. It can also be a file, BLOB Storage, Storage account, SQL Azure Database, Access Database, NoSQL Databases like DocumentDB, Microsoft’s HD Insight, etc. All these data stores can be created in an azure subscription.
Gotchas to be aware:
- Power BI Reports can also be created using Streaming Datasets like Azure Stream Analytics, but at the time of writing this article, power BI Embed doesn’t support rendering reports that are created to display streaming data. We shall therefore be going through report creation and embedding using non-streaming data sources like SQL Azure.
- SQL Azure supports creating reports using Power BI Desktop version. It doesn’t support directly connecting to the Online version. So, first let’s download Power BI Desktop to be able to connect to SQL Azure and create a report out of it. Desktop version can be downloaded by going to Downloads section in Power BI (powerbi.microsoft.com) or going to the databases section in it and selecting SQL Azure. See the screenshot below.
Let’s get started.
- Go to the Azure portal (portal.azure.com), sign in with company or work account (if not signed in already), click on plus sign on left menu bar.
- Go the Databases section and select ‘SQL Database’ from the options
- Create SQL Database – Fill unique Database Name, Server, Pricing Tier, etc. for the SQL Database and click on Create button at the bottom.
- Once the database gets created, it will be shown on the Azure Dashboard along with all its properties.
- Data injection, There are many ways to pump the data into the database. I’m skipping the details on this step to keep us stick to the objective of this blog post. (BTW, I got the data from my simulated IoT devices -> IoT Hub -> Stream Analytics -> Database.)
- Open Power Bi Desktop, a blank report layout automatically opens. Click on Get Data -> Azure -> Microsoft Azure SQL Database. Click on Connect.
- Copy the server name of the database from Azure portal, created earlier.
- Use the Database Server Name thus copied and paste it in the popup asking for the Server name while establishing a connection from the report to the database. Click on OK.
- The Database Navigator will come up showing the Database and its associated tables. The data will also be shown alongside.
- Click on the Table from which you want to fetch the fields. Its data, if loaded, will be shown alongside. Click on ‘Load’.
- Once the database is loaded, the table fields will appear on the right side pane of the report under Fields tab
- Firstly, click on the field for which the value is to be plotted. Single bar will appear.
- Now, select the different fields as per the type of report being plotted.
In this session we are taking an example of an Area report, so we require the Latitude and Longitude. Drag and drop the lat, long fields to the Latitude, Longitude. Click on both fields and set them to ‘Don’t Summarize’ so that Power BI does not put an aggregate on the latitude & longitude fields.
Also click on Size and take an aggregate, possibly Average to represent the average of the values for a particular latitude-longitude pair.
- Currently we can see that all bubbles are coming of same color irrespective of their sizes (average values). To show a distinction between them, drag the same field which is in Size column from Fields tab and put it in ‘Color Saturation’. Click on it and take its Average.
- Click on the roller icon, click on Data Colors and set Diverging to ‘On’, give different colors to the Minimum, Center and Maximum levels. Also set values for the 3 levels against which they will be compared.
- Save the report in your hard drive. It will have a .pbix extension.
- For the report to be available online, we need to publish it to the online service. Click on Publish icon at the top to publish the report to the Power Bi Online service.
- The report will take a few seconds to publish to the online service.
- Once report is published, it will show a success message.
- Now open Power BI online (powerbi.microsoft.com) to access the report. We can view our report in the Reports section and it’s dataset in the Dataset section.
- To view the report in a dashboard, we have to pin it to an existing one or create a new one. Click on the small pin at the top right of the report and choose an existing Dashboard from the drop-down and click on Pin.
- Go the dashboard to which the report has been pinned, click on the top right of the report and the below visual will come up with the original name of the report. Click on the Pen icon to Edit the report Tile.
- By default reports come with the titles set as the field names. Change the Title/Subtitle of the report as required.
- To embed the code, we need to publish the report to web. For this open the report in Power BI Online. Click on File -> Publish to Web.
- Click on ‘Create Embed Code’ in the popup which open up.
- Click on ‘Publish’ to fetch the embedded code.
Fig 19: Publish to get embedded code
- A success message with embedded code having email link and web link will appear. We can use these links to embed our report in a website. We can also adjust the report size to show the report in a size that will fit in our website.
- Copy the iframe link from the above and paste it to your website (HTML). You can change height-width of iframe to suit your website
- Open the HTML page in your browser. The report should open in your page as per the size adjustments.
Conclusion: After going through the series of steps, we can see a definite mapping between SQL Azure and Power BI which can be managed by the same Azure subscription. Also, reports created using SQL Azure as a data source are not exactly real-time but can hold thousands of records which keep refreshing after a certain amount of time. So, if we don’t have requirement for real-time data analytics but want to have a large pool of data, we can very well go in for SQL Azure and use it in our Power BI reports. Embedding the reports and making them live isn’t too much of a hassle and we can very well embed multiple meaningful reports to our website dashboard to make it look more comprehensive and elegant.