cr_mdwt_adventureworksdw.sql

数据仓库开发的很好的一个例子

/****** Object:  Database MDWT_AdventureWorksDW    Script Date: 6/8/2006 1:00:48 PM ******/
/*
Kimball Group, The Microsoft Data Warehouse Toolkit
Generate a database from the datamodel worksheet

You can use this Excel workbook as a data modeling tool during the logical design phase of your project.
As discussed in the book, it is in some ways preferable to a real data modeling tool during the inital design.
We expect you to move away from this spreadsheet and into a real modeling tool during the physical design phase.
The authors provide this macro so that the spreadsheet isn't a dead-end. You can 'import' into your
data modeling tool by generating a database using this script, then reverse-engineering that database into
your tool.

Uncomment the next lines if you want to drop and create the database
*/
/*
DROP DATABASE MDWT_AdventureWorksDW
GO
CREATE DATABASE MDWT_AdventureWorksDW
GO
*/
USE MDWT_AdventureWorksDW
GO
EXEC sys.sp_addextendedproperty @name = 'Description', @value = 'Forward Engineer from MDWT Excel workbook'
GO





/* Drop table DimCustomer */
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DimCustomer]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [DimCustomer] 
GO

/* Create table DimCustomer */
CREATE TABLE [DimCustomer] (
   [CustomerKey]  int IDENTITY  NOT NULL
,  [BKAccountNumber]  varchar(10)   NOT NULL
,  [CustomerType]  char(10)   NOT NULL
,  [CustomerIDName]  varchar(100)   NULL
,  [CustomerTitle]  char(5)   NULL
,  [FirstName]  varchar(30)   NULL
,  [MiddleName]  varchar(30)   NULL
,  [LastName]  varchar(30)   NULL
,  [CustomerFullName]  varchar(100)   NULL
,  [BirthDate]  datetime   NULL
,  [MaritalStatus]  char(7)   NULL
,  [Gender]  char(7)   NULL
,  [EmailAddress]  varchar(50)   NULL
,  [IncomeRange]  varchar(50)   NULL
,  [TotalChildren]  tinyint   NULL
,  [NumberChildrenAtHome]  tinyint   NULL
,  [Education]  varchar(30)   NULL
,  [Occupation]  varchar(30)   NULL
,  [HomeOwnerStatus]  varchar(13)   NULL
,  [NumberCarsOwned]  tinyint   NULL
,  [DateFirstPurchase]  datetime   NULL
,  [CommuteDistance]  varchar(15)   NULL
,  [CustomerValueScore]  varchar(15)   NULL
,  [Phone]  varchar(20)   NULL
,  [AddressLine1]  varchar(60)   NULL
,  [AddressLine2]  varchar(60)   NULL
,  [PostalCode]  varchar(15)   NULL
,  [City]  varchar(100)   NULL
,  [CityAsRecorded]  varchar(100)   NULL
,  [StateProvinceCode]  char(3)   NULL
,  [StateProvince]  varchar(50)   NULL
,  [CountryCode]  char(3)   NULL
,  [Country]  varchar(50)   NULL
,  [ResellerName]  varchar(50)   NULL
,  [BusinessType]  varchar(20)   NULL
,  [BKCustomerSalesTerritoryId]  int   NULL
,  [CustomerSalesTerritory]  varchar(50)   NULL
,  [CustomerSalesTerritoryCountry]  varchar(50)   NULL
,  [CustomerSalesTerritoryGroup]  varchar(50)   NULL