下载掌阅APP，畅读海量书库
立即打开
使用Power Query进行SQL Server数据集成有几种不同的方式，在Excel实现的SQL Server 集成和Power BI实现的SQL Server集成在功能上也有差别。Power BI在SQL Server的集成方面可以实现实时的数据查询，这一点是使用Excel无法实现的。在Excel和Power BI中进行数据集成过程中的共同点如下。
■ 完整的数据导入：导入的过程中完成所有可获取的数据导入。
■ 基于T-SQL语句的数据导入：在导入过程中筛选需要的数据导入。
安装Power BI后就集成了SQL Server 连接工具，不再需要安装额外的连接工具完成Power BI到SQL Server的连接。
在SQL Server 进行数据连接的过程中，有时会碰到这样的数据场景。
■ 需要进行数据处理的量不是很多，但是可以有不同的处理方式。
■ 从Power BI到SQL Server的带宽不足，不足以支撑从数据库到本地的数据。
在这样的场景下，我们是先把所有数据导入再进行处理，还是在连接的时候进行数据筛选呢？有些人认为应该获取完整的数据后再进行筛选。从性能方面来说，这种方案不可取，因为会消耗非常多的网络资源，同时也将耗费本地的CPU和内存等计算资源。如果只需获取数据表中的前十行数据，最好的处理方式是在SQL 语句层将数据进行筛选后再获取数据。如果我们把所有的几十万条或几百万条数据全部都下载到Excel或Power BI的客户端，这将极大地消耗网络资源对数据进行获取和保存，同时也消耗了本地的计算资源来进行保存和计算。
在对SQL Server部分数据获取的过程中，基于Power Query的T-SQL语句功能，可以帮助我们更好地降低网络资源的消耗，同时也会大大节省本地资源。接下来以一个典型的案例分析在什么场景下应用全部数据获取和部分数据获取。案例以下面的参数来定义当前数据库连接的必选参数。
■ 数据库服务器的IP地址：65.52.184.126。
■ 数据库的当前数据库名称：Mydb。
■ 当前数据表名称：Mydb。
数据库的当前表格中有1000条数据，每行包含5个字段用于数据的读取。
■ Record Date：记录写入时间。
■ Temp：温度读数。
■ Light：光感读数。
■ Humidity：湿度读数。
■ Electricity：电流读数。
在实际的数据处理和接收过程中，我们既可以实现较少的数据获取，也可以获取所有的数据，差别在于我们进行SQL Server的获取过程中是否写入相关的T-SQL 语句，如果只是需要部分的数据，可以在这里使用T-SQL来实现。
默认情况下，Power Query支持的数据获取方式是全部数据获取，我们只需要填写数据库名称和数据连接服务器，即可实现所有数据的获取，不需要设置其他的参数。图2.54所示为获取所有数据连接的参数设置。
图2.54 Power Query连接参数设定
在获取全部数据模式下，以下参数为必选参数，数据库参数和SQL语句为可选参数。
■ 服务器：所有的数据库都有相应的宿主服务器，宿主服务器可以是NetBIOS名称、服务器域名或IP地址。
■ 数据库：Power Query 连接的数据库的实例，在当前模式下为可选参数。
■ 数据库凭据：使用相应的凭据进行数据库的连接，凭据包含了连接所使用的用户名和密码（凭据窗口将会在单击“确定”按钮后显示）。
如果希望获取的是特定类型的数据，比较好的方式是在SQL Server中执行相应的T-SQL语句来返回需要的结果。例如，我们希望获取当前表中前10行数据，可以通过T-SQL语句进行初步的数据筛选，这能够大大降低网络和CPU资源消耗。基于T-SQL的数据筛选和全数据获取只有一个差别，就是是否在SQL语句中写入筛选的T-SQL语句，如果写入了T-SQL语句，则以筛选模式工作。下面T-SQL语句是获取表数据前10行的筛选。
在SQL Server 的T-SQL语句中进行数据的获取和筛选，下面参数都是必选参数。
■ 服务器：所有的数据库都有相应的宿主服务器，宿主服务器可以是Net BIOS名称、服务器域名或IP地址。
■ 数据库：Power Query连接的数据库的实例，在数据筛选模式下，连接的数据库是必选参数。
■ 数据库凭据：使用相应的凭据进行数据库的连接，凭据包含了连接所使用的用户名和密码。
■ SQL语句：在进行数据筛选的过程中，需要写入进行筛选的SQL语句，在筛选模式下这个内容也是必选的。如图2.55所示的框中内容为筛选模式下的必选参数。
图2.55 筛选模式下的必选参数
在Excel中通过Power Query连接数据库实现数据的访问和集成，先来看下如何进行全部数据的获取，打开Power Query编辑器，输入连接的服务器和数据库（在获取全部数据后可选），为了方便，这里写入了连接的数据库，图2.56所示为Excel连接SQL Server数据库的参数设定，这里只有数据库的服务器名称为必选。
图2.56 Excel 连接SQL Server数据库的配置界面
如果是第一次使用Excel 连接SQL Server，它会弹出需要进行设置的连接凭据，凭据按照需要进行设置。第一次完成设置之后，后期就不再用设置了。图2.57所示为具体的凭据设置界面，这里通过数据库账户凭据连接SQL Server。
图2.57 Excel连接SQL Server的凭据设置
完成凭据和服务器的设置之后，就进入数据库的连接界面。SQL Server 数据库拥有多张表，按照需求选择需要的表数据内容。图2.58所示为数据表选择界面，如果在Excel中希望导入多个表格，必须选中“选择多项”复选框进行多个表格的选择，然后单击“转换数据”按钮进入Power Query编辑器界面。
在Power Query编辑器界面中，可以实现数据的集成、合并、拆分和清洗操作，如图2.59所示，可以根据实际需要进行相应的数据操作。
图2.58 数据库表选择区域
图2.59 Power Query编辑器界面
在完成了数据的清洗和集成之后，Excel 支持将最终的数据实现多向输出，通过“关闭并上载到”按钮的下拉菜单可以实现数据保存和再处理。
在进行数据导入处理之前，我们可能需要进行数据的筛选，而这个筛选步骤不是由本地Excel的Power Query完成，而是在进行数据连接的时候由T-SQL语句来完成相应的数据筛选，那么怎么来实现相应的T-SQL的数据筛选呢？在连接的过程中需要填写相应的T-SQL语句进行筛选，这里使用Excel进行数据筛选连接方式的必须参数如下。
■ 服务器：连接数据库的宿主服务器。
■ 数据库：数据表所在的数据库。
■ SQL语句：提取数据的T-SQL语句。
图2.60显示了利用T-SQL进行数据筛选模式下需要的参数内容，图中所有的项目都是必填项目。
图2.60 Excel 通过T-SQL进行数据筛选
如果是第一次进行连接，则需要填写相关的数据连接凭据，设置完成后，可以直接单击“连接”按钮进行筛选数据的连接，图2.61所示为连接后的显示界面，通常导入的数据都会存在一定的问题，需要进行进一步清洗，这时需要单击“转换数据”按钮进入Power Query数据清洗重构界面。
图2.61 通过T-SQL筛选后获得的数据
数据载入Power Query界面之后，可以通过Power Query和M语言进行数据的清洗与重构，图2.62所示为Power Query编辑器界面的操作。
图2.62 Power Query实现数据的重构与清洗
依据数据处理之后的需求，可以保存在Excel中，也可以依据实际需求进行其他处理，如加载到Excel文件中、加载为连接或加载为数据透视表等。
在Power BI进行数据导入的过程中，存在两种不同的方式来进行数据库数据的获取，这两种方式使用的场景有很大的不同。
■ 数据导入方式： 所有的数据以缓存方式导入Power BI的缓存中，缓存将依据Power BI设置的策略进行刷新。使用导入的方式获取数据有一定的周期性，但是针对数据量较大的情况，采用导入的方式进行数据获取是比较好的解决方案，因为通过导入方式进行数据导入支持增量数据刷新。
■ Direct Query数据直接查询方式： 所有的数据不保存在缓存中，直接通过Direct Query查询相关的数据。Direct Query方式适用于数据量较少，更新比较频繁的数据。
在Power BI与SQL Server 进行连接的过程中有一个功能是目前Excel所没有的，就是Power BI Desktop支持秒级数据刷新功能，即通过设置即时刷新来实时获取来自数据库的数据。
Power BI与Excel在数据导入方式上略有不同，Power BI以导入的方式进行数据集成，支持完整的数据导入和筛选两种方式，这一点和Excel实现SQL Server 数据导入没有太大的差别。下面通过T-SQL 数据筛选方式进行数据的导入，如图2.63所示的参数都是必选参数。
图2.63 Power BI 数据导入参数设置
■ 服务器：提供连接服务的SQL Server 服务器。
■ 数据库：以筛选方式获取数据。
■ 数据库连接模式：数据库进行连接的方式。
■ SQL 语句：以筛选方式获取数据。
完成后就可以单击“确定”按钮进入Power BI的数据库数据获取界面，图2.64所示为数据库数据获取界面，由于当前界面使用了T-SQL语句进行筛选，这里获取的是经过T-SQL筛选后的数据结果。通常获取相关的数据之后都需要进行数据格式的再修改，因此大多数情况下都单击“转换数据”按钮实现数据的清洗和重构。
图2.64 获取数据最终界面
选择转换数据之后就进入Power Query编辑器的界面，在其中可以实现数据列的新建、删除和计算等操作。
在完成数据的清洗和重构之后，数据将保存在Power BI的缓存中，在Power BI主界面中可以通过选择“数据”选项卡签实现数据的再处理，图2.65所示为数据表处理界面。
图2.65 Power BI数据表处理界面
如果进行数据库连接时使用导入的方式会产生一个比较大的问题，就是随着导入的数据越来越多，Power BI的文件会越来越大。
在实际中可能会出现这样的业务场景，需要及时了解目前业务的运行状态。例如，我们针对当前的各个区域部署了温度传感器，希望非常及时地了解目前酒店各个区域的温度情况，如果温度低于或高于一个值都要及时调整。在这类业务场景中，使用传感器收集到的数据要求及时性很高，甚至可能达到秒级。这时数据就是以秒级展现，类似于图2.66中所示的实时看板，所有的数据都能非常及时地反馈到信息看板中，Power BI中数据查询采用Direct Query模式可以帮助我们完成这个任务。
图2.66 Power BI的实时看板
Power BI Desktop版本支持最低一秒间隔的数据更新，而目前数据的秒级刷新只支持一些特定的数据源，而SQL Server恰好是支持的数据源中的一种。如果希望在Power BI中支持秒级数据刷新，以下条件缺一不可。
■ 数据源是SQL Server，目前不支持其他版本的数据库。
■ 在连接SQL Server时使用Direct Query方式。
■ 在Power BI中开启数据刷新，并且设置刷新方式和间隔。
为让读者理解如何在Power BI实现秒级数据刷新，下面将完成这样的即时刷新面板构建，这里将数据间隔刷新设置为5秒。
首先我们需要按照一定的时间间隔往数据库写入数据，数据的写入环节有以下两个小要求。
■ 本地部署SQL Server 模块，需要在Power Shell环境中执行Install-Module sqlserver命令完成SQL模块部署。
■ 执行数据库插入脚本，完成了模块的部署和代码执行之后，可以看到如图2.67所示的结果，表示5秒钟插入测试数据的步骤已经成功。
图2.67 Power Shell进行数据插入
下面这部分代码为使用Power Shell脚本每隔5秒钟在SQL Server 数据库插入一条数据记录，将文件保存为后缀为ps1的文件。
接下来就需要使用Power BI连接数据库来实现数据内容的实时获取，这里必须使用Direct Query方式来实现数据的集成和查询。打开Power BI Desktop，选择从数据库SQL Server获取相关的数据。Direct Query应用场景和数据导入模式的使用场景是完全不同的，通常来说，Direct Query不进行大型的数据集查询，如果针对100万个数据实现实时查询，这是非常不可靠并且低效的。这里查询到的数据是数据库最新的30个值。T-SQL语句和基本设置如图2.68所示，注意这里的设置我们选择的连接方式是Direct Query。
图2.68 SQL Server的Direct Query的连接设置
单击“确定”按钮后获取数据，进入数据获取的预览界面。当连接是以Direct Query进行时，我们可以选择直接加载或转换数据。在Direct Query模式下单击“转换数据”按钮的意义不大，因为在Direct Query模式下不支持数据格式的修改。
需要注意的是，我们利用Direct Query模式进行数据获取，如果这时进行任何的数据格式修改，都会有需要将数据从查询模式修改为导入模式的提示，利用Direct Query获取的数据不支持任何数据类型的修改。
通常来说，实时看板与时间相关性比较高，在当前案例中选择折线图，这里将时间列作为横轴，纵轴为我们的数据轴，图2.69所示为最终的数据内容设定。
图2.69 折线图的横轴与纵轴选择
完成数据的查询之后，接下来就需要启用数据看板的即时刷新了，如何启用即时看板呢？我们选中页面空白的地方，然后按照以下操作启用页面自动刷新功能和设置刷新相关内容，如图2.70所示。
①单击页面空白处，确定没有选定任何视觉对象。
②选定格式设置，开启页面刷新。
③设定刷新时间间隔，间隔刷新时间最低为一秒。
图2.70 秒级刷新参数设置
完成这些操作的设定后，所有的数据就可以实现秒级刷新了。