加快数据仓库加载无需添加硬件的解决方法

加快数据仓库加载无需添加硬件的解决方法

很多公司流行使用数据仓库进行数据分析,一般从线上数据源备库(mirror,logshipping,slave等)抽取到ods 层

在从ods层到dw再到dm.特别在ods层到dw时,数据的清洗装载需要一定的时间和硬件资源.

但是当硬件成为瓶颈时,怎么能快速完成清洗转载,及时的提供数据分析?

下面提供一种方法使用Ssis 加载到 ods层后,直接通过分区表把数据加载到 dw 

1 准备

 /*create filegroup*/  
  ALTER DATABASE [testxwj] ADD FILEGROUP [account_1]   
 go   
ALTER DATABASE [testxwj] ADD FILEGROUP [account_2]   
go   
ALTER DATABASE [testxwj] ADD FILEGROUP [account_3]  
 
/*create file to filegroup*/  
 
ALTER DATABASE [testxwj] ADD FILE ( NAME = N'account_1', FILENAME = N'E:\account_1.ndf' , SIZE = 409600KB , FILEGROWTH = 20480KB ) TO FILEGROUP [account_1]  
  GO  
ALTER DATABASE [testxwj] ADD FILE ( NAME = N'account_2', FILENAME = N'E:\account_2.ndf' , SIZE = 409600KB , FILEGROWTH = 20480KB ) TO FILEGROUP [account_2]  
 GO  
ALTER DATABASE [testxwj] ADD FILE ( NAME = N'account_3', FILENAME = N'E:\account_3.ndf' , SIZE = 409600KB , FILEGROWTH = 20480KB ) TO FILEGROUP [account_3]  
GO16 

2 使用ssis copy table

sp_spaceused accountdetail; 

/* delete EarnTime is not null*/   
 
/*23 sec*/   
delete from accountdetail where EarnTime is null 
/*26 sec*/   
delete from accountdetail where isnull(CommitStatus,0)<1   
/*12 sec*/  
delete from accountdetail where  isnull(EarnStatus,0) =0 

对传输过来的表进行分区

/*create partition function*/  
declare @bdate char(8),@edate varchar(8),@sql varchar(500)   
select   
@bdate=convert(char(8),GETDATE()-1 ,112)   
,@edate=convert(char(8),GETDATE() ,112)   
select @bdate,@edate;  
set @sql='   
CREATE PARTITION FUNCTION ac_EarnTime (datetime)  
AS11 RANGE RIGHT FOR VALUES ( '''+@bdate+''' ,'''+@edate+''')'  
 execute(@sql)  
/*create partition schema*/  
CREATE PARTITION SCHEME ac_schema_ac_EarnTime
AS PARTITION ac_EarnTime TO (account_1,account_2,account_3);  
/*create partition table */  
alter table accountdetail  
alter column EarnTime datetime not null;  
alter TABLE accountdetail  
add CONSTRAINT [PK_PARTITIONmis] PRIMARY KEY 
(   id,EarnTime
 )ON ac_schema_ac_EarnTime(EarnTime)

把分区partition 2指向给 dw 值得注意的是 accountdetail_dw 必须跟partition 2 分区所在同一个文件组

/*switch accountdetail to accountdetail_dwl*/  
 ALTER TABLE accountdetail SWITCH PARTITION 2 TO accountdetail_dw ;  
/**/ 

整个过程在 5分钟内.数据仓库最重要的还在当初的设计和选型.

原文标题：当硬件成为瓶颈时怎么提高数据仓库的加载？

链接：http://www.cnblogs.com/xwj1985/archive/2010/08/19/1803272.html