Introduction
Microsoft Power BI is a very popular Reporting Tool. There is a Desktop version and a Web version. In this article, we will show how to connect to a Azure SQL database and generate reports in Power BI in the Web version. We will finally connect Power BI to Google Analytics (a very popular tool to analyze web statistics).
Requirements
1. A Power BI Account (it is free per 60 days and they renewed my account per 60 extra days).
2. An Azure Account.
3. The Google Analytics with some data collected (I used a free blog account in Google Analytics). For more information about adding Google Analytics to a blog or website, go to Set up Analytic tracking.
Getting Started
1. We will create a new Azure SQL database first. Go the the Azure Portal and press SQL databases and press New. You will need to specify a database name, the subscription (it is selected by default), the group where you want to store. In the select source option, select sample and select the AdventureWorks Database:
2. In the server section create a new one. You will need a login and a password. In the password tier, I recommend you to use the cheapest to test (S0 Standard):
3. Once the Azure SQL Database is created, go to the Power BI website, sign in and in the options, select the Get Data button:
4. We will connect to our Azure SQL. Go to Databases and click the get button:
5. Click the Azure SQL Database option:
6. Press the Connect button:
7. Go the Azure Portal, click on the Database created and check the Server Name.
8. Copy the server name in Power BI and specify the Database name used in the step 1:
9. Specify the name and password used in step 2:
10. Click the Azure SQL Database link:
11. Select the OrderQty column of the SalesOrderDetail and the clustered column chart:
12. Select the name column of the Product and drag it to the Axis property in the report:
13. Select the treemap and you can view the order quantity per product:
14. For advanced reports, you can select the Get Data option:
15. The Samples link contains nice report examples:
16. I love these examples, let's try the Opportunity Sample:
17. Press Connect:
18. You can now play and check the interactive reports. There are nice examples to learn:
19. Now we are going to connect to Google Analytics. This is a very popular Google tool to get information about your blog or website. Go to step 14 and then press the Get button in the Services section:
20. The number of services that you can connect to is amazing! Here you have some examples of Services available:
- Bing
- Google Analytics
- Adobe Analytics
- Microsoft Dynamics
- SQL Sentry
- Webtrends
- Mandrill
21. In this example, we will connect to Google Analytics to get some reports about my blog:
22. You will need to choose the connection method (by default is the open Standard Authorization 2):
23. You will need to provide the credentials, allow access and specify the URL of your site:
24. If everything is OK, you will receive a success message. Press the Go to dashboard option.
25. Click on the report created:
26. Hits, bounces, duration of the sessions. Select the system usage page. You can now see maps with the regions with more visitors (Mexico and Spain). You can also notice that most of the user use Google Chrome as a browser and that the people usually access from a Desktop PC:
Conclusion
As you can see, you can generate reports in the Web Power BI from different sources like Azure SQL Databases, csv files, Google Analytics and more.
Power BI is a very straightforward and extremely powerful tool.
