SQL Server, SSIS or SSAS: Fastest and easiest way to create a time dimension

Level: 2 where 1 is noob and 5 is totally awesome
System: SQL Server 2005 and above

One of the most vital components of a dataware house, is the time dimension. It also seems to be the part which people to have most difficulties to generate. I seen all from black magic in MS Excel to big complex SQL scripts and even fancy SSIS solutions, just to generate time dimensions.

I do believe, I have found a better solution.

My solution is this script, I have written(inspired from a script I did found on Stack Overflow). You set a start and end date, plus the granularity. My recommendation is to generate times for at least a decade. A decade with the granularity of one hour, takes less than 5 Mb of data storage and 87601 records with this script as default. A decade is generated in a few seconds.

 DECLARE @StartDate DATETIME = '2010-01-01';   
 DECLARE @EndDate DATETIME = '2019-12-31';   
   
 WITH DateTimeGenerator AS  
 (  
  SELECT @StartDate AS DateAndTime  
  UNION ALL  
  SELECT DATEADD(HOUR, 1, DateAndTime) -- Change datepart to ajust granularity, see http://msdn.microsoft.com/en-us/library/ms186819.aspx for more  
  FROM  DateTimeGenerator    
  WHERE  DateAndTime + 1 < @EndDate  
 )  
 SELECT ROW_NUMBER() OVER (ORDER BY DateAndTime) As Id,   
 DateAndTime,   
 DATEPART(HOUR, DateAndTime) AS [Hour],  
 DATEPART(DAY, DateAndTime) AS [Day],  
 DATEPART(WEEK, DateAndTime) AS [Week],  
 DATEPART(MONTH, DateAndTime) AS [Month],  
 DATEPART(QUARTER, DateAndTime) AS [Quarter],  
 DATEPART(Year, DateAndTime) AS [Year]  
 INTO DimTime  
 FROM  DateTimeGenerator  
 OPTION (MAXRECURSION 0)  

Enjoy