AdventureWorks
Tables
Complete reference guide to all tables in the AdventureWorks Data Warehouse. Understand the structure, relationships, and key columns for effective data analysis.
Sales & Territory
DimSalesTerritory
Contains information about sales territories—regions, countries, and sales groups where sales activities are conducted. Useful for analyzing sales by geography.
Key Columns
- SalesTerritoryKey: Unique ID for the territory
- SalesTerritoryRegion: Region name (e.g., Northwest)
- SalesTerritoryCountry: Country name
- SalesTerritoryGroup: Grouping of territories for higher-level analysis
Business Scenarios
DimScenario
Stores different business scenarios such as Actual, Budget, or Forecast. Used in financial or sales analysis to compare different scenarios.
Key Columns
- ScenarioKey: Scenario identifier
- ScenarioName: Name of the scenario (e.g., Actual, Budget)
Fact Tables
FactAdditionalInternationalProductDescription
Holds product descriptions localized by culture or language, enabling multi-lingual reporting.
Key Columns
- ProductKey: Identifier for the product
- CultureName: Language or culture code (e.g., 'en-US')
- ProductDescription: Localized product description text
FactCallCenter
Tracks performance and operational metrics for call centers such as number of operators, calls handled, and service grades. Important for customer service analysis.
Key Columns
- FactCallCenterID: Unique call center record ID
- DateKey: Date of record
- TotalOperators: Number of operators on shift
- Calls: Number of calls handled
- ServiceGrade: Quality grade or score for service
FactCurrencyRate
Stores currency exchange rates on specific dates. Vital for converting sales or financial figures into a common currency for analysis.
Key Columns
- CurrencyKey: Currency identifier
- DateKey: Date of the rate
- AverageRate: Average exchange rate for the day
- EndOfDayRate: Exchange rate at market close
FactFinance
Captures financial transactions including amounts related to departments, accounts, and organizations across different scenarios. Used for detailed financial reporting.
Key Columns
- FinanceKey: Unique finance record ID
- DateKey: Date of transaction
- OrganizationKey: Organization involved
- AccountKey: Financial account
- Amount: Transaction amount
FactInternetSales
Main fact table for online sales orders, including quantities, pricing, discounts, and dates. Central for e-commerce sales analysis.
Key Columns
- ProductKey: Product sold
- OrderDateKey: Order date
- SalesOrderNumber: Unique order identifier
- OrderQuantity: Number of units ordered
- SalesAmount: Total sales value
FactInternetSalesReason
Links internet sales orders to reasons for purchase or promotions used, supporting analysis of sales drivers.
Key Columns
- SalesOrderNumber: Order identifier
- SalesReasonKey: Reason or promotion ID
FactProductInventory
Tracks inventory movements such as stock coming in or going out, and current balance per product and date. Used to manage stock levels.
Key Columns
- ProductKey: Product ID
- MovementDate: Date of inventory movement
- UnitsIn: Quantity received
- UnitsOut: Quantity shipped/sold
- UnitsBalance: Ending inventory
FactResellerSales
Details sales through resellers, including product, customer, and financial data. Useful for channel sales analysis.
Key Columns
- ResellerKey: Reseller ID
- OrderDateKey: Sale date
- SalesOrderNumber: Order ID
- SalesAmount: Sales revenue
FactSalesQuota
Stores sales target quotas for employees over specific time periods, supporting performance evaluation and goal tracking.
Key Columns
- EmployeeKey: Employee ID
- CalendarYear: Year of quota
- SalesAmountQuota: Sales target amount
FactSurveyResponse
Contains customer survey responses linked to product categories, aiding in customer satisfaction and product feedback analysis.
Key Columns
- SurveyResponseKey: Survey record ID
- CustomerKey: Customer ID
- ProductCategoryKey: Category surveyed
NewFactCurrencyRate
An updated currency rates table with similar data to FactCurrencyRate, possibly for performance or historical reasons.
Key Columns
- CurrencyID: Currency code
- CurrencyDate: Date of rate
- AverageRate: Exchange rate
Customer & Prospects
ProspectiveBuyer
Holds demographic and contact info for potential customers, used for marketing and lead generation.
Key Columns
- ProspectiveBuyerKey: Unique prospect ID
- FirstName, LastName: Names
- YearlyIncome: Estimated income
- EmailAddress: Contact email
DimCustomer
Customer master data including demographics, contact details, and purchasing attributes.
Key Columns
- CustomerKey: Customer ID
- FirstName, LastName: Names
- EmailAddress: Email
- YearlyIncome: Income
Financial Dimensions
DimAccount
Dimension for financial accounts, their hierarchy, and types used in finance reporting.
Key Columns
- AccountKey: Account ID
- AccountDescription: Description
- AccountType: Type (e.g., Asset, Liability)
DimCurrency
Dimension listing currencies used in transactions with keys and names.
Key Columns
- CurrencyKey: Currency ID
- CurrencyName: Currency name (e.g., USD)
Time & Calendar
DimDate
Date dimension table with extensive calendar info like day, week, month, quarter, fiscal periods, and multilingual names for dates.
Key Columns
- DateKey: Date ID (YYYYMMDD)
- FullDateAlternateKey: Actual date
- CalendarYear, CalendarQuarter
- EnglishMonthName, SpanishMonthName
Organization & Employee
DimDepartmentGroup
Organizational department group hierarchy for categorizing departments in reports.
Key Columns
- DepartmentGroupKey: Dept group ID
- DepartmentGroupName: Group name
DimEmployee
Employee dimension with personal info, job details, and contact info, useful for HR and sales analysis.
Key Columns
- EmployeeKey: Employee ID
- FirstName, LastName
- HireDate, BirthDate
- EmailAddress
DimOrganization
Organization hierarchy and ownership details for business units or companies in the data warehouse.
Key Columns
- OrganizationKey: Org ID
- OrganizationName
- PercentageOfOwnership
Geography
DimGeography
Geographical dimension with detailed location info for customers and sales territories.
Key Columns
- GeographyKey: Geo ID
- City, StateProvinceCode, CountryRegionCode
Product Catalog
DimProduct
Product catalog dimension containing product attributes, descriptions in multiple languages, costs, pricing, and status.
Key Columns
- ProductKey: Product ID
- EnglishProductName, SpanishProductName
- StandardCost, ListPrice
- ProductLine, ProductSubcategoryKey
DimProductCategory
High-level product categories grouping related products.
Key Columns
- ProductCategoryKey: Category ID
- EnglishProductCategoryName
DimProductSubcategory
Subcategories under product categories providing more granular grouping.
Key Columns
- ProductSubcategoryKey: Subcategory ID
- EnglishProductSubcategoryName
- ProductCategoryKey
Marketing & Sales
DimPromotion
Stores details on promotions and discounts used in sales campaigns.
Key Columns
- PromotionKey: Promotion ID
- EnglishPromotionName
- DiscountPct
- StartDate, EndDate
DimReseller
Information about resellers including company name, contact details, and sales territory.
Key Columns
- ResellerKey: Reseller ID
- ResellerName
- Phone, EmailAddress
- AnnualSales
DimSalesReason
Reasons behind sales promotions or events, supporting cause analysis of sales spikes.
Key Columns
- SalesReasonKey: Reason ID
- SalesReasonName
- SalesReasonReasonType
What's Next?
Now that you understand the table structure, explore sample queries and discover key business questions to ask.