January 12, 2011 at 10:34 pm
i,can any one help me in populating time dimensions in sql from 2000 to 2030 for every 15 min.now i need to create time dimensions from 2000 jan 01 to 2030 dec 31 by taking 15 min interval,for every day 96 rows will come.it has to done through ssis(sql server integration services)but not through stored procedures........plz help me if any one know
January 16, 2011 at 6:50 pm
From the description of the problem, no. Can you show us what you are trying to accomplish?
January 17, 2011 at 1:46 am
gupta.sreekanth (1/12/2011)
i,can any one help me in populating time dimensions in sql from 2000 to 2030 for every 15 min.now i need to create time dimensions from 2000 jan 01 to 2030 dec 31 by taking 15 min interval,for every day 96 rows will come.it has to done through ssis(sql server integration services)but not through stored procedures........plz help me if any one know
SSIS is not a data generating tool (at least not out of the box), it is an extraction tool. You can write some TSQL that will generate the data for you and place this in an SSIS package, but then what is the difference between this and stored procedures?
Kimball suggests that you create the date dimensions in a spreadsheet such as Excel and then load this into your data warehouse.
On a side note: wouldn't it be better to create a date dimensions with a grain of a single day and a seperate time dimension to list the time of day?
The grain of this time dimension can be as low as a second, or a minute or if you want, 15 minutes.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 17, 2011 at 11:29 pm
Hi,
Please check the following code. This may help you.
------code
declare @time table (id int identity (1,1),v_time time,v_date date)
insert into @time
SELECT
convert(time,CONVERT(VARCHAR(8),GETDATE(),108)) AS HourMinuteSecond,
CONVERT(VARCHAR(10),GETDATE(),101) AS DateOnly
from msdb.INFORMATION_SCHEMA.COLUMNS
update @time set v_time = DATEADD(mi,15*id,v_time),v_date = DATEADD(day,id/97,v_date)
select * from @time where v_date = CONVERT(VARCHAR(10),GETDATE(),101)
------ end of code
Regards
Siva Kumar
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply