sql server – Data Warehouse: Automatic generation of slowly changing dimension code for any table with Dynamic Sql

Can anyone improve this, parameterize more, improve it and improve it? Working on this for a couple of weeks. Feel free to try and edit below.

create procedure dbo.Dim_Type2_GenerateCode
@TableNameSource varchar (255),
@TableSourceLoadDate varchar (255) = & # 39; LoadDatetime & # 39 ;,

@NaturalKey varchar (255),
@NaturalBeginDateChange varchar (255),
@NaturalEndDateChange varchar (255) = null,

@RepeatedDataFlag bit = 0,
@TempTableFlag bit = 0,
@ColumnExcludeList varchar (max) = null

as

set nocount on

- Purpose: generate dimensions that change slowly for any table
- Note:
- In the case of renaming columns or unions between the Scenario and the Dimension, use MapVw, for example, FoodVw, CustomerMapVw, ProductMapVw
- To include only certain columns between Staging and Dimension, use MapVw, for example, FoodVw, CustomerMapVw, ProductMapVw
- To load data into a smaller temporary table to avoid repeated queries, use @TempTableFlag = 1
- The Column Exclusion parameter can be added to exclude certain columns
- It works with three types of tables below: Transaction dates, tables with start / end dates already, tables with repeated data

- ProductId ProductName TransactionDate ProductId ProductName TransactionDate EndEffDate ProductId ProductName TransactionDate (repeated)
- 1 Apple 4/1/2018 1 Apple 4/1/2018 4/3/2018 1 Apple 4/1/2018
- 1 Apple 4/3/2018 1 Apple 4/3/2018 4/7/2018 1 Apple 4/1/2018
- 1 Apple 4/7/2018 1 Apple 4/12/2018 12/31/999 1 Apple 4/2/2018
- 1 block 4/3/2018

set @TableNameSource = right (@TableNameSource, len (@TableNameSource) - charindex (& # 39;. & # 39 ;, @TableNameSource))
declare @StageTableName varchar (255)
declare @DimTableName varchar (255)
set @TempTableFlag = (case when @NaturalBeginDateChange is null or @NaturalEndDateChange is null or @RepeatedDataFlag = 1 then 1 else 0 end)



DECLARE the table @ColumnExcludeTable (ColumnExcludeValue varchar (500) not null);
insert in @ColumnExcludeTable (ColumnExcludeValue)
select ltrim (rtrim (value)) as ColumnExcludeValue from string_split (@ColumnExcludeList, & # 39;, & # 39;);



declare @TempTableDeclare varchar (max) = & # 39; create table # & # 39; + @ TableNameSource +
& # 39;
(
& # 39; + @TableNameSource + & # 39; _id Bigint primary key identity (1,1), & # 39; +
(select STUFF ((
SELECT
& # 39;
+ c.name + & # 39; & # 39; +
case
when t.name as & # 39;% char% & # 39; then t.name + & # 39; (& # 39; + cast (c.max_length as varchar (10)) + & # 39;) & # 39;
when t.name as & # 39;% numeric% & # 39; or t.name as & # 39;% decimal% & # 39; then t.name + & # 39; (& # 39; + cast (c.precision as varchar (10)) + & # 39;, & # 39; + cast (c.scale as varchar (10)) + & # 39;) & # 39;
more t.name
finish
OF .sys.columns c
interior JOIN sys.types t
in t.user_type_id = c.user_type_id
and t.system_type_id = c.system_type_id
where c.object_id = object_id (@TableNameSource) and is_identity = 0
and c.name is not like & # 39;% @ NaturalKey% & # 39;
and c.name is not like & # 39;% EndEffDate% & # 39;
and c.name <> @NaturalBeginDateChange
and c.name is not in (select columnexcludetable.ColumnExcludeValue from @ColumnExcludeTable columnexcludetable)
FOR XML PATH (& # 39; & # 39;), TYPE) .value (& # 39;. & # 39 ;, & # 39; Nvarchar (max) & # 39;), 1,2, & # 39 ; & # 39;))
+ & # 39;
, Date and time of login
, Date and time of the weekend
) & # 39;


declare @TempTableCodeInsert varchar (max) =
(select STUFF ((
SELECT
& # 39;
+ QUOTENAME (c.name)
OF .sys.columns c
interior JOIN sys.types t
in t.user_type_id = c.user_type_id
and t.system_type_id = c.system_type_id
where c.object_id = object_id (@TableNameSource) and is_identity = 0
and c.name is not like & # 39;% BegEffDate% & # 39;
and c.name is not like & # 39;% EndEffDate% & # 39;
and c.name <> @NaturalBeginDateChange
and c.name is not in (select columnexcludetable.ColumnExcludeValue from @ColumnExcludeTable columnexcludetable)
FOR XML PATH (& # 39; & # 39;), TYPE) .value (& # 39;. & # 39 ;, & # 39; Nvarchar (max) & # 39;), 1,2, & # 39 ; & # 39;))

declare @TempTableCodeInsertNoLoadDate varchar (max) = (replace (@ TempTableCodeInsert, quotename (@TableSourceLoadDate), & # 39; & # 39;))
set @TempTableCodeInsertNoLoadDate = LEFT (@TempTableCodeInsertNoLoadDate, LEN (@TempTableCodeInsertNoLoadDate) -5)
--set @TempTableCodeInsertNoLoadDate = (replace (@ TempTableCodeInsertNoLoadDate, quotename (@NaturalBeginDateChange), & # 39; & # 39;))


declare @ColumnListNoPrimary varchar (max) =
(select STUFF ((
SELECT
& # 39;
+ QUOTENAME (c.name)
OF .sys.columns c
where c.object_id = object_id (@TableNameSource) and is_identity = 0
FOR XML PATH (& # 39; & # 39;), TYPE) .value (& # 39;. & # 39 ;, & # 39; Nvarchar (max) & # 39;), 1,2, & # 39 ; & # 39;))


declare @ColumnListNoPrimaryNoTimeStamp varchar (max) =
(select STUFF ((
SELECT
& # 39;
+ QUOTENAME (c.name)
OF .sys.columns c
where c.object_id = object_id (@TableNameSource) and is_identity = 0
and c.name <> @NaturalKey
and c.name is not like & # 39;% BeginEffDate% & # 39;
and c.name is not like & # 39;% EndEffDate% & # 39;
and c.name is not like & # 39;% CreateDatetime% & # 39;
and c.name is not like & # 39;% UpdateDatetime% & # 39;
and c.name is not like & # 39;% Loaddate% & # 39;
and c.name <> @NaturalBeginDateChange
and c.name is not in (select columnexcludetable.ColumnExcludeValue from @ColumnExcludeTable columnexcludetable)
FOR XML PATH (& # 39; & # 39;), TYPE) .value (& # 39;. & # 39 ;, & # 39; Nvarchar (max) & # 39;), 1,2, & # 39 ; & # 39;))



declare @ColumnListNoPrimaryCompareCheck varchar (max) =
(select STUFF ((
SELECT & # 39; or
& # 39;
+ & # 39; stg. & # 39; + QUOTENAME (c.name) + & # 39; <> dim & # 39 ;. + QUOTENAME (c.name)
OF .sys.columns c
where c.object_id = object_id (@TableNameSource) and is_identity = 0
and c.name <> @NaturalKey
and c.name is not like & # 39;% BeginEffDate% & # 39;
and c.name is not like & # 39;% EndEffDate% & # 39;
and c.name is not like & # 39;% CreateDatetime% & # 39;
and c.name is not like & # 39;% UpdateDatetime% & # 39;
and c.name is not like & # 39;% Loaddate% & # 39;
and c.name <> @NaturalBeginDateChange
and c.name is not in (select columnexcludetable.ColumnExcludeValue from @ColumnExcludeTable columnexcludetable)
FOR XML PATH (& # 39; & # 39;), TYPE) .value (& # 39;. & # 39 ;, & # 39; Nvarchar (max) & # 39;), 1,3, & # 39 ; & # 39;))



set @StageTableName = REPLACE (@ TableNameSource, & # 39; Dim & # 39 ;, & # 39; Stage & # 39;)
set @StageTableName = (case when @TempTableFlag = 1 then & # 39; # & # 39; + @StageTableName else & # 39; dbo. & # 39; + @StageTableName end)

set @DimTableName = REPLACE (@ TableNameSource, & # 39; Stage & # 39 ;, & # 39; Dim & # 39;)
set @DimTableName = REPLACE (@ DimTableName, & # 39; MapVw & # 39 ;, & # 39; & # 39;)
set @DimTableName = (case when left (@ DimTableName, 3) <> & # 39; Dim & # 39; then & # 39; Dim _ & # 39; + @DimTableName else @DimTableName end)

set @NaturalBeginDateChange = (case when @TempTableFlag = 1 then @NaturalBeginDateChange else @NaturalBeginDateChange end)
declare @NaturalBeginDateChangeRename varchar (255) = (case when @TempTableFlag = 1 then & # 39; BegEffDatetime & # 39; else @NaturalBeginDateChange end)



declare @TableListGenerateCode nvarchar (max) =

& # 39; create procedure dbo & # 39; + @DimTableName + & # 39; Update
@LoadDatetimeParam datetime
as

declare @NewDatetime datetime = getdate ()

& # 39;
+

& # 39; - Declare temporary data
& # 39; +
case when @TempTableFlag = 1

so
+ @TempTableDeclare + & # 39;

Insert in & # 39; + @StageTableName +
& # 39;
(
& # 39; + @TempTableCodeInsert + & # 39;
, BegEffDatetime
, EndEffDatetime
) & # 39; +
case when @RepeatedDataFlag = 1
so
& # 39; select
& # 39; + replace (@ TempTableCodeInsert, quotename (@TableSourceLoadDate), & # 39; min (& # 39; + quotename (@TableSourceLoadDate) + & # 39;) & # 39;) + & # 39;
, min (& # 39; + @NaturalBeginDateChange + & # 39;) like BegEffDatetime
, case when max (stg. & # 39; + @NaturalBeginDateChange + & # 39;) = (select max (subtage. & # 39; + @NaturalBeginDateChange + & # 39;) from & # 39; + @TableNameSource + & # 39; substance where stg. & # 39; + @NaturalKey + & # 39; = substage. & # 39; + @NaturalKey + & # 39;) then & # 39; & # 39; 12 / 31/9999 & # 39; & # 39; else else max (stg. & # 39; + @NaturalBeginDateChange + & # 39;) ends as EndDate
from & # 39; + @TableNameSource + & # 39; stg
where LoadDatetime> @LoadDatetimeParam
group by & # 39; + @TempTableCodeInsertNoLoaddate
when @RepeatedDataFlag = 0
so
& # 39;
select
different & # 39;
+ @TempTableCodeInsert + & # 39;
, & # 39; + @NaturalBeginDateChange + & # 39; as BegEffDatetime
, ISNULL (Leader (& # 39; + @NaturalBeginDateChange + & # 39; + 1) Over (partition by & # 39; + @NaturalKey + & # 39; sorted by & # 39; + @NaturalBeginDateChange + & # 39; asc), & # 39; & # 39; 12/31/9999 & # 39; & # 39;) as EndEffDatetime & # 39;


+ & # 39;
of & # 39; + @TableNameSource +
& # 39;
where LoadDatetime> @LoadDatetimeParam & # 39;
finish
plus
& # 39; & # 39;
finish
+ & # 39;
- And the temporary data section & # 39; + & # 39;


- Begin the transaction

start the transaction

- Close existing records that have changed

update dbo. + @DimTableName + & # 39;
set
UpdateDatetime = @NewDatetime,
EndEffDatetime = (select min (& # 39; + @NaturalBeginDateChangeRename + & # 39;) from & # 39; + @StageTableName + & # 39; subpath where stg. & # 39; + @NaturalKey + & # 39; = subpath . & # 39; + @NaturalKey + & # 39;)
of dbo. + @DimTableName + & # 39; dim
internal union & # 39; + @StageTableName + & # 39; stg
in the dark. + @NaturalKey + & # 39; = stg. & # 39; + @NaturalKey + & # 39;
and dim.EndEffDatetime = & # 39; & # 39; 12/31/9999 & # 39; & # 39;
where
stg.LoadDatetime> @LoadDatetimeParam
and (& # 39;
+ @ColumnListNoPrimaryCompareCheck +


& # 39;)
- Insert new updated records

insert in dbo. + @DimTableName +
& # 39;
(
& # 39; + @NaturalKey + & # 39 ;,
& # 39; + @ColumnListNoPrimaryNoTimeStamp + & # 39 ;, & # 39; +
& # 39;
        [CreateDatetime],
        [UpdateDatetime],
        [BegEffDatetime],
        [EndEffDatetime]
    )
select
stg. + @NaturalKey + & # 39;, & # 39;
+ replace (@ColumnListNoPrimaryNoTimeStamp, & # 39;[& # 39;, & # 39; stg.[& # 39;) +
& # 39;
, @ newdatetime as CreateDatetime
, @ newdatetime as UpdateDatetime
, stg.BegEffDatetime as BegEffDatetime
, stg.EndEffDatetime as EndEffDatetime
from & # 39; + @StageTableName + & # 39; stg
internal union & # 39; + @DimTableName + & # 39; dim
in the dark. + @NaturalKey + & # 39; = stg. & # 39; + @NaturalKey + & # 39;
and dim.UpdateDatetime = @NewDatetime
--Verify updates
where
stg.LoadDatetime> @LoadDatetimeParam
and (& # 39;
+ @ColumnListNoPrimaryCompareCheck + & # 39;)

- Insert New Business Key records that do not exist

UNION ALL
select
stg. + @NaturalKey + & # 39 ;,
& # 39; + replace (@ColumnListNoPrimaryNoTimeStamp, & # 39;[& # 39;, & # 39; stg.[& # 39;) +
& # 39;
, @ newdatetime as CreateDatetime
, @ newdatetime as UpdateDatetime
, stg.BegEffDatetime as BegEffDatetime
, & # 39; & # 39; 12/31/9999 & # 39; & # 39; as EndEffDate
from & # 39; + @StageTableName + & # 39; stg
left join dbo & # 39; + @DimTableName + & # 39; dim
in the dark. + @NaturalKey + & # 39; = stg. & # 39; + @NaturalKey + & # 39;
where dim & # 39; + @NaturalKey + & # 39; is null

commitment transaction

--the final code & # 39;

- Print columns in three steps, print and select you can only print the first 8000 characters
print substring (@ TableListGenerateCode, charindex (& # 39; - Create procedure & # 39 ;, @ TableListGenerateCode), charindex (& # 39; - Declare temporary data & # 39 ;, @ TableListGenerateCode))
print the substring (@ TableListGenerateCode, charindex (& # 39; - Declare temp data & # 39 ;, @ TableListGenerateCode), charindex (& # 39; - End temp data & # 39 ;, @ TableListGenerateCode) -charindex (& # 39; - Declare temp data & # 39 ;, TableListGenerateCode))
print substring (@ TableListGenerateCode, charindex (& # 39; - Begin transaction & # 39 ;, @ TableListGenerateCode), charindex (& # 39; - Close existing records & # 39 ;, @ TableListGenerateCode) -charindex (& # 39; - Begin Transaction & # 39 ;, @ TableListGenerateCode)
print & # 39; & # 39; + substring (@ TableListGenerateCode, charindex (& # 39; - Close existing records & # 39 ;, @ TableListGenerateCode), charindex (& # 39; - Insert new updated records & # 39 ;, @ TableListGenerateCode) -charindex (& # 39; - Close existing records & # 39 ;, @TableListGenerateCode))
print & # 39; & # 39; + substring (@ TableListGenerateCode, charindex (& # 39; - Insert new updated records & # 39 ;, @ TableListGenerateCode), charindex (& # 39; - Check updates & # 39 ;, @ TableListGenerateCode) -charindex (& # 39; - Insert new updated records & # 39 ;, @ TableListGenerateCode))
print & # 39; & # 39; + substring (@ TableListGenerateCode, charindex (& # 39; - Verify updates & # 39 ;, @ TableListGenerateCode), charindex (& # 39; - Insert New Business & # 39 ;, @ TableListGenerateCode) -charindex (& # 39; - Verify updates & # 39 ;, @ TableListGenerateCode)
print & # 39; & # 39; + substring (@ TableListGenerateCode, charindex (& # 39; - Insert New Business & # 39 ;, @ TableListGenerateCode), charindex (& # 39; - end code & # 39 ;, @ TableListGenerateCode) -charindex (& # 39; - Insert New Business & # 39 ;, @ TableListGenerateCode)

Test scenarios:
The staff keeps the mislabeled items in the grocery store. Keep a record of the story.

folding table [dbo].[Stage_Food]
create a table [dbo].[Stage_Food]
(
    [Stagefoodid] int identity (1,1),
    [FoodNaturalId] [int]    NOT NULL,
    [FoodName] [varchar](255) NULL,
    [FoodCategory] [varchar](255) NULL,
    [FoodTransactionDate] Date and Time,
    [LoadDatetime] Date and Time
primary key grouped ([Stagefoodid] ASC)
)

drop table dbo.[Dim_food]
create a table [dbo].[Dim_food]
(
    [DimFoodId] [int]    IDENTITY (1,1) NOT NULL,
    [FoodNaturalId] [int]    NULL,
    [FoodName] [varchar](255) NULL,
    [FoodCategory] [varchar](255) NULL,
    [BegEffDatetime] [datetime]    NULL,
    [EndEffDatetime] [datetime]    NULL,
    [CreateDatetime] [datetime]    NULL,
    [Updatedatetime] [datetime]    NULL
primary key grouped ( [DimFoodId] ASC)
)


exec dbo.Dim_Type2_GenerateCode
@TableNameSource = & # 39; Stage_Food & # 39 ;,
@TableSourceLoadDate = & # 39; LoadDatetime & # 39 ;,

@NaturalKey = & # 39; FoodNaturalId & # 39 ;,
@NaturalBeginDateChange = & # 39; FoodTransactionDate & # 39;



Insert in dbo.Stage_Food
values ​​(1, & # 39; Apple & # 39 ;, & # 39; Vegetable & # 39 ;, & # 39; 5/2/2018 & # 39 ;, & # 39; 5/4/2018 & # 39;)

exec dbo.Dim_FoodUpdate & # 39; 5/3/2018 & # 39;

select * from dbo.Dim_food


Insert in dbo.Stage_Food
values ​​(2, & # 39 ;, & # 39 ;, & # 39 ;, & # 39; 5/3/2018 & # 39 ;, & # 39; 5/5/2018 & # 39;)

exec dbo.Dim_FoodUpdate & # 39; 5/4/2018 & # 39;

select * from dbo.Dim_food

Insert in dbo.Stage_Food
values ​​(1, & # 39; Apple & # 39 ;, & # 39; Candy & # 39 ;, & # 39; 5/8/2018 & # 39 ;, & # 39; 5/9/2018 & # 39;)

dbo.Dim_FoodUpdate & # 39; 5/8/2018 & # 39;

select * from dbo.Dim_food


Insert in dbo.Stage_Food
values ​​(2, & # 39; Bread & # 39 ;, & # 39; Grain & # 39; 5/8/2018 & # 39 ;, & # 39; 5/10/2018 & # 39;)


dbo.Dim_FoodUpdate & # 39; 5/9/2018 & # 39;

select * from dbo.Dim_food


---------------------------------------
- Test2 indicator of repeated data
---------------------------------------


exec dbo.Dim_Type2_GenerateCode
@TableNameSource = & # 39; Stage_Food & # 39 ;,
@TableSourceLoadDate = & # 39; LoadDatetime & # 39 ;,

@NaturalKey = & # 39; FoodNaturalId & # 39 ;,
@NaturalBeginDateChange = & # 39; FoodTransactionDate & # 39 ;,
@RepeatedDataFlag = 1,
@TempTableFlag = 1


Insert in dbo.Stage_Food
values ​​(1, & # 39; Apple & # 39 ;, & # 39; Vegetable & # 39 ;, & # 39; 5/2/2018 & # 39 ;, & # 39; 5/4/2018 & # 39;),
(1, & # 39; Apple & # 39 ;, & # 39; Vegetable & # 39 ;, & # 39; 5/2/2018 & # 39 ;, & # 39; 5/4/2018 & # 39;)

exec dbo.Dim_FoodUpdate & # 39; 5/3/2018 & # 39;

select * from dbo.Dim_food


Insert in dbo.Stage_Food
values ​​(2, & # 39 ;, & # 39 ;, & # 39 ;, & # 39; 5/2/2018 & # 39 ;, & # 39; 5/5/2018 & # 39;),
(2, & # 39 ;, & # 39 ;, Meat & # 39 ;, & # 39; 5/3/2018 & # 39 ;, & # 39; 5/5/2018 & # 39;)



exec dbo.Dim_FoodUpdate & # 39; 5/4/2018 & # 39;

select * from dbo.Dim_food

Insert in dbo.Stage_Food
values ​​(1, & # 39; Apple & # 39 ;, & # 39; Candy & # 39 ;, & # 39; 5/6/2018 & # 39 ;, & # 39; 5/9/2018 & # 39;),
(1, & # 39; Apple & # 39 ;, & # 39 ;, Candy & # 39 ;, & # 39; 5/7/2018 & # 39 ;, & # 39; 5/9/2018 & # 39;),
(1, & # 39; Apple & # 39 ;, & # 39 ;, & # 39 ;, & # 39; 5/8/2018 & # 39 ;, & # 39; 5/9/2018 & # 39;)

dbo.Dim_FoodUpdate & # 39; 5/8/2018 & # 39;

select * from dbo.Dim_food


Insert in dbo.Stage_Food
values ​​(2, & # 39; Bread & # 39 ;, & # 39; Grain & # 39; 5/8/2018 & # 39 ;, & # 39; 5/10/2018 & # 39;)


dbo.Dim_FoodUpdate & # 39; 5/9/2018 & # 39;

select * from dbo.Dim_food

You can try more elements, temporary table indicators, column exclusion list, mapping views