1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
|
declare @row INT
Set @row = 0
DECLARE @rowcountsType TABLE
(
mergeaction NVARCHAR(10)
)
--Merge 1- Handling type 3 attributes
Merge
INTO DATAWAREHOUSE.DWH.DimensionXXX [TARGET]
USING STAGING.DWH.StgDimensionXXX [SOURCE]
ON
(
[TARGET].BK_Dimension = [SOURCE].BK_Dimension
)
WHEN MATCHED AND EXISTS
(
SELECT
[TARGET].AttributZ
EXCEPT
SELECT
[SOURCE].AttributZ
) -- SCD 3
THEN
UPDATE SET
[TARGET].AttributZ_Old = [TARGET].AttributZ,
[TARGET].ModifiedOn = GETDATE(),
[TARGET].ModifiedBy = 'Package_Nom_DimensionXXX'
OUTPUT $action
INTO @rowcountsType;
--Merge 2- Handling type 1 attributes
Merge
INTO DATAWAREHOUSE.DWH.DimensionXXX [TARGET]
USING STAGING.DWH.StgDimensionXXX [SOURCE]
ON
(
[TARGET].BK_Dimension = [SOURCE].BK_Dimension
)
WHEN NOT MATCHED THEN
INSERT (
BK_Dimension,
AttributX,
AttributY,
AttributZ,
CreatedOn,
ModifiedOn,
CreatedBy,
ModifiedBy
,RowStartDate,
RowEndDate,
RowIsCurrent
)
VALUES (
[SOURCE].BK_Dimension,
[SOURCE].AttributX,
[SOURCE].AttributY,
[SOURCE].AttributZ,
GETDATE(),
NULL,
'Package_Nom_DimensionXXX',
NULL
,GETDATE(),
NULL,
1
)
WHEN MATCHED AND EXISTS
(
SELECT
[TARGET].AttributX
,[TARGET].AttributZ
EXCEPT
SELECT
[SOURCE].AttributX
,[SOURCE].AttributZ
) -- SCD 1
THEN
UPDATE SET
[TARGET].AttributX = [SOURCE].AttributX,
[TARGET].AttributZ = [SOURCE].AttributZ,
[TARGET].ModifiedOn = GETDATE(),
[TARGET].ModifiedBy = 'Package_Nom_DimensionXXX'
OUTPUT $action
INTO @rowcountsType;
INSERT INTO DATAWAREHOUSE.DWH.DimensionXXX
(
BK_Dimension,
AttributX,
AttributY,
AttributZ,
CreatedOn,
ModifiedOn,
CreatedBy,
ModifiedBy,
RowStartDate,
RowEndDate,
RowIsCurrent
)
SELECT
MERGE_OUT.BK_Dimension,
MERGE_OUT.AttributX,
MERGE_OUT.AttributY,
MERGE_OUT.AttributZ,
MERGE_OUT.CreatedOn,
MERGE_OUT.ModifiedOn,
MERGE_OUT.CreatedBy,
MERGE_OUT.ModifiedBy,
MERGE_OUT.RowStartDate,
MERGE_OUT.RowEndDate,
MERGE_OUT.RowIsCurrent
FROM (
MERGE DATAWAREHOUSE.DWH.DimensionXXX [TARGET]
USING STAGING.DWH.StgDimensionXXX [SOURCE]
ON (
[TARGET].BK_Dimension = [SOURCE].BK_Dimension
)
WHEN MATCHED AND
[TARGET].RowIsCurrent = 1
AND
EXISTS(
SELECT
[TARGET].AttributY
EXCEPT
SELECT
[SOURCE].AttributY
) -- SCD 2
THEN
UPDATE SET
ModifiedOn = GETDATE(),
ModifiedBy = 'Package_Nom_DimensionXXX',
RowEndDate = GETDATE(),
RowIsCurrent = 0
OUTPUT $Action Action_Out,
[SOURCE].BK_Dimension,
[SOURCE].AttributX,
[SOURCE].AttributY,
[SOURCE].AttributZ,
GETDATE() AS CreatedOn,
GETDATE() AS ModifiedOn,
'Package_Nom_DimensionXXX' AS CreatedBy,
'Package_Nom_DimensionXXX' AS ModifiedBy,
GETDATE() AS RowStartDate,
NULL AS RowEndDate,
1 AS RowIsCurrent
) AS MERGE_OUT
WHERE
MERGE_OUT.Action_Out = 'UPDATE'
set @row=@@rowcount
--we want to check and log how many records have been updated
SELECT Isnull(Sum(CASE WHEN mergeaction = 'INSERT' THEN 1 ELSE 0 END), 0) + @row AS RecordsInserted,
Isnull(Sum(CASE WHEN mergeaction = 'UPDATE' THEN 1 ELSE 0 END), 0) + @row AS RecordsUpdated
FROM @rowcountsType |
Optimisation de temps de traitement.
Répondre avec citation
Partager