· 8 years ago · Feb 24, 2018, 08:40 PM
1-- CREATE A DATABASE
2USE master
3GO
4
5IF EXISTS (SELECT *FROM sys.sysdatabases WHERE name = 'DWDemo')
6BEGIN
7 USE DWDemo
8 EXEC sys.sp_cdc_disable_table
9 @source_schema = N'src',
10 @source_name = N'Clients',
11 @capture_instance = 'all'
12
13 EXEC sys.sp_cdc_disable_table
14 @source_schema = N'src',
15 @source_name = N'Fournisseurs',
16 @capture_instance = 'all'
17
18 EXEC sys.sp_cdc_disable_db
19
20 USE master
21 DROP DATABASE DWDemo
22END
23GO
24
25-- remove CDC jobs from SQL Agent
26DECLARE @numJobs int
27SET @numJobs = (SELECT count(*) FROM msdb.dbo.sysjobs_view WHERE name LIKE 'cdc.DWDemo%')
28WHILE @numJobs > 0
29BEGIN
30 DECLARE @jobName nvarchar(128)
31 SELECT @jobName = (SELECT MIN(name) FROM msdb.dbo.sysjobs_view WHERE name LIKE 'cdc.DWDemo%')
32 EXEC msdb.dbo.sp_delete_job @job_name = @jobName
33 SET @numJobs = (SELECT count(*) FROM msdb.dbo.sysjobs_view WHERE name LIKE 'cdc.DWDemo%')
34END
35GO
36
37CREATE DATABASE DWDemo
38GO
39
40USE DWDemo
41GO
42
43
44-- Create schemas
45CREATE SCHEMA dw
46GO
47
48CREATE SCHEMA src
49GO
50
51CREATE SCHEMA stg
52GO
53
54-- CREATE DIMENSION TABLES
55CREATE TABLE dw.dimProduit
56(ProductKey int identity NOT NULL PRIMARY KEY NONCLUSTERED,
57 ProductAltKey nvarchar(10) NOT NULL,
58 ProductName nvarchar(50) NULL,
59 ProductDescription nvarchar(100) NULL,
60 ProductCategoryName nvarchar(50))
61GO
62
63CREATE TABLE dw.DimGeographie
64 (GeographyKey int identity NOT NULL PRIMARY KEY NONCLUSTERED,
65 PostalCode nvarchar(15) NULL,
66 City nvarchar(50) NULL,
67 Region nvarchar(50) NULL,
68 Country nvarchar(50) NULL)
69GO
70
71 CREATE TABLE dw.dimClient
72(CustomerKey int identity NOT NULL PRIMARY KEY NONCLUSTERED,
73 CustomerAltKey nvarchar(10) NOT NULL,
74 CustomerName nvarchar(50) NULL,
75 CustomerEmail nvarchar(50) NULL,
76 CustomerGeographyKey int NULL REFERENCES dw.DimGeographie(GeographyKey),
77 CurrentRecord bit)
78GO
79
80
81CREATE TABLE dw.DimVendeur
82(SalespersonKey int identity NOT NULL PRIMARY KEY NONCLUSTERED,
83 SalesPersonAltKey nvarchar(10) NOT NULL,
84 SalespersonName nvarchar(50) NULL,
85 StoreName nvarchar(50) NULL,
86 StoreGeographyKey int NULL REFERENCES dw.DimGeographie(GeographyKey),
87 CurrentRecord bit)
88 GO
89
90 CREATE TABLE dw.DimFournisseur
91(ShipperKey int identity NOT NULL PRIMARY KEY NONCLUSTERED,
92 ShipperAltKey nvarchar(10) NOT NULL,
93 ShipperName nvarchar(50) NULL,
94 Deleted bit DEFAULT 0)
95GO
96
97CREATE TABLE dw.DimDate
98 (DateKey int NOT NULL PRIMARY KEY NONCLUSTERED,
99 DateAltKey datetime NOT NULL,
100 CalendarYear int NOT NULL,
101 CalendarQuarter int NOT NULL,
102 MonthOfYear int NOT NULL,
103 [MonthName] nvarchar(15) NOT NULL,
104 [DayOfMonth] int NOT NULL,
105 [DayOfWeek] int NOT NULL,
106 [DayName] nvarchar(15) NOT NULL,
107 FiscalYear int NOT NULL,
108 FiscalQuarter int NOT NULL)
109GO
110
111 -- CREATE A FACT TABLE
112 CREATE TABLE dw.FactBonsCommandes
113 (ProductKey int NOT NULL REFERENCES dw.dimProduit(ProductKey),
114 CustomerKey int NOT NULL REFERENCES dw.dimClient(CustomerKey),
115 SalespersonKey int NOT NULL REFERENCES dw.DimVendeur(SalespersonKey),
116 ShipperKey int NULL REFERENCES dw.DimFournisseur(ShipperKey),
117 OrderDateKey int NOT NULL REFERENCES dw.DimDate(DateKey),
118 OrderNo int NOT NULL,
119 ItemNo int NOT NULL,
120 Quantity int NOT NULL,
121 SalesAmount money NOT NULL,
122 Cost money NOT NULL
123 CONSTRAINT [PK_ FactSalesOrder] PRIMARY KEY NONCLUSTERED
124 (
125 [ProductKey],[CustomerKey],[SalesPersonKey],[OrderDateKey],[OrderNo],[ItemNo]
126 )
127 )
128GO
129
130-- POPULATE THE TABLES
131DECLARE @StartDate datetime
132DECLARE @EndDate datetime
133SET @StartDate = dateadd(YEAR, -1, getdate())
134SET @EndDate = dateadd(YEAR, 1, getdate())
135DECLARE @LoopDate datetime
136SET @LoopDate = @StartDate
137WHILE @LoopDate <= @EndDate
138BEGIN
139 INSERT INTO dw.DimDate VALUES
140 (
141 CAST(CONVERT(VARCHAR(8), @LoopDate, 112) AS int) , -- date key
142 @LoopDate, -- date alt key
143 Year(@LoopDate), -- calendar year
144 datepart(qq, @LoopDate), -- calendar quarter
145 Month(@LoopDate), -- month number of year
146 datename(mm, @LoopDate), -- month name
147 Day(@LoopDate), -- day number of month
148 datepart(dw, @LoopDate), -- day number of week
149 datename(dw, @LoopDate), -- day name of week
150 CASE
151 WHEN Month(@LoopDate) < 7 THEN Year(@LoopDate)
152 ELSE Year(@Loopdate) + 1
153 END, -- Fiscal year (assuming fiscal year runs from Jul to June)
154 CASE
155 WHEN Month(@LoopDate) IN (1, 2, 3) THEN 3
156 WHEN Month(@LoopDate) IN (4, 5, 6) THEN 4
157 WHEN Month(@LoopDate) IN (7, 8, 9) THEN 1
158 WHEN Month(@LoopDate) IN (10, 11, 12) THEN 2
159 END -- fiscal quarter
160 )
161 SET @LoopDate = DateAdd(dd, 1, @LoopDate)
162END
163GO
164
165INSERT INTO dw.DimGeographie
166VALUES
167('10001', 'New York', 'New York', 'United States')
168GO
169
170INSERT INTO dw.DimGeographie
171VALUES
172('98101', 'Seattle', 'Washington', 'United States')
173GO
174
175INSERT INTO dw.DimGeographie
176VALUES
177('90010', 'Los Angeles', 'California', 'United States')
178GO
179
180INSERT INTO dw.dimProduit
181VALUES
182('1', 'Red Racer 100', 'Red racing bike', 'Bikes')
183GO
184
185INSERT INTO dw.dimProduit
186VALUES
187('2', 'Blue Racer 100', 'Blue racing bike', 'Bikes')
188GO
189
190INSERT INTO dw.dimProduit
191VALUES
192('3', 'Racing Gloves', 'Gloves for racing', 'Clothing')
193GO
194
195INSERT INTO dw.dimProduit
196VALUES
197('4', 'Helmet', 'Cycling helmet', 'Clothing')
198GO
199
200INSERT INTO dw.dimClient
201VALUES
202('1', 'Ellen Adams', 'ellen@adatum.com', 1, 1)
203GO
204
205INSERT INTO dw.dimClient
206VALUES
207('2', 'Walter Harp', 'walter@northwindtraders.com', 1, 1)
208GO
209
210INSERT INTO dw.dimClient
211VALUES
212('3', 'Holly Holt', 'holly@wingtiptoys.com', 1, 1)
213GO
214
215INSERT INTO dw.dimClient
216VALUES
217('4', 'Jeff Price', 'jeff@proseware.com', 2, 1)
218GO
219
220INSERT INTO dw.dimClient
221VALUES
222('5', 'Roya Asbari', 'roya@adatum.com', 3, 1)
223GO
224
225INSERT INTO dw.DimVendeur
226VALUES
227('1', 'Wendy Khan', 'New York Bike Store', 1, 1)
228GO
229
230INSERT INTO dw.DimVendeur
231VALUES
232('2', 'Andy Jacobs', 'Seattle Bike Store', 2, 1)
233GO
234
235INSERT INTO dw.DimVendeur
236VALUES
237('3', 'Matt Berg', 'LA Bike Store', 3, 1)
238GO
239
240INSERT INTO dw.FactBonsCommandes
241VALUES
242(1, 1, 1, NULL, CAST(CONVERT(VARCHAR(8), dateadd(dd, -2, getdate()), 112) AS int), 1001, 1, 1, 200, 100)
243GO
244
245INSERT INTO dw.FactBonsCommandes
246VALUES
247(3, 1, 1, NULL, CAST(CONVERT(VARCHAR(8), dateadd(dd, -2, getdate()), 112) AS int), 1001, 2, 1, 20, 10)
248GO
249
250INSERT INTO dw.FactBonsCommandes
251VALUES
252(2, 2, 2, NULL, CAST(CONVERT(VARCHAR(8), dateadd(dd, -1, getdate()), 112) AS int), 1002, 1, 1, 200, 100)
253GO
254
255INSERT INTO dw.FactBonsCommandes
256VALUES
257(4, 3, 3, NULL, CAST(CONVERT(VARCHAR(8), dateadd(dd, -1, getdate()), 112) AS int), 1003, 1, 1, 25, 10)
258GO
259
260INSERT INTO dw.FactBonsCommandes
261VALUES
262(1, 4, 1, NULL, CAST(CONVERT(VARCHAR(8), dateadd(dd, -1, getdate()), 112) AS int), 1004, 1, 1, 200, 100)
263GO
264
265
266-- Create staging tables
267CREATE TABLE stg.Produits
268(ProductID int,
269 ProductName nvarchar(50),
270 ProductDescription nvarchar(100),
271 ProductCategoryName nvarchar(50))
272GO
273
274CREATE TABLE stg.Clients
275(CustomerID nvarchar(10) NOT NULL,
276 CustomerName nvarchar(50) NULL,
277 CustomerEmail nvarchar(50) NULL,
278 PostalCode nvarchar(15) NULL,
279 City nvarchar(50) NULL,
280 Region nvarchar(50) NULL,
281 Country nvarchar(50) NULL)
282GO
283
284CREATE TABLE stg.Vendeurs
285(SalesPersonID nvarchar(10) NOT NULL,
286 SalespersonName nvarchar(50) NULL,
287 StoreName nvarchar(50) NULL,
288 PostalCode nvarchar(15) NULL,
289 City nvarchar(50) NULL,
290 Region nvarchar(50) NULL,
291 Country nvarchar(50) NULL)
292GO
293
294 CREATE TABLE stg.FournisseurInserts
295(ShipperID nvarchar(10) NOT NULL,
296 ShipperName nvarchar(50) NULL)
297GO
298
299 CREATE TABLE stg.FournisseurUpdates
300(ShipperID nvarchar(10) NOT NULL,
301 ShipperName nvarchar(50) NULL)
302GO
303
304 CREATE TABLE stg.FournisseurDeletes
305(ShipperID nvarchar(10) NOT NULL,
306 ShipperName nvarchar(50) NULL)
307GO
308
309CREATE TABLE stg.BonsCommandes
310 (OrderNo int NOT NULL,
311 ItemNo int NOT NULL,
312 ProductID int,
313 CustomerID int,
314 SalespersonID int,
315 ShipperID int,
316 OrderDate datetime,
317 Quantity int NOT NULL,
318 SalesAmount money NOT NULL,
319 Cost money NOT NULL)
320GO
321
322CREATE TABLE stg.ExtractLog
323(SourceName varchar(20),
324 LastExtractTime datetime,
325 LastExtractedVersion int)
326GO
327
328-- Insert extract log data
329INSERT INTO stg.ExtractLog
330VALUES
331('Produits', dateadd(DAY, -1, getdate()), 0)
332GO
333
334INSERT INTO stg.ExtractLog
335VALUES
336('Clients', dateadd(DAY, -1, getdate()), 0)
337GO
338
339INSERT INTO stg.ExtractLog
340VALUES
341('Vendeurs', dateadd(DAY, -1, getdate()), 0)
342GO
343
344INSERT INTO stg.ExtractLog
345VALUES
346('BonsCommandes', dateadd(DAY, -1, getdate()), 0)
347GO
348
349-- Create source tables
350CREATE TABLE src.Produits
351(ProductID int PRIMARY KEY,
352 ProductName nvarchar(50),
353 ProductDescription nvarchar(100),
354 ProductCategoryName nvarchar(50),
355 LastModified datetime)
356GO
357
358CREATE TABLE src.Clients
359(CustomerID int PRIMARY KEY,
360 CustomerName nvarchar(50) NULL,
361 CustomerEmail nvarchar(50) NULL,
362 PostalCode nvarchar(15) NULL,
363 City nvarchar(50) NULL,
364 Region nvarchar(50) NULL,
365 Country nvarchar(50) NULL)
366GO
367
368CREATE TABLE src.Vendeurs
369(SalesPersonID int PRIMARY KEY,
370 SalespersonName nvarchar(50) NULL,
371 StoreName nvarchar(50) NULL,
372 PostalCode nvarchar(15) NULL,
373 City nvarchar(50) NULL,
374 Region nvarchar(50) NULL,
375 Country nvarchar(50) NULL)
376GO
377
378 CREATE TABLE src.Fournisseurs
379(ShipperID nvarchar(10) NOT NULL PRIMARY KEY,
380 ShipperName nvarchar(50) NULL)
381GO
382
383CREATE TABLE src.BonsCommandes
384 (OrderNo int NOT NULL,
385 ItemNo int NOT NULL,
386 ProductID int,
387 CustomerID int,
388 SalespersonID int,
389 ShipperID int,
390 OrderDate datetime,
391 Quantity int NOT NULL,
392 SalesAmount money NOT NULL,
393 Cost money NOT NULL)
394GO
395
396-- Insert source data
397INSERT INTO src.Clients
398VALUES
399(1, 'Ellen Adams', 'ellen@adatum.com','10001', 'New York', 'New York', 'United States')
400GO
401
402INSERT INTO src.Clients
403VALUES
404(2, 'Walter Harp', 'walter@northwindtraders.com', '10001', 'New York', 'New York', 'United States')
405GO
406
407INSERT INTO src.Clients
408VALUES
409(3, 'Holly Holt', 'holly@wingtiptoys.com', '10001', 'New York', 'New York', 'United States')
410GO
411
412INSERT INTO src.Clients
413VALUES
414(4, 'Jeff Price', 'jeff@proseware.com', '98101', 'Seattle', 'Washington', 'United States')
415GO
416
417INSERT INTO src.Clients
418VALUES
419(5, 'Roya Asbari', 'roya@adatum.com', '90010', 'Los Angeles', 'California', 'United States')
420GO
421
422INSERT INTO src.Vendeurs
423VALUES
424(1, 'Wendy Khan', 'New York Bike Store', '10001', 'New York', 'New York', 'United States')
425GO
426
427INSERT INTO src.Vendeurs
428VALUES
429(2, 'Andy Jacobs', 'Seattle Bike Store', '98101', 'Seattle', 'Washington', 'United States')
430GO
431
432INSERT INTO src.Vendeurs
433VALUES
434(3, 'Matt Berg', 'LA Bike Store', '90010', 'Los Angeles', 'California', 'United States')
435GO
436
437INSERT INTO src.Produits
438VALUES
439(1, 'Red Racer 100', 'Red racing bike', 'Bikes', dateadd(MONTH, -1, getdate()))
440GO
441
442INSERT INTO src.Produits
443VALUES
444(2, 'Blue Racer 100', 'Blue racing bike', 'Bikes', dateadd(MONTH, -1, getdate()))
445GO
446
447INSERT INTO src.Produits
448VALUES
449(3, 'Racing Gloves', 'Gloves for racing', 'Clothing', dateadd(MONTH, -1, getdate()))
450GO
451
452INSERT INTO src.Produits
453VALUES
454(4, 'Helmet', 'Cycling helmet', 'Clothing', dateadd(MONTH, -1, getdate()))
455GO
456
457INSERT INTO src.Fournisseurs
458VALUES
459(1, 'Shipper One')
460GO
461
462INSERT INTO src.Fournisseurs
463VALUES
464(2, 'Shipper Two')
465GO
466
467INSERT INTO src.Fournisseurs
468VALUES
469(3, 'Shipper Three')
470GO
471
472INSERT INTO src.Fournisseurs
473VALUES
474(4, 'Shipper Four')
475GO
476
477INSERT INTO src.BonsCommandes
478VALUES
479(1001, 1, 1, 1, 1, 1, dateadd(dd, -2, getdate()), 1, 200, 100)
480GO
481
482INSERT INTO src.BonsCommandes
483VALUES
484(1001, 2, 3, 1, 1, 2, dateadd(dd, -2, getdate()), 1, 20, 10)
485GO
486
487INSERT INTO src.BonsCommandes
488VALUES
489(1002, 1, 2, 2, 2, 3, dateadd(dd, -1, getdate()), 1, 200, 100)
490GO
491
492INSERT INTO src.BonsCommandes
493VALUES
494(1003, 1, 4, 3, 3, 4, dateadd(dd, -1, getdate()), 1, 25, 10)
495GO
496
497INSERT INTO src.BonsCommandes
498VALUES
499(1004, 1, 1, 4, 1, 1, dateadd(dd, -1, getdate()), 1, 200, 100)
500GO