· 9 years ago · Dec 09, 2016, 05:54 PM
1USE [DATABASE]
2GO
3
4/****** Object: StoredProcedure [TFM].[TFMPrep_FactProcedure1] Script Date: 12/9/2016 11:50:24 AM ******/
5SET ANSI_NULLS ON
6GO
7
8SET QUOTED_IDENTIFIER ON
9GO
10
11IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TFM].[TFMPrep_FactProcedure1]') AND type in (N'P', N'PC'))
12BEGIN
13EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [TFM].[TFMPrep_FactProcedure1] AS'
14END
15GO
16
17
18 /****** [TFM].[TFMPrep_FactProcedure1] ******/
19 CREATE PROCEDURE [TFM].[TFMPrep_FactProcedure1]
20 (
21 @StartDate date = NULL
22 , @EndDate date = NULL
23 , @TruncateDestination bit = 1
24 , @LogItemServer nvarchar(100) = NULL
25 , @ProcessKey nvarchar(100) = NULL
26 , @LogEvent bit = 1
27 )
28 AS
29 --=============================================================================================================
30 -- Author: Zach Lau
31 -- Created Date: 9/3/2016
32 -- Description: This stored procedure is used to process transformation preparation
33 -- @StartDate - The date in which the results set is filtered. Defaults to current date.
34 -- @EndDate - The date in which the results set is filtered. Defaults to current date.
35 -- @TruncateDestination - Indicates whether the destination table should be truncated prior to loading. Defaults to 0 (no).
36 -- @LogItemServer - Indicates the server from which the log is coming. Defaults to @@SERVERNAME value.
37 -- @ProcessKey - Indicates the process in which the execution is related. Defaults to 'NOT_DEFINED'.
38 -- @LogEvent - Identifies whether the execution of the stored procedure is logged in the event log. Defaults to 1 (yes).
39 --=============================================================================================================
40 BEGIN
41 BEGIN TRY
42
43 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
44 SET NOCOUNT ON;
45 SET DEADLOCK_PRIORITY LOW; -- if a deadlock happens, this sproc will be the victim.
46
47 -- gets current date and handles null parameters
48 DECLARE @dt date = GETDATE();
49 SET @StartDate = COALESCE(@StartDate,@dt);
50 SET @EndDate = COALESCE(@EndDate,@dt);
51 SET @LogItemServer = COALESCE(@LogItemServer,@@SERVERNAME);
52 SET @ProcessKey = COALESCE(@ProcessKey,'MDM_GenericProcess');
53
54 -- get sproc name and schema
55 DECLARE @spname sysname = COALESCE(OBJECT_NAME(@@ProcID),'NOT_DEFINED'); -- this sproc name
56 DECLARE @shname sysname = COALESCE(OBJECT_SCHEMA_NAME(@@ProcID),'NOT_DEFINED'); -- this sproc schema name
57 DECLARE @LogText nvarchar(max) = '';
58
59 -- general variables
60 DECLARE @tbl nvarchar(200) = '[TFM].[FactProcedure1]';
61 DECLARE @ct int = 0;
62 DECLARE @PassStatus nvarchar(max);
63 DECLARE @SourceStr nvarchar(max);
64 DECLARE @BatchKey nvarchar(max);
65 SELECT @PassStatus = [FieldA] FROM [ETL].[FunctionA] ('Value1', @LogItemServer, @ProcessKey);
66 SELECT @SourceStr = [FieldB] FROM [ETL].[FunctionA] ('Value2', @LogItemServer, @ProcessKey);
67 SELECT @BatchKey = [FieldC] FROM [ETL].[FunctionA] ('Value3', @LogItemServer, @ProcessKey);
68
69 -- update [ETL].[ETLDWHTable]
70 DECLARE @now datetime = GETDATE();
71 EXECUTE [ETL].[UpdateProcedure2] @tbl, @SourceStr, NULL, 0, 0, 0, @now, NULL, @spname,NULL,@LogItemServer,@ProcessKey,0;
72
73 -- truncates destination table
74 IF COALESCE(@TruncateDestination,0) = 1 TRUNCATE TABLE [TFM].[FactTable1];
75
76 -- get real date range based on start of week and end of week dates
77
78 SET @StartDate = COALESCE(@StartDate,@dt);
79 SET @EndDate = COALESCE(@EndDate,@dt);
80 DECLARE @FOMDateKey bigint;
81 DECLARE @EOMDateKey bigint;
82 SELECT @FOMDateKey = dt.[Calendar_FOM_Date_Key] FROM [TFM].[DimDateTable] dt WHERE dt.[Calendar_FullDate] = @StartDate;
83 SELECT @EOMDateKey = dt.[Calendar_LOM_Date_Key] FROM [TFM].[DimDateTable] dt WHERE dt.[Calendar_FullDate] = @EndDate;
84
85 IF OBJECT_ID('tempdb..#tmpfacttbl1') IS NOT NULL DROP TABLE #tmpfactbs
86
87 -- capture results for use in the 'insert into destination' data set
88 CREATE TABLE #tmpfacttbl1
89 (
90 [TFM_Source] [nvarchar](100) NULL,
91 [TFM_Date_Key] [bigint] NULL,
92 [TFM_Status] [nvarchar](50) NULL,
93 [TFM_IsPass] [bit] NULL,
94 [Field1] [nvarchar](20) NULL,
95 [Field2] [nvarchar](20) NULL,
96 [Field3] [int] NULL,
97 [Field4] [nvarchar](100) NULL,
98 [Field5] [decimal](19, 5) NULL
99 ) ON [PRIMARY]
100
101 -- insert into destination
102 INSERT INTO #tmpfactbs
103 (
104 [TFM_Source]
105 , [TFM_Date_Key]
106 , [TFM_Status]
107 , [TFM_IsPass]
108 , [Field1]
109 , [Field2]
110 , [Field4]
111 , [Field5]
112
113 )
114 SELECT [TFM_Source]
115 , [TFM_Date_Key]
116 , [TFM_Status]
117 , [TFM_IsPass]
118 , [Field1]
119 , [Field2]
120 , [Field4]
121 , [Field5]
122 FROM
123 (
124 SELECT
125 CONVERT(nvarchar(100),@SourceStr) as [TFM_Source]
126 , CONVERT(int,COALESCE(REPLACE(CONVERT(varchar(10),@dt,102),'.',''),0)) as [TFM_Date_Key]
127 , CONVERT(nvarchar(50),@PassStatus) as [TFM_Status]
128 , CONVERT(bit,1) as [TFM_IsPass]
129 , CONVERT(nvarchar(20),COALESCE(LTRIM(RTRIM(bum.[Field1])),'0000')) as [Alias1]
130 , CONVERT(nvarchar(20),COALESCE(LTRIM(RTRIM(cmv.[Field2])),'0000')) as [Alias2]
131 , CONVERT(datetime, b.[Field33]) as [Field33]
132 , CONVERT(nvarchar(10), dt.Calendar_MonthNameShort) as [LoadMonth]
133 , CONVERT(int, dt.Calendar_Year) as [LoadYear]
134 , CAST(SUM(CONVERT(decimal(19, 5), CASE b.[Field11]
135 WHEN '' THEN '0'
136 ELSE b.[Alias10]
137 END)) as decimal(19, 5)) as [Field11]
138 , CAST(SUM(CONVERT(decimal(19, 5), CASE b.[Field9]
139 WHEN '' THEN '0'
140 ELSE b.[Field9]
141 END) -
142 CONVERT(decimal(19, 5), CASE b.[Field11]
143 WHEN '' THEN '0'
144 ELSE b.[Field11]
145 END)) as decimal(19, 5)) as [Alias11]
146
147 , CAST(SUM(CONVERT(decimal(19, 5), CASE b.[Field12]
148 WHEN '' THEN '0'
149 ELSE b.[Field12]
150 END)) as decimal(19, 5)) as [Alias12]
151 , CAST(SUM(CONVERT(decimal(19, 5), CASE b.[Field13]
152 WHEN '' THEN '0'
153 ELSE b.[Field13]
154 END)) as decimal(19, 5)) as [Alias13]
155 , CAST(SUM(CONVERT(decimal(19, 5), CASE b.[Field14]
156 WHEN '' THEN '0'
157 ELSE b.[Field14]
158 END)) as decimal(19, 5)) as [Alias14]
159 , CAST(SUM(CONVERT(decimal(19, 5), CASE b.[Field15]
160 WHEN '' THEN '0'
161 ELSE b.[Field15]
162 END)) as decimal(19, 5)) as [Alias15]
163 , CAST(SUM(CONVERT(decimal(19, 5), CASE b.[Field16]
164 WHEN '' THEN '0'
165 ELSE b.[Field16]
166 END)) as decimal(19, 5)) as [Alias16]
167 , CAST(SUM(CONVERT(decimal(19, 5), CASE b.[Field17]
168 WHEN '' THEN '0'
169 ELSE b.[Field17]
170 END)) as decimal(19, 5)) as [Alias17]
171 , CAST(AVG(CONVERT(decimal(19, 5), CASE b.[Field18]
172 WHEN '' THEN '0'
173 ELSE b.[Field18]
174 END)) as decimal(19, 5)) as [Alias19]
175 , CAST(SUM(CONVERT(decimal(19, 5), CASE b.[Field19]
176 WHEN '' THEN '0'
177 ELSE b.[Field19]
178 END)) as decimal(19, 5)) as [Alias18]
179 , CAST(SUM(CONVERT(decimal(19, 5), CASE b.[Field20]
180 WHEN '' THEN '0'
181 ELSE b.[Field20]
182 END)) as decimal(19, 5)) as [Alias20]
183 , CAST(SUM(CONVERT(decimal(19, 5), CASE b.[Field21]
184 WHEN '' THEN '0'
185 ELSE b.[Field21]
186 END)) as decimal(19, 5)) as [Alias21]
187 , CAST(SUM(CONVERT(decimal(19, 5), CASE b.[Field22]
188 WHEN '' THEN '0'
189 ELSE b.[Field22]
190 END)) as decimal(19, 5)) as [Alias22]
191 , CAST(SUM(CONVERT(decimal(19, 5), CASE b.[Field23]
192 WHEN '' THEN '0'
193 ELSE b.[Field23]
194 END) +
195 CONVERT(decimal(19, 5), CASE b.[Field24]
196 WHEN '' THEN '0'
197 ELSE b.[Field24]
198 END)) as decimal(19, 5)) as [Alias23]
199
200 , CAST(AVG(CONVERT(decimal(19, 5), CASE b.[Field25]
201 WHEN '' THEN '0'
202 ELSE b.[Field25]
203 END)) as decimal(19, 5)) as [Alias24]
204 , CAST(SUM(CONVERT(decimal(19, 5), CASE b.[Field26]
205 WHEN '' THEN '0'
206 ELSE b.[Field26]
207 END)) as decimal(19, 5)) as [Alias25]
208
209 FROM [TFM].[Table1] b
210 INNER JOIN [TFM].[Ref_Table2] bm
211 ON b.[Field32] = bm.[Field1]
212 INNER JOIN [TFM].[View1] cmv
213 ON bm.[Field2] = cmv.[Field3]
214 INNER JOIN [TFM].[Ref_Table3] bum
215 ON LEFT(cmv.[Field4], 3) = bum.[Field2]
216 INNER JOIN
217 (
218 SELECT DISTINCT
219 dt.[Date_Key]
220 , dt.[Calendar_FullDate]
221 , dt.[Calendar_MonthNameShort]
222 , dt.[Calendar_Year]
223 FROM
224 [TFM].[DimDateTable] dt
225 WHERE
226 dt.[Date_Key] >= @FOMDateKey
227 AND dt.[Date_Key] <= @EOMDateKey
228 ) dt
229 ON CONVERT(date,b.[Field33]) = CONVERT(date, dt.[Calendar_FullDate])
230
231 GROUP BY
232 CONVERT(nvarchar(20),COALESCE(LTRIM(RTRIM(bum.[Field1])),'0000'))
233 , CONVERT(nvarchar(20),COALESCE(LTRIM(RTRIM(cmv.[Field2])),'0000'))
234 , CONVERT(datetime, b.Field33)
235 , CONVERT(nvarchar(10), dt.Calendar_MonthNameShort)
236 , CONVERT(int, dt.Calendar_Year)
237 ) a
238
239 UNPIVOT
240 (
241 [Value] FOR [Statistic]
242 IN
243 (
244 [Alias10]
245 , [Alias11]
246 , [Alias12]
247 , [Alias13]
248 , [Alias14]
249 , [Alias15]
250 , [Alias16]
251 , [Alias17]
252 , [Alias18]
253 , [Alias19]
254 , [Alias20]
255 , [Alias21]
256 , [Alias22]
257 , [Alias23]
258 , [Alias24]
259 , [Alias25]
260 )
261 ) as [Value];
262
263
264 WITH ctedt
265 AS
266 (
267 SELECT DISTINCT
268 dt.[Date_Key]
269 , dt.[Calendar_FullDate]
270 , sdt.[Calendar_FullDate] as [Calendar_FOM_FullDate]
271 , edt.[Calendar_FullDate] as [Calendar_LOM_FullDate]
272 , dt.[Calendar_FOM_Date_Key]
273 , dt.[Calendar_LOM_Date_Key]
274 FROM
275 [TFM].[DimDateTable] dt
276 INNER JOIN [TFM].[DimDateTable] sdt
277 ON dt.[Calendar_FOM_Date_Key] = sdt.[Date_Key]
278 INNER JOIN [TFM].[DimDateTable] edt
279 ON dt.[Calendar_LOM_Date_Key] = edt.[Date_Key]
280 WHERE
281 dt.[Date_Key] >= @FOMDateKey
282 AND dt.[Date_Key] <= @EOMDateKey
283 )
284
285
286 INSERT INTO [TFM].[FactTable1]
287 (
288 [TFM_Source]
289 , [TFM_Date_Key]
290 , [TFM_Status]
291 , [TFM_IsPass]
292 , [Field1]
293 , [Field2]
294 , [Field3]
295 , [Field4]
296 , [Field5]
297 , [SnapshotStartDate_Key]
298 , [SnapshotEndDate_Key]
299 )
300
301
302 SELECT tbs.[TFM_Source]
303 , tbs.[TFM_Date_Key]
304 , tbs.[TFM_Status]
305 , tbs.[TFM_IsPass]
306 , tbs.[Field1]
307 , tbs.[Field2]
308 , sa.[Field3]
309 , tbs.[Field4]
310 , tbs.[Field5]
311 , CONVERT(int, dd.[Calendar_FOM_Date_Key]) as [FOM_Date_Key]
312 , CONVERT(int, dd.[Calendar_LOM_Date_Key]) as [LOM_Date_Key]
313 FROM
314 #tmpfactbs tbs
315 INNER JOIN [TFM].[Ref_Table1] sa
316 ON tbs.[Field4] = sa.[Field2]
317 INNER JOIN ctedt dd
318 ON tbs.[TFM_Date_Key] = dd.[Date_Key]
319
320 IF OBJECT_ID('tempdb..#tmpfacttbl1') IS NOT NULL DROP TABLE #tmpfactbs
321 --capture record count
322 SET @ct = @@ROWCOUNT;
323
324 -- log in [ETL].[ETLEventLog]
325 IF COALESCE(@LogEvent,0) = 1
326 BEGIN
327 -- defines the body text of the email and for event log
328 SET @LogText = 'Transformation Preparation for ' + COALESCE(@tbl,'NOT_DEFINED') + ' has completed with ' + CONVERT(varchar(50),COALESCE(@ct,0)) + ' records inserted.';
329
330 -- captures in log
331 EXEC [ETL].[InsertLogProcedure1]
332 @LogItemKey = 'LOG_NOTETYPE_INFO'
333 , @LogItemValue = @LogText
334 , @LogItemXML = NULL
335 , @ProcessKey = @ProcessKey
336 , @BatchKey = @BatchKey
337 , @SchemaKey = @shname
338 , @SourceKey = @spname
339 , @LogItemServer = @LogItemServer
340 , @SendNotification = 0;
341 END
342
343 END TRY
344 BEGIN CATCH
345
346 DECLARE @ErrorMessage NVARCHAR(4000);
347 DECLARE @ErrorSeverity INT;
348 DECLARE @ErrorState INT;
349
350 -- captures errors in vars
351 SELECT
352 @ErrorMessage = ERROR_MESSAGE(),
353 @ErrorSeverity = ERROR_SEVERITY(),
354 @ErrorState = ERROR_STATE();
355
356 -- captures in log
357 EXEC [ETL].[InsertLogProcedure1]
358 @LogItemKey = 'LOG_NOTETYPE_ERR'
359 , @LogItemValue = @ErrorMessage
360 , @LogItemXML = NULL
361 , @ProcessKey = @ProcessKey
362 , @BatchKey = @BatchKey
363 , @SchemaKey = @shname
364 , @SourceKey = @spname
365 , @LogItemServer = @LogItemServer
366 , @SendNotification = 0;
367
368 -- raises error
369 RAISERROR (@ErrorMessage, -- Message text.
370 @ErrorSeverity, -- Severity.
371 @ErrorState -- State.
372 );
373 END CATCH;
374 END;
375
376GO