当前位置：文江博客话题详情

MDX 表示持续时间和一天中的时间

发布于 2025-01-01 04:21:08 字数 897 浏览 1 评论 0原文

我正在创建一个数据仓库来存储用户会话数据。我当前的星型架构如下所示：

session_fact

session_id
user_id
session_duration
date_id (ref date_dimension)
time_if_day_id (ref time_of_day_dimension)

date_dimension

date_id
季度
date_of_month

time_of_day_dimension

time_of_day_id
hour_of_day
分钟_of_hour

会话事实将链接使用会话的开始时间转换为日期和时间维度。

问题：我想创建一个 MDX 查询，返回一天中每个小时的“活动”会话。例如，有一天我们可能会有这些会话：

session id |start time |duration
session 1  |10am       |1hr
session 2  |10am       |2hr

我想以这种形式检索数据：

time of day |active session count
10am        |2
11am        |1

有什么想法吗？我很高兴按照建议重新构建架构，我只是不知道应该怎么做。

感谢您阅读本文。

拍

原文

I am creating a data warehouse to store user session data. My current star schema looks like this:

session_fact

session_id
user_id
session_duration
date_id (ref date_dimension)
time_if_day_id (ref time_of_day_dimension)

date_dimension

date_id
quarter
month
date_of_month

time_of_day_dimension

time_of_day_id
hour_of_day
minute_of_hour

The session fact will link to the date and time of day dimensions using the start time of a session.

Problem:
I would like to create an MDX query that returns the 'active' sessions for each hour of a day.
E.g. for one day we may have these sessions:

session id |start time |duration
session 1  |10am       |1hr
session 2  |10am       |2hr

I would like to retrieve data in this form:

time of day |active session count
10am        |2
11am        |1

Any ideas? I'm very happy to restructure the schema following advice, I just don't know how I should do so.

Thanks for reading this.

Pat

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

以可爱出名 2025-01-08 04:21:08

如果您有必要的硬件资源（磁盘空间），则可以通过创建定期快照事实表轻松解决该问题。粒度将是每小时一次，因此您将获得该小时活跃的每个会话的记录。这将极大地简化查询以按小时提取活动会话......

SELECT
   [Measures].[Active Session Count] ON 0,
   (
      [Date].[Date].[Date].Members *
      [Time].[Hour].[Hour].Members 
   ) ON 1
FROM 
   [Cube]

If you have the necessary hardware resources (disk space), the problem could be easily solved by creating a periodic snapshot fact table. The grain would be hourly so you would have a record for each session that was active that hour. This would GREATLY simplify the query to pull active sessions by hour...

SELECT
   [Measures].[Active Session Count] ON 0,
   (
      [Date].[Date].[Date].Members *
      [Time].[Hour].[Hour].Members 
   ) ON 1
FROM 
   [Cube]

回复收藏 0 原文

~没有更多了~