how to derive season in a date dimension

Question

I'm implementing a DataWarehouse date dimension. According to my scenario I have to mark days between October and January as Season_A and days between April and August as Season_B.

I have created the dimDate table as below:

CREATE TABLE dbo.dimDate
(
DateKey INT NOT NULL,
FullDate DATE NOT NULL,
MonthNumberName NVARCHAR(15) NULL,
CalendarQuarter TINYINT NULL,
CalendarYear SMALLINT NULL,
Season NVARCHAR(10) 
CONSTRAINT PK_Dates PRIMARY KEY (DateKey)
);

Answer 1

Baring other concerns / factors, my initial reaction to this would be to use a computed column and calculate the Season using a simple between clause in the stored procedure

Sample Implementation pseudo-code

---

CREATE TABLE dbo.dimDate 
( 
   DateKey INT NOT NULL
  ,FullDate DATE NOT NULL
  ,MonthNumberName NVARCHAR(15) NULL
  ,CalendarQuarter TINYINT NULL
  ,CalendarYear SMALLINT NULL
  ,Season NVARCHAR(10) as GetSeason(FullDate)
  ,CONSTRAINT PK_Dates PRIMARY KEY (DateKey) 
);
GO;

Create Procedure dbo.GetSeason
    @Date as datetime
as
   if(Month(@Date) between 1 and 3)
      select 'Season A'
   --etc

To be completely honest, my experience has been that dimDate tables should pretty much have ALL columns computed off a single date column (ie: FullDate key) to prevent data inconsistency during ETL processing but others mileage may vary...