ContosoRetailDW
Tables (25)
DimAccount
- AccountKey — int — Primary key — NOT NULL
- ParentAccountKey — int
- AccountLabel — nvarchar(100)
- AccountName — nvarchar(50)
- AccountDescription — nvarchar(50)
- AccountType — nvarchar(50)
- Operator — nvarchar(50)
- CustomMembers — nvarchar(300)
- ValueType — nvarchar(50)
- CustomMemberOptions — nvarchar(200)
- ETLLoadID — int
- LoadDate — datetime
- UpdateDate — datetime
- STATISTICS_NORECOMPUTE — =
- IGNORE_DUP_KEY — =
- ALLOW_ROW_LOCKS — =
- ALLOW_PAGE_LOCKS — =
DimChannel
- ChannelKey — int — Primary key — NOT NULL
- ChannelLabel — nvarchar(100) — NOT NULL
- ChannelName — nvarchar(20)
- ChannelDescription — nvarchar(50)
- ETLLoadID — int
- LoadDate — datetime
- UpdateDate — datetime
- STATISTICS_NORECOMPUTE — =
- IGNORE_DUP_KEY — =
- ALLOW_ROW_LOCKS — =
- ALLOW_PAGE_LOCKS — =
- DATA_COMPRESSION — =
DimCurrency
- CurrencyKey — int — Primary key — NOT NULL
- CurrencyLabel — nvarchar(10) — NOT NULL
- CurrencyName — nvarchar(20) — NOT NULL
- CurrencyDescription — nvarchar(50) — NOT NULL
- ETLLoadID — int
- LoadDate — datetime
- UpdateDate — datetime
- STATISTICS_NORECOMPUTE — =
- IGNORE_DUP_KEY — =
- ALLOW_ROW_LOCKS — =
- ALLOW_PAGE_LOCKS — =
- STATISTICS_NORECOMPUTE — =
- IGNORE_DUP_KEY — =
- ALLOW_ROW_LOCKS — =
- ALLOW_PAGE_LOCKS — =
DimCustomer
- CustomerKey — int — Primary key — NOT NULL
- GeographyKey — int — Foreign key → DimGeography.GeographyKey — NOT NULL
- CustomerLabel — nvarchar(100) — NOT NULL
- Title — nvarchar(8)
- FirstName — nvarchar(50)
- MiddleName — nvarchar(50)
- LastName — nvarchar(50)
- NameStyle — bit
- BirthDate — date
- MaritalStatus — nchar(1)
- Suffix — nvarchar(10)
- Gender — nvarchar(1)
- EmailAddress — nvarchar(50)
- YearlyIncome — money
- TotalChildren — tinyint
- NumberChildrenAtHome — tinyint
- Education — nvarchar(40)
- Occupation — nvarchar(100)
- HouseOwnerFlag — nchar(1)
- NumberCarsOwned — tinyint
- AddressLine1 — nvarchar(120)
- AddressLine2 — nvarchar(120)
- Phone — nvarchar(20)
- DateFirstPurchase — date
- CustomerType — nvarchar(15)
- CompanyName — nvarchar(100)
- ETLLoadID — int
- LoadDate — datetime
- UpdateDate — datetime
- STATISTICS_NORECOMPUTE — =
- IGNORE_DUP_KEY — =
- ALLOW_ROW_LOCKS — =
- ALLOW_PAGE_LOCKS — =
- STATISTICS_NORECOMPUTE — =
- IGNORE_DUP_KEY — =
- ALLOW_ROW_LOCKS — =
- ALLOW_PAGE_LOCKS — =
DimDate
- Datekey — datetime — Primary key — NOT NULL
- FullDateLabel — nvarchar(20) — NOT NULL
- DateDescription — nvarchar(20) — NOT NULL
- CalendarYear — int — NOT NULL
- CalendarYearLabel — nvarchar(20) — NOT NULL
- CalendarHalfYear — int — NOT NULL
- CalendarHalfYearLabel — nvarchar(20) — NOT NULL
- CalendarQuarter — int — NOT NULL
- CalendarQuarterLabel — nvarchar(20)
- CalendarMonth — int — NOT NULL
- CalendarMonthLabel — nvarchar(20) — NOT NULL
- CalendarWeek — int — NOT NULL
- CalendarWeekLabel — nvarchar(20) — NOT NULL
- CalendarDayOfWeek — int — NOT NULL
- CalendarDayOfWeekLabel — nvarchar(10) — NOT NULL
- FiscalYear — int — NOT NULL
- FiscalYearLabel — nvarchar(20) — NOT NULL
- FiscalHalfYear — int — NOT NULL
- FiscalHalfYearLabel — nvarchar(20) — NOT NULL
- FiscalQuarter — int — NOT NULL
- FiscalQuarterLabel — nvarchar(20) — NOT NULL
- FiscalMonth — int — NOT NULL
- FiscalMonthLabel — nvarchar(20) — NOT NULL
- IsWorkDay — nvarchar(20) — NOT NULL
- IsHoliday — int — NOT NULL
- HolidayName — nvarchar(20) — NOT NULL
- EuropeSeason — nvarchar(50)
- NorthAmericaSeason — nvarchar(50)
- AsiaSeason — nvarchar(50)
- STATISTICS_NORECOMPUTE — =
- IGNORE_DUP_KEY — =
- ALLOW_ROW_LOCKS — =
- ALLOW_PAGE_LOCKS — =
DimEmployee
- EmployeeKey — int — Primary key — NOT NULL
- ParentEmployeeKey — int — Foreign key → DimEmployee.EmployeeKey
- FirstName — nvarchar(50) — NOT NULL
- LastName — nvarchar(50) — NOT NULL
- MiddleName — nvarchar(50)
- Title — nvarchar(50)
- HireDate — date
- BirthDate — date
- EmailAddress — nvarchar(50)
- Phone — nvarchar(25)
- MaritalStatus — nchar(1)
- EmergencyContactName — nvarchar(50)
- EmergencyContactPhone — nvarchar(25)
- SalariedFlag — bit
- Gender — nchar(1)
- PayFrequency — tinyint
- BaseRate — money
- VacationHours — smallint
- CurrentFlag — bit — NOT NULL
- SalesPersonFlag — bit — NOT NULL
- DepartmentName — nvarchar(50)
- StartDate — date
- EndDate — date
- Status — nvarchar(50)
- ETLLoadID — int
- LoadDate — datetime
- UpdateDate — datetime
- STATISTICS_NORECOMPUTE — =
- IGNORE_DUP_KEY — =
- ALLOW_ROW_LOCKS — =
- ALLOW_PAGE_LOCKS — =
DimEntity
- EntityKey — int — Primary key — NOT NULL
- EntityLabel — nvarchar(100)
- ParentEntityKey — int
- ParentEntityLabel — nvarchar(100)
- EntityName — nvarchar(50)
- EntityDescription — nvarchar(100)
- EntityType — nvarchar(100)
- StartDate — datetime
- EndDate — datetime
- Status — nvarchar(50)
- ETLLoadID — int
- LoadDate — datetime
- UpdateDate — datetime
- STATISTICS_NORECOMPUTE — =
- IGNORE_DUP_KEY — =
- ALLOW_ROW_LOCKS — =
- ALLOW_PAGE_LOCKS — =
- DATA_COMPRESSION — =
DimGeography
- GeographyKey — int — Primary key — NOT NULL
- GeographyType — nvarchar(50) — NOT NULL
- ContinentName — nvarchar(50) — NOT NULL
- CityName — nvarchar(100)
- StateProvinceName — nvarchar(100)
- RegionCountryName — nvarchar(100)
- Geometry — geometry
- ETLLoadID — int
- LoadDate — datetime
- UpdateDate — datetime
- STATISTICS_NORECOMPUTE — =
- IGNORE_DUP_KEY — =
- ALLOW_ROW_LOCKS — =
- ALLOW_PAGE_LOCKS — =
DimMachine
- MachineKey — int — Primary key — NOT NULL
- MachineLabel — nvarchar(100)
- StoreKey — int — Foreign key → DimStore.StoreKey — NOT NULL
- MachineType — nvarchar(50) — NOT NULL
- MachineName — nvarchar(100) — NOT NULL
- MachineDescription — nvarchar(200) — NOT NULL
- VendorName — nvarchar(50) — NOT NULL
- MachineOS — nvarchar(50) — NOT NULL
- MachineSource — nvarchar(100) — NOT NULL
- MachineHardware — nvarchar(100)
- MachineSoftware — nvarchar(100) — NOT NULL
- Status — nvarchar(50) — NOT NULL
- ServiceStartDate — datetime — NOT NULL
- DecommissionDate — datetime
- LastModifiedDate — datetime
- ETLLoadID — int
- LoadDate — datetime
- UpdateDate — datetime
- STATISTICS_NORECOMPUTE — =
- IGNORE_DUP_KEY — =
- ALLOW_ROW_LOCKS — =
- ALLOW_PAGE_LOCKS — =
DimOutage
- OutageKey — int — Primary key — NOT NULL
- OutageLabel — nvarchar(100) — NOT NULL
- OutageName — nvarchar(50) — NOT NULL
- OutageDescription — nvarchar(200) — NOT NULL
- OutageType — nvarchar(50) — NOT NULL
- OutageTypeDescription — nvarchar(200) — NOT NULL
- OutageSubType — nvarchar(50) — NOT NULL
- OutageSubTypeDescription — nvarchar(200) — NOT NULL
- ETLLoadID — int
- LoadDate — datetime
- UpdateDate — datetime
- STATISTICS_NORECOMPUTE — =
- IGNORE_DUP_KEY — =
- ALLOW_ROW_LOCKS — =
- ALLOW_PAGE_LOCKS — =
DimProduct
- ProductKey — int — Primary key — NOT NULL
- ProductLabel — nvarchar(255)
- ProductName — nvarchar(500)
- ProductDescription — nvarchar(400)
- ProductSubcategoryKey — int — Foreign key → DimProductSubcategory.ProductSubcategoryKey
- Manufacturer — nvarchar(50)
- BrandName — nvarchar(50)
- ClassID — nvarchar(10)
- ClassName — nvarchar(20)
- StyleID — nvarchar(10)
- StyleName — nvarchar(20)
- ColorID — nvarchar(10)
- ColorName — nvarchar(20) — NOT NULL
- Size — nvarchar(50)
- SizeRange — nvarchar(50)
- SizeUnitMeasureID — nvarchar(20)
- Weight — float
- WeightUnitMeasureID — nvarchar(20)
- UnitOfMeasureID — nvarchar(10)
- UnitOfMeasureName — nvarchar(40)
- StockTypeID — nvarchar(10)
- StockTypeName — nvarchar(40)
- UnitCost — money
- UnitPrice — money
- AvailableForSaleDate — datetime
- StopSaleDate — datetime
- Status — nvarchar(7)
- ImageURL — nvarchar(150)
- ProductURL — nvarchar(150)
- ETLLoadID — int
- LoadDate — datetime
- UpdateDate — datetime
- STATISTICS_NORECOMPUTE — =
- IGNORE_DUP_KEY — =
- ALLOW_ROW_LOCKS — =
- ALLOW_PAGE_LOCKS — =
- DATA_COMPRESSION — =
DimProductCategory
- ProductCategoryKey — int — Primary key — NOT NULL
- ProductCategoryLabel — nvarchar(100)
- ProductCategoryName — nvarchar(30) — NOT NULL
- ProductCategoryDescription — nvarchar(50) — NOT NULL
- ETLLoadID — int
- LoadDate — datetime
- UpdateDate — datetime
- STATISTICS_NORECOMPUTE — =
- IGNORE_DUP_KEY — =
- ALLOW_ROW_LOCKS — =
- ALLOW_PAGE_LOCKS — =
- STATISTICS_NORECOMPUTE — =
- IGNORE_DUP_KEY — =
- ALLOW_ROW_LOCKS — =
- ALLOW_PAGE_LOCKS — =
DimProductSubcategory
- ProductSubcategoryKey — int — Primary key — NOT NULL
- ProductSubcategoryLabel — nvarchar(100)
- ProductSubcategoryName — nvarchar(50) — NOT NULL
- ProductSubcategoryDescription — nvarchar(100)
- ProductCategoryKey — int — Foreign key → DimProductCategory.ProductCategoryKey
- ETLLoadID — int
- LoadDate — datetime
- UpdateDate — datetime
- STATISTICS_NORECOMPUTE — =
- IGNORE_DUP_KEY — =
- ALLOW_ROW_LOCKS — =
- ALLOW_PAGE_LOCKS — =
- STATISTICS_NORECOMPUTE — =
- IGNORE_DUP_KEY — =
- ALLOW_ROW_LOCKS — =
- ALLOW_PAGE_LOCKS — =
DimPromotion
- PromotionKey — int — Primary key — NOT NULL
- PromotionLabel — nvarchar(100)
- PromotionName — nvarchar(100)
- PromotionDescription — nvarchar(255)
- DiscountPercent — float
- PromotionType — nvarchar(50)
- PromotionCategory — nvarchar(50)
- StartDate — datetime — NOT NULL
- EndDate — datetime
- MinQuantity — int
- MaxQuantity — int
- ETLLoadID — int
- LoadDate — datetime
- UpdateDate — datetime
- STATISTICS_NORECOMPUTE — =
- IGNORE_DUP_KEY — =
- ALLOW_ROW_LOCKS — =
- ALLOW_PAGE_LOCKS — =
- STATISTICS_NORECOMPUTE — =
- IGNORE_DUP_KEY — =
- ALLOW_ROW_LOCKS — =
- ALLOW_PAGE_LOCKS — =
DimSalesTerritory
- SalesTerritoryKey — int — Primary key — NOT NULL
- GeographyKey — int — Foreign key → DimGeography.GeographyKey — NOT NULL
- SalesTerritoryLabel — nvarchar(100)
- SalesTerritoryName — nvarchar(50) — NOT NULL
- SalesTerritoryRegion — nvarchar(50) — NOT NULL
- SalesTerritoryCountry — nvarchar(50) — NOT NULL
- SalesTerritoryGroup — nvarchar(50)
- SalesTerritoryLevel — nvarchar(10)
- SalesTerritoryManager — int
- StartDate — datetime
- EndDate — datetime
- Status — nvarchar(50)
- ETLLoadID — int
- LoadDate — datetime
- UpdateDate — datetime
- STATISTICS_NORECOMPUTE — =
- IGNORE_DUP_KEY — =
- ALLOW_ROW_LOCKS — =
- ALLOW_PAGE_LOCKS — =
- STATISTICS_NORECOMPUTE — =
- IGNORE_DUP_KEY — =
- ALLOW_ROW_LOCKS — =
- ALLOW_PAGE_LOCKS — =
DimScenario
- ScenarioKey — int — Primary key — NOT NULL
- ScenarioLabel — nvarchar(100) — NOT NULL
- ScenarioName — nvarchar(20)
- ScenarioDescription — nvarchar(50)
- ETLLoadID — int
- LoadDate — datetime
- UpdateDate — datetime
- STATISTICS_NORECOMPUTE — =
- IGNORE_DUP_KEY — =
- ALLOW_ROW_LOCKS — =
- ALLOW_PAGE_LOCKS — =
DimStore
- StoreKey — int — Primary key — NOT NULL
- GeographyKey — int — Foreign key → DimGeography.GeographyKey — NOT NULL
- StoreManager — int
- StoreType — nvarchar(15)
- StoreName — nvarchar(100) — NOT NULL
- StoreDescription — nvarchar(300) — NOT NULL
- Status — nvarchar(20) — NOT NULL
- OpenDate — datetime — NOT NULL
- CloseDate — datetime
- EntityKey — int
- ZipCode — nvarchar(20)
- ZipCodeExtension — nvarchar(10)
- StorePhone — nvarchar(15)
- StoreFax — nvarchar(14)
- AddressLine1 — nvarchar(100)
- AddressLine2 — nvarchar(100)
- CloseReason — nvarchar(20)
- EmployeeCount — int
- SellingAreaSize — float
- LastRemodelDate — datetime
- GeoLocation — geography
- Geometry — geometry
- ETLLoadID — int
- LoadDate — datetime
- UpdateDate — datetime
- STATISTICS_NORECOMPUTE — =
- IGNORE_DUP_KEY — =
- ALLOW_ROW_LOCKS — =
- ALLOW_PAGE_LOCKS — =
- DATA_COMPRESSION — =
FactExchangeRate
- ExchangeRateKey — int — Primary key — NOT NULL
- CurrencyKey — int — Foreign key → DimCurrency.CurrencyKey — NOT NULL
- DateKey — datetime — Foreign key → DimDate.Datekey — NOT NULL
- AverageRate — float — NOT NULL
- EndOfDayRate — float — NOT NULL
- ETLLoadID — int
- LoadDate — datetime
- UpdateDate — datetime
- STATISTICS_NORECOMPUTE — =
- IGNORE_DUP_KEY — =
- ALLOW_ROW_LOCKS — =
- ALLOW_PAGE_LOCKS — =
- DATA_COMPRESSION — =
FactITMachine
- ITMachinekey — int — Primary key — NOT NULL
- MachineKey — int — Foreign key → DimMachine.MachineKey — NOT NULL
- Datekey — datetime — Foreign key → DimDate.Datekey — NOT NULL
- CostAmount — money
- CostType — nvarchar(200) — NOT NULL
- ETLLoadID — int
- LoadDate — datetime
- UpdateDate — datetime
- STATISTICS_NORECOMPUTE — =
- IGNORE_DUP_KEY — =
- ALLOW_ROW_LOCKS — =
- ALLOW_PAGE_LOCKS — =
- DATA_COMPRESSION — =
FactITSLA
- ITSLAkey — int — Primary key — NOT NULL
- DateKey — datetime — Foreign key → DimDate.Datekey — NOT NULL
- StoreKey — int — Foreign key → DimStore.StoreKey — NOT NULL
- MachineKey — int — Foreign key → DimMachine.MachineKey — NOT NULL
- OutageKey — int — Foreign key → DimOutage.OutageKey — NOT NULL
- OutageStartTime — datetime — NOT NULL
- OutageEndTime — datetime — NOT NULL
- DownTime — int — NOT NULL
- ETLLoadID — int
- LoadDate — datetime
- UpdateDate — datetime
- STATISTICS_NORECOMPUTE — =
- IGNORE_DUP_KEY — =
- ALLOW_ROW_LOCKS — =
- ALLOW_PAGE_LOCKS — =
FactInventory
- InventoryKey — int — Primary key — NOT NULL
- DateKey — datetime — Foreign key → DimDate.Datekey — NOT NULL
- StoreKey — int — Foreign key → DimStore.StoreKey — NOT NULL
- ProductKey — int — Foreign key → DimProduct.ProductKey — NOT NULL
- CurrencyKey — int — Foreign key → DimCurrency.CurrencyKey — NOT NULL
- OnHandQuantity — int — NOT NULL
- OnOrderQuantity — int — NOT NULL
- SafetyStockQuantity — int
- UnitCost — money — NOT NULL
- DaysInStock — int
- MinDayInStock — int
- MaxDayInStock — int
- Aging — int
- ETLLoadID — int
- LoadDate — datetime
- UpdateDate — datetime
- STATISTICS_NORECOMPUTE — =
- IGNORE_DUP_KEY — =
- ALLOW_ROW_LOCKS — =
- ALLOW_PAGE_LOCKS — =
- DATA_COMPRESSION — =
FactOnlineSales
- OnlineSalesKey — int — Primary key — NOT NULL
- DateKey — datetime — Foreign key → DimDate.Datekey — NOT NULL
- StoreKey — int — Foreign key → DimStore.StoreKey — NOT NULL
- ProductKey — int — Foreign key → DimProduct.ProductKey — NOT NULL
- PromotionKey — int — Foreign key → DimPromotion.PromotionKey — NOT NULL
- CurrencyKey — int — Foreign key → DimCurrency.CurrencyKey — NOT NULL
- CustomerKey — int — Foreign key → DimCustomer.CustomerKey — NOT NULL
- SalesOrderNumber — nvarchar(20) — NOT NULL
- SalesOrderLineNumber — int
- SalesQuantity — int — NOT NULL
- SalesAmount — money — NOT NULL
- ReturnQuantity — int — NOT NULL
- ReturnAmount — money
- DiscountQuantity — int
- DiscountAmount — money
- TotalCost — money — NOT NULL
- UnitCost — money
- UnitPrice — money
- ETLLoadID — int
- LoadDate — datetime
- UpdateDate — datetime
- STATISTICS_NORECOMPUTE — =
- IGNORE_DUP_KEY — =
- ALLOW_ROW_LOCKS — =
- ALLOW_PAGE_LOCKS — =
- DATA_COMPRESSION — =
FactSales
- SalesKey — int — Primary key — NOT NULL
- DateKey — datetime — Foreign key → DimDate.Datekey — NOT NULL
- channelKey — int — Foreign key → DimChannel.ChannelKey — NOT NULL
- StoreKey — int — Foreign key → DimStore.StoreKey — NOT NULL
- ProductKey — int — Foreign key → DimProduct.ProductKey — NOT NULL
- PromotionKey — int — Foreign key → DimPromotion.PromotionKey — NOT NULL
- CurrencyKey — int — Foreign key → DimCurrency.CurrencyKey — NOT NULL
- UnitCost — money — NOT NULL
- UnitPrice — money — NOT NULL
- SalesQuantity — int — NOT NULL
- ReturnQuantity — int — NOT NULL
- ReturnAmount — money
- DiscountQuantity — int
- DiscountAmount — money
- TotalCost — money — NOT NULL
- SalesAmount — money — NOT NULL
- ETLLoadID — int
- LoadDate — datetime
- UpdateDate — datetime
- STATISTICS_NORECOMPUTE — =
- IGNORE_DUP_KEY — =
- ALLOW_ROW_LOCKS — =
- ALLOW_PAGE_LOCKS — =
- DATA_COMPRESSION — =
FactSalesQuota
- SalesQuotaKey — int — Primary key — NOT NULL
- ChannelKey — int — Foreign key → DimChannel.ChannelKey — NOT NULL
- StoreKey — int — Foreign key → DimStore.StoreKey — NOT NULL
- ProductKey — int — Foreign key → DimProduct.ProductKey — NOT NULL
- DateKey — datetime — Foreign key → DimDate.Datekey — NOT NULL
- CurrencyKey — int — Foreign key → DimCurrency.CurrencyKey — NOT NULL
- ScenarioKey — int — Foreign key → DimScenario.ScenarioKey — NOT NULL
- SalesQuantityQuota — money — NOT NULL
- SalesAmountQuota — money — NOT NULL
- GrossMarginQuota — money — NOT NULL
- ETLLoadID — int
- LoadDate — datetime
- UpdateDate — datetime
- STATISTICS_NORECOMPUTE — =
- IGNORE_DUP_KEY — =
- ALLOW_ROW_LOCKS — =
- ALLOW_PAGE_LOCKS — =
- DATA_COMPRESSION — =
FactStrategyPlan
- StrategyPlanKey — int — Primary key — NOT NULL
- Datekey — datetime — Foreign key → DimDate.Datekey — NOT NULL
- EntityKey — int — Foreign key → DimEntity.EntityKey — NOT NULL
- ScenarioKey — int — Foreign key → DimScenario.ScenarioKey — NOT NULL
- AccountKey — int — Foreign key → DimAccount.AccountKey — NOT NULL
- CurrencyKey — int — Foreign key → DimCurrency.CurrencyKey — NOT NULL
- ProductCategoryKey — int — Foreign key → DimProductCategory.ProductCategoryKey
- Amount — money — NOT NULL
- ETLLoadID — int
- LoadDate — datetime
- UpdateDate — datetime
- STATISTICS_NORECOMPUTE — =
- IGNORE_DUP_KEY — =
- ALLOW_ROW_LOCKS — =
- ALLOW_PAGE_LOCKS — =
- DATA_COMPRESSION — =