Data warehouse for user data - design Q -

how best store user data vs date/time dimension? usecase trying store user actions per day, per hour. such number of shares, likes, friends etc. have time table , date table. time easy - have each row = user_id , colunms = 1 24 each hour of day. problem dates. if give each day = 1 colunm have 365 colunms year. cannot archive data way either because analytic needs past data too. other strategies?

dimdate : 1 row per date dimtime : 1 row per minute 

at beginning have state "grain" of fact table , stick it.

if grain 1 day, timekey points key of "23:59".

if grain 1 hour, timekey points entries of "hh:59".

if grain 1 minute, timekey points respective "hh:mm"

if grain 15 minutes, timekey points respective "hh:14", "hh:29", "hh:44", "hh:59"

and on...

-- how many new friends did specific user gain -- in first 3 months of years 2008, 2009 , 2010 -- between hours 3 , 5 in morning -- day of week -- not counting holidays ?  select       dayofweek     , sum(newfriends) friendcount factuseraction f join dbo.dimuser    u on u.userkey = f.userkey join dbo.dimdate    d on d.datekey = f.datekey join dbo.dimtime    t on t.timekey = f.timekey calendaryear between 2008 , 2010   , monthnumberinyear between 1 , 3   , t.hour between 3 , 5   , d.isholiday = 'no'   , useremail = 'john_doe@gmail.com'  group dayofweek order dayofweek ;