Saat sedang membantu teman kantor yang progress projectnya dah mau kelar saya sedikit terkejut dengan DW(Datawarehouse) yang beliau bikin. Ternyata dari awal project sampai project hampir selesai dia belum memasang constraint(foreign key dan unique key) di DW. Awalnya memang sengaja tidak beliau pasang dengan alasan agar tidak lemot mengingat data yang dihandle cukup besar dengan resource yang kurang. Tetapi saya kurang setuju dengan alasan tersebut.
Constraint di DW sangatlah penting apalagi saat proses development untuk memastikan data yang masuk ke DW adalah data yang clean dan tidak redundan.
Akhirnya beliau minta saya untuk memasang constraint yang dibutuhkan di DW. Pada mulanya saya bermaksut membuat constraint yg dibutuhkan satu2 secara manual. Tapi beliau menyarankan untuk membuat generator saja mengingat jumlah constraint yang dibutuhkan lumayan banyak.
Untuk membuat generator ini tentunya ada beberapa aturan yang harus diperhatikan yaitu standarisasi penamaan tabel dan kolom pada DW. pada kesempatan kali ini saya sudah menyesuaikan generator yg telah saya buat dengan standar dari AdventureWorksDW dimana nama tabel diawali dengan kata Dim dan Fact untuk tabel dimensi dan fact. kemudian untuk kolom primary key dan foreign key di belakang nama kolomnya ada tambahan kata2 Key
Karena dibutuhkan 2 tipe constraint yaitu constraint untuk foreign key dan unique key maka saya membuat ke dua generatornya secara terpisah.
Berikut adalah script untuk men-generate constraint foreign key:
DECLARE @MainTable NVARCHAR(50)
DECLARE @ReferenceTable NVARCHAR(50)
DECLARE @sqlFK varchar(max)
DECLARE @ColumnName NVARCHAR(50)
DECLARE @Count int
DECLARE @dummy int
DECLARE curCreateFK CURSOR
FOR
SELECT so.name,
sc.name
FROM sysobjects so,
syscolumns sc
WHERE so.id = sc.id
AND so.type = 'U'
AND so.name LIKE 'Fact%'
AND RIGHT(sc.name,3) = 'Key'
ORDER BY
so.name,
sc.colorder
OPEN curCreateFK
FETCH NEXT FROM curCreateFK INTO @MainTable, @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Count = 0
SELECT @Count = count(so.name)
FROM sysobjects so,
syscolumns sc
WHERE so.id = sc.id
AND so.type = 'U'
AND so.name LIKE 'Dim%'
AND sc.isnullable=0
AND sc.name = @ColumnName
IF @Count = 1
BEGIN
SELECT @ReferenceTable = so.name
FROM sysobjects so,
syscolumns sc
WHERE so.id = sc.id
AND so.type = 'U'
AND so.name LIKE 'Dim%'
AND sc.isnullable=0
AND sc.name = @ColumnName
SET @sqlFK = 'ALTER TABLE [dbo].[' + @MainTable + '] WITH CHECK ADD CONSTRAINT [FK_' + @MainTable +'_'+@ColumnName+'] FOREIGN KEY(['+@ColumnName+'])
REFERENCES [dbo].[' + @ReferenceTable + '] ([' + @ColumnName +'])
'
END
ELSE
BEGIN
IF @Count=0
BEGIN
IF @ColumnName LIKE '%Date%'
BEGIN
SET @sqlFK = 'ALTER TABLE [dbo].[' + @MainTable + '] WITH CHECK ADD CONSTRAINT [FK_' + @MainTable +'_'+@ColumnName+'] FOREIGN KEY(['+@ColumnName+'])
REFERENCES [dbo].[DimTime] ([TimeKey])
'
END
ELSE
BEGIN
-- Do it manually for different column name
PRINT 'Match Not Found: ' + @MainTable + '-' + @ColumnName +'
'
SET @sqlFK=''
END
END
ELSE
BEGIN
-- Found more than 1 primary key with the same name
PRINT 'Double: ' + @MainTable + '-' + @ColumnName +'
'
END
END
BEGIN TRY
EXEC (@sqlFK)
PRINT 'Success ' + @sqlFK
END TRY
BEGIN CATCH
PRINT 'Already Exists ' + @sqlFK
END CATCH
FETCH NEXT FROM curCreateFK INTO @MainTable, @ColumnName
END
CLOSE curCreateFK
DEALLOCATE curCreateFK
Dan ini generator untuk unique constraintnya:
DECLARE curTable CURSOR FOR
SELECT so.name
FROM sysobjects so
WHERE so.type = 'U'
AND so.name LIKE 'Fact%'
ORDER BY
so.name
DECLARE @tablename varchar(50),@UQ_Query varchar(max),@columnname varchar(50),@columncount int
OPEN curTable
FETCH NEXT FROM curTable INTO @tablename
WHILE @@fetch_status=0
BEGIN
SET @columncount=0
SET @UQ_Query='ALTER TABLE ['+@tablename+'] ADD CONSTRAINT [UQ_'+@tablename+'] UNIQUE ('
DECLARE curColumn CURSOR FOR
SELECT sc.name
FROM sysobjects so,
syscolumns sc
WHERE so.id = sc.id
AND RIGHT(sc.name,3) = 'Key'
AND so.name LIKE @tablename
ORDER BY
sc.colorder
OPEN curColumn
FETCH NEXT FROM curColumn INTO @columnname
WHILE @@fetch_status=0
BEGIN
SET @columncount=@columncount+1
IF @columncount=1
BEGIN
SET @UQ_Query=@UQ_Query+@columnname
END
ELSE
BEGIN
SET @UQ_Query=@UQ_Query+','+@columnname
END
FETCH NEXT FROM curColumn INTO @columnname
END
CLOSE curColumn
DEALLOCATE curColumn
--Exception Goes Here
IF @tablename='FactInternetSales' OR @tablename='FactResellerSales' OR @tablename='FactInternetSalesReason'
BEGIN
SET @UQ_Query=@UQ_Query+',SalesOrderNumber,SalesOrderLineNumber'
END
SET @UQ_Query=@UQ_Query+')'
BEGIN TRY
EXEC (@UQ_Query)
PRINT 'Success '+@UQ_Query
END TRY
BEGIN CATCH
PRINT 'Already Exists '+@UQ_Query
END CATCH
FETCH NEXT FROM curTable INTO @tablename
END
CLOSE curTable
DEALLOCATE curTable