【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]
-- 此两句话是默认的，照抄即可
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
--此存储过程通过链接服务器实现数据增量更新
--并通过关键字 billdate，即订单日期作为增量关键字
--每次执行时先检查是否存在新的订单日期，如果是，就将新订单日期对应的订单同步过来
--形参@afterdate 用于手动更新数据
--即数据更新起始日期：可传入想要从哪一天开始更新
--这里的sp_etl_orders为存储过程的名字，可自定义
ALTER PROCEDURE [dbo].[sp_etl_orders](@afterdate datetime = null)
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'
	set @lastdate = @afterdate
-- 将要更新的起始日期传给@billdate
set @billdate=convert(varchar(20), @lastdate, 120)
print(@billdate)
--设置要在源数据库执行的语句，即要传入的数据
set @querysql = 'select * from business_order where billdate > '''''+@billdate+''''''

一、新建数据库

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

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

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

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