WideWorldImportersDW database catalog
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.
The different types of tables are organized in three schemas.
|Integration||Staging tables and other objects needed for ETL.|
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.
WideWorldImportersDW has the following dimension tables. The description includes the relationship with the source tables in the WideWorldImporters database.
|Date||New table with information about dates, including financial year (based on November 1st start for financial year).|
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|
||Sales people, picker/packer productivity, and on time to pick orders. In addition, low stock situations leading to back orders.|
||Sales dates, delivery dates, profitability over time, profitability by sales person.|
||Expected vs actual lead times|
||Measuring issue dates vs finalization dates, and amounts.|
||Movements over time.|
||On-hand stock levels and value.|
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.
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.
|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.|
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 the
Procedures to configure the sequences in the database.
|ReseedAllSequences||Calls the procedure
|ReseedSequenceBeyondTableValue||Used to reposition the next sequence value beyond the value in any table that uses the same sequence. (Like a