数据仓库中清空维度表问题

 

       数据仓库搭建好以后,若维度表数据量不是很大,可采用全部更新的方式来提高效率,因为若用增量更新,则需要渐变维度组件,效率较底.

       采用全部更新,每次加载维度表之前必须先清空维度表,可在加载维度表的控制流前面加一个“执行SQL任务”用来清空维度表。但由于数据仓库往往包有主外键约束,清空表时会遇到问题,这时要注意:

       1、因为存在主外键引用关系，必须按照一定的顺序清空表，否则会提示主外键约束，不能清空。顺序原则是：先清空事实表，再清空维度表。若是雪花型结构，先清空事实表，然后再清空离事实表最近的维度表，即最后清空“叶子”维度表。

       2、清空时的语句用DELETE语句，不能用TRUNCATE语句。

如：

delete from dbo.ManufacturingFact
delete from dbo.DimProduct
delete from dbo.DimProductSubtype
delete from dbo.DimProductType
delete from dbo.DimBatch
delete from dbo.DimMachine
delete from dbo.DimMachineType
delete from dbo.DimMaterial
delete from dbo.DimPlant
delete from dbo.DimCountry