· 6 years ago · Mar 05, 2019, 02:36 PM
1/*============================================================================
2 File: instawdwdb.sql
3
4 Summary: Creates the AdventureWorks data warehouse sample database.
5
6 Date: June 29, 2005
7
8 SQL Server Version: 9.00.1273.00
9------------------------------------------------------------------------------
10 This file is part of the Microsoft SQL Server Code Samples.
11
12 Copyright (C) Microsoft Corporation. All rights reserved.
13
14 This source code is intended only as a supplement to Microsoft
15 Development Tools and/or on-line documentation. See these other
16 materials for detailed information regarding Microsoft code samples.
17
18 THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
19 KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
20 IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
21 PARTICULAR PURPOSE.
22============================================================================*/
23
24PRINT CONVERT(VARCHAR(1000), @@VERSION);
25GO
26
27USE [master]
28GO
29
30-- ****************************************
31-- Drop Database
32-- ****************************************
33PRINT '';
34PRINT '*** Dropping Database';
35GO
36
37IF EXISTS (SELECT [name] FROM [master].[sys].[databases] WHERE [name] = N'AdventureWorksDW')
38 DROP DATABASE [AdventureWorksDW];
39GO
40
41-- ****************************************
42-- Create Database
43-- ****************************************
44PRINT '';
45PRINT '*** Creating Database';
46GO
47
48DECLARE
49 @sql_path NVARCHAR(256);
50
51SELECT @sql_path = SUBSTRING([physical_name], 1, CHARINDEX(N'master.mdf', LOWER([physical_name])) - 1)
52FROM [master].[sys].[master_files]
53WHERE [database_id] = 1
54 AND [file_id] = 1;
55
56EXECUTE (N'CREATE DATABASE [AdventureWorksDW] ON (NAME = ''AdventureWorksDW_Data'',
57 FILENAME = ''' + @sql_path + 'AdventureWorksDW_Data.mdf'', SIZE = 64, FILEGROWTH = 4) LOG ON (NAME = ''AdventureWorksDW_Log'',
58 FILENAME = ''' + @sql_path + 'AdventureWorksDW_Log.LDF'' , SIZE = 2, FILEGROWTH = 8)');
59GO
60
61ALTER DATABASE AdventureWorksDW
62SET RECOVERY SIMPLE,
63 ANSI_NULLS ON,
64 ANSI_PADDING ON,
65 ANSI_WARNINGS ON,
66 ARITHABORT ON,
67 CONCAT_NULL_YIELDS_NULL ON,
68 QUOTED_IDENTIFIER ON,
69 NUMERIC_ROUNDABORT OFF,
70 PAGE_VERIFY CHECKSUM,
71 ALLOW_SNAPSHOT_ISOLATION ON;
72GO
73
74USE [AdventureWorksDW]
75GO
76
77-- ****************************************
78-- Create DDL Trigger for Database
79-- ****************************************
80PRINT '';
81PRINT '*** Creating DDL Trigger for Database';
82GO
83
84SET QUOTED_IDENTIFIER ON;
85GO
86
87CREATE TABLE [dbo].[DatabaseLog](
88 [DatabaseLogID] [int] IDENTITY (1, 1) NOT NULL,
89 [PostTime] [datetime] NOT NULL,
90 [DatabaseUser] [sysname] NOT NULL,
91 [Event] [sysname] NOT NULL,
92 [Schema] [sysname] NULL,
93 [Object] [sysname] NULL,
94 [TSQL] [nvarchar](max) NOT NULL,
95 [XmlEvent] [xml] NOT NULL
96) ON [PRIMARY];
97GO
98
99CREATE TRIGGER [ddlDatabaseTriggerLog]
100ON DATABASE
101FOR DDL_DATABASE_LEVEL_EVENTS
102AS
103BEGIN
104 SET NOCOUNT ON
105
106 DECLARE @data XML;
107 DECLARE @schema sysname;
108 DECLARE @object sysname;
109 DECLARE @eventType sysname;
110
111 SET @data = EVENTDATA();
112 SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
113 SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
114 SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')
115
116 IF @object IS NOT NULL
117 PRINT ' ' + @eventType + ' - ' + @schema + '.' + @object;
118 ELSE
119 PRINT ' ' + @eventType + ' - ' + @schema;
120
121 IF @eventType IS NULL
122 PRINT CONVERT(nvarchar(max), @data);
123
124 INSERT [dbo].[DatabaseLog]
125 (
126 [PostTime],
127 [DatabaseUser],
128 [Event],
129 [Schema],
130 [Object],
131 [TSQL],
132 [XmlEvent]
133 )
134 VALUES
135 (
136 GETDATE(),
137 CONVERT(sysname, CURRENT_USER),
138 @eventType,
139 CONVERT(sysname, @schema),
140 CONVERT(sysname, @object),
141 @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),
142 @data
143 );
144END;
145GO
146
147-- ******************************************************
148-- Create tables
149-- ******************************************************
150PRINT '';
151PRINT '*** Creating Tables';
152GO
153
154CREATE TABLE [dbo].[AdventureWorksDWBuildVersion] (
155 [DBVersion] [nvarchar] (50) NULL,
156 [VersionDate] [datetime] NULL
157) ON [PRIMARY];
158
159CREATE TABLE [dbo].[DimAccount] (
160 [AccountKey] [int] IDENTITY(1, 1) NOT NULL,
161 [ParentAccountKey] [int] NULL,
162 [AccountCodeAlternateKey] [int] NULL,
163 [ParentAccountCodeAlternateKey] [int] NULL,
164 [AccountDescription] [nvarchar] (50) NULL,
165 [AccountType] [nvarchar] (50) NULL,
166 [Operator] [nvarchar] (50) NULL,
167 [CustomMembers] [nvarchar] (300) NULL,
168 [ValueType] [nvarchar] (50) NULL,
169 [CustomMemberOptions] [nvarchar] (200) NULL
170) ON [PRIMARY];
171
172CREATE TABLE [dbo].[DimCurrency] (
173 [CurrencyKey] [int] IDENTITY(1, 1) NOT NULL,
174 [CurrencyAlternateKey] [nchar] (3) NOT NULL,
175 [CurrencyName] [nvarchar] (50) NOT NULL
176) ON [PRIMARY];
177
178CREATE TABLE [dbo].[DimCustomer] (
179 [CustomerKey] [int] IDENTITY(1, 1) NOT NULL,
180 [GeographyKey] [int] NULL,
181 [CustomerAlternateKey] [nvarchar] (15) NOT NULL,
182 [Title] [nvarchar] (8) NULL,
183 [FirstName] [nvarchar] (50) NULL,
184 [MiddleName] [nvarchar] (50) NULL,
185 [LastName] [nvarchar] (50) NULL,
186 [NameStyle] [bit] NULL,
187 [BirthDate] [datetime] NULL,
188 [MaritalStatus] [nchar] (1) NULL,
189 [Suffix] [nvarchar] (10) NULL,
190 [Gender] [nvarchar] (1) NULL,
191 [EmailAddress] [nvarchar] (50) NULL,
192 [YearlyIncome] [money] NULL,
193 [TotalChildren] [tinyint] NULL,
194 [NumberChildrenAtHome] [tinyint] NULL,
195 [EnglishEducation] [nvarchar] (40) NULL,
196 [SpanishEducation] [nvarchar] (40) NULL,
197 [FrenchEducation] [nvarchar] (40) NULL,
198 [EnglishOccupation] [nvarchar] (100) NULL,
199 [SpanishOccupation] [nvarchar] (100) NULL,
200 [FrenchOccupation] [nvarchar] (100) NULL,
201 [HouseOwnerFlag] [nchar] (1) NULL,
202 [NumberCarsOwned] [tinyint] NULL,
203 [AddressLine1] [nvarchar] (120) NULL,
204 [AddressLine2] [nvarchar] (120) NULL,
205 [Phone] [nvarchar] (20) NULL,
206 [DateFirstPurchase] [datetime] NULL,
207 [CommuteDistance] [nvarchar] (15) NULL
208) ON [PRIMARY];
209
210CREATE TABLE [dbo].[DimDepartmentGroup] (
211 [DepartmentGroupKey] [int] IDENTITY(1, 1) NOT NULL,
212 [ParentDepartmentGroupKey] [int] NULL,
213 [DepartmentGroupName] [nvarchar] (50) NULL
214) ON [PRIMARY];
215GO
216
217CREATE TABLE [dbo].[DimEmployee] (
218 [EmployeeKey] [int] IDENTITY(1, 1) NOT NULL,
219 [ParentEmployeeKey] [int] NULL,
220 [EmployeeNationalIDAlternateKey] [nvarchar] (15) NULL,
221 [ParentEmployeeNationalIDAlternateKey] [nvarchar] (15) NULL,
222 [SalesTerritoryKey] [int] NULL,
223 [FirstName] [nvarchar] (50) NOT NULL,
224 [LastName] [nvarchar] (50) NOT NULL,
225 [MiddleName] [nvarchar] (50) NULL,
226 [NameStyle] [bit] NOT NULL,
227 [Title] [nvarchar] (50) NULL,
228 [HireDate] [datetime] NULL,
229 [BirthDate] [datetime] NULL,
230 [LoginID] [nvarchar] (256) NULL,
231 [EmailAddress] [nvarchar] (50) NULL,
232 [Phone] [nvarchar] (25) NULL,
233 [MaritalStatus] [nchar] (1) NULL,
234 [EmergencyContactName] [nvarchar] (50) NULL,
235 [EmergencyContactPhone] [nvarchar] (25) NULL,
236 [SalariedFlag] [bit] NULL,
237 [Gender] [nchar] (1) NULL,
238 [PayFrequency] [tinyint] NULL,
239 [BaseRate] [money] NULL,
240 [VacationHours] [smallint] NULL,
241 [SickLeaveHours] [smallint] NULL,
242 [CurrentFlag] [bit] NOT NULL,
243 [SalesPersonFlag] [bit] NOT NULL,
244 [DepartmentName] [nvarchar] (50) NULL,
245 [StartDate] [datetime] NULL,
246 [EndDate] [datetime] NULL,
247 [Status] [nvarchar] (50)
248) ON [PRIMARY];
249
250CREATE TABLE [dbo].[DimGeography] (
251 [GeographyKey] [int] IDENTITY(1, 1) NOT NULL,
252 [City] [nvarchar] (30) NULL,
253 [StateProvinceCode] [nvarchar] (3) NULL,
254 [StateProvinceName] [nvarchar] (50) NULL,
255 [CountryRegionCode] [nvarchar] (3) NULL,
256 [EnglishCountryRegionName] [nvarchar] (50) NULL,
257 [SpanishCountryRegionName] [nvarchar] (50) NULL,
258 [FrenchCountryRegionName] [nvarchar] (50) NULL,
259 [PostalCode] [nvarchar] (15) NULL,
260 [SalesTerritoryKey] [int] NULL
261) ON [PRIMARY];
262
263CREATE TABLE [dbo].[DimOrganization] (
264 [OrganizationKey] [int] IDENTITY(1, 1) NOT NULL,
265 [ParentOrganizationKey] [int] NULL,
266 [PercentageOfOwnership] [nvarchar] (16) NULL,
267 [OrganizationName] [nvarchar] (50) NULL,
268 [CurrencyKey] [int] NULL
269) ON [PRIMARY];
270
271CREATE TABLE [dbo].[DimProduct] (
272 [ProductKey] [int] IDENTITY(1, 1) NOT NULL,
273 [ProductAlternateKey] [nvarchar] (25) NULL,
274 [ProductSubcategoryKey] [int] NULL,
275 [WeightUnitMeasureCode] [nchar] (3) NULL,
276 [SizeUnitMeasureCode] [nchar] (3) NULL,
277 [EnglishProductName] [nvarchar] (50) NOT NULL,
278 [SpanishProductName] [nvarchar] (50) NOT NULL,
279 [FrenchProductName] [nvarchar] (50) NOT NULL,
280 [StandardCost] [money] NULL,
281 [FinishedGoodsFlag] [bit] NOT NULL,
282 [Color] [nvarchar] (15) NOT NULL,
283 [SafetyStockLevel] [smallint] NULL,
284 [ReorderPoint] [smallint] NULL,
285 [ListPrice] [money] NULL,
286 [Size] [nvarchar] (50) NULL,
287 [SizeRange] [nvarchar] (50) NULL,
288 [Weight] [float] NULL,
289 [DaysToManufacture] [int] NULL,
290 [ProductLine] [nchar] (2) NULL,
291 [DealerPrice] [money] NULL,
292 [Class] [nchar] (2) NULL,
293 [Style] [nchar] (2) NULL,
294 [ModelName] [nvarchar] (50) NULL,
295 [LargePhoto] [varbinary] (max) NULL,
296 [EnglishDescription] [nvarchar] (400) NULL,
297 [FrenchDescription] [nvarchar] (400) COLLATE French_CI_AS NULL,
298 [ChineseDescription] [nvarchar] (400) COLLATE Chinese_PRC_CI_AI NULL,
299 [ArabicDescription] [nvarchar] (400) COLLATE Arabic_CI_AS NULL,
300 [HebrewDescription] [nvarchar] (400) COLLATE Hebrew_CI_AS NULL,
301 [ThaiDescription] [nvarchar] (400) COLLATE Thai_CI_AS NULL,
302 [StartDate] [datetime] NULL,
303 [EndDate] [datetime] NULL,
304 [Status] [nvarchar] (7) NULL
305) ON [PRIMARY];
306
307CREATE TABLE [dbo].[DimProductCategory] (
308 [ProductCategoryKey] [int] IDENTITY(1, 1) NOT NULL,
309 [ProductCategoryAlternateKey] [int] NULL,
310 [EnglishProductCategoryName] [nvarchar] (50) NOT NULL,
311 [SpanishProductCategoryName] [nvarchar] (50) NOT NULL,
312 [FrenchProductCategoryName] [nvarchar] (50) NOT NULL
313) ON [PRIMARY];
314
315CREATE TABLE [dbo].[DimProductSubcategory] (
316 [ProductSubcategoryKey] [int] IDENTITY(1, 1) NOT NULL,
317 [ProductSubcategoryAlternateKey] [int] NULL,
318 [EnglishProductSubcategoryName] [nvarchar] (50) NOT NULL,
319 [SpanishProductSubcategoryName] [nvarchar] (50) NOT NULL,
320 [FrenchProductSubcategoryName] [nvarchar] (50) NOT NULL,
321 [ProductCategoryKey] [int] NULL
322) ON [PRIMARY];
323
324CREATE TABLE [dbo].[DimPromotion] (
325 [PromotionKey] [int] IDENTITY(1, 1) NOT NULL,
326 [PromotionAlternateKey] [int] NULL,
327 [EnglishPromotionName] [nvarchar] (255) NULL,
328 [SpanishPromotionName] [nvarchar] (255) NULL,
329 [FrenchPromotionName] [nvarchar] (255) NULL,
330 [DiscountPct] [float] NULL,
331 [EnglishPromotionType] [nvarchar] (50) NULL,
332 [SpanishPromotionType] [nvarchar] (50) NULL,
333 [FrenchPromotionType] [nvarchar] (50) NULL,
334 [EnglishPromotionCategory] [nvarchar] (50) NULL,
335 [SpanishPromotionCategory] [nvarchar] (50) NULL,
336 [FrenchPromotionCategory] [nvarchar] (50) NULL,
337 [StartDate] [datetime] NOT NULL,
338 [EndDate] [datetime] NULL,
339 [MinQty] [int] NULL,
340 [MaxQty] [int] NULL
341) ON [PRIMARY];
342
343CREATE TABLE [dbo].[DimReseller] (
344 [ResellerKey] [int] IDENTITY(1, 1) NOT NULL,
345 [GeographyKey] [int] NULL,
346 [ResellerAlternateKey] [nvarchar] (15) NULL,
347 [Phone] [nvarchar] (25) NULL,
348 [BusinessType] [varchar] (20) NOT NULL,
349 [ResellerName] [nvarchar] (50) NOT NULL,
350 [NumberEmployees] [int] NULL,
351 [OrderFrequency] [char] (1) NULL,
352 [OrderMonth] [tinyint] NULL,
353 [FirstOrderYear] [int] NULL,
354 [LastOrderYear] [int] NULL,
355 [ProductLine] [nvarchar] (50) NULL,
356 [AddressLine1] [nvarchar] (60) NULL,
357 [AddressLine2] [nvarchar] (60) NULL,
358 [AnnualSales] [money] NULL,
359 [BankName] [nvarchar] (50) NULL,
360 [MinPaymentType] [tinyint] NULL,
361 [MinPaymentAmount] [money] NULL,
362 [AnnualRevenue] [money] NULL,
363 [YearOpened] [int] NULL
364) ON [PRIMARY];
365
366CREATE TABLE [dbo].[DimSalesReason] (
367 [SalesReasonKey] [int] IDENTITY(1, 1) NOT NULL,
368 [SalesReasonAlternateKey] INT NOT NULL,
369 [SalesReasonName] [nvarchar] (50) NOT NULL,
370 [SalesReasonReasonType] [nvarchar] (50) NOT NULL
371) ON [PRIMARY];
372
373CREATE TABLE [dbo].[DimSalesTerritory] (
374 [SalesTerritoryKey] [int] IDENTITY(1, 1) NOT NULL,
375 [SalesTerritoryAlternateKey] [int] NULL,
376 [SalesTerritoryRegion] [nvarchar] (50) NOT NULL,
377 [SalesTerritoryCountry] [nvarchar] (50) NOT NULL,
378 [SalesTerritoryGroup] [nvarchar] (50) NULL
379) ON [PRIMARY];
380
381CREATE TABLE [dbo].[DimScenario] (
382 [ScenarioKey] [int] IDENTITY(1, 1) NOT NULL,
383 [ScenarioName] [nvarchar] (50) NULL
384) ON [PRIMARY];
385
386CREATE TABLE [dbo].[DimTime] (
387 [TimeKey] [int] IDENTITY(1, 1) NOT NULL,
388 [FullDateAlternateKey] [datetime] NULL,
389 [DayNumberOfWeek] [tinyint] NULL,
390 [EnglishDayNameOfWeek] [nvarchar] (10) NULL,
391 [SpanishDayNameOfWeek] [nvarchar] (10) NULL,
392 [FrenchDayNameOfWeek] [nvarchar] (10) NULL,
393 [DayNumberOfMonth] [tinyint] NULL,
394 [DayNumberOfYear] [smallint] NULL,
395 [WeekNumberOfYear] [tinyint] NULL,
396 [EnglishMonthName] [nvarchar] (10) NULL,
397 [SpanishMonthName] [nvarchar] (10) NULL,
398 [FrenchMonthName] [nvarchar] (10) NULL,
399 [MonthNumberOfYear] [tinyint] NULL,
400 [CalendarQuarter] [tinyint] NULL,
401 [CalendarYear] [char] (4) NULL,
402 [CalendarSemester] [tinyint] NULL,
403 [FiscalQuarter] [tinyint] NULL,
404 [FiscalYear] [char] (4) NULL,
405 [FiscalSemester] [tinyint] NULL
406) ON [PRIMARY];
407
408CREATE TABLE [dbo].[FactCurrencyRate] (
409 [CurrencyKey] [int] NOT NULL,
410 [TimeKey] [int] NOT NULL,
411 [AverageRate] [float] NOT NULL,
412 [EndOfDayRate] [float] NOT NULL
413) ON [PRIMARY];
414
415CREATE TABLE [dbo].[FactFinance] (
416 [TimeKey] [int] NULL,
417 [OrganizationKey] [int] NULL,
418 [DepartmentGroupKey] [int] NULL,
419 [ScenarioKey] [int] NULL,
420 [AccountKey] [int] NULL,
421 [Amount] [float] NULL
422) ON [PRIMARY];
423
424CREATE TABLE [dbo].[FactInternetSales] (
425 [ProductKey] [int] NOT NULL,
426 [OrderDateKey] [int] NOT NULL,
427 [DueDateKey] [int] NOT NULL,
428 [ShipDateKey] [int] NOT NULL,
429 [CustomerKey] [int] NOT NULL,
430 [PromotionKey] [int] NOT NULL,
431 [CurrencyKey] [int] NOT NULL,
432 [SalesTerritoryKey] [int] NOT NULL,
433 [SalesOrderNumber] [nvarchar] (20) NOT NULL,
434 [SalesOrderLineNumber] [tinyint] NOT NULL,
435 [RevisionNumber] [tinyint] NULL,
436 [OrderQuantity] [smallint] NULL,
437 [UnitPrice] [money] NULL,
438 [ExtendedAmount] [money] NULL,
439 [UnitPriceDiscountPct] [float] NULL,
440 [DiscountAmount] [float] NULL,
441 [ProductStandardCost] [money] NULL,
442 [TotalProductCost] [money] NULL,
443 [SalesAmount] [money] NULL,
444 [TaxAmt] [money] NULL,
445 [Freight] [money] NULL,
446 [CarrierTrackingNumber] [nvarchar] (25) NULL,
447 [CustomerPONumber] [nvarchar] (25) NULL
448) ON [PRIMARY];
449
450CREATE TABLE [dbo].[FactResellerSales] (
451 [ProductKey] [int] NOT NULL,
452 [OrderDateKey] [int] NOT NULL,
453 [DueDateKey] [int] NOT NULL,
454 [ShipDateKey] [int] NOT NULL,
455 [ResellerKey] [int] NOT NULL,
456 [EmployeeKey] [int] NOT NULL,
457 [PromotionKey] [int] NOT NULL,
458 [CurrencyKey] [int] NOT NULL,
459 [SalesTerritoryKey] [int] NOT NULL,
460 [SalesOrderNumber] [nvarchar] (20) NOT NULL,
461 [SalesOrderLineNumber] [tinyint] NOT NULL,
462 [RevisionNumber] [tinyint] NULL,
463 [OrderQuantity] [smallint] NULL,
464 [UnitPrice] [money] NULL,
465 [ExtendedAmount] [money] NULL,
466 [UnitPriceDiscountPct] [float] NULL,
467 [DiscountAmount] [float] NULL,
468 [ProductStandardCost] [money] NULL,
469 [TotalProductCost] [money] NULL,
470 [SalesAmount] [money] NULL,
471 [TaxAmt] [money] NULL,
472 [Freight] [money] NULL,
473 [CarrierTrackingNumber] [nvarchar] (25) NULL,
474 [CustomerPONumber] [nvarchar] (25) NULL
475) ON [PRIMARY];
476
477CREATE TABLE [dbo].[FactInternetSalesReason] (
478 [SalesOrderNumber] [nvarchar] (20) NOT NULL,
479 [SalesOrderLineNumber] [tinyint] NOT NULL,
480 [SalesReasonKey] [int] NOT NULL
481) ON [PRIMARY];
482
483CREATE TABLE [dbo].[FactSalesQuota] (
484 [EmployeeKey] [int] NULL,
485 [TimeKey] [int] NULL,
486 [CalendarYear] [char] (4) NULL,
487 [CalendarQuarter] [tinyint] NULL,
488 [SalesAmountQuota] [money] NULL
489) ON [PRIMARY];
490
491CREATE TABLE [dbo].[ProspectiveBuyer] (
492 [ProspectAlternateKey] [nvarchar] (15) NULL ,
493 [FirstName] [nvarchar] (50) NULL ,
494 [MiddleName] [nvarchar] (50) NULL ,
495 [LastName] [nvarchar] (50) NULL ,
496 [BirthDate] [datetime] NULL ,
497 [MaritalStatus] [nchar] (1) NULL ,
498 [Gender] [nvarchar] (1) NULL ,
499 [EmailAddress] [nvarchar] (50) NULL ,
500 [YearlyIncome] [money] NULL ,
501 [TotalChildren] [tinyint] NULL ,
502 [NumberChildrenAtHome] [tinyint] NULL ,
503 [Education] [nvarchar] (40) NULL ,
504 [Occupation] [nvarchar] (100) NULL ,
505 [HouseOwnerFlag] [nchar] (1) NULL ,
506 [NumberCarsOwned] [tinyint] NULL ,
507 [AddressLine1] [nvarchar] (120) NULL ,
508 [AddressLine2] [nvarchar] (120) NULL ,
509 [City] [nvarchar](30) NULL,
510 [StateProvinceCode] [nvarchar] (3) NULL,
511 [PostalCode] [nvarchar](15) NULL,
512 [Phone] [nvarchar] (20) NULL,
513 [Salutation] [nvarchar] (8) NULL,
514 [Unknown] [int] NULL
515) ON [PRIMARY];
516GO
517
518-- ******************************************************
519-- Load data
520-- ******************************************************
521PRINT '';
522PRINT '*** Loading Data';
523GO
524
525DECLARE
526 @data_path NVARCHAR(256)
527 ,@sql_path NVARCHAR(256);
528
529SELECT @sql_path = SUBSTRING([physical_name], 1, CHARINDEX(N'master.mdf', LOWER([physical_name])) - 1)
530FROM [master].[sys].[master_files]
531WHERE [database_id] = 1
532 AND [file_id] = 1;
533
534SET @data_path = @sql_path + 'awdwdb\';
535
536--
537-- To reinstall AdventureWorksDW from this script change the SELECT statement above to the following.
538--
539-- SET @data_path = 'C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Data Warehouse\';
540--
541
542PRINT 'Loading [AdventureWorksDW].[dbo].[AdventureWorksDWBuildVersion]';
543
544EXECUTE (N'BULK INSERT [AdventureWorksDW].[dbo].[AdventureWorksDWBuildVersion] FROM ''' + @data_path + N'AdventureWorksDWBuildVersion.csv''
545WITH (
546 CODEPAGE=''ACP'',
547 DATAFILETYPE = ''char'',
548 FIELDTERMINATOR= ''\t'',
549 ROWTERMINATOR = ''\n'' ,
550 KEEPIDENTITY,
551 TABLOCK
552)');
553
554PRINT 'Loading [AdventureWorksDW].[dbo].[DimAccount]';
555
556EXECUTE (N'BULK INSERT [AdventureWorksDW].[dbo].[DimAccount] FROM ''' + @data_path + N'DimAccount.csv''
557WITH (
558 CODEPAGE=''ACP'',
559 DATAFILETYPE = ''char'',
560 FIELDTERMINATOR= ''\t'',
561 ROWTERMINATOR = ''\n'' ,
562 KEEPIDENTITY,
563 TABLOCK
564)');
565
566PRINT 'Loading [AdventureWorksDW].[dbo].[DimCurrency]';
567
568EXECUTE (N'BULK INSERT [AdventureWorksDW].[dbo].[DimCurrency] FROM ''' + @data_path + N'DimCurrency.csv''
569WITH (
570 CODEPAGE=''ACP'',
571 DATAFILETYPE = ''char'',
572 FIELDTERMINATOR= ''\t'',
573 ROWTERMINATOR = ''\n'' ,
574 KEEPIDENTITY,
575 TABLOCK
576)');
577
578PRINT 'Loading [AdventureWorksDW].[dbo].[DimCustomer]';
579
580EXECUTE (N'BULK INSERT [AdventureWorksDW].[dbo].[DimCustomer] FROM ''' + @data_path + N'DimCustomer.csv''
581WITH (
582 CODEPAGE=''ACP'',
583 DATAFILETYPE = ''widechar'',
584 FIELDTERMINATOR= ''\t'',
585 ROWTERMINATOR = ''\n'' ,
586 KEEPIDENTITY,
587 TABLOCK
588)');
589
590PRINT 'Loading [AdventureWorksDW].[dbo].[DimDepartmentGroup]';
591
592EXECUTE (N'BULK INSERT [AdventureWorksDW].[dbo].[DimDepartmentGroup] FROM ''' + @data_path + N'DimDepartmentGroup.csv''
593WITH (
594 CODEPAGE=''ACP'',
595 DATAFILETYPE = ''char'',
596 FIELDTERMINATOR= ''\t'',
597 ROWTERMINATOR = ''\n'' ,
598 KEEPIDENTITY,
599 TABLOCK
600)');
601
602PRINT 'Loading [AdventureWorksDW].[dbo].[DimEmployee]';
603
604EXECUTE (N'BULK INSERT [AdventureWorksDW].[dbo].[DimEmployee] FROM ''' + @data_path + N'DimEmployee.csv''
605WITH (
606 CODEPAGE=''ACP'',
607 DATAFILETYPE = ''widechar'',
608 FIELDTERMINATOR= ''\t'',
609 ROWTERMINATOR = ''\n'' ,
610 KEEPIDENTITY,
611 TABLOCK
612)');
613
614PRINT 'Loading [AdventureWorksDW].[dbo].[DimGeography]';
615
616EXECUTE (N'BULK INSERT [AdventureWorksDW].[dbo].[DimGeography] FROM ''' + @data_path + N'DimGeography.csv''
617WITH (
618 CODEPAGE=''ACP'',
619 DATAFILETYPE = ''widechar'',
620 FIELDTERMINATOR= ''\t'',
621 ROWTERMINATOR = ''\n'' ,
622 KEEPIDENTITY,
623 TABLOCK
624)');
625
626PRINT 'Loading [AdventureWorksDW].[dbo].[DimOrganization]';
627
628EXECUTE (N'BULK INSERT [AdventureWorksDW].[dbo].[DimOrganization] FROM ''' + @data_path + N'DimOrganization.csv''
629WITH (
630 CODEPAGE=''ACP'',
631 DATAFILETYPE = ''char'',
632 FIELDTERMINATOR= ''\t'',
633 ROWTERMINATOR = ''\n'' ,
634 KEEPIDENTITY,
635 TABLOCK
636)');
637
638PRINT 'Loading [AdventureWorksDW].[dbo].[DimProduct]';
639
640EXECUTE (N'BULK INSERT [AdventureWorksDW].[dbo].[DimProduct] FROM ''' + @data_path + N'DimProduct.csv''
641WITH (
642 CODEPAGE=''ACP'',
643 DATAFILETYPE = ''widechar'',
644 FIELDTERMINATOR= ''\t'',
645 ROWTERMINATOR = ''\n'' ,
646 KEEPIDENTITY,
647 TABLOCK
648)');
649
650PRINT 'Loading [AdventureWorksDW].[dbo].[DimProductCategory]';
651
652EXECUTE (N'BULK INSERT [AdventureWorksDW].[dbo].[DimProductCategory] FROM ''' + @data_path + N'DimProductCategory.csv''
653WITH (
654 CODEPAGE=''ACP'',
655 DATAFILETYPE = ''widechar'',
656 FIELDTERMINATOR= ''\t'',
657 ROWTERMINATOR = ''\n'' ,
658 KEEPIDENTITY,
659 TABLOCK
660)');
661
662PRINT 'Loading [AdventureWorksDW].[dbo].[DimProductSubcategory]';
663
664EXECUTE (N'BULK INSERT [AdventureWorksDW].[dbo].[DimProductSubcategory] FROM ''' + @data_path + N'DimProductSubcategory.csv''
665WITH (
666 CODEPAGE=''ACP'',
667 DATAFILETYPE = ''widechar'',
668 FIELDTERMINATOR= ''\t'',
669 ROWTERMINATOR = ''\n'' ,
670 KEEPIDENTITY,
671 TABLOCK
672)');
673
674PRINT 'Loading [AdventureWorksDW].[dbo].[DimPromotion]';
675
676EXECUTE (N'BULK INSERT [AdventureWorksDW].[dbo].[DimPromotion] FROM ''' + @data_path + N'DimPromotion.csv''
677WITH (
678 CODEPAGE=''ACP'',
679 DATAFILETYPE = ''widechar'',
680 FIELDTERMINATOR= ''\t'',
681 ROWTERMINATOR = ''\n'' ,
682 KEEPIDENTITY,
683 TABLOCK
684)');
685
686PRINT 'Loading [AdventureWorksDW].[dbo].[DimReseller]';
687
688EXECUTE (N'BULK INSERT [AdventureWorksDW].[dbo].[DimReseller] FROM ''' + @data_path + N'DimReseller.csv''
689WITH (
690 CODEPAGE=''ACP'',
691 DATAFILETYPE = ''char'',
692 FIELDTERMINATOR= ''\t'',
693 ROWTERMINATOR = ''\n'' ,
694 KEEPIDENTITY,
695 TABLOCK
696)');
697
698PRINT 'Loading [AdventureWorksDW].[dbo].[DimSalesReason]';
699
700EXECUTE (N'BULK INSERT AdventureWorksDW..DimSalesReason FROM ''' + @data_path + N'DimSalesReason.csv''
701WITH (
702 CODEPAGE=''ACP'',
703 DATAFILETYPE = ''char'',
704 FIELDTERMINATOR= ''\t'',
705 ROWTERMINATOR = ''\n'' ,
706 KEEPIDENTITY,
707 TABLOCK
708)');
709
710PRINT 'Loading [AdventureWorksDW].[dbo].[DimSalesTerritory]';
711
712EXECUTE (N'BULK INSERT [AdventureWorksDW].[dbo].[DimSalesTerritory] FROM ''' + @data_path + N'DimSalesTerritory.csv''
713WITH (
714 CODEPAGE=''ACP'',
715 DATAFILETYPE = ''char'',
716 FIELDTERMINATOR= ''\t'',
717 ROWTERMINATOR = ''\n'' ,
718 KEEPIDENTITY,
719 TABLOCK
720)');
721
722PRINT 'Loading [AdventureWorksDW].[dbo].[DimScenario]';
723
724EXECUTE (N'BULK INSERT [AdventureWorksDW].[dbo].[DimScenario] FROM ''' + @data_path + N'DimScenario.csv''
725WITH (
726 CODEPAGE=''ACP'',
727 DATAFILETYPE = ''char'',
728 FIELDTERMINATOR= ''\t'',
729 ROWTERMINATOR = ''\n'' ,
730 KEEPIDENTITY,
731 TABLOCK
732)');
733
734PRINT 'Loading [AdventureWorksDW].[dbo].[DimTime]';
735
736EXECUTE (N'BULK INSERT [AdventureWorksDW].[dbo].[DimTime] FROM ''' + @data_path + N'DimTime.csv''
737WITH (
738 CODEPAGE=''ACP'',
739 DATAFILETYPE = ''widechar'',
740 FIELDTERMINATOR= ''\t'',
741 ROWTERMINATOR = ''\n'' ,
742 KEEPIDENTITY,
743 TABLOCK
744)');
745
746PRINT 'Loading [AdventureWorksDW].[dbo].[FactFinance]';
747
748EXECUTE (N'BULK INSERT [AdventureWorksDW].[dbo].[FactFinance] FROM ''' + @data_path + N'FactFinance.csv''
749WITH (
750 CODEPAGE=''ACP'',
751 DATAFILETYPE = ''char'',
752 FIELDTERMINATOR= ''\t'',
753 ROWTERMINATOR = ''\n'' ,
754 KEEPIDENTITY,
755 TABLOCK
756)');
757
758PRINT 'Loading [AdventureWorksDW].[dbo].[FactCurrencyRate]';
759
760EXECUTE (N'BULK INSERT [AdventureWorksDW].[dbo].[FactCurrencyRate] FROM ''' + @data_path + N'FactCurrencyRate.csv''
761WITH (
762 CODEPAGE=''ACP'',
763 DATAFILETYPE = ''char'',
764 FIELDTERMINATOR= ''\t'',
765 ROWTERMINATOR = ''\n'' ,
766 KEEPIDENTITY,
767 TABLOCK
768)');
769
770PRINT 'Loading [AdventureWorksDW].[dbo].[FactInternetSalesReason]';
771
772EXECUTE (N'BULK INSERT [AdventureWorksDW].[dbo].[FactInternetSalesReason] FROM ''' + @data_path + N'FactInternetSalesReason.csv''
773WITH (
774 CODEPAGE=''ACP'',
775 DATAFILETYPE = ''char'',
776 FIELDTERMINATOR= ''\t'',
777 ROWTERMINATOR = ''\n'' ,
778 KEEPIDENTITY,
779 TABLOCK
780)');
781
782PRINT 'Loading [AdventureWorksDW].[dbo].[FactInternetSales]';
783
784EXECUTE (N'BULK INSERT [AdventureWorksDW].[dbo].[FactInternetSales] FROM ''' + @data_path + N'FactInternetSales.csv''
785WITH (
786 CODEPAGE=''ACP'',
787 DATAFILETYPE = ''char'',
788 FIELDTERMINATOR= ''\t'',
789 ROWTERMINATOR = ''\n'' ,
790 KEEPIDENTITY,
791 TABLOCK
792)');
793
794PRINT 'Loading [AdventureWorksDW].[dbo].[FactResellerSales]';
795
796EXECUTE (N'BULK INSERT [AdventureWorksDW].[dbo].[FactResellerSales] FROM ''' + @data_path + N'FactResellerSales.csv''
797WITH (
798 CODEPAGE=''ACP'',
799 DATAFILETYPE = ''char'',
800 FIELDTERMINATOR= ''\t'',
801 ROWTERMINATOR = ''\n'' ,
802 KEEPIDENTITY,
803 TABLOCK
804)');
805
806PRINT 'Loading [AdventureWorksDW].[dbo].[FactSalesQuota]';
807
808EXECUTE (N'BULK INSERT [AdventureWorksDW].[dbo].[FactSalesQuota] FROM ''' + @data_path + N'FactSalesQuota.csv''
809WITH (
810 CODEPAGE=''ACP'',
811 DATAFILETYPE = ''char'',
812 FIELDTERMINATOR= ''\t'',
813 ROWTERMINATOR = ''\n'' ,
814 KEEPIDENTITY,
815 TABLOCK
816)');
817
818PRINT 'Loading [AdventureWorksDW].[dbo].[ProspectiveBuyer]';
819
820EXECUTE (N'BULK INSERT [AdventureWorksDW].[dbo].[ProspectiveBuyer] FROM ''' + @data_path + N'Prospect.csv''
821WITH (
822 CODEPAGE=''ACP'',
823 DATAFILETYPE = ''char'',
824 FIELDTERMINATOR= '','',
825 ROWTERMINATOR = ''\n'' ,
826 KEEPIDENTITY,
827 TABLOCK
828)');
829
830-- ******************************************************
831-- Add Primary Keys
832-- ******************************************************
833PRINT '';
834PRINT '*** Adding Primary Keys';
835GO
836
837ALTER TABLE [dbo].[DimAccount] WITH NOCHECK ADD
838 CONSTRAINT [PK_DimAccount] PRIMARY KEY CLUSTERED
839 (
840 [AccountKey]
841 ) ON [PRIMARY];
842
843ALTER TABLE [dbo].[DimCurrency] WITH NOCHECK ADD
844 CONSTRAINT [PK_DimCurrency_CurrencyKey] PRIMARY KEY CLUSTERED
845 (
846 [CurrencyKey]
847 ) ON [PRIMARY];
848
849ALTER TABLE [dbo].[DimCustomer] WITH NOCHECK ADD
850 CONSTRAINT [PK_DimCustomer_CustomerKey] PRIMARY KEY CLUSTERED
851 (
852 [CustomerKey]
853 ) ON [PRIMARY];
854
855ALTER TABLE [dbo].[DimDepartmentGroup] WITH NOCHECK ADD
856 CONSTRAINT [PK_DimDepartmentGroup] PRIMARY KEY CLUSTERED
857 (
858 [DepartmentGroupKey]
859 ) ON [PRIMARY];
860
861ALTER TABLE [dbo].[DimEmployee] WITH NOCHECK ADD
862 CONSTRAINT [PK_DimEmployee_EmployeeKey] PRIMARY KEY CLUSTERED
863 (
864 [EmployeeKey]
865 ) ON [PRIMARY];
866
867ALTER TABLE [dbo].[DimGeography] WITH NOCHECK ADD
868 CONSTRAINT [PK_DimGeography_GeographyKey] PRIMARY KEY CLUSTERED
869 (
870 [GeographyKey]
871 ) ON [PRIMARY];
872
873ALTER TABLE [dbo].[DimOrganization] WITH NOCHECK ADD
874 CONSTRAINT [PK_DimOrganization] PRIMARY KEY CLUSTERED
875 (
876 [OrganizationKey]
877 ) ON [PRIMARY];
878
879ALTER TABLE [dbo].[DimProduct] WITH NOCHECK ADD
880 CONSTRAINT [PK_DimProduct_ProductKey] PRIMARY KEY CLUSTERED
881 (
882 [ProductKey]
883 ) ON [PRIMARY];
884
885ALTER TABLE [dbo].[DimProductCategory] WITH NOCHECK ADD
886 CONSTRAINT [PK_DimProductCategory_ProductCategoryKey] PRIMARY KEY CLUSTERED
887 (
888 [ProductCategoryKey]
889 ) ON [PRIMARY];
890
891ALTER TABLE [dbo].[DimProductSubcategory] WITH NOCHECK ADD
892 CONSTRAINT [PK_DimProductSubcategory_ProductSubcategoryKey] PRIMARY KEY CLUSTERED
893 (
894 [ProductSubcategoryKey]
895 ) ON [PRIMARY];
896
897ALTER TABLE [dbo].[DimPromotion] WITH NOCHECK ADD
898 CONSTRAINT [PK_DimPromotion_PromotionKey] PRIMARY KEY CLUSTERED
899 (
900 [PromotionKey]
901 ) ON [PRIMARY];
902
903ALTER TABLE [dbo].[DimReseller] WITH NOCHECK ADD
904 CONSTRAINT [PK_DimReseller_ResellerKey] PRIMARY KEY CLUSTERED
905 (
906 [ResellerKey]
907 ) ON [PRIMARY];
908
909ALTER TABLE [dbo].[DimSalesReason] WITH NOCHECK ADD
910 CONSTRAINT [PK_DimSalesReason_SalesReasonKey] PRIMARY KEY CLUSTERED
911 (
912 [SalesReasonKey]
913 ) ON [PRIMARY];
914
915ALTER TABLE [dbo].[DimSalesTerritory] WITH NOCHECK ADD
916 CONSTRAINT [PK_DimSalesTerritory_SalesTerritoryKey] PRIMARY KEY CLUSTERED
917 (
918 [SalesTerritoryKey]
919 ) ON [PRIMARY];
920
921ALTER TABLE [dbo].[DimScenario] WITH NOCHECK ADD
922 CONSTRAINT [PK_DimScenario] PRIMARY KEY CLUSTERED
923 (
924 [ScenarioKey]
925 ) ON [PRIMARY];
926
927ALTER TABLE [dbo].[DimTime] WITH NOCHECK ADD
928 CONSTRAINT [PK_DimTime_TimeKey] PRIMARY KEY CLUSTERED
929 (
930 [TimeKey]
931 ) ON [PRIMARY];
932
933-- ******************************************************
934-- Add Indexes
935-- ******************************************************
936PRINT '';
937PRINT '*** Adding Indexes';
938GO
939
940CREATE UNIQUE INDEX [AK_DimAccount_AccountCodeAlternateKey] ON [dbo].[DimAccount]([AccountCodeAlternateKey]) ON [PRIMARY];
941
942ALTER TABLE [dbo].[DimCurrency] ADD
943 CONSTRAINT [AK_DimCurrency_CurrencyAlternateKey] UNIQUE NONCLUSTERED
944 (
945 [CurrencyAlternateKey]
946 ) ON [PRIMARY];
947
948ALTER TABLE [dbo].[DimCustomer] ADD
949 CONSTRAINT [IX_DimCustomer_CustomerAlternateKey] UNIQUE NONCLUSTERED
950 (
951 [CustomerAlternateKey]
952 ) ON [PRIMARY];
953
954CREATE INDEX [IX_DimCustomer_GeographyKey] ON [dbo].[DimCustomer]([GeographyKey]) ON [PRIMARY];
955
956CREATE INDEX [IX_DimEmployee_ParentEmployeeKey] ON [dbo].[DimEmployee]([ParentEmployeeKey]) ON [PRIMARY];
957CREATE INDEX [IX_DimEmployee_SalesTerritoryKey] ON [dbo].[DimEmployee]([SalesTerritoryKey]) ON [PRIMARY];
958
959ALTER TABLE [dbo].[DimProduct] ADD
960 CONSTRAINT [AK_DimProduct_ProductAlternateKey_StartDate] UNIQUE NONCLUSTERED
961 (
962 [ProductAlternateKey],
963 [StartDate]
964 ) ON [PRIMARY];
965
966CREATE INDEX [IX_DimProduct_ProductSubcategoryKey] ON [dbo].[DimProduct]([ProductSubcategoryKey]) ON [PRIMARY];
967
968ALTER TABLE [dbo].[DimProductCategory] ADD
969 CONSTRAINT [AK_DimProductCategory_ProductCategoryAlternateKey] UNIQUE NONCLUSTERED
970 (
971 [ProductCategoryAlternateKey]
972 ) ON [PRIMARY];
973
974ALTER TABLE [dbo].[DimProductSubcategory] ADD
975 CONSTRAINT [AK_DimProductSubcategory_ProductSubcategoryAlternateKey] UNIQUE NONCLUSTERED
976 (
977 [ProductSubcategoryAlternateKey]
978 ) ON [PRIMARY];
979
980
981ALTER TABLE [dbo].[DimPromotion] ADD
982 CONSTRAINT [AK_DimPromotion_PromotionAlternateKey] UNIQUE NONCLUSTERED
983 (
984 [PromotionAlternateKey]
985 ) ON [PRIMARY];
986
987ALTER TABLE [dbo].[DimReseller] ADD
988 CONSTRAINT [AK_DimReseller_ResellerAlternateKey] UNIQUE NONCLUSTERED
989 (
990 [ResellerAlternateKey]
991 ) ON [PRIMARY];
992
993CREATE INDEX [IX_DimReseller_GeographyKey] ON [dbo].[DimReseller]([GeographyKey]) ON [PRIMARY];
994
995ALTER TABLE [dbo].[DimSalesTerritory] ADD
996 CONSTRAINT [AK_DimSalesTerritory_SalesTerritoryAlternateKey] UNIQUE NONCLUSTERED
997 (
998 [SalesTerritoryAlternateKey]
999 ) ON [PRIMARY];
1000
1001ALTER TABLE [dbo].[DimTime] ADD
1002 CONSTRAINT [AK_DimTime_FullDateAlternateKey] UNIQUE NONCLUSTERED
1003 (
1004 [FullDateAlternateKey]
1005 ) ON [PRIMARY];
1006
1007ALTER TABLE [dbo].[FactInternetSalesReason] ADD
1008 CONSTRAINT [AK_FactInternetSalesReason_SalesOrderNumber_SalesOrderLineNumber_SalesReasonKey] UNIQUE NONCLUSTERED
1009 (
1010 [SalesOrderNumber],
1011 [SalesOrderLineNumber],
1012 [SalesReasonKey]
1013 ) ON [PRIMARY];
1014
1015ALTER TABLE [dbo].[FactInternetSales] ADD
1016 CONSTRAINT [AK_FactInternetSales_SalesOrderNumber_SalesOrderLineNumber] UNIQUE NONCLUSTERED
1017 (
1018 [SalesOrderNumber],
1019 [SalesOrderLineNumber]
1020 ) ON [PRIMARY];
1021
1022CREATE INDEX [IX_FactInternetSales_CurrencyKey] ON [dbo].[FactInternetSales]([CurrencyKey]) ON [PRIMARY];
1023CREATE INDEX [IX_FactInternetSales_CustomerKey] ON [dbo].[FactInternetSales]([CustomerKey]) ON [PRIMARY];
1024CREATE INDEX [IX_FactInternetSales_DueDateKey] ON [dbo].[FactInternetSales]([DueDateKey]) ON [PRIMARY];
1025CREATE INDEX [IX_FactInternetSales_OrderDateKey] ON [dbo].[FactInternetSales]([OrderDateKey]) ON [PRIMARY];
1026CREATE INDEX [IX_FactInternetSales_ProductKey] ON [dbo].[FactInternetSales]([ProductKey]) ON [PRIMARY];
1027CREATE INDEX [IX_FactInternetSales_PromotionKey] ON [dbo].[FactInternetSales]([PromotionKey]) ON [PRIMARY];
1028CREATE INDEX [IX_FactIneternetSales_ShipDateKey] ON [dbo].[FactInternetSales]([ShipDateKey]) ON [PRIMARY];
1029
1030ALTER TABLE [dbo].[FactResellerSales] ADD
1031 CONSTRAINT [AK_FactResellerSales_SalesOrderNumber_SalesOrderLineNumber] UNIQUE NONCLUSTERED
1032 (
1033 [SalesOrderNumber],
1034 [SalesOrderLineNumber]
1035 ) ON [PRIMARY];
1036
1037CREATE INDEX [IX_FactResellerSales_CurrencyKey] ON [dbo].[FactResellerSales]([CurrencyKey]) ON [PRIMARY];
1038CREATE INDEX [IX_FactResellerSales_DueDateKey] ON [dbo].[FactResellerSales]([DueDateKey]) ON [PRIMARY];
1039CREATE INDEX [IX_FactResellerSales_OrderDateKey] ON [dbo].[FactResellerSales]([OrderDateKey]) ON [PRIMARY];
1040CREATE INDEX [IX_FactResellerSales_ProductKey] ON [dbo].[FactResellerSales]([ProductKey]) ON [PRIMARY];
1041CREATE INDEX [IX_FactResellerSales_PromotionKey] ON [dbo].[FactResellerSales]([PromotionKey]) ON [PRIMARY];
1042CREATE INDEX [IX_FactResellerSales_ShipDateKey] ON [dbo].[FactResellerSales]([ShipDateKey]) ON [PRIMARY];
1043CREATE INDEX [IX_FactResellerSales_EmployeeKey] ON [dbo].[FactResellerSales]([EmployeeKey]) ON [PRIMARY];
1044CREATE INDEX [IX_FactResellerSales_ResellerKey] ON [dbo].[FactResellerSales]([ResellerKey]) ON [PRIMARY];
1045
1046CREATE INDEX [IX_FactSalesQuota_EmployeeKey] ON [dbo].[FactSalesQuota]([EmployeeKey]) ON [PRIMARY];
1047CREATE INDEX [IX_FactSalesQuota_TimeKey] ON [dbo].[FactSalesQuota]([TimeKey]) ON [PRIMARY];
1048
1049CREATE INDEX [IX_ProspectiveBuyer_ProspectAlternateKey] ON [dbo].[ProspectiveBuyer]([ProspectAlternateKey]) ON [PRIMARY];
1050GO
1051
1052-- ****************************************
1053-- Create Foreign key constraints
1054-- ****************************************
1055PRINT '';
1056PRINT '*** Creating Foreign Key Constraints';
1057GO
1058
1059ALTER TABLE [dbo].[DimAccount] ADD
1060 CONSTRAINT [FK_DimAccount_DimAccount] FOREIGN KEY
1061 (
1062 [ParentAccountKey]
1063 ) REFERENCES [dbo].[DimAccount] (
1064 [AccountKey]
1065 );
1066
1067ALTER TABLE [dbo].[DimCustomer] ADD
1068 CONSTRAINT [FK_DimCustomer_DimGeography] FOREIGN KEY
1069 (
1070 [GeographyKey]
1071 ) REFERENCES [dbo].[DimGeography] (
1072 [GeographyKey]
1073 );
1074
1075ALTER TABLE [dbo].[DimDepartmentGroup] ADD
1076 CONSTRAINT [FK_DimDepartmentGroup_DimDepartmentGroup] FOREIGN KEY
1077 (
1078 [ParentDepartmentGroupKey]
1079 ) REFERENCES [dbo].[DimDepartmentGroup] (
1080 [DepartmentGroupKey]
1081 );
1082
1083ALTER TABLE [dbo].[DimEmployee] ADD
1084 CONSTRAINT [FK_DimEmployee_DimEmployee] FOREIGN KEY
1085 (
1086 [ParentEmployeeKey]
1087 ) REFERENCES [dbo].[DimEmployee] (
1088 [EmployeeKey]
1089 ),
1090 CONSTRAINT [FK_DimEmployee_DimSalesTerritory] FOREIGN KEY
1091 (
1092 [SalesTerritoryKey]
1093 ) REFERENCES [dbo].[DimSalesTerritory] (
1094 [SalesTerritoryKey]
1095 );
1096
1097ALTER TABLE [dbo].[DimGeography] ADD
1098 CONSTRAINT [FK_DimGeography_DimSalesTerritory] FOREIGN KEY
1099 (
1100 [SalesTerritoryKey]
1101 ) REFERENCES [dbo].[DimSalesTerritory] (
1102 [SalesTerritoryKey]
1103 );
1104
1105ALTER TABLE [dbo].[DimOrganization] ADD
1106 CONSTRAINT [FK_DimOrganization_DimOrganization] FOREIGN KEY
1107 (
1108 [ParentOrganizationKey]
1109 ) REFERENCES [dbo].[DimOrganization] (
1110 [OrganizationKey]
1111 ),
1112 CONSTRAINT [FK_DimOrganization_DimCurrency] FOREIGN KEY
1113 (
1114 [CurrencyKey]
1115 ) REFERENCES [dbo].[DimCurrency] (
1116 [CurrencyKey]
1117 );
1118
1119ALTER TABLE [dbo].[DimProduct] ADD
1120 CONSTRAINT [FK_DimProduct_DimProductSubcategory] FOREIGN KEY
1121 (
1122 [ProductSubcategoryKey]
1123 ) REFERENCES [dbo].[DimProductSubcategory] (
1124 [ProductSubcategoryKey]
1125 );
1126
1127ALTER TABLE [dbo].[DimProductSubcategory] ADD
1128 CONSTRAINT [FK_DimProductSubcategory_DimProductCategory] FOREIGN KEY
1129 (
1130 [ProductCategoryKey]
1131 ) REFERENCES [dbo].[DimProductCategory] (
1132 [ProductCategoryKey]
1133 );
1134
1135ALTER TABLE [dbo].[DimReseller] ADD
1136 CONSTRAINT [FK_DimReseller_DimGeography] FOREIGN KEY
1137 (
1138 [GeographyKey]
1139 ) REFERENCES [dbo].[DimGeography] (
1140 [GeographyKey]
1141 );
1142
1143ALTER TABLE [dbo].[FactCurrencyRate] ADD
1144 CONSTRAINT [FK_FactCurrencyRate_DimTime] FOREIGN KEY
1145 (
1146 [TimeKey]
1147 ) REFERENCES [dbo].[DimTime] (
1148 [TimeKey]
1149 ),
1150 CONSTRAINT [FK_FactCurrencyRate_DimCurrency] FOREIGN KEY
1151 (
1152 [CurrencyKey]
1153 ) REFERENCES [dbo].[DimCurrency] (
1154 [CurrencyKey]
1155 );
1156
1157ALTER TABLE [dbo].[FactFinance] ADD
1158 CONSTRAINT [FK_FactFinance_DimAccount] FOREIGN KEY
1159 (
1160 [AccountKey]
1161 ) REFERENCES [dbo].[DimAccount] (
1162 [AccountKey]
1163 ),
1164 CONSTRAINT [FK_FactFinance_DimDepartmentGroup] FOREIGN KEY
1165 (
1166 [DepartmentGroupKey]
1167 ) REFERENCES [dbo].[DimDepartmentGroup] (
1168 [DepartmentGroupKey]
1169 ),
1170 CONSTRAINT [FK_FactFinance_DimOrganization] FOREIGN KEY
1171 (
1172 [OrganizationKey]
1173 ) REFERENCES [dbo].[DimOrganization] (
1174 [OrganizationKey]
1175 ),
1176 CONSTRAINT [FK_FactFinance_DimScenario] FOREIGN KEY
1177 (
1178 [ScenarioKey]
1179 ) REFERENCES [dbo].[DimScenario] (
1180 [ScenarioKey]
1181 ),
1182 CONSTRAINT [FK_FactFinance_DimTime] FOREIGN KEY
1183 (
1184 [TimeKey]
1185 ) REFERENCES [dbo].[DimTime] (
1186 [TimeKey]
1187 );
1188
1189ALTER TABLE [dbo].[FactInternetSalesReason] ADD
1190 CONSTRAINT [FK_FactInternetSalesReason_DimSalesReason] FOREIGN KEY
1191 (
1192 [SalesReasonKey]
1193 ) REFERENCES [dbo].[DimSalesReason] (
1194 [SalesReasonKey]
1195 ),
1196 CONSTRAINT [FK_FactInternetSalesReason_FactInternetSales] FOREIGN KEY
1197 (
1198 [SalesOrderNumber],
1199 [SalesOrderLineNumber]
1200 ) REFERENCES [dbo].[FactInternetSales] (
1201 [SalesOrderNumber],
1202 [SalesOrderLineNumber]
1203 );
1204
1205ALTER TABLE [dbo].[FactInternetSales] ADD
1206 CONSTRAINT [FK_FactInternetSales_DimCurrency] FOREIGN KEY
1207 (
1208 [CurrencyKey]
1209 ) REFERENCES [dbo].[DimCurrency] (
1210 [CurrencyKey]
1211 ),
1212 CONSTRAINT [FK_FactInternetSales_DimCustomer] FOREIGN KEY
1213 (
1214 [CustomerKey]
1215 ) REFERENCES [dbo].[DimCustomer] (
1216 [CustomerKey]
1217 ),
1218 CONSTRAINT [FK_FactInternetSales_DimProduct] FOREIGN KEY
1219 (
1220 [ProductKey]
1221 ) REFERENCES [dbo].[DimProduct] (
1222 [ProductKey]
1223 ),
1224 CONSTRAINT [FK_FactInternetSales_DimPromotion] FOREIGN KEY
1225 (
1226 [PromotionKey]
1227 ) REFERENCES [dbo].[DimPromotion] (
1228 [PromotionKey]
1229 ),
1230 CONSTRAINT [FK_FactInternetSales_DimTime] FOREIGN KEY
1231 (
1232 [OrderDateKey]
1233 ) REFERENCES [dbo].[DimTime] (
1234 [TimeKey]
1235 ),
1236 CONSTRAINT [FK_FactInternetSales_DimTime1] FOREIGN KEY
1237 (
1238 [DueDateKey]
1239 ) REFERENCES [dbo].[DimTime] (
1240 [TimeKey]
1241 ),
1242 CONSTRAINT [FK_FactInternetSales_DimTime2] FOREIGN KEY
1243 (
1244 [ShipDateKey]
1245 ) REFERENCES [dbo].[DimTime] (
1246 [TimeKey]
1247 ),
1248 CONSTRAINT [FK_FactInternetSales_DimSalesTerritory] FOREIGN KEY
1249 (
1250 [SalesTerritoryKey]
1251 ) REFERENCES [dbo].[DimSalesTerritory] (
1252 [SalesTerritoryKey]
1253 );
1254
1255ALTER TABLE [dbo].[FactResellerSales] ADD
1256 CONSTRAINT [FK_FactResellerSales_DimCurrency] FOREIGN KEY
1257 (
1258 [CurrencyKey]
1259 ) REFERENCES [dbo].[DimCurrency] (
1260 [CurrencyKey]
1261 ),
1262 CONSTRAINT [FK_FactResellerSales_DimProduct] FOREIGN KEY
1263 (
1264 [ProductKey]
1265 ) REFERENCES [dbo].[DimProduct] (
1266 [ProductKey]
1267 ),
1268 CONSTRAINT [FK_FactResellerSales_DimPromotion] FOREIGN KEY
1269 (
1270 [PromotionKey]
1271 ) REFERENCES [dbo].[DimPromotion] (
1272 [PromotionKey]
1273 ),
1274 CONSTRAINT [FK_FactResellerSales_DimTime] FOREIGN KEY
1275 (
1276 [OrderDateKey]
1277 ) REFERENCES [dbo].[DimTime] (
1278 [TimeKey]
1279 ),
1280 CONSTRAINT [FK_FactResellerSales_DimTime1] FOREIGN KEY
1281 (
1282 [DueDateKey]
1283 ) REFERENCES [dbo].[DimTime] (
1284 [TimeKey]
1285 ),
1286 CONSTRAINT [FK_FactResellerSales_DimTime2] FOREIGN KEY
1287 (
1288 [ShipDateKey]
1289 ) REFERENCES [dbo].[DimTime] (
1290 [TimeKey]
1291 ),
1292 CONSTRAINT [FK_FactResellerSales_DimEmployee] FOREIGN KEY
1293 (
1294 [EmployeeKey]
1295 ) REFERENCES [dbo].[DimEmployee] (
1296 [EmployeeKey]
1297 ),
1298 CONSTRAINT [FK_FactResellerSales_DimReseller] FOREIGN KEY
1299 (
1300 [ResellerKey]
1301 ) REFERENCES [dbo].[DimReseller] (
1302 [ResellerKey]
1303 ),
1304 CONSTRAINT [FK_FactResellerSales_DimSalesTerritory] FOREIGN KEY
1305 (
1306 [SalesTerritoryKey]
1307 ) REFERENCES [dbo].[DimSalesTerritory] (
1308 [SalesTerritoryKey]
1309 );
1310
1311ALTER TABLE [dbo].[FactSalesQuota] ADD
1312 CONSTRAINT [FK_FactSalesQuota_DimEmployee] FOREIGN KEY
1313 (
1314 [EmployeeKey]
1315 ) REFERENCES [dbo].[DimEmployee] (
1316 [EmployeeKey]
1317 ),
1318 CONSTRAINT [FK_FactSalesQuota_DimTime] FOREIGN KEY
1319 (
1320 [TimeKey]
1321 ) REFERENCES [dbo].[DimTime] (
1322 [TimeKey]
1323 );
1324GO
1325
1326-- ******************************************************
1327-- Add database views.
1328-- ******************************************************
1329PRINT '';
1330PRINT '*** Creating Table Views';
1331GO
1332
1333-- vDMPrep will be used as a data source by the other data mining views.
1334-- Uses DW data at customer, product, day, etc. granularity and
1335-- gets region, model, year, month, etc.
1336CREATE VIEW [dbo].[vDMPrep]
1337AS
1338 SELECT
1339 pc.[EnglishProductCategoryName]
1340 ,Coalesce(p.[ModelName], p.[EnglishProductName]) AS [Model]
1341 ,c.[CustomerKey]
1342 ,s.[SalesTerritoryGroup] AS [Region]
1343 ,CASE
1344 WHEN Month(GetDate()) < Month(c.[BirthDate])
1345 THEN DateDiff(yy,c.[BirthDate],GetDate()) - 1
1346 WHEN Month(GetDate()) = Month(c.[BirthDate])
1347 AND Day(GetDate()) < Day(c.[BirthDate])
1348 THEN DateDiff(yy,c.[BirthDate],GetDate()) - 1
1349 ELSE DateDiff(yy,c.[BirthDate],GetDate())
1350 END AS [Age]
1351 ,CASE
1352 WHEN c.[YearlyIncome] < 40000 THEN 'Low'
1353 WHEN c.[YearlyIncome] > 60000 THEN 'High'
1354 ELSE 'Moderate'
1355 END AS [IncomeGroup]
1356 ,t.[CalendarYear]
1357 ,t.[FiscalYear]
1358 ,t.[MonthNumberOfYear] AS [Month]
1359 ,f.[SalesOrderNumber] AS [OrderNumber]
1360 ,f.SalesOrderLineNumber AS LineNumber
1361 ,f.OrderQuantity AS Quantity
1362 ,f.ExtendedAmount AS Amount
1363 FROM
1364 [dbo].[FactInternetSales] f
1365 INNER JOIN [dbo].[DimTime] t
1366 ON f.[OrderDateKey] = t.[TimeKey]
1367 INNER JOIN [dbo].[DimProduct] p
1368 ON f.[ProductKey] = p.[ProductKey]
1369 INNER JOIN [dbo].[DimProductSubcategory] psc
1370 ON p.[ProductSubcategoryKey] = psc.[ProductSubcategoryKey]
1371 INNER JOIN [dbo].[DimProductCategory] pc
1372 ON psc.[ProductCategoryKey] = pc.[ProductCategoryKey]
1373 INNER JOIN [dbo].[DimCustomer] c
1374 ON f.[CustomerKey] = c.[CustomerKey]
1375 INNER JOIN [dbo].[DimGeography] g
1376 ON c.[GeographyKey] = g.[GeographyKey]
1377 INNER JOIN [dbo].[DimSalesTerritory] s
1378 ON g.[SalesTerritoryKey] = s.[SalesTerritoryKey]
1379;
1380GO
1381
1382-- vTimeSeries view supports time series data mining model.
1383-- - Replaces predecessor model with successor model.
1384-- - Abbreviates model names to improve view in mining model viewer
1385-- concatenates model and region so that table only has one input.
1386CREATE VIEW [dbo].[vTimeSeries]
1387AS
1388 SELECT
1389 CASE [Model]
1390 WHEN 'Mountain-100' THEN 'M200'
1391 WHEN 'Road-150' THEN 'R250'
1392 WHEN 'Road-650' THEN 'R750'
1393 WHEN 'Touring-1000' THEN 'T1000'
1394 ELSE Left([Model], 1) + Right([Model], 3)
1395 END + ' ' + [Region] AS [ModelRegion]
1396 ,(Convert(Integer, [CalendarYear]) * 100) + Convert(Integer, [Month]) AS [TimeIndex]
1397 ,Sum([Quantity]) AS [Quantity]
1398 ,Sum([Amount]) AS [Amount]
1399 FROM
1400 [dbo].[vDMPrep]
1401 WHERE
1402 [Model] IN ('Mountain-100', 'Mountain-200', 'Road-150', 'Road-250',
1403 'Road-650', 'Road-750', 'Touring-1000')
1404 GROUP BY
1405 CASE [Model]
1406 WHEN 'Mountain-100' THEN 'M200'
1407 WHEN 'Road-150' THEN 'R250'
1408 WHEN 'Road-650' THEN 'R750'
1409 WHEN 'Touring-1000' THEN 'T1000'
1410 ELSE Left(Model,1) + Right(Model,3)
1411 END + ' ' + [Region],
1412 (Convert(Integer, [CalendarYear]) * 100) + Convert(Integer, [Month])
1413;
1414GO
1415
1416-- vTargetMail supports targeted mailing data model
1417-- Uses vDMPrep to determine if a customer buys a bike and joins to DimCustomer
1418CREATE VIEW [dbo].[vTargetMail]
1419AS
1420 SELECT
1421 c.[CustomerKey],
1422 c.[GeographyKey],
1423 c.[CustomerAlternateKey],
1424 c.[Title],
1425 c.[FirstName],
1426 c.[MiddleName],
1427 c.[LastName],
1428 c.[NameStyle],
1429 c.[BirthDate],
1430 c.[MaritalStatus],
1431 c.[Suffix],
1432 c.[Gender],
1433 c.[EmailAddress],
1434 c.[YearlyIncome],
1435 c.[TotalChildren],
1436 c.[NumberChildrenAtHome],
1437 c.[EnglishEducation],
1438 c.[SpanishEducation],
1439 c.[FrenchEducation],
1440 c.[EnglishOccupation],
1441 c.[SpanishOccupation],
1442 c.[FrenchOccupation],
1443 c.[HouseOwnerFlag],
1444 c.[NumberCarsOwned],
1445 c.[AddressLine1],
1446 c.[AddressLine2],
1447 c.[Phone],
1448 c.[DateFirstPurchase],
1449 c.[CommuteDistance],
1450 x.[Region],
1451 x.[Age],
1452 CASE x.[Bikes]
1453 WHEN 0 THEN 0
1454 ELSE 1
1455 END AS [BikeBuyer]
1456 FROM
1457 [dbo].[DimCustomer] c INNER JOIN (
1458 SELECT
1459 [CustomerKey]
1460 ,[Region]
1461 ,[Age]
1462 ,Sum(
1463 CASE [EnglishProductCategoryName]
1464 WHEN 'Bikes' THEN 1
1465 ELSE 0
1466 END) AS [Bikes]
1467 FROM
1468 [dbo].[vDMPrep]
1469 GROUP BY
1470 [CustomerKey]
1471 ,[Region]
1472 ,[Age]
1473 ) AS [x]
1474 ON c.[CustomerKey] = x.[CustomerKey]
1475;
1476GO
1477
1478-- vAssocSeqOrders supports assocation and sequence clustering data mmining models.
1479-- - Limits data to FY2004.
1480-- - Creates order case table and line item nested table.
1481CREATE VIEW [dbo].[vAssocSeqOrders]
1482AS
1483 SELECT DISTINCT
1484 [OrderNumber]
1485 ,[CustomerKey]
1486 ,[Region]
1487 ,[IncomeGroup]
1488 FROM
1489 [dbo].[vDMPrep]
1490 WHERE
1491 [FiscalYear] = '2004'
1492GO
1493
1494CREATE VIEW [dbo].[vAssocSeqLineItems]
1495AS
1496 SELECT
1497 OrderNumber
1498 ,LineNumber
1499 ,Model
1500 FROM
1501 [dbo].[vDMPrep]
1502 WHERE
1503 FiscalYear = '2004'
1504;
1505GO
1506
1507-- ******************************************************
1508-- Add database functions.
1509-- ******************************************************
1510PRINT '';
1511PRINT '*** Creating Functions';
1512GO
1513
1514CREATE FUNCTION [dbo].[udfMinimumDate] (
1515 @x DATETIME,
1516 @y DATETIME
1517) RETURNS DATETIME
1518AS
1519BEGIN
1520 DECLARE @z DATETIME
1521
1522 IF @x <= @y
1523 SET @z = @x
1524 ELSE
1525 SET @z = @y
1526
1527 RETURN(@z)
1528END
1529GO
1530
1531-- ****************************************
1532-- Drop DDL Trigger for Database
1533-- ****************************************
1534PRINT '';
1535PRINT '*** Disabling DDL Trigger for Database';
1536GO
1537
1538DISABLE TRIGGER [ddlDatabaseTriggerLog]
1539ON DATABASE;
1540GO
1541
1542/*
1543-- Output database object creation messages
1544SELECT [PostTime], [DatabaseUser], [Event], [Schema], [Object], [TSQL], [XmlEvent]
1545FROM [AdventureWorksDW].[dbo].[DatabaseLog];
1546*/
1547GO
1548
1549
1550-- ****************************************
1551-- Change File Growth Values for Database
1552-- ****************************************
1553PRINT '';
1554PRINT '*** Changing File Growth Values for Database';
1555GO
1556
1557ALTER DATABASE [AdventureWorksDW]
1558MODIFY FILE (NAME = 'AdventureWorksDW_Data', FILEGROWTH = 16)
1559;
1560ALTER DATABASE [AdventureWorksDW]
1561MODIFY FILE (NAME = 'AdventureWorksDW_Log', FILEGROWTH = 16)
1562;
1563GO
1564
1565
1566-- ****************************************
1567-- Shrink Database
1568-- ****************************************
1569PRINT '';
1570PRINT '*** Shrinking Database';
1571GO
1572
1573DBCC SHRINKDATABASE ([AdventureWorksDW])
1574;
1575GO
1576
1577USE [master]
1578GO