October 20, 2010 at 6:01 am
This is a current scrip that I use to populate dimension. I run this on Staging database, and if there is new data row, It will keep it here (in staging db) and removes the existing in DW, and gives them surrogate key.
select DID, REG_DATE, FILE_NO, ST_CHNG_DATE
into #temp5
from ETL.dbo.FAR f1
group by DID, REG_DATE, FILE_NO, ST_CHNG_DATE
delete from #temp5
from #temp5 f1, DW.dbo.DimF1 f2
where f1.DID=f2.Doclink_ID and f1.REG_DATE=f2.Reg_Date and f1.FILE_NO=f2.File_No and f1.ST_CHNG_DATE=f2.St_Chng_Date
insert into dbo.DimF1
select ROW_NUMBER() over (order by DID) as 'F_SK', DID, REG_DATE, FILE_NO, ST_CHNG_DATE
from #temp5
group by DID, REG_DATE, FILE_NO, ST_CHNG_DATE
drop table #temp5
--get the master locaton max row_id, and update accordingly
declare @maxNo as int;
set @maxNo=(select coalesce (max(Doclink_ID),0) from DW.dbo.DimF1)
update dbo.DimF1 set F_SK=(F_SK+@maxNo)
insert into dbo.DimF1
values ('-1', '-1', '1900-01-01 00:00:00.000','NA', '1900-01-01 00:00:00.000')
delete from dbo.DimF1
from dbo.DimF1 f1, DW.dbo.DimF1 f2
where f1.Doclink_ID=f2.Doclink_ID and f1.Reg_Date=f2.Reg_Date and f1.File_No=f2.File_No
Now I want to add at the end of table one more column with the name of R_CHANGE date, which is the date, and may be changes over the time. for the particular row. I need to keep that updated data on the same existing row in DW, after checking that has date has been updated.
Hope I could explain the problem, if not let me know, and thanks beforehand.
October 21, 2010 at 1:35 am
I guess it was slowly changing dimension, topic is closed
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply