【Pbi报表服务器3】SQL SERVER创建数据库并实现数据每日自动更新

此文章为连载系列：【实战目录】PowerBI 在业务侧的落地应用 【实现动态报表服务器】的内容之一。

此前，我们已经完成了SQL SERVER数据库与其官方管理工具SSMS的安装。但是很多同学对SQL SERVER并不太熟悉，所以这文章主要介绍SSMS的使用，及如何将PowerBI需要的数据同步到数据库中。

注意：本篇教程所有行为均在虚拟机内执行。

一、新建数据库

上一教程的最后，我们已成功通过SSMS打开了SQL SERVER引擎，接下来就新建数据库，用于存放PowerBI所需的数据。

在SSMS界面左侧找到“数据库”，右击弹出的选项中单击“新建数据库”

弹出的菜单中，输入数据库名称，这里就命名为PowerBI其他选项不用管，直接确定即可

如图，点开“数据库”选项后，就可以看到我们新建好的PowerBI数据库

二、从其他数据库中添加数据——建立链接数据库

在实际工作中，我们分析所需的数据往往来自不同系统，例如会员数据来自CRM，订单数据来自中台或POS等。所以需要完成分析工作的话，就需要将数据从其他系统数据库同步到我们自己可操作的数据库中。接下来我们通过“链接服务器”的形式实现它：

在左侧“对象资源管理器”中找到“服务器对象”，右击找到”链接服务器“，右击单击“新建链接服务器”

弹出的框中，“选择页”下点击“常规”输入需要数据源服务器名称，自定义一个就好在服务器类型-其他数据源中：“提供程序”处选择数据源的数据库类型；“数据源”输入数据源服务器地址

接下来在左侧点击“安全性”，设置账号密码

选择“使用此安全上下文建立连接” “远程登录”输入源数据库账号；“使用密码”输入源数据库密码即可

此时，在左侧“对象资源管理器”-“链接服务器” 下就出现了刚设置好的服务器对象，点开可以看到它里面的表

三、从其他数据库中添加数据——从链接服务器中抽取数据

完成添加链接服务器后，就可以从源数据库中提取数据：

源数据库中查询数据

新建查询选择要接受数据的目标数据库，也就是在第一步新建好的'PowerBI' 数据库在语句编辑区输入查询语句：通过Openquery() 函数就可以实现在源数据库中查询数据

SELECT * FROM OPENQUERY([CDP],'SELECT * FROM BUSINESS_ORDER'])

点击执行，也可以按快捷键 F5在右下角就可以看到查询的结果

将源数据库数据插入目标数据库

新建查询选择要接受数据的目标数据库数据库更新操作，也就是在第一步新建好的'PowerBI' 数据库在语句编辑区输入语句：通过 'INTO' 关键字+Openqury函数 就可以实现将源数据库中数据插入到我们的目标数据库中

SELECT * INTO orders FROM OPENQUERY([CDP],'SELECT * FROM BUSINESS_ORDER'])

4. 点击执行，也可以按快捷键 F5

5. 在右下角就可以看到执行的结果

6. 在左侧“对象资源管理器”栏中，PowerBI数据库下，就可以看到我们新建好，且同步了数据的orders表

四、通过 存储过程 实现数据的增量更新

至此，我们已经学会了如何从源数据库中提取需要的数据到我们自己可操作性的目标数据库里。但是，只要业务在继续，数据是动态不断更新的，我们也不可能每天手动去增量更新数据。所以接下来，我们就要实现通过“存储过程+作业”的方法，在SQL SERVER 数据库中实现数据自动同步更新。

此前已经从源数据库中导入了订单数据，先查询一下最新日期，用于之后验证数据是否有更新：

SELECT MAX(BILLDATE) '更新前最大日期' FROM orders

新建查询-编写查询语句【详解见注释】【重要】编写完查询语句后，要“执行”

-- 此步骤声明在PowerBI数据库下执行该存储过程
USE [PowerBI]
GO
-- 此两句话是默认的，照抄即可
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--此存储过程通过链接服务器实现数据增量更新
--并通过关键字 billdate，即订单日期作为增量关键字
--每次执行时先检查是否存在新的订单日期，如果是，就将新订单日期对应的订单同步过来

--形参@afterdate 用于手动更新数据
--即数据更新起始日期：可传入想要从哪一天开始更新
--这里的sp_etl_orders为存储过程的名字，可自定义
ALTER PROCEDURE [dbo].[sp_etl_orders](@afterdate datetime = null)
AS
set nocount on
--声明存储过程的变量
declare @billdate varchar(20), @lastdate datetime
declare @querysql varchar(3000), @execsql varchar(3000)
--判断是否有传入参数，如果没有，就默认从现有订单表中最新的日期开始更新
if @afterdate is null
	begin
		select @lastdate=max(billdate)  from orders
		if @lastdate is null set @lastdate = '2018-01-01'
	end
else
	set @lastdate = @afterdate
-- 将要更新的起始日期传给@billdate
set @billdate=convert(varchar(20), @lastdate, 120)
print(@billdate)
--设置要在源数据库执行的语句，即要传入的数据
set @querysql = 'select * from business_order where billdate > '''''+@billdate+''''''
-- 设置执行语句，将数据插入到PowerBI数据库的orders表中
set @execsql = 'insert into orders select * from openquery([CDP], '''+@querysql+''')'
print @execsql
-- 执行数据
exec(@execsql)

此时，在左侧“对象资源管理器”-数据库-PowerBI-可编程性-存储过程下就可以找到我们写好的存储过程。

此时，再新建查询，写语句执行存储过程

exec sp_etl_orders

如图，结果区显示已完成执行存储过程。

此时，再次查询orders表中最大日期，已与更新前不同，证明更新成功！

五、通过 作业 实现数据的每日自动更新

开启 SQL SERVER代理

作业 功能依赖SQL SERVER代理服务，所以第一步要先将它开启：

打开SQL SERVER资源管理器（如何打开见上一步的SQL SERVER安装教程），点击“SQL Server服务”进行配置右侧找到“SQL Server代理”，右击点击启动即可

新建作业

回到SSMS，左侧“对象资源管理器”中的 SQL SERVER代理此时带着绿色的标记，证明已开启

找到“作业”，右击单击“新建作业...”

配置作业

左侧，选择“常规”输入作业名称，这里起名叫”同步订单数据“类别处选择“数据收集器”

左侧选择“步骤”，此时右侧是空的，需要我们来新建点击“新建”在弹出的框左侧，选择“常规”输入步骤名称，此处叫“执行SP更新数据”选择要执行的数据库“PowerBI"输入执行存储过程语句

exec sp_etl_orders

左侧选择“高级，在右侧”成功时要执行的操作”选择“退出报告成功的作业”，即执行成功后就推出。（这里是因为我只有一个步骤进行更新订单，所以才这么选。）选好后，点确定

接下来，在左侧选择“计划”继续设置此时计划也是空的，需要我们来“新建”在弹出的框中，输入计划名称因为我们要每天自动更新数据，所以计划类型选择“重复执行”“频率”处选择每天执行每天频率处，设置每天执行的时间，这里我选择凌晨3点选择计划开始执行的日期设置完后，会在说明处有个设置结果说明，检查一下没问题就点确定即可

此时已完成新建作业，点确定即可

此时，如果在SQL SERVER代理-作业下看到我们刚新建好的“同步订单数据”作业，恭喜你！实现了数据库自动同步数据！

（编辑：晋中站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!