Benefit of having time dimension in a star schema?

Let me answer this question with a scenario starting with a simple Transaction table. When our business started, management wanted to know the 'name' of the month, so I've included that information in the table.

DECLARE @Transactions TABLE (
    TransactionId INT
    ,UserId VARCHAR(10)
    ,CountryId INT
    ,TransactionDate DATE
    ,[MonthName] VARCHAR(20)
    ,SalesAmount DECIMAL(18, 2)
    )

Business has been good and we already have 1 million rows in our Transactions table. In fact, business is so good that management is now asking more in depth questions about our sales. They wanted to know what 'quarter' the sale was made.

ALTER TABLE Transactions ADD [QuarterName] VARCHAR(10)
UPDATE Transactions SET QuarterName = ... 

We just updated 1 million rows.

As time goes by, management starts asking more and more questions about our sales.

• What DayOfTheWeek was that sale made?
• Was that a holiday?
• Was the moon full on that day?

ALTER TABLE Transaction ADD ...

UPDATE TABLE SET ...

Hopefully you can see where this is going. Additionally, all of that redundant data on each and every Transaction row can contribute to reduced performance and increase resource utilization (memory, disk space, etc.). Our databases are bigger and take longer to back up. All of the redundant data takes up memory.

With a Date Dimension table, all of that information is stored in one place. A Date Dimension table with dates from 2000-01-01 to 2100-01-01 contains just 36525 rows. Anytime we want to track a new attribute of a date, we only have to alter that table by adding the additional attribute and update 36525 rows.

When we want specific information about the 'Date' attributes of a sale, we simply join up against the Date Dimension table

Additionally, the data in a Date Dimension is consistent. January is spelled correctly, Saturday is spelled correctly, etc. Storing this kind of data in the Transaction table can lead to all kinds of discrepancies with incorrect spellings, etc.

For more information on the creation of a Date Dimension table, check out Creating a date dimension or calendar table in SQL Server