使用CCI时是否应将所有日期维度字段存入SQL Server事实表？

Great question—this is a super common dilemma when optimizing columnstore fact tables for date-heavy analytics, especially with tools like Power BI. Let me share what I’ve learned from working with SQL Server CCI implementations over the years, plus actionable steps for your testing.

Storage Considerations #

• Columnstore compression is excellent at handling low-cardinality, repetitive data like date attributes (year, month, quarter, date key). Even if you embed these fields directly in the fact table, the storage overhead is usually minimal—often only 5-10% increase compared to keeping them in a separate dimension. Archival compression (if you’re using it for older fact data) will shrink these repeated values even more.
• The alternative (joining to a date dimension) saves you redundant storage, but you pay for it with join overhead. For date fields specifically, the storage tradeoff is almost always worth the performance boost, since date attributes are small (integers or short date types) and compress extremely well.

Performance Impacts #

• Power BI queries that slice, dice, or filter by date attributes (YTD comparisons, monthly rollups, holiday filters) will run faster without a join to the date dimension. CCI scans are already optimized for analytical workloads, but removing a join simplifies the query plan entirely—no more index seeks/scans on the date dimension, which can become a bottleneck during high-concurrency reporting.
• A word of caution: don’t overdo it. Only embed the date attributes your team actually uses in reports. Adding every possible derived field (like fiscal week number, holiday flags, or custom date categories) will start to bloat the table unnecessarily and give diminishing returns on performance. Stick to the high-frequency filters/aggregates first.

Real-World Practitioner Experience #

• Most teams I’ve collaborated with that use CCI for BI workloads end up flattening core date attributes into their fact tables. Both Power BI’s Import and DirectQuery modes benefit: Import mode can pre-aggregate these fields during refresh, while DirectQuery avoids cross-table joins that slow down interactive reporting.
• The only exception is if your date dimension has dynamic attributes—like a is_current_month flag that updates daily. Embedding these in the fact table would require regular updates to millions of rows, which is way more expensive than updating a single row in the date dimension. For static date attributes (year, month number, calendar quarter), flattening is a no-brainer.

Testing Recommendations for Your Environment #

To validate this for your specific workload, set up a side-by-side test:

• Create two copies of your fact table: one with embedded date attributes (year, month, quarter, etc.), one that relies on joining to your existing date dimension.
• Run your most common date-based queries against both tables. Enable SET STATISTICS IO, TIME ON to compare logical reads, CPU usage, and execution plans—look for reduced CPU time and fewer reads on the flattened table.
• Compare storage sizes using sp_spaceused or SSMS’s storage reports to quantify the overhead.
• Simulate concurrency: have multiple users run date-focused queries at the same time to see if the flattened table handles load better (no joins mean less resource contention on the date dimension).

内容的提问来源于stack exchange，提问作者Chris Harrington