Power BI is an extremely popular new Microsoft Tool to create reports. In a previous article, we talked about Power BI Cloud based services. In that article, we connected from a SQL Azure Data Warehouse database to the Power BI Cloud services. In this new article, we will work with the Power BI Desktop Tool from zero. The Power BI Desktop is a reporting software that can be installed in your Windows machine to create reports locally and connect to different data sources like Azure, SharePoint, Active Directory, Exchange, Facebook, Google Analytics and many other data sources. We will create Reports from a SQL Server database and finally we will extract information from Facebook. With Power BI, you can easily access to maps at street level around the world.
Windows 7 or later or Windows 2008 R2 or later.
Internet Explorer 9 or later.
The AdventureWorks Database.
SQL Server Installed.
Internet access in the machine with Power BI.
A Facebook Account.
Note: There is an installer for 32 and 64 bits.
You can download the installer from the following path:
The installer is an 84 MB file and the installation takes 1 minute. It is very easy to install.
Once installed, we will create a Power BI report that connects to SQL Server:
Creating a Power BI Report from SQL Server
Open the Power BI Desktop:
Figure 1. Shortcut to Power BI
What I really like about Power BI is that there are many forums, documentation, YouTube tutorials, blogs and material about Power BI. Select the Get Data option to obtain data from a specified data source:
Figure 2. The option to Get Data
As you can see there are several types of data that Power BI can connect
Figure 3. The different Data Sources supported
Connect to a SQL Server Database:
Figure 4. The SQL Server Database Data Source
In the connection of Power BI Desktop, select the SQL Server Name and the database name. In this example, we will connect to the local SQL Server and the Adventureworks 2014 database. You can also run Direct Queries and retrieve the query results. This option is very useful when you need to run specific and customized queries. There are also advanced options to specify the timeout if the query takes long time to execute and an option the specify the SQL Commands:
Figure 5. Database connection options
Select the credentials to connect to the AdventureWorks database. Make sure that the account has privileges in the database. In this example, we are using the credentials of the Windows user. You can apply the setting at Server or database level. In this example, we will apply the credentials at the AdventureWorks2014 level:
Figure 6. Connection credentials to the AdventureWorks database
You will receive a message about the connection in Power BI Desktop. It does not support encrypted connection. Click OK to accept the unencrypted connection:
Figure 7. Encryption support message in Power BI
Select a table. In this example, we will use the Sales.SalesTerritory table from the AdventureWorks Database. You can import several tables at the same time, but in this example, we will extract just one:
Figure 8. The SalesTerritory Table
You will have the following PowerBI UI. If you read our article about Power BI as a cloud service, you will notice that the UI is similar:
Figure 9. The Power BI UI
We will create a Map. In visualizations, select the MAP icon:
Figure 10. Creating a report with maps
Check the CountryRegion and SalesLastYear columns. You will be able to see a Graphical report in a Map. The Light Blue circles shows the sales per region, if the circle is bigger, the sales are bigger:
Figure 11. The Sales per Region
Power BI, can easily graph geographical countries, cities, etc. It integrates to Bing Maps and it can work at street level of work with specific satellite coordinates. As you can see, Power BI can easily connect to SQL Server.
What about other data sources?
Let’s try to connect to Facebook. Is it possible?
Connect to Facebook with Power BI
What is amazing about Power BI is the number of sources that it can connect. In this new demo, we will extract Facebook information using Power BI.
In order to Connect to Facebook, click the Get Data icon and select More:
Figure 12. Get Data option
Select the Facebook data source:
Figure 13. The Facebook Data Source
You will receive a warning about the functionality. Facebook may change and some features may change in the future and it is possible that some functionality will not work in the same way or that the Power BI may require updates to work:
Figure 14. Power BI Warning message
In the object, specify Me to obtain the personal information. You can optionally specify the Username or Object ID. In connection, we will get the Likes information. You can also get information about posts, videos, likes and more:
Figure 15. Facebook object to import
Press the Sign in button in Power BI:
Figure 16. Sign in option
Specify your Facebook email and password associated and press Log In:
Figure 17. The login information to Facebook
You will receive a message that you are sending your soul and all your private information to Power BI 🙂
Figure 18. Confirmation message of Power BI to receive your private information
The last message will ask for permissions to access to your Facebook page and App insights:
Figure 19. Permissions to access to Page and App
A table will be created in Power BI Desktop with the name, category, ID, creation time:
Figure 20. The information about Facebook likes
You can also obtain information about, friends, videos, posts and more. As you can see, Power BI can connect to Relational and NoSQL data.
Power BI is an extremely powerful tool that can be connected to several data sources. Not only relational databases, but also NoSQL databases. In this demonstration, we create reports from SQL Server using Maps and then we worked connected to Facebook to extract information.
For more information, refer to these links: