Difficulty in creating Time Dimension - SSAS

Difficulty in creating Time Dimension - SSAS

• saniya

  Thanks crysty that was really helpful.

  regards,

  deepti.

  
  

• Paul Kromann

  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

  
  

• djfrey

  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

  
  
  
• Difficulty in creating Time Dimension - SSAS