WideWorldImportersDW database catalog
Applies to: 
SQL Server 
Azure SQL Database
Azure Synapse Analytics
Analytics Platform System (PDW)
Explanations for the schemas, tables, and stored procedures in the WideWorldImportersDW database.
The WideWorldImportersDW database is used for data warehousing and analytical processing. The transactional data about sales and purchases is generated in the WideWorldImporters database, and loaded into the WideWorldImportersDW database using a daily ETL process.
The data in WideWorldImportersDW thus mirrors the data in WideWorldImporters, but the tables are organized differently. While WideWorldImporters has a traditional normalized schema, WideWorldImportersDW uses the star schema approach for its table design. Besides the fact and dimension tables, the database includes a number of staging tables that are used in the ETL process.
Schemas
The different types of tables are organized in three schemas.
| Schema | Description |
|---|---|
| Dimension | Dimension tables. |
| Fact | Fact tables. |
| Integration | Staging tables and other objects needed for ETL. |
Tables
The dimension and fact tables are listed below. The tables in the Integration schema are used only for the ETL process, and are not listed.
Dimension tables
WideWorldImportersDW has the following dimension tables. The description includes the relationship with the source tables in the WideWorldImporters database.
| Table | Source tables |
|---|---|
| City | Application.Cities, Application.StateProvinces, Application.Countries. |
| Customer | Sales.Customers, Sales.BuyingGroups, Sales.CustomerCategories. |
| Date | New table with information about dates, including financial year (based on November 1st start for financial year). |
| Employee | Application.People. |
| StockItem | Warehouse.StockItems, Warehouse.Colors, Warehouse.PackageType. |
| Supplier | Purchasing.Suppliers, Purchasing.SupplierCategories. |
| PaymentMethod | Application.PaymentMethods. |
| TransactionType | Application.TransactionTypes. |
Fact tables
WideWorldImportersDW has the following fact tables. The description includes the relationship with the source tables in the WideWorldImporters database, as well as the classes of analytics/reporting queries each fact table is typically used with.
| Table | Source tables | Sample Analytics |
|---|---|---|
| Order | Sales.Orders and Sales.OrderLines |
Sales people, picker/packer productivity, and on time to pick orders. In addition, low stock situations leading to back orders. |
| Sale | Sales.Invoices and Sales.InvoiceLines |
Sales dates, delivery dates, profitability over time, profitability by sales person. |
| Purchase | Purchasing.PurchaseOrderLines |
Expected vs actual lead times |
| Transaction | Sales.CustomerTransactions and Purchasing.SupplierTransactions |
Measuring issue dates vs finalization dates, and amounts. |
| Movement | Warehouse.StockTransactions |
Movements over time. |
| Stock Holding | Warehouse.StockItemHoldings |
On-hand stock levels and value. |
Stored procedures
The stored procedures are used primarily for the ETL process and for configuration purposes.
Any extensions of the sample are encouraged to use the Reports schema for Reporting Services reports, and the PowerBI schema for Power-BI access.
Application Schema
These procedures are used to configure the sample. They are used to apply enterprise edition features to the standard edition version of the sample, add PolyBase, and reseed ETL.
| Procedure | Purpose |
|---|---|
| Configuration_ApplyPartitionedColumnstoreIndexing | Applies both partitioning and columnstore indexes for fact tables. |
| Configuration_ConfigureForEnterpriseEdition | Applies partitioning, columnstore indexing and in-memory. |
| Configuration_EnableInMemory | Replaces the integration staging tables with SCHEMA_ONLY memory-optimized tables to improve ETL performance. |
| Configuration_ApplyPolyBase | Configures an external data source, file format, and table. |
| Configuration_PopulateLargeSaleTable | Applies enterprise edition changes, then populates a larger amount of data for the 2012 calendar year as additional history. |
| Configuration_ReseedETL | Removes existing data and restarts the ETL seeds. This allows for repopulating the OLAP database to match updated rows in the OLTP database. |
Integration Schema
Procedures used in the ETL process fall in these categories:
- Helper procedures for the ETL package - All Get* procedures.
- Procedures used by the ETL package for migrating staged data into the DW tables - All Migrate* procedures.
PopulateDateDimensionForYear- Takes a year and ensures that all dates for that year are populated in theDimension.Datetable.
Sequences Schema
Procedures to configure the sequences in the database.
| Procedure | Purpose |
|---|---|
| ReseedAllSequences | Calls the procedure ReseedSequenceBeyondTableValue for all sequences. |
| ReseedSequenceBeyondTableValue | Used to reposition the next sequence value beyond the value in any table that uses the same sequence. (Like a DBCC CHECKIDENT for identity columns equivalent for sequences but across potentially multiple tables.) |