USE ERP_DW
GO/*
功能说明: 创建业务库【销售表】
修改说明: Create by LY on 2011-09-08
*/
IF EXISTS (SELECT 1
FROM SYSOBJECTS
WHERE id = OBJECT_ID('COM_EXE_SaleCar')
AND type = 'U')
DROP TABLE COM_EXE_SaleCar
GO
CREATE TABLE [dbo].[COM_EXE_SaleCar]
(
SaleCode varchar(20) not null,
SaleName varchar(50) null,
BusinessDate datetime null,
constraint PK_COM__EXE_SaleCar primary key (SaleCode)
);
GO
INSERT INTO [COM_EXE_SaleCar]
SELECT '001','代理商','2011-9-6'
UNION ALL
SELECT '002','分公司','2011-9-7'
UNION ALL
SELECT '003','商店','2011-9-8'
GO/*
功能说明: 创建数据仓库【销售表】
修改说明: Create by LY on 2011-09-08
*/IF EXISTS (SELECT 1
FROM SYSOBJECTS
WHERE id = OBJECT_ID('Fact_SaleCar')
AND type = 'U')
DROP TABLE Fact_SaleCar
GO
CREATE TABLE [dbo].[Fact_SaleCar]
(
SaleCode varchar(20) not null,
SaleName varchar(50) null,
BusinessDate datetime null,
constraint PK_Fact_SaleCar primary key (SaleCode)
);
GOINSERT INTO [Fact_SaleCar]
SELECT '001','代理商','2011-9-6'
UNION ALL
SELECT '002','分公司','2011-9-7'
GO
SELECT * FROM [COM_EXE_SaleCar]
SELECT * FROM [Fact_SaleCar]/*
实现的功能:通过增量抽取每天把 【COM_EXE_SaleCar】的数据抽取到 【Fact_SaleCar】,并且如果
【COM_EXE_SaleCar】表的历史数据【除了当天的数据】有改动的话,【Fact_SaleCar】也要跟着改动.
目前【COM_EXE_SaleCar】表的数据:
SaleCode SaleName BusinessDate
-------------------- -------------------------------------------------- -----------------------
001 代理商 2011-09-06 00:00:00.000
002 分公司 2011-09-07 00:00:00.000
003 商店 2011-09-08 00:00:00.000
目前【Fact_SaleCar】表的数据:
SaleCode SaleName BusinessDate
-------------------- -------------------------------------------------- -----------------------
001 代理商 2011-09-06 00:00:00.000
002 分公司 2011-09-07 00:00:00.000
*/
UPDATE COM_EXE_SaleCar
SET SaleName='门店'
WHERE SaleCode='001'
GO
/*
目前【COM_EXE_SaleCar】表的数据:
SaleCode SaleName BusinessDate
-------------------- -------------------------------------------------- -----------------------
001 门店 2011-09-06 00:00:00.000
002 分公司 2011-09-07 00:00:00.000
003 商店 2011-09-08 00:00:00.000
如何通过增量抽取,把 【COM_EXE_SaleCar】的数据抽取到 【Fact_SaleCar】,
并且条件是当天.想要的结果是.
SaleCode SaleName BusinessDate
-------------------- -------------------------------------------------- -----------------------
001 门店 2011-09-06 00:00:00.000
002 分公司 2011-09-07 00:00:00.000
003 商店 2011-09-08 00:00:00.000
*/
INSERT INTO Fact_SaleCar
SELECT SALECODE,SALENAME,BusinessDate
FROM COM_EXE_SaleCar
WHERE BusinessDate=CONVERT(DATE,GETDATE())
GO
/*
而通过目前的增量抽取语句不能更改以前的历史,比如,代理商改成门店了,如何让Fact_SaleCar表的数据也跟着改动
【Fact_SaleCar】表的数据:
SaleCode SaleName BusinessDate
-------------------- -------------------------------------------------- -----------------------
001 代理商 2011-09-06 00:00:00.000
002 分公司 2011-09-07 00:00:00.000
003 商店 2011-09-08 00:00:00.000 【Fact_SaleCar】想要的结果如下
SaleCode SaleName BusinessDate
-------------------- -------------------------------------------------- -----------------------
001 门店 2011-09-06 00:00:00.000
002 分公司 2011-09-07 00:00:00.000
003 商店 2011-09-08 00:00:00.000*/
SELECT * FROM Fact_SaleCar
insert,UPDATE 触发器create tigger test_update on COM_EXE_SaleCar
for insert,update
asif update(SaleName)
insert into
Fact_SaleCar
SELECT
SALECODE,SALENAME,BusinessDate
FROM
inserted
WHERE
BusinessDate=CONVERT(DATE,GETDATE())
select * from [COM_EXE_SaleCar] a
where BusinessDate=convert(varchar(10),getdate(),120) and --getdate()带上了时分秒,直接与当天0点比是不等的.
not exists(select 1 from [Fact_SaleCar] where SaleCode=a.SaleCode)
select * from [Fact_SaleCar]
/*
SaleCode SaleName BusinessDate
-------------------- -------------------------------------------------- -----------------------
001 代理商 2011-09-06 00:00:00.000
002 分公司 2011-09-07 00:00:00.000
003 商店 2011-09-08 00:00:00.000(3 行受影响)*/
改动要有记录,比如有改变日期,编号,这样可以在update语句中直接根据改动记录去更新,否则只能全表对比了.
如果遇到晚上要处理的时候停电,你咋办?在程序里已经写上了 getdate 又不能动,难不成去动系统日期?而且,用户并不知道有这些关节.
这个语句在哪?不需要实时性。。
很少,不是不会.如果出现,就是个BUG.
另建一个新表,只有两列,SaleCode,BusinessDate,更新触发器在对[COM_EXE_SaleCar]表更新的时候,向这个表插入SaleCode和当前日期(这个不会引起很长的延时),然后你的定时作业可以根据这个表中的日期和编号对更新内容进行抽取,从而对【Fact_SaleCar】表进行更新.
有几个方法,
1、如果目标和源的表结构完全一致,可以用CDC,效果也很好。
2、如果数据量不是很大,可以考虑使用Service Broker,把新数据(包括Update)更新到目标;
3、从长远看,fact 数据很少使用上面方法Load数据,所以Source端还是应该,提供一个增量提取的信息,比如UpdateDate(初始时和BusinessDate一致,有变更则是变更时期),增量提取使用这个字段来控制。另外有个建议,如果搂主真的想做个DW/BI,fact table这样设计可能真的不行。
select *
into #temp_result
from COM_EXE_SaleCar
except
select *
from Fact_SaleCar;create index on #temp_result(SaleCode);delete from COM_EXE_SaleCar
where SaleCode in(select SaleCode from #temp_result);insert into COM_EXE_SaleCar
select * from #temp_result
这样行不?
SALENAME= ,BusinessDate
FROM Fact_SaleCar A,COM_EXE_SaleCar B
WHERE A.SALECODE = B.SALECODE
AND B.最后修改时间 >= CONVERT(DATE,GETDATE())INSERT INTO Fact_SaleCar
SELECT SALECODE,SALENAME,BusinessDate
FROM COM_EXE_SaleCar A
WHERE BusinessDate>=CONVERT(DATE,GETDATE())
AND NOT EXISTS ( -- 加个条件防止重复出错
SELECT 1
FROM Fact_SaleCar
WHERE SALECODE = A.SALECODE
)
也许可以使用维度代理键,不用业务数据库中的主键作为维度表主键