Difficulty in creating Time Dimension - SSAS

Hello all,

I am familiar with Analysis Services 2000 in which creating a time dimension is very easy.

In SSAS (2005) , i have a table in which there is field for DateTime which i want to use as time dimesion.

In time dimension wizard , it asks for the column for year , month etc (which is created by default in AS 2000) .

Since i have only one column having datetime datatype, how should i proceed

by creating heirarchy (year,month ,day - same as AS 2000).

Thanks,

deepti

i

You can create a stored procedure that populate a table with "time elements". See this example where I created a procedure that add rows into a table named CALSTD:

create procedure AddCalendar @.nYear int as

if not exists(select * from sysobjects where name='calstd')

CREATE TABLE [dbo].[CalStd](

[Data] [datetime],

[Data_lunii] [datetime],

[An] [smallint],

[Luna] [smallint],

[LunaAlfa] [char](15) ,

[Zi] [smallint],

[Saptamana] [smallint],

[Trimestru] [smallint],

[Zi_alfa] [char](10),

[Camp1] [char](10),

[Camp2] [char](10),

[Camp3] [char](10),

[Fel_zi] [char](1)

) ON [PRIMARY]

DECLARE @.I INT,@.DDATA DATETIME,@.DDATAL DATETIME,@.DDATAST DATETIME,@.NAN INT,@.nZi int,@.cZiChar char(10),@.cLunaAlfa char(15),@.nZileAn int

DELETE FROM CALSTD WHERE YEAR(DATA)=@.NAN

set @.nan=@.nyear

SET @.I=0

SET @.DDATAST=CONVERT(DATETIME,'01/01/'+RTRIM(STR(@.NAN)))

if (@.NAN%4)=0 and (@.nAn%400)<>0

SET @.nZileAn=366

else

set @.nZileAn=365

WHILE @.I<@.nZileAn

BEGIN

SET @.DDATA=DATEADD(DAY,@.i,@.DDATAST)

SET @.DDATAL=dateadd(day, -day(dateadd(month, 1, @.dData)), dateadd(month, 1, @.dData))

Set @.nZi=datepart(weekday,@.dData)

set @.cZiChar=(case when @.nZi=1 then 'Sunday'

when @.nZi=2 then 'Monday'

when @.nZi=3 then 'Tuesday'

when @.nZi=4 then 'Wednesday'

when @.nZi=5 then 'Thursday'

when @.nZi=6 then 'Friday'

else 'Saturday' end)

set @.cLunaAlfa=(case when datepart(month,@.dData)=1 then 'January'

when datepart(month,@.dData)=2 then 'February'

when datepart(month,@.dData)=3 then 'March'

when datepart(month,@.dData)=4 then 'April'

when datepart(month,@.dData)=5 then 'May'

when datepart(month,@.dData)=6 then 'June'

when datepart(month,@.dData)=7 then 'July'

when datepart(month,@.dData)=8 then 'August'

when datepart(month,@.dData)=9 then 'September'

when datepart(month,@.dData)=10 then 'Octomber'

when datepart(month,@.dData)=11 then 'November'

else 'December'

end)

INSERT INTO CALSTD(Data,Data_lunii,An,Luna,LunaAlfa,Zi,Saptamana,Trimestru,Zi_alfa,Camp1,Camp2,Camp3,Fel_zi)

VALUES(@.dData,@.dDatal,datepart(year,@.dData),datepart(month,@.dData),@.cLunaAlfa,datepart(day,@.dData),datepart(week,@.dData),datepart(quarter,@.dData),@.cZiChar,'','','','L')

SET @.I=@.I+1

END

|||

Thanks crysty that was really helpful.

regards,

deepti.

|||

Hi Deepti, I was in the same situation, but I found a simple way to achieve the Time Dimension Generation, please visit the following url, to know something about it. http://www.ssw.com.au/ssw/Standards/Rules/CreatingATimeDimensionIn10EasySteps.aspx

So for more information about sqlserver 2005, i'm building a blog with some resources at: http://grupohix.blogspot.com

Regards

FraGar