cr_mdwt_adventureworksdw.sql

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

,  [NumberEmployees]  int   NULL
,  [AnnualSales]  money   NULL
,  [AnnualRevenue]  money   NULL
,  [YearOpened]  int   NULL
,  [BankName]  varchar(50)   NULL
,  [OrderFrequency]  char(12)   NULL
,  [CurrentStoreValueScore]  char(12)   NULL
,  [FirstOrderDate]  datetime   NULL
,  [LastOrderDate]  datetime   NULL
,  [RowIsCurrent]  char(1)   NULL
,  [RowStartDate]  datetime   NULL
,  [RowEndDate]  datetime  DEFAULT '12/31/9999' NULL
,  [RowChangeReason]  varchar(200)   NULL
,  [AuditKey]  int   NOT NULL
, CONSTRAINT [PK_DimCustomer] PRIMARY KEY CLUSTERED 
( [CustomerKey] )
) ON [PRIMARY]
GO

exec sys.sp_addextendedproperty @name=N'Table Type', @value=N'Dimension', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer'
exec sys.sp_addextendedproperty @name=N'View Name', @value=N'Customer', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer'
exec sys.sp_addextendedproperty @name=N'Description', @value=N'The Customer dimension includes all Adventure Works customers', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer'
exec sys.sp_addextendedproperty @name=N'Used in schemas', @value=N'Orders, Returns, CustomerCare, Shipping', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer'
GO

SET IDENTITY_INSERT DimCustomer ON
GO
INSERT INTO DimCustomer (CustomerKey, BKAccountNumber, CustomerType, CustomerIDName, CustomerTitle, FirstName, MiddleName, LastName, CustomerFullName, BirthDate, MaritalStatus, Gender, EmailAddress, IncomeRange, TotalChildren, NumberChildrenAtHome, Education, Occupation, HomeOwnerStatus, NumberCarsOwned, DateFirstPurchase, CommuteDistance, CustomerValueScore, Phone, AddressLine1, AddressLine2, PostalCode, City, CityAsRecorded, StateProvinceCode, StateProvince, CountryCode, Country, ResellerName, BusinessType, BKCustomerSalesTerritoryId, CustomerSalesTerritory, CustomerSalesTerritoryCountry, CustomerSalesTerritoryGroup, NumberEmployees, AnnualSales, AnnualRevenue, YearOpened, BankName, OrderFrequency, CurrentStoreValueScore, FirstOrderDate, LastOrderDate, RowIsCurrent, RowStartDate, RowEndDate, RowChangeReason, AuditKey)
VALUES (-1, 'Unknown', 'Unknown', 'Unknown', NULL, 'Unknown', 'Unknown', 'Unknown', 'Unknown', NULL, 'Unknown', 'Unknown', 'Unknown', NULL, NULL, NULL, 'Unknown', 'Unknown', 'Unknown', NULL, NULL, 'Unknown', 'Unknown', 'Unknown', 'Unknown', 'Unknown', 'Unknown', 'Unknown', 'Unknown', 'N/A', 'Unknown', 'N/A', 'Unknown', 'Unknown', 'Unknown', NULL, 'Unknown', 'Unknown', 'Unknown', NULL, NULL, NULL, NULL, 'Unknown', 'Unknown', 'Unknown', NULL, NULL, 'Y', NULL, '12/31/9999', 'N/A', -1)
GO
SET IDENTITY_INSERT DimCustomer OFF
GO

exec sys.sp_addextendedproperty @name=N'Description', @value=N'Surrogate primary key', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerKey'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Account Number from the transaction system', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BKAccountNumber'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'The type of the customer based on our relationship', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerType'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Customer full name (Last, First Middle) prepended with CustomerID', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerIDName'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Courtesy title', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerTitle'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Customer''s first name', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'FirstName'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Customer''s middle name (often NULL)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'MiddleName'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Customer''s last name', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'LastName'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Customer''s full name as Last, First Middle', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerFullName'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Customer''s date of birth', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BirthDate'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Customer''s marital status', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'MaritalStatus'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Customer''s gender', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Gender'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Customer''s email address', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'EmailAddress'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Customer''s annual Income', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'IncomeRange'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Customer''s total number of children', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'TotalChildren'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Customer''s number of children at home', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'NumberChildrenAtHome'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Customer''s education level', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Education'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Customer''s general occupation (eg Managerial)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Occupation'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Is the customer a homeowner?', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'HomeOwnerStatus'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Number of cars the customer owns', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'NumberCarsOwned'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Date person first purchased a bike (self-reported)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'DateFirstPurchase'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Customer''s average commute distance', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CommuteDistance'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Customer''s current lifetime value score to AdventureWorks', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerValueScore'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Customer''s phone number', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Phone'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'First line of customer''s address', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AddressLine1'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'2nd line of customer''s address (usually NULL)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AddressLine2'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Postal code, eg zip code', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'PostalCode'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'City, cleaned up by way of postal code', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'City'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'City as it actually exists in the source system', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CityAsRecorded'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'State or Province code', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'StateProvinceCode'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'State or Province', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'StateProvince'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Country code', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CountryCode'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Country', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Country'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Reselling store''s name', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'ResellerName'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Reseller''s business type', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BusinessType'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Natural key for the customer''s current sales territory', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BKCustomerSalesTerritoryId'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Customer sales territory', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerSalesTerritory'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Customer sales territory country', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerSalesTerritoryCountry'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Customer sales territory group', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerSalesTerritoryGroup'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Number of employees at the store', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'NumberEmployees'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Store''s annual sales, self-reported', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AnnualSales'; 
exec sys.sp_addextendedproperty @name=N'Description', @value=N'Store''s annual revenue, self-reported', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AnnualRevenue';