使用Power BI Desktop连接到SQL Server和Facebook

介绍 (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.

Power BI是一种非常流行的新Microsoft工具，用于创建报告。 在上一篇文章中 ，我们讨论了基于Power BI Cloud的服务。 在该文章中，我们从SQL Azure数据仓库数据库连接到Power BI Cloud服务。 在这篇新文章中，我们将从零开始使用Power BI Desktop工具。 Power BI Desktop是一种报告软件，可以安装在Windows计算机中，以在本地创建报告并连接到不同的数据源，例如Azure，SharePoint，Active Directory，Exchange，Facebook，Google Analytics和其他许多数据源。 我们将从SQL Server数据库创建报告，最后从Facebook中提取信息。 使用Power BI，您可以轻松访问世界各地街道上的地图。

要求 (Requirements)

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

   Windows 7或更高版本或Windows 2008 R2或更高版本。

2. Internet Explorer 9 or later.

   Internet Explorer 9或更高版本。

3. The AdventureWorks Database.

   AdventureWorks数据库。

4. SQL Server Installed.

   已安装SQL Server。

5. Internet access in the machine with Power BI.

   使用Power BI在计算机中访问Internet。

6. A Facebook Account.

   一个Facebook帐户。

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

注意：有一个32位和64位安装程序。

入门 (Getting started)

You can download the installer from the following path:

您可以从以下路径下载安装程序：

• Power BI Desktop installer

  Power BI Desktop安装程序

The installer is an 84 MB file and the installation takes 1 minute. It is very easy to install.

安装程序是一个84 MB的文件，安装需要1分钟。 这很容易安装。

Once installed, we will create a Power BI report that connects to SQL Server:

安装后，我们将创建一个连接到SQL Server的Power BI报表：

从SQL Server创建Power BI报表 (Creating a Power BI Report from SQL Server)

1. Open the Power BI Desktop:

   
   
   Figure 1. Shortcut to Power BI

   打开Power BI桌面：

   
   

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

   我真正喜欢Power BI的地方是有许多关于Power BI的论坛，文档，YouTube教程，博客和材料。 选择“获取数据”选项以从指定的数据源获取数据：

   
   

   图2.获取数据的选项

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

   
   
   Figure 3. The different Data Sources supported

   如您所见，Power BI可以连接多种类型的数据

   
   

   图3.支持的不同数据源

4. Connect to a SQL Server Database:

   
   
   Figure 4. The SQL Server Database Data Source

   连接到SQL Server数据库：

   
   

   图4. SQL Server数据库数据源

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

   在Power BI Desktop的连接中，选择“ SQL Server名称”和数据库名称。 在此示例中，我们将连接到本地SQL Server和Adventureworks 2014数据库。 您还可以运行直接查询并检索查询结果。 当您需要运行特定的和自定义的查询时，此选项非常有用。 如果查询需要很长时间来执行，还有一些高级选项可以指定超时，还有一些选项可以指定SQL命令：

   
   

   图5.数据库连接选项

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

   选择凭据以连接到AdventureWorks数据库。 确保该帐户在数据库中具有特权。 在此示例中，我们使用Windows用户的凭据。 您可以在服务器或数据库级别应用设置。 在此示例中，我们将在AdventureWorks2014级别应用凭据：

   
   

   图6. AdventureWorks数据库的连接凭证

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

   您将收到有关Power BI Desktop中的连接的消息。 它不支持加密连接。 单击“确定”接受未加密的连接：

   
   

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

   选择一个表。 在此示例中，我们将使用AdventureWorks数据库中的Sales.SalesTerritory表。 您可以同时导入多个表，但是在此示例中，我们将仅提取其中一个：

   
   

   图8. SalesTerritory表

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

   您将具有以下PowerBI UI。 如果您阅读了有关将Power BI作为云服务的文章，您将注意到UI相似：

   
   

   图9. Power BI UI

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

    
    
    
    Figure 10. Creating a report with maps

    我们将创建一个地图。 在可视化中，选择MAP图标：

    
    

    图10.使用地图创建报告

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

    检查CountryRegion和SalesLastYear列。 您将能够在地图中看到图形报告。 浅蓝色圆圈显示每个区域的销售额，如果圆圈越大，则销售额越大：

    
    

    图11.每个地区的销售额

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.

Power BI可以轻松绘制地理国家，城市等的图形。它与Bing Maps集成在一起，并且可以在具有特定卫星坐标的街道级工作。 如您所见，Power BI可以轻松连接到SQL Server。

What about other data sources?

那么其他数据源呢？

Let’s try to connect to Facebook. Is it possible?

让我们尝试连接到Facebook。 可能吗？

使用Power BI连接到Facebook (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.

Power BI的惊人之处在于它可以连接的源数量。 在这个新的演示中，我们将使用Power BI提取Facebook信息。

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

   
   
   Figure 12. Get Data option

   为了连接到Facebook，请单击“获取数据”图标，然后选择“更多”：

   
   

   图12.获取数据选项

2. Select the Facebook data source:

   
   
   Figure 13. The Facebook Data Source

   选择Facebook数据源：

   
   

   图13. Facebook数据源

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

   您将收到有关该功能的警告。 Facebook可能会更改，将来某些功能可能会更改，并且某些功能可能无法以相同的方式起作用，或者Power BI可能需要更新才能起作用：

   
   

   图14. Power BI警告消息

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

   在对象中，指定Me以获取个人信息。 您可以选择指定用户名或对象ID。 与此相关，我们将获得“点赞”信息。 您还可以获取有关帖子，视频，喜欢和更多内容的信息：

   
   

   图15.导入的Facebook对象

5. Press the Sign in button in Power BI:

   
   
   Figure 16. Sign in option

   在Power BI中按“登录”按钮：

   
   

   图16.登录选项

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

   
   
   Figure 17. The login information to Facebook

   指定您关联的Facebook电子邮件和密码，然后按登录：

   
   

   图17.登录到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

   您将收到一条消息，表示您正在将自己的灵魂和所有私人信息发送给Power BI????

   
   图18. Power BI接收您的私人信息的确认消息

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

   最后一条消息将要求您访问Facebook页面和App见解的权限：

   
   

   图19.访问页面和应用程序的权限

9. A table will be created in Power BI Desktop with the name, category, ID, creation time:

   
   
   
   Figure 20. The information about Facebook likes

   将在Power BI Desktop中创建一个表，其名称，类别，ID，创建时间为：

   
   

   图20.有关Facebook喜欢的信息

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可以连接到Relational和NoSQL数据。

结论 (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.

Power BI是一种非常强大的工具，可以连接到多个数据源。 不仅关系数据库，而且NoSQL数据库。 在此演示中，我们使用Maps从SQL Server创建报表，然后我们连接到Facebook以提取信息。

参考资料 (References)

For more information, refer to these links:

有关更多信息，请参考以下链接：

• What is Power BI?
  什么是Power BI？
• Create stunning reports and visualizations with Power BI Desktop
  使用Power BI Desktop创建出色的报告和可视化效果
• Tips and Tricks for Power BI Map visualizations
  Power BI Map可视化的技巧和窍门

翻译自: https://www.sqlshack.com/using-power-bi-desktop-connect-sql-server-facebook/