用户数据的数据仓库-设计Q

如何最好地存储用户数据与日期/时间维度？用例是我试图存储每天、每小时的用户操作。例如分享数、喜欢数、好友数等。我有一个时间表和日期表。对于时间来说很简单 - 我每天的每个小时的每一行 = user_id 和列 = 1 到 24 。但问题在于日期。如果我每天= 1 列，那么我一年将有 365 列。我也无法存档数据，因为分析也需要过去的数据。还有哪些其他策略？

dimDate : 1 row per date
dimTime : 1 row per minute

一开始你必须声明“grain事实表的“然后坚持下去.

如果一粒粮食是一日，那么TimeKey总是指向“23:59”这个键。

如果谷物是一小时，那么TimeKey指向“HH:59”的条目。

如果谷物是一分钟，那么TimeKey指向相应的“HH:MM”

如果谷物是15分钟，那么TimeKey指向相应的“HH:14”、“HH:29”、“HH:44”、“HH:59”

等等...

-- How many new friends did specific user gain
-- in first three months of years 2008, 2009 and 2010
-- between hours 3 and 5 in the morning
-- by day of week
-- not counting holidays ?

select
      DayOfWeek
    , sum(NewFriends) as FriendCount
from factUserAction as f
join dbo.dimUser    as u on u.UserKey = f.UserKey
join dbo.dimDate    as d on d.DateKey = f.DateKey
join dbo.dimTime    as t on t.TimeKey = f.TimeKey
where CalendarYear between 2008 and 2010
  and MonthNumberInYear between 1 and 3
  and t.Hour between 3 and 5
  and d.IsHoliday = 'no'
  and UserEmail = '[email protected]' 
group by DayOfWeek
order by DayOfWeek ;