Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

generating or populating time dimensions Expand / Collapse
Author
Message
Posted Wednesday, January 12, 2011 10:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 20, 2011 2:30 AM
Points: 19, Visits: 43
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
Post #1047007
Posted Sunday, January 16, 2011 6:50 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:35 PM
Points: 22,992, Visits: 31,471
From the description of the problem, no. Can you show us what you are trying to accomplish?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1048523
Posted Monday, January 17, 2011 1:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:20 PM
Points: 13,252, Visits: 10,133
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1048611
Posted Monday, January 17, 2011 11:29 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 10, 2014 7:40 AM
Points: 60, Visits: 518
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
Post #1049075
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse