Introduction

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.

Requirements

1. Windows 7 or later or Windows 2008 R2 or later.

2. Internet Explorer 9 or later.

3. The AdventureWorks Database.

4. SQL Server Installed.

5. Internet access in the machine with Power BI.

6. A Facebook Account.

Note: There is an installer for 32 and 64 bits.

Getting started

You can download the installer from the following path:

• Power BI Desktop installer

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

1. Open the Power BI Desktop:

   
   

   Figure 1. Shortcut to Power BI

2. 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

3. As you can see there are several types of data that Power BI can connect

   
   

   Figure 3. The different Data Sources supported

4. Connect to a SQL Server Database:

   
   

   Figure 4. The SQL Server Database Data Source

5. 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

6. 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

7. 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

8. 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

9. 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

10. We will create a Map. In visualizations, select the MAP icon:

    
    

    Figure 10. Creating a report with maps

11. 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.

1. In order to Connect to Facebook, click the Get Data icon and select More:

   
   

   Figure 12. Get Data option

2. Select the Facebook data source:

   
   

   Figure 13. The Facebook Data Source

3. 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

4. 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

5. Press the Sign in button in Power BI:

   
   

   Figure 16. Sign in option

6. Specify your Facebook email and password associated and press Log In:

   
   

   Figure 17. The login information to Facebook

7. 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

8. 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

9. 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.

Conclusions

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.

References

For more information, refer to these links:

• What is Power BI?
• Create stunning reports and visualizations with Power BI Desktop
• Tips and Tricks for Power BI Map visualizations

• Author
• Recent Posts

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.

He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.

He also helps with translating SQLShack articles to Spanish

View all posts by Daniel Calbimonte

Latest posts by Daniel Calbimonte (see all)

• PostgreSQL tutorial to create a user - November 12, 2023
• PostgreSQL Tutorial for beginners - April 6, 2023
• PSQL stored procedures overview and examples - February 14, 2023