SSAS将日期维链接到间隔列 [英] SSAS linking a date dimension to an interval columns

问题描述

我有以下事实表:

ID_Activ  Date_Start   Date_End     ID_session  DayOfWeek   Time_Start  Time_End   
1         01/02/2018   15/02/2018   11          4           08:00:00    10:00:00
2         01/02/2018   15/02/2018   21          4           09:00:00    10:00:00 
3         01/03/2018   15/03/2018   31          2           09:00:00    10:00:00 

我有一个常规尺寸日期和一个带有小时，分钟和秒的自定义尺寸时间. 我想使用这些尺寸切片或切块以获得此输出:

I have a conventional dimensions date, and a custom dimension time with hours, minutes and seconds. I would like to slice or dice using these dimensions to get this output:

Date        hour     Count_session
01/02/2018  08       1
01/02/2018  09       2
01/02/2018  10       2
08/02/2018  08       1
08/02/2018  09       2
08/02/2018  10       2
15/02/2018  08       1
15/02/2018  09       2
15/02/2018  10       2
06/03/2018  09       1
06/03/2018  10       1
13/03/2018  09       1
13/03/2018  10       1

我们不会显示空的Count_session.

we don't show up the null Count_session.

我试图创建一个命名查询，其中所有小时都包含在日期和小时范围内，但是它会生成大量数据！ 您是否还有另一种解决方案，可以将维度与范围日期链接起来?

I tried to created a named query with all hours include in date and hours range, but it generate very lot of data! Do you have another solution to linking a dimension with range dates ?

谢谢.

推荐答案

您需要使用非空.您的查询看起来像这样

You need to use non empty. Your query would look like this

select [measures].[count_session] on 0,
non empty
([Dim Date].[Date].[Date],[Dim CustomDate].[Hour].[Hour])
on 1
from 
[YourCube]

这篇关于SSAS将日期维链接到间隔列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！