Answer: Create a date dimension table that has an integer key in the format of YYYYMMDD., In the fact table, use integer columns for the date fields.

## Explanation In designing a star schema for optimal query performance with date-based filtering and fiscal calendar aggregations, the most efficient approach involves using integer-based date keys rather than DateTime data types. Here's the detailed reasoning: ### **Selected Options: C and D** **C: Create a date dimension table that has an integer key in the format of YYYYMMDD** - **Performance Benefits**: Integer keys provide faster join operations compared to DateTime keys. Integer comparisons are computationally simpler and more efficient for database engines. - **Storage Efficiency**: Integer keys (4 bytes) are more compact than DateTime keys (8 bytes), reducing storage requirements and improving cache utilization. - **Fiscal Calendar Support**: A dedicated date dimension table allows for pre-calculated fiscal calendar attributes (fiscal year, fiscal quarter, fiscal month), eliminating the need for runtime calculations during queries. - **Consistency**: Integer keys ensure consistent date representation across the data warehouse. **D: In the fact table, use integer columns for the date fields** - **Optimized Joins**: When both the dimension table and fact table use integer date keys, join operations become highly efficient with minimal data type conversion overhead. - **Index Performance**: Integer columns typically have better indexing performance for range queries on arbitrary date ranges. - **Query Simplicity**: Direct integer comparisons for date ranges are more straightforward than DateTime range operations. ### **Why Other Options Are Less Suitable** **A: Create a date dimension table that has a DateTime key** - **Performance Overhead**: DateTime keys require more complex comparison operations and are less efficient for join operations. - **Storage Inefficiency**: DateTime data types consume more storage space than integer representations. **B: Use built-in SQL functions to extract date attributes** - **Runtime Performance Impact**: Using functions during query execution prevents predicate pushdown and forces full table scans, significantly degrading performance. - **Limited Fiscal Calendar Support**: Built-in functions typically don't support custom fiscal calendar definitions without complex calculations. **E: Use DateTime columns for the date fields** - **Join Inefficiency**: DateTime-to-integer joins (if using option C) would require implicit conversions, impacting query performance. - **Storage Overhead**: DateTime columns consume twice the storage of integer columns for the same date information. ### **Best Practice Justification** The combination of options C and D follows established data warehousing best practices: - **Star Schema Optimization**: Using surrogate integer keys between fact and dimension tables is a fundamental star schema design principle. - **Query Performance**: Integer-based date keys enable efficient filtering, sorting, and aggregation operations. - **Fiscal Calendar Requirements**: A dedicated date dimension with pre-calculated fiscal attributes supports complex business calendar requirements without runtime computation overhead. - **Scalability**: This approach scales well with large datasets and maintains performance consistency across various query patterns.