Descriptive Data Warehouse Workload Attributes The FTDW workload can be defined through the properties of the following subject areas related to database operations: User requirements and access pattern Data model Data architecture Database optimization The following table summarizes data warehouse workload attributes; contrast is provided through comparison to an OLTP workload. Attribute Workload Affinity Data Warehouse OLTP Read-mostly (90%-10%) Balanced read-update ratio (60%-40%) Use Case Updates generally limited to data quality Concurrent query throughput Description requirements characterized by operational needs High-volume bulk inserts Fine-grained inserts and updates Medium to low overall query High transaction throughput (for example, concurrency; peak concurrent query 10s K/sec) request ranging from 10-30. Medium-to-high overall user concurrency. Concurrent query throughput Peak concurrent query request ranging characterized by analysis and reporting from 50-100 or more needs Usually very short transactions (for Large range scans and/or aggregations example, discrete minimal row lookups) Complex queries (filter, join, group-by, aggregation) Highly normalized centralized data Highly normalized operational data model Data Model warehouse model Frequent denormalization for decision Denormalization in support of reporting support; high concurrency, low latency requirements often serviced from BI discrete lookups applications such as SQL Server Historical retention of data is limited Analysis Services Denormalized data models extracted from Dimensional data structures hosted on other source systems in support of the database with relatively low operational event decision making concurrency, high volume analytical requests Large range scans are common Ad-hoc analytical use cases
Descriptive Data Warehouse Workload Attributes The FTDW workload can be defined through the properties of the following subject areas related to database operations: User requirements and access pattern Data model Data architecture Database optimization The following table summarizes data warehouse workload attributes; contrast is provided through comparison to an OLTP workload. Attribute Workload Affinity Data Warehouse OLTP Use Case Description Read-mostly (90%-10%) Updates generally limited to data quality requirements High-volume bulk inserts Medium to low overall query concurrency; peak concurrent query request ranging from 10-30. Concurrent query throughput characterized by analysis and reporting needs Large range scans and/or aggregations Complex queries (filter, join, group-by, aggregation) Balanced read-update ratio (60%-40%) Concurrent query throughput characterized by operational needs Fine-grained inserts and updates High transaction throughput (for example, 10s K/sec) Medium-to-high overall user concurrency. Peak concurrent query request ranging from 50-100 or more Usually very short transactions (for example, discrete minimal row lookups) Data Model Highly normalized centralized data warehouse model Denormalization in support of reporting requirements often serviced from BI applications such as SQL Server Analysis Services Dimensional data structures hosted on the database with relatively low concurrency, high volume analytical requests Large range scans are common Ad-hoc analytical use cases Highly normalized operational data model Frequent denormalization for decision support; high concurrency, low latency discrete lookups Historical retention of data is limited Denormalized data models extracted from other source systems in support of operational event decision making