time dimensions

[mjd3000]

I am new to SSAS and working through the Microsoft SQL Server 2005
Analysis Services Step By Step book. When it comes to creating time
dimensions, the example shows all sorts of different fields for
DayNumberOfMonth, DayNumberOfYear etc. My own tables that I want to
create time dimensions all only have a single datetime field, with the
date of an action time stamped in the format dd/MM/YYYY hh:mm:ss. How
can I create a time dimension that breaks down time into
year/month/quarter etc when I only has this one field to work with?

 

[RiverGuy]

I usually use a script to create them from within SQL. Here's an example, run this in Management Studio:

DECLARE @Time TABLE (TheDate DATETIME, Month_Number INT, Month_Name VARCHAR(20), Quarter_Number INT, Quarter_Name VARCHAR(20), Year_Number INT)

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SELECT @StartDate = '20000101'
SELECT @EndDate = '20201231'

WHILE @StartDate < @EndDate 
BEGIN
	INSERT INTO @Time (TheDate, Month_Number, Month_Name, Quarter_Number, Quarter_Name, Year_Number)
	SELECT @StartDate,
	CONVERT(INT, CONVERT(CHAR(4), YEAR(@StartDate)) +  RIGHT('0' + CONVERT(VARCHAR(2), MONTH(@StartDate)), 2)),
	DATENAME(month, @StartDate) + ' ' + CONVERT(CHAR(4), YEAR(@StartDate)),
	CONVERT(INT, CONVERT(CHAR(4), YEAR(@StartDate)) +  RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(quarter, @StartDate)), 2)),
	'Q' + DATENAME(quarter, @StartDate) + ' ' + CONVERT(CHAR(4), YEAR(@StartDate)),
	YEAR(@StartDate)
	SELECT @StartDate =  DATEADD(day, 1, @StartDate )
END

SELECT * FROM @Time

Your tutorial probably has many more columns than you will end up using. Another way to create the table if I recall correctly is to create a server time dimension, and I believe it will give you the option of creating a script to create the table, columns and records for you, which you can copy and paste in Management Studio.

 

[MDXer]

I always manually build my time dimensions with a process seperate from what my data.

Create a Table with the needed columns, it looks like you want at the very least.

1)Date_SK int
2)Date datetime
3)Date_Desc varchar
4)Year int
5)Month int
6)Month_Name varchar
7)Month_Desc varchar
8)QTR int
9)QTR_Desc varchar

Use the various Date Part functions to seperate out year month and those columns.

Use the Desc column to hold a User friendly string that represents the member.

For month you could have May but every year has may so to make it more user friendly you can have May 06 as the description QTR you would use QTR 1 06. Date you want to convert to a useable string because if you use the date column you will get the full Date and Time value.