MDX报告呈现缓慢

Question

这是我的MDX查询：

with member [Measures].[Rent] as IIF(IsEmpty([Measures].[Budget]), NULL, [Measures].[Rent])
select {[Measures].[Rent]} on columns,
          [Property].[Address].[All].children *
         DESCENDANTS([Account].[Account Hierarchy].[Account Group].[Expenditures],
                     [Account].[Account Tree].[Account]) *
                     [Asset].[asset].[All].children on rows
from    [Master]

当我评论[Asset] Dimension成员时，我会得到结果，但我需要MDX中的资产维度。

有人知道该怎么办吗？？

Answer 1

在交叉连接维度之前，应该尝试过滤维度。在您的情况下，如果预算为空，请将其保留。另外，不要忘记添加NON EMPTY子句以从结果集中删除空值：

with 
     member hasBudget as
(
  IIF(IsEmpty([Measures].[Budget]), 0, 1)
)
member [Rent] as IIF(IsEmpty([Measures].[Budget]), NULL, [Measures].[Rent])
set accounts = Filter(DESCENDANTS([Account].[Account Hierarchy].[Account Group].[Expenditures],[Account].[Account Tree].[Account]), [Measures].[hasBudget] = 1)
select non empty
{ [Measures].[Rent] } on 0,
non empty
{ ([accounts] * (EXISTING [Asset].[asset].[All].children) * (EXISTING [Property].[Address].[All].children)) } on 1
from ( select { Filter([Asset].[asset].[All].children, [Measures].[hasBudget] = 1) } on 0
from ( select { Filter([Property].[Address].[All].children, [Measures].[hasBudget] = 1) } on 0
from [Master]))