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 Significant use of heap table structures Minimal use of heap table structures Data Architecture Large partitioned tables with clustered Clustered index table structures that indexes supporting range-restricted support detailed record lookups (1 to few scans rows per request) Very large fact tables (for example, Smaller fact tables (for example, less hundreds of gigabytes to multiple than100 GB) terabytes) Relatively small data sizes (for example, a Very large data sizes (for example, few terabytes) hundreds of terabytes to a petabyte) Minimal use of secondary indexes Heavy utilization of secondary index Database (described earlier as index-light) optimization Optimization Partitioning is common Choosing a FTDW Reference Configuration There are three general approaches to using the FTDW methodology described within this document. The first two are specific to the use of published, conforming Fast Track reference architectures for data warehousing. These approaches enable the selection of predesigned systems published as part of the FTDW program. The third approach treats core Fast Track methodology as a guideline for the creation of a user-defined data warehousing system. This final approach implies detailed workload profiling and system benchmarking in advance of purchase or deployment. It requires a high degree of technical knowledge. Option Pros Cons Very fast system set-up and Possibility of over-specified storage 1. Basic Evaluation procurement (days to weeks) or under-specified CPU Minimize cost of design and evaluation Lower infrastructure skill requirements Predefined reference architecture Evaluation takes effort and time 2. Full Evaluation tailored to expected workload (weeks to months)
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 Data Architecture Significant use of heap table structures Large partitioned tables with clustered indexes supporting range-restricted scans Very large fact tables (for example, hundreds of gigabytes to multiple terabytes) Very large data sizes (for example, hundreds of terabytes to a petabyte) Minimal use of heap table structures Clustered index table structures that support detailed record lookups (1 to few rows per request) Smaller fact tables (for example, less than100 GB) Relatively small data sizes (for example, a few terabytes) Database Optimization Minimal use of secondary indexes (described earlier as index-light) Partitioning is common Heavy utilization of secondary index optimization Choosing a FTDW Reference Configuration There are three general approaches to using the FTDW methodology described within this document. The first two are specific to the use of published, conforming Fast Track reference architectures for data warehousing. These approaches enable the selection of predesigned systems published as part of the FTDW program. The third approach treats core Fast Track methodology as a guideline for the creation of a user-defined data warehousing system. This final approach implies detailed workload profiling and system benchmarking in advance of purchase or deployment. It requires a high degree of technical knowledge. Option Pros Cons 1. Basic Evaluation Very fast system set-up and procurement (days to weeks) Minimize cost of design and evaluation Lower infrastructure skill requirements Possibility of over-specified storage or under-specified CPU 2. Full Evaluation Predefined reference architecture tailored to expected workload Evaluation takes effort and time (weeks to months)
Fast_Track_Configuration_Guide.docx