针对"where date between"类型查询优化 Dax 和模型

Question

我正在构建一个模型，允许在两个独立的数据集上报告，对于这个例子，我们将说一个学生数据集&a Staff数据集。

数据集是非常独立的，两者之间唯一真正的链接是Date，所以从模型的角度来看，有一个Students星型模式&a Staff Star Schema.

显示的数据为快照类型数据，回答如下问题:—对于选定的日期，显示所有活动的员工-对于选定的日期，显示所有注册的学生

这意味着当选择单个日期时，模型然后查找所选日期在雇佣开始日期内的所有员工;结束日期，并查找所选日期属于注册开始日期的所有学生;结束日期。

这意味着我必须做出决定，如何从具有单个日期维度的每个模式返回正确的数据。创建关系将无法工作，因为表格中的关系不允许"之间"类型的查询，所以我取而代之的是一个不相关的日期维度和每个模型的Dax找到适用的行。

问题是它不是性能最好的。对于大约50k行，添加一个度量可能需要5-10秒。

我问是否有更好的方法来编写查询，或者改变模型，仍然让我做"之间"风格的查询，但提供更好的性能。

下面是一个返回在特定日期注册的所有学生的dax查询示例。

谢谢你的建议。

All Enrolled Students:=IF (
HASONEVALUE ( 'Date'[Date] ),
CALCULATE (
    DISTINCTCOUNT ( 'Students'[StudentID] ),
    FILTER (
        'Students',
        'Students'[StudentStartDateID] <= MIN ( 'Date'[DateID] )
            && 'Students'[StudentEndDateID] >= MAX ( 'Date'[DateID] )
    )
),
BLANK ())

Answer 1

不相关或"断开连接";在某些情况下，表格可以很好地为切片器、时间轴和过滤器提供动力。正如您在问题中所说，您有两个优化选项:重新构建数据集或优化现有度量语法。

听数据集

在开始日期和结束日期之间复制每一天的每一行，并为该迭代日期添加一列。根据获取数据集的方式，有几种方法可以做到这一点，但可能会很繁琐。然后，在此迭代日期上关联表，并使用关系从date到FACT进行筛选。如果这是一个循环报表，并且/或者您正在使用SQL提取数据，那么利用PowerPivot的关系计算能力可能是值得的。

优化DAX语句

如果这是一个一次性的请求，或者数据集太繁琐而不能每天重复，那么坚持使用断开连接的表方法并清理度量语法。因为您已经包含了MIN()和MAX()函数，并且您的CALCULATE()返回DISTINCTCOUNT()，所以没有必要使用条件HASONEVALUE()函数。我在模拟环境中运行了这个程序，并获得了不错的结果，但这可能会随着计算机性能和数据集大小而变化。

All Enrolled Students:=CALCULATE (
    DISTINCTCOUNT('Students'[StudentID]),
    FILTER(
        'Students',
        'Students'[StudentStartDateID]<= MIN('Date'[DateID]) &&
        'Students'[StudentEndDateID]  >= MAX('Date'[DateID])
    )
)

如果你的StudentID列是唯一的，这对我来说是有意义的，你可以进一步加快速度。

All Enrolled Students:=CALCULATE (
    COUNT('Students'[StudentID]),
    FILTER(
        'Students',
        'Students'[StudentStartDateID]<= MIN('Date'[DateID]) &&
        'Students'[StudentEndDateID]  >= MAX('Date'[DateID])
    )
)

如果StudentID不是一个数字，将COUNT()替换为COUNTA()，以获得所需的效果

Answer 2

这种类型的场景通常被称为"正在进行的事件"或"具有持续时间的事件"。看看下面的链接。答案将取决于您的SSAS版本和事件持续时间长度。

https://www.sqlbi.com/articles/analyzing-events-with-a-duration-in-dax/https://www.sqlbi.com/articles/understanding-dax-query-plans/https://blog.gbrueckl.at/2014/12/events-in-progress-for-time-periods-in-dax/

如果这些度量不能很好地执行(对于持续时间较长的事件可能会发生这种情况)，则可能需要生成一个表，其中包含事件的每一天的一行。SQL语句看起来像这样:

SELECT        
   d.CalendarDate      
  ,s.StudentID
FROM dbo.Students AS s 
CROSS JOIN dbo.DimDate AS d      
WHERE d.CalendarDate >= StudentStartDateID      
AND d.CalendarDate <= StudentEndDateID

创建该表到日期/日历表的关系。

在这种设计下，您可以使用一个简单的DISTINCTCOUNT(Students[studententid])度量，它应该执行得更好。代价是这个表可能变得非常大。为了获得最佳性能和内存节约，将它保持尽可能窄。另一种优化方法是使用不同的粒度，例如用周或月代替日。