/****** 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 , [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'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Year the store opened', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'YearOpened'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Store''s bank name', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BankName'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Does this reseller tend to order Annually, Semiannually, or Quarterly?', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'OrderFrequency'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Current store value to AdventureWorks', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CurrentStoreValueScore'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Date store first ordered from us', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'FirstOrderDate'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Date store most recently ordered from us', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'LastOrderDate'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Is this the current row for this member (Y/N)?', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'RowIsCurrent'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'When did this row become valid for this member?', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'RowStartDate'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'When did this row become invalid? (12/31/9999 if current row)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'RowEndDate'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Why did the row change last?', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'RowChangeReason'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'What process loaded this row?', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AuditKey'; exec sys.sp_addextendedproperty @name=N'FK To', @value=N'DimAudit.AuditKey', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AuditKey'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'1, 2, 3, 4 …', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerKey'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'AW00000001', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BKAccountNumber'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'Reseller, Individual', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerType'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'Ms., Mr.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerTitle'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'Tom, Dick, Harry', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'FirstName'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'Married, Single, Unknown', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'MaritalStatus'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'Male, Female, Unknown', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Gender'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'Homeowner, Not 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'Example Values', @value=N'Y, N', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'RowIsCurrent'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'3/19/2004', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'RowStartDate'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'1/14/1998, 12/31/9999', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'RowEndDate'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerType'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerIDName'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerTitle'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'FirstName'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'MiddleName'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'LastName'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerFullName'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BirthDate'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'MaritalStatus'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Gender'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'EmailAddress'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'IncomeRange'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'TotalChildren'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'NumberChildrenAtHome'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Education'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Occupation'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'HomeOwnerStatus'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'NumberCarsOwned'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'DateFirstPurchase'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CommuteDistance'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerValueScore'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Phone'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AddressLine1'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AddressLine2'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'2', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'PostalCode'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'2', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'City'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'2', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CityAsRecorded'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'2', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'StateProvinceCode'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'2', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'StateProvince'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'2', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CountryCode'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'2', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Country'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'ResellerName'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BusinessType'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BKCustomerSalesTerritoryId'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerSalesTerritory'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerSalesTerritoryCountry'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerSalesTerritoryGroup'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'NumberEmployees'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AnnualSales'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AnnualRevenue'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'YearOpened'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BankName'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'OrderFrequency'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CurrentStoreValueScore'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'FirstOrderDate'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'LastOrderDate'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BKAccountNumber'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived in ETL', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerType'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived in ETL', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerIDName'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerTitle'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'FirstName'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'MiddleName'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'LastName'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived in ETL', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerFullName'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BirthDate'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'MaritalStatus'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Gender'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'EmailAddress'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'IncomeRange'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'TotalChildren'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'NumberChildrenAtHome'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Education'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Occupation'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'HomeOwnerStatus'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'NumberCarsOwned'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'DateFirstPurchase'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CommuteDistance'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived in ETL', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerValueScore'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Phone'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AddressLine1'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AddressLine2'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'PostalCode'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived in ETL', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'City'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CityAsRecorded'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'StateProvinceCode'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'StateProvince'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CountryCode'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Country'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'ResellerName'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BusinessType'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BKCustomerSalesTerritoryId'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerSalesTerritory'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerSalesTerritoryCountry'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerSalesTerritoryGroup'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'NumberEmployees'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AnnualSales'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AnnualRevenue'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'YearOpened'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BankName'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived in ETL', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'OrderFrequency'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived in ETL', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CurrentStoreValueScore'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived in ETL', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'FirstOrderDate'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived in ETL', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'LastOrderDate'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived in ETL', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'RowIsCurrent'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived in ETL', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'RowStartDate'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived in ETL', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'RowEndDate'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived in ETL', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'RowChangeReason'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived in ETL', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AuditKey'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Sales', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BKAccountNumber'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Sales', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerType'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Person', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerTitle'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Person', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'FirstName'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Person', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'MiddleName'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Person', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'LastName'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Sales', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BirthDate'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Sales', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'MaritalStatus'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Sales', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Gender'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Person', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'EmailAddress'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Sales', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'IncomeRange'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Sales', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'TotalChildren'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Sales', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'NumberChildrenAtHome'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Sales', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Education'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Sales', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Occupation'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Sales', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'HomeOwnerStatus'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Sales', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'NumberCarsOwned'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Sales', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'DateFirstPurchase'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Sales', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CommuteDistance'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Person', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Phone'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Sales', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AddressLine1'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Sales', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AddressLine2'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Person', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'PostalCode'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Person', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CityAsRecorded'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Person', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'StateProvinceCode'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Person', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'StateProvince'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Person', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CountryCode'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Person', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Country'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Sales', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'ResellerName'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Sales', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BusinessType'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Sales', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BKCustomerSalesTerritoryId'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Sales', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerSalesTerritory'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Person', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerSalesTerritoryCountry'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Sales', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerSalesTerritoryGroup'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Sales', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'NumberEmployees'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Sales', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AnnualSales'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Sales', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AnnualRevenue'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Sales', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'YearOpened'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Sales', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BankName'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'Customer', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BKAccountNumber'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'Customer', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerType'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'Contact', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerTitle'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'Contact', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'FirstName'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'Contact', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'MiddleName'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'Contact', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'LastName'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'Individual', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BirthDate'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'Individual', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'MaritalStatus'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'Individual', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Gender'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'Contact', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'EmailAddress'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'Individual', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'IncomeRange'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'Individual', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'TotalChildren'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'Individual', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'NumberChildrenAtHome'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'Individual', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Education'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'Individual', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Occupation'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'Individual', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'HomeOwnerStatus'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'Individual', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'NumberCarsOwned'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'Individual', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'DateFirstPurchase'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'Individual', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CommuteDistance'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'Contact', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Phone'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'CustomerAddress', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AddressLine1'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'CustomerAddress', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AddressLine2'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'Address', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'PostalCode'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'Address', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CityAsRecorded'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'Address', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'StateProvinceCode'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'StateProvince', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'StateProvince'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'StateProvince', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CountryCode'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'CountryRegion', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Country'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'Store', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'ResellerName'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'Store', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BusinessType'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'Customer', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BKCustomerSalesTerritoryId'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'SalesTerritory', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerSalesTerritory'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'CountryRegion', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerSalesTerritoryCountry'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'SalesTerritory', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerSalesTerritoryGroup'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'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'Source Table', @value=N'Store', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AnnualSales'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'Store', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AnnualRevenue'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'Store', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'YearOpened'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'Store', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BankName'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'AccountNumber', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BKAccountNumber'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'CustomerType', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerType'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'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'Source Field Name', @value=N'FirstName', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'FirstName'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'MiddleName', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'MiddleName'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'LastName', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'LastName'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'Demographics', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BirthDate'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'Demographics', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'MaritalStatus'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'Demographics', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Gender'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'EmailAddress', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'EmailAddress'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'Demographics', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'IncomeRange'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'Demographics', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'TotalChildren'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'Demographics', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'NumberChildrenAtHome'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'Demographics', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Education'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'Demographics', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Occupation'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'Demographics', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'HomeOwnerStatus'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'Demographics', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'NumberCarsOwned'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'Demographics', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'DateFirstPurchase'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'Demographics', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CommuteDistance'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'Phone', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Phone'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'AddressLine1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AddressLine1'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'AddressLine2', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AddressLine2'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'PostalCode', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'PostalCode'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'City', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CityAsRecorded'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'StateProvinceCode', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'StateProvinceCode'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'Name', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'StateProvince'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'CountryRegionCode', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CountryCode'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'Name', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Country'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'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'Source Field Name', @value=N'Demographics', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BusinessType'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'TerritoryID', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BKCustomerSalesTerritoryId'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'Name', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerSalesTerritory'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'Name', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerSalesTerritoryCountry'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'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'Source Field Name', @value=N'Demographics', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'NumberEmployees'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'Demographics', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AnnualSales'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'Demographics', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AnnualRevenue'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'Demographics', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'YearOpened'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'Demographics', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BankName'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'varchar(10)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BKAccountNumber'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'nvarchar(8)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerTitle'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'nvarchar(50)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'FirstName'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'nvarchar(50)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'MiddleName'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'nvarchar(50)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'LastName'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'xml', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BirthDate'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'xml', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'MaritalStatus'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'xml', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Gender'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'nvarchar(50)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'EmailAddress'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'xml', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'IncomeRange'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'xml', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'TotalChildren'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'xml', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'NumberChildrenAtHome'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'xml', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Education'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'xml', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Occupation'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'xml', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'HomeOwnerStatus'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'xml', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'NumberCarsOwned'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'xml', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'DateFirstPurchase'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'xml', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CommuteDistance'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'nvarchar(25)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Phone'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'nvarchar(60)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AddressLine1'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'nvarchar(60)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AddressLine2'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'nvarchar(15)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'PostalCode'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'nvarchar(30)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'City'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'nvarchar(30)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CityAsRecorded'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'nchar(3)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'StateProvinceCode'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'nvarchar(50)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'StateProvince'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'nchar(3)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CountryCode'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'nvarchar(50)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Country'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'xml', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BusinessType'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'int', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BKCustomerSalesTerritoryId'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'nvarchar(50)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerSalesTerritory'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'nvarchar(50)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerSalesTerritoryCountry'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'nvarchar(50)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerSalesTerritoryGroup'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'xml', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'NumberEmployees'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'xml', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AnnualSales'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'xml', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AnnualRevenue'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'xml', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'YearOpened'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'xml', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BankName'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Decode CustomerType from S/I to Reseller/Individual else Unknown', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerType'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'AcctNum + '' '' + DW.FullName', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerIDName'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'LastName + '', '' + FirstName + '' '' + MiddleName', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerFullName'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Shred Demographics: ', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BirthDate'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Shred Demographics: . Decode to Single/Married', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'MaritalStatus'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Shred Demographics: . Decode to Female/Male/Unknown', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Gender'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Shred Demographics: ', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'IncomeRange'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Shred Demographics: ', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'TotalChildren'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Shred Demographics: ', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'NumberChildrenAtHome'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Shred Demographics: ', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Education'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Shred Demographics: ', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Occupation'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Shred Demographics: . Decode to Homeowner / Not 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'ETL Rules', @value=N'Shred Demographics: ', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'NumberCarsOwned'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Shred Demographics: ', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'DateFirstPurchase'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Shred Demographics: ', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CommuteDistance'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Out of scope for Phase 1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerValueScore'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Join from Person.Contact on CustomerID; pick up the most recent 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'ETL Rules', @value=N'see notes for AddrLine1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AddressLine2'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'see notes for AddrLine1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'PostalCode'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Out of scope for Phase 1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'City'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'see notes for AddrLine1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CityAsRecorded'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Join from Person.Address.StateProvinceId', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'StateProvinceCode'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Join from Person.Address.StateProvinceId', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'StateProvince'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Join from Person.StateProvince.CountryRegionCode', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CountryCode'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Join from Person.StateProvince.CountryRegionCode', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'Country'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Shred Demographics: ', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BusinessType'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Join on Customer.SalesTerritoryID= SalesTerritory.SalesTerritoryID', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BKCustomerSalesTerritoryId'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Join to Sales.SalesTerritory on TerritoryID', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerSalesTerritory'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Join from SalesTerritory to CountryRegion on CountryRegionCode', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerSalesTerritoryCountry'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'see note for Current_Sales_Territory', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerSalesTerritoryGroup'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Shred Demographics: ', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'NumberEmployees'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Shred Demographics: ', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AnnualSales'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Shred Demographics: ', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AnnualRevenue'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Shred Demographics: ', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'YearOpened'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Shred Demographics: ', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BankName'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Out of scope for Phase 1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'OrderFrequency'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Out of scope for Phase 1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CurrentStoreValueScore'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Derived from order trxns. Post-load update.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'FirstOrderDate'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Derived from order trxns. Post-load update.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'LastOrderDate'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Standard SCD-2', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'RowIsCurrent'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Standard SCD-2', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'RowStartDate'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Standard SCD-2', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'RowEndDate'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Standard SCD-2', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'RowChangeReason'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Standard Audit dim', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AuditKey'; exec sys.sp_addextendedproperty @name=N'Comments', @value=N'Data mining', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerValueScore'; exec sys.sp_addextendedproperty @name=N'Comments', @value=N'Customer Sales Territory assigned at time of first order - does not seem to ever change.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'BKCustomerSalesTerritoryId'; exec sys.sp_addextendedproperty @name=N'Comments', @value=N'We may want to do this decoding in the ETL; copy CountryRegion to staging area?', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CustomerSalesTerritoryCountry'; exec sys.sp_addextendedproperty @name=N'Comments', @value=N'Look at pattern of orders: annual? Quarterly?', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'OrderFrequency'; exec sys.sp_addextendedproperty @name=N'Comments', @value=N'Data mining', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'CurrentStoreValueScore'; exec sys.sp_addextendedproperty @name=N'Comments', @value=N'After load, look for 1st trxn for any dim rows where first_order_date is null (ongoing, should be today''s load)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'FirstOrderDate'; exec sys.sp_addextendedproperty @name=N'Comments', @value=N'After fact load, update all dim rows where there was an order today', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'LastOrderDate'; GO /* Drop table DimCurrency */ IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DimCurrency]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE [DimCurrency] GO /* Create table DimCurrency */ CREATE TABLE [DimCurrency] ( [CurrencyKey] smallint IDENTITY NOT NULL , [BKCurrencyCode] char(3) NOT NULL , [Currency] varchar(50) NOT NULL , [IsCurrencyInUse] char(1) NOT NULL , [AuditKey] int NOT NULL , CONSTRAINT [PK_DimCurrency] PRIMARY KEY CLUSTERED ( [CurrencyKey] ) ) 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'DimCurrency' exec sys.sp_addextendedproperty @name=N'View Name', @value=N'Currency', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCurrency' exec sys.sp_addextendedproperty @name=N'Description', @value=N'Dimension table that itemizes different currencies', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCurrency' exec sys.sp_addextendedproperty @name=N'Used in schemas', @value=N'Sales', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCurrency' GO SET IDENTITY_INSERT DimCurrency ON GO INSERT INTO DimCurrency (CurrencyKey, BKCurrencyCode, Currency, IsCurrencyInUse, AuditKey) VALUES (-1, 'UNK', 'Unknown', 'Y', -1) GO SET IDENTITY_INSERT DimCurrency 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'DimCurrency', @level2type=N'COLUMN', @level2name=N'CurrencyKey'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Currency Code from trxn system', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCurrency', @level2type=N'COLUMN', @level2name=N'BKCurrencyCode'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Currency name', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCurrency', @level2type=N'COLUMN', @level2name=N'Currency'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Is this currency currently in use in the system?', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCurrency', @level2type=N'COLUMN', @level2name=N'IsCurrencyInUse'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'What process loaded this row?', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCurrency', @level2type=N'COLUMN', @level2name=N'AuditKey'; exec sys.sp_addextendedproperty @name=N'FK To', @value=N'DimAudit.AuditKey', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCurrency', @level2type=N'COLUMN', @level2name=N'AuditKey'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'1, 2, 3…', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCurrency', @level2type=N'COLUMN', @level2name=N'CurrencyKey'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'Dollar, Euro', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCurrency', @level2type=N'COLUMN', @level2name=N'Currency'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'Y, N', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCurrency', @level2type=N'COLUMN', @level2name=N'IsCurrencyInUse'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCurrency', @level2type=N'COLUMN', @level2name=N'Currency'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCurrency', @level2type=N'COLUMN', @level2name=N'IsCurrencyInUse'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCurrency', @level2type=N'COLUMN', @level2name=N'CurrencyKey'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCurrency', @level2type=N'COLUMN', @level2name=N'BKCurrencyCode'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCurrency', @level2type=N'COLUMN', @level2name=N'Currency'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'AW', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCurrency', @level2type=N'COLUMN', @level2name=N'IsCurrencyInUse'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived in ETL', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCurrency', @level2type=N'COLUMN', @level2name=N'AuditKey'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Sales', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCurrency', @level2type=N'COLUMN', @level2name=N'BKCurrencyCode'; exec sys.sp_addextendedproperty @name=N'Source Schema', @value=N'Sales', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCurrency', @level2type=N'COLUMN', @level2name=N'Currency'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'Currency', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCurrency', @level2type=N'COLUMN', @level2name=N'BKCurrencyCode'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'Currency', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCurrency', @level2type=N'COLUMN', @level2name=N'Currency'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'CurrencyCode', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCurrency', @level2type=N'COLUMN', @level2name=N'BKCurrencyCode'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'Name', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCurrency', @level2type=N'COLUMN', @level2name=N'Currency'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'nchar(3)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCurrency', @level2type=N'COLUMN', @level2name=N'BKCurrencyCode'; exec sys.sp_addextendedproperty @name=N'Source Datatype', @value=N'nvarchar(50)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCurrency', @level2type=N'COLUMN', @level2name=N'Currency'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Populate only as we encounter a new ToCurrency in the Sales data.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCurrency', @level2type=N'COLUMN', @level2name=N'BKCurrencyCode'; exec sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Derived in ETL, from the distinct set of currency codes in Sales.CurrencyRate', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCurrency', @level2type=N'COLUMN', @level2name=N'IsCurrencyInUse'; GO /* Drop table DimDate */ IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DimDate]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE [DimDate] GO /* Create table DimDate */ CREATE TABLE [DimDate] ( [DateKey] int NOT NULL , [FullDate] datetime NULL , [DateName] char(11) NULL , [DayOfWeek] tinyint NULL , [DayNameOfWeek] char(10) NULL , [DayOfMonth] tinyint NULL , [DayOfYear] smallint NULL , [WeekdayWeekend] char(7) NULL , [WeekOfYear] tinyint NULL , [MonthName] char(10) NULL , [MonthOfYear] tinyint NULL , [IsLastDayOfMonth] char(1) NULL , [CalendarQuarter] tinyint NULL , [CalendarYear] smallint NULL , [CalendarYearMonth] char(7) NULL , [CalendarYearQtr] char(7) NULL , [FiscalMonthOfYear] tinyint NULL , [FiscalQuarter] tinyint NULL , [FiscalYear] int NULL , [FiscalYearMonth] char(9) NULL , [FiscalYearQtr] char(8) NULL , [AuditKey] int NOT NULL , CONSTRAINT [PK_DimDate] PRIMARY KEY CLUSTERED ( [DateKey] ) ) 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'DimDate' exec sys.sp_addextendedproperty @name=N'View Name', @value=N'Date', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate' exec sys.sp_addextendedproperty @name=N'Description', @value=N'Date dimension contains one row for every day, beginning at 1/1/2000. There may also be rows for "hasn''t happened yet."', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate' exec sys.sp_addextendedproperty @name=N'Used in schemas', @value=N'Sales (3 roles); Finance; Currency Rates; Sales Quota (2 roles; one at Cal Qtr level)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate' GO SET IDENTITY_INSERT DimDate ON GO INSERT INTO DimDate (DateKey, FullDate, DateName, DayOfWeek, DayNameOfWeek, DayOfMonth, DayOfYear, WeekdayWeekend, WeekOfYear, MonthName, MonthOfYear, IsLastDayOfMonth, CalendarQuarter, CalendarYear, CalendarYearMonth, CalendarYearQtr, FiscalMonthOfYear, FiscalQuarter, FiscalYear, FiscalYearMonth, FiscalYearQtr, AuditKey) VALUES (-1, NULL, 'Unknown', NULL, 'Unknown', NULL, NULL, 'Unknown', NULL, 'Unknown', NULL, 'N', NULL, NULL, 'Unknown', 'Unknown', NULL, NULL, NULL, 'Unknown', 'Unknown', -1) GO SET IDENTITY_INSERT DimDate 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'DimDate', @level2type=N'COLUMN', @level2name=N'DateKey'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Full date as a SQL date (time=00:00:00)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'FullDate'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'String expression of the full date in users'' favored format', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'DateName'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Number of the day of week; Sunday = 1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'DayOfWeek'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Day name of week', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'DayNameOfWeek'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Number of the day in the month', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'DayOfMonth'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Number of the day in the year', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'DayOfYear'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Is today a weekday or a weekend', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'WeekdayWeekend'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Week of year', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'WeekOfYear'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Month name', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'MonthName'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Month of year', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'MonthOfYear'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Is this the last day of the calendar month?', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'IsLastDayOfMonth'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Calendar quarter', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'CalendarQuarter'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Calendar year', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'CalendarYear'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Calendar year and month', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'CalendarYearMonth'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Calendar year and quarter', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'CalendarYearQtr'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Fiscal month of year (1..12). FY starts in July', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'FiscalMonthOfYear'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Fiscal quarter', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'FiscalQuarter'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Fiscal year. Fiscal year begins in July.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'FiscalYear'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Fiscal year and month', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'FiscalYearMonth'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Fiscal year and quarter', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'FiscalYearQtr'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'What process loaded this row?', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'AuditKey'; exec sys.sp_addextendedproperty @name=N'FK To', @value=N'DimAudit.AuditKey', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'AuditKey'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'20041123', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'DateKey'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'11/23/2004', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'FullDate'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'23-Nov-2004', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'DateName'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'1..7', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'DayOfWeek'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'Sunday', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'DayNameOfWeek'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'1..31', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'DayOfMonth'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'1..365', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'DayOfYear'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'Weekday, Weekend', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'WeekdayWeekend'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'1..52 or 53', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'WeekOfYear'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'November', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'MonthName'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'1, 2, …, 12', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'MonthOfYear'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'Y, N', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'IsLastDayOfMonth'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'1, 2, 3, 4', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'CalendarQuarter'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'2004', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'CalendarYear'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'2004-01', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'CalendarYearMonth'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'2004Q1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'CalendarYearQtr'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'1, 2, …, 12', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'FiscalMonthOfYear'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'1, 2, 3, 4', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'FiscalQuarter'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'2004', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'FiscalYear'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'FY2004-01', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'FiscalYearMonth'; exec sys.sp_addextendedproperty @name=N'Example Values', @value=N'FY2004Q1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'FiscalYearQtr'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'DateName'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'DayOfWeek'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'DayNameOfWeek'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'DayOfMonth'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'DayOfYear'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'WeekdayWeekend'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'WeekOfYear'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'MonthName'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'MonthOfYear'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'IsLastDayOfMonth'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'CalendarQuarter'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'CalendarYear'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'CalendarYearMonth'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'CalendarYearQtr'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'FiscalMonthOfYear'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'FiscalQuarter'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'FiscalYear'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'FiscalYearMonth'; exec sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'FiscalYearQtr'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'DateKey'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'FullDate'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'DateName'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'DayOfWeek'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'DayNameOfWeek'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'DayOfMonth'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'DayOfYear'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'WeekdayWeekend'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'WeekOfYear'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'MonthName'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'MonthOfYear'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'IsLastDayOfMonth'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'CalendarQuarter'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'CalendarYear'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'CalendarYearMonth'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'CalendarYearQtr'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'FiscalMonthOfYear'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'FiscalQuarter'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'FiscalYear'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'FiscalYearMonth'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'FiscalYearQtr'; exec sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived in ETL', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'AuditKey'; exec sys.sp_addextendedproperty @name=N'Comments', @value=N'In the form: yyyymmdd', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'DateKey'; GO /* Drop table DimEmployee */ IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DimEmployee]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE [DimEmployee] GO /* Create table DimEmployee */ CREATE TABLE [DimEmployee] ( [EmployeeKey] int IDENTITY NOT NULL , [BKEmployeeID] int NOT NULL , [NationalIDNumber] char(15) NOT NULL , [EmployeeIDName] varchar(125) NULL , [EmployeeFullName] varchar(100) NULL , [EmployeeFirstName] varchar(50) NULL , [EmployeeLastName] varchar(50) NULL , [EmployeeMiddleName] varchar(50) NULL , [BKManagerID] int NULL , [ManagerFullName] varchar(100) NULL , [ManagerEmail] varchar(50) NULL , [BirthDate] datetime NULL , [MaritalStatus] char(7) NULL , [Gender] char(7) NULL , [IsSalaried] char(1) NULL , [IsCurrentEmployee] char(1) NULL , [IsSalesPerson] char(1) NULL , [JobTitle] varchar(50) NULL , [DepartmentID] int NULL , [Department] varchar(50) NULL , [DepartmentGroup] varchar(50) NULL , [BKCurrentSalesTerritoryId] int NULL , [CurrentSalesTerritory] varchar(50) NULL , [CurrentSalesTerritoryCountry] varchar(50) NULL , [CurrentSalesTerritoryGroup] varchar(50) NULL , [BKHistoricalSalesTerritoryId] int NULL , [HistoricalSalesTerritory] varchar(50) NULL , [HistoricalSalesTerritoryCountry] varchar(50) NULL , [HistoricalSalesTerritoryGroup] varchar(50) NULL , [HireDate] datetime NULL , [EmploymentEndDate] datetime NULL , [LoginID] varchar(50) NULL , [EmployeeEmail] varchar(50) NULL , [EmployeePhone] varchar(25) 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_DimEmployee] PRIMARY KEY CLUSTERED ( [EmployeeKey] ) ) 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'DimEmployee' exec sys.sp_addextendedproperty @name=N'View Name', @value=N'Employee', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimEmployee' exec sys.sp_addextendedproperty @name=N'Description', @value=N'Employee info, including sales territory for sales reps', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimEmployee' exec sys.sp_addextendedproperty @name=N'Used in schemas', @value=N'Orders', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimEmployee' GO SET IDENTITY_INSERT DimEmployee ON GO INSERT INTO DimEmployee (EmployeeKey, BKEmployeeID, NationalIDNumber, EmployeeIDName, EmployeeFullName, EmployeeFirstName, EmployeeLastName, EmployeeMiddleName, BKManagerID, ManagerFullName, ManagerEmail, BirthDate, MaritalStatus, Gender, IsSalaried, IsCurrentEmployee, IsSalesPerson, JobTitle, DepartmentID, Department, DepartmentGroup, BKCurrentSalesTerritoryId, CurrentSalesTerritory, CurrentSalesTerritoryCountry, CurrentSalesTerritoryGroup, BKHistoricalSalesTerritoryId, HistoricalSalesTerritory, HistoricalSalesTerritoryCountry, HistoricalSalesTerritoryGroup, HireDate, EmploymentEndDate, LoginID, EmployeeEmail, EmployeePhone, RowIsCurrent, RowStartDate, RowEndDate, RowChangeReason, AuditKey) VALUES (-1, -1, 'Unknown Member', 'Unknown Member', 'Unknown', 'Unknown', 'Unknown', 'Unknown', NULL, 'Unknown', 'Unknown', NULL, 'Unknown', 'Unknown', 'U', 'U', 'U', 'Unknown', -1, 'Unknown', 'Unknown', NULL, 'Unknown', 'Unknown', 'Unknown', NULL, 'Unknown', 'Unknown', 'Unknown', NULL, NULL, 'Unknown', 'Unknown', 'Unknown', 'Y', NULL, '12/31/9999', 'N/A', -1) GO SET IDENTITY_INSERT DimEmployee 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'DimEmployee', @level2type=N'COLUMN', @level2name=N'EmployeeKey'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Employee ID used in the transaction system', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimEmployee', @level2type=N'COLUMN', @level2name=N'BKEmployeeID'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'National ID Number (eg SSN) for employee', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimEmployee', @level2type=N'COLUMN', @level2name=N'NationalIDNumber'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Employee full name prepended with national ID# (eg SSN)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimEmployee', @level2type=N'COLUMN', @level2name=N'EmployeeIDName'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Employee full name: last, first middle', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimEmployee', @level2type=N'COLUMN', @level2name=N'EmployeeFullName'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Employee''s first name', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimEmployee', @level2type=N'COLUMN', @level2name=N'EmployeeFirstName'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Employee''s last name', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimEmployee', @level2type=N'COLUMN', @level2name=N'EmployeeLastName'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Employee''s middle name', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimEmployee', @level2type=N'COLUMN', @level2name=N'EmployeeMiddleName'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Manager ID used in the transaction system', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimEmployee', @level2type=N'COLUMN', @level2name=N'BKManagerID'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Manager''s full name', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimEmployee', @level2type=N'COLUMN', @level2name=N'ManagerFullName'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Manager''s email address', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimEmployee', @level2type=N'COLUMN', @level2name=N'ManagerEmail'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Employee''s birth date', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimEmployee', @level2type=N'COLUMN', @level2name=N'BirthDate'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'What is the employee''s marital status?', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimEmployee', @level2type=N'COLUMN', @level2name=N'MaritalStatus'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'What is the employee''s gender?', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimEmployee', @level2type=N'COLUMN', @level2name=N'Gender'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Is the employee salaried?', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimEmployee', @level2type=N'COLUMN', @level2name=N'IsSalaried'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Is this person currently an employee?', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimEmployee', @level2type=N'COLUMN', @level2name=N'IsCurrentEmployee'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Is the employee a sales person?', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimEmployee', @level2type=N'COLUMN', @level2name=N'IsSalesPerson'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Employee''s job title', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimEmployee', @level2type=N'COLUMN', @level2name=N'JobTitle'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Department ID from the source system', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimEmployee', @level2type=N'COLUMN', @level2name=N'DepartmentID'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Department', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimEmployee', @level2type=N'COLUMN', @level2name=N'Department'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Department group', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimEmployee', @level2type=N'COLUMN', @level2name=N'DepartmentGroup'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Natural key for the employee''s current sales territory', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimEmployee', @level2type=N'COLUMN', @level2name=N'BKCurrentSalesTerritoryId'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Current sales territory', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimEmployee', @level2type=N'COLUMN', @level2name=N'CurrentSalesTerritory'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Current sales territory country', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimEmployee', @level2type=N'COLUMN', @level2name=N'CurrentSalesTerritoryCountry'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Current sales territory group', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimEmployee', @level2type=N'COLUMN', @level2name=N'CurrentSalesTerritoryGroup'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Natural key for the employee''s historical sales territory', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimEmployee', @level2type=N'COLUMN', @level2name=N'BKHistoricalSalesTerritoryId'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Historical sales territory', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimEmployee', @level2type=N'COLUMN', @level2name=N'HistoricalSalesTerritory'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Historical sales territory country', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimEmployee', @level2type=N'COLUMN', @level2name=N'HistoricalSalesTerritoryCountry'; exec sys.sp_addextendedproperty @name=N'Description', @value=N'Historical sales territory group', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimEmployee', @level2type=N'COLUMN', @level2name=N'HistoricalS