文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >使用聚合筛选器计算成员的MDX查询速度太慢

问使用聚合筛选器计算成员的MDX查询速度太慢
EN

Stack Overflow用户
提问于 2016-10-28 17:37:34
回答 1查看 461关注 0票数 0

我是MDX新手。我正在尝试在SSRS报告中运行以下MDX查询，但运行完它需要很长时间。有时我不得不取消查询，因为它太慢了。

代码语言：javascript
复制
WITH 
  --All forms ordered ASC
  SET [OrderedSet] AS 
    Order
    (
      NonEmpty
      (
        [Form].[Number].[Number]
       ,[Measures].[Sales]
      )
     ,[Measures].[Sales]
     ,BASC
    ) 
  --Rank the forms in [orderedSet]
  MEMBER [Measures].[Rank] AS 
    Rank
    (
      [Form].[Number].CurrentMember
     ,[OrderedSet]
    ) 
  --Running Total
  MEMBER [Measures].[Running Total] AS 
    Sum
    (
      Head
      (
        [OrderedSet]
       ,(
          [Measures].[Rank]
         ,[Form].[Number].CurrentMember
        )
      )
     ,[Measures].[Sales]
    ) 
  --Total Sales
  MEMBER [Measures].[Total Sales] AS 
    Sum
    (
      [OrderedSet]
     ,[Measures].[Sales]
    ) 
  --Find the spending band Sales limits
  MEMBER [Measures].[3%Sales] AS 
    0.03 * [Measures].[Total Sales] 
  MEMBER [Measures].[8%Sales] AS 
    0.08 * [Measures].[Total Sales] 
  MEMBER [Measures].[18%Sales] AS 
    0.18 * [Measures].[Total Sales] 
  MEMBER [Measures].[38%Sales] AS 
    0.38 * [Measures].[Total Sales] 
  MEMBER [Measures].[62%Sales] AS 
    0.62 * [Measures].[Total Sales] 
  MEMBER [Measures].[82%Sales] AS 
    0.82 * [Measures].[Total Sales] 
  MEMBER [Measures].[92%Sales] AS 
    0.92 * [Measures].[Total Sales] 
  MEMBER [Measures].[97%Sales] AS 
    0.97 * [Measures].[Total Sales] 
  --Assign Sales limits for each spending bands
  MEMBER [Measures].[MinSales] AS 
    CASE 
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[0-3] 
      THEN 1
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[3-8] 
      THEN 
        [Measures].[3%Sales]
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[8-18] 
      THEN 
        [Measures].[8%Sales]
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[18-38] 
      THEN 
        [Measures].[18%Sales]
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[38-62] 
      THEN 
        [Measures].[38%Sales]
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[62-82] 
      THEN 
        [Measures].[62%Sales]
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[82-92] 
      THEN 
        [Measures].[82%Sales]
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[92-97] 
      THEN 
        [Measures].[92%Sales]
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[97-100] 
      THEN 
        [Measures].[97%Sales]
    END 
  MEMBER [Measures].[MaxSales] AS 
    CASE 
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[0-3] 
      THEN 
        [Measures].[3%Sales]
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[3-8] 
      THEN 
        [Measures].[8%Sales]
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[8-18] 
      THEN 
        [Measures].[18%Sales]
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[18-38] 
      THEN 
        [Measures].[38%Sales]
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[38-62] 
      THEN 
        [Measures].[62%Sales]
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[62-82] 
      THEN 
        [Measures].[82%Sales]
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[82-92] 
      THEN 
        [Measures].[92%Sales]
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[92-97] 
      THEN 
        [Measures].[97%Sales]
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[97-100] 
      THEN 
        [Measures].[Total Sales]
    END 
  --Create Spending bands based on the Running Total ranges
  MEMBER [Form].[Number].[0-3] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > 0
        AND 
          [Measures].[Running Total] <= [Measures].[3%Sales]
      )
    ) 
  MEMBER [Form].[Number].[3-8] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > [Measures].[3%Sales]
        AND 
          [Measures].[Running Total] <= [Measures].[8%Sales]
      )
    ) 
  MEMBER [Form].[Number].[8-18] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > [Measures].[8%Sales]
        AND 
          [Measures].[Running Total] <= [Measures].[18%Sales]
      )
    ) 
  MEMBER [Form].[Number].[18-38] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > [Measures].[18%Sales]
        AND 
          [Measures].[Running Total] <= [Measures].[38%Sales]
      )
    ) 
  MEMBER [Form].[Number].[38-62] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > [Measures].[38%Sales]
        AND 
          [Measures].[Running Total] <= [Measures].[62%Sales]
      )
    ) 
  MEMBER [Form].[Number].[62-82] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > [Measures].[62%Sales]
        AND 
          [Measures].[Running Total] <= [Measures].[82%Sales]
      )
    ) 
  MEMBER [Form].[Number].[82-92] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > [Measures].[82%Sales]
        AND 
          [Measures].[Running Total] <= [Measures].[92%Sales]
      )
    ) 
  MEMBER [Form].[Number].[92-97] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > [Measures].[92%Sales]
        AND 
          [Measures].[Running Total] <= [Measures].[97%Sales]
      )
    ) 
  MEMBER [Form].[Number].[97-100] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > [Measures].[97%Sales]
        AND 
          [Measures].[Running Total] <= [Measures].[Total Sales]
      )
    ) 
SELECT 
  NON EMPTY 
    {
      [Measures].[MinSales]
     ,[Measures].[MaxSales]
     ,[Measures].[Form Count]
     ,[Measures].[Sales]
    } ON 0
 ,{
    [Form].[Number].[0-3]
   ,[Form].[Number].[3-8]
   ,[Form].[Number].[8-18]
   ,[Form].[Number].[18-38]
   ,[Form].[Number].[38-62]
   ,[Form].[Number].[62-82]
   ,[Form].[Number].[82-92]
   ,[Form].[Number].[92-97]
   ,[Form].[Number].[97-100]
  } ON 1
FROM 
(
  SELECT 
    [blah] ON COLUMNS
  FROM [Cube]
);

经过几次测试和google搜索，我知道罪魁祸首是聚合过滤后的表单编号的计算成员。此外，Form.Number.Number维度是一个拥有数十万成员的巨大维度。所以我想知道，如果我们在这样的维度中创建一个计算成员，会不会减慢查询速度，如果是的话，有什么解决办法？

非常感谢

reporting-services
filter
aggregate
mdx
EN

回答 1

Stack Overflow用户

发布于 2016-10-28 20:18:13

CASE和FILTER都是很慢的mdx函数。对于一个普通的CASE，切换到IIF很容易，后者通常性能更好--但是你有太多的条件来嵌套IIF。

以下三点可能会有所帮助：

1.我注意到您的case语句中有一个错误-您应该使用IS运算符而不是=

代码语言：javascript
复制
CASE 
  WHEN 
    [Form].[Number].CurrentMember IS [Form].[Number].[0-3] 
  THEN 1
  WHEN 
    [Form].[Number].CurrentMember IS [Form].[Number].[3-8] 
  THEN 
    [Measures].[3%Sales]
  WHEN 
    [Form].[Number].CurrentMember IS [Form].[Number].[8-18] 
  THEN 
    [Measures].[8%Sales]
  WHEN 
    [Form].[Number].CurrentMember IS [Form].[Number].[18-38] 
  THEN 
    [Measures].[18%Sales]
  WHEN 
    [Form].[Number].CurrentMember IS [Form].[Number].[38-62] 
  THEN 
    [Measures].[38%Sales]
  WHEN 
    [Form].[Number].CurrentMember IS [Form].[Number].[62-82] 
  THEN 
    [Measures].[62%Sales]
  WHEN 
    [Form].[Number].CurrentMember IS [Form].[Number].[82-92] 
  THEN 
    [Measures].[82%Sales]
  WHEN 
    [Form].[Number].CurrentMember IS [Form].[Number].[92-97] 
  THEN 
    [Measures].[92%Sales]
  WHEN 
    [Form].[Number].CurrentMember IS [Form].[Number].[97-100] 
  THEN 
    [Measures].[97%Sales]
END

2.您的WITH子句的运行顺序是错误的-这些不同的成员[Form].[Number].[97-100]是在CASE语句中使用它们之后计算的。

3.我无法想象您对[Total Sales]的计算将需要任何排序，那么为什么要使用有序集呢？事实上，您可以只使用一个带有层次结构的All成员的元组来获得此值：

代码语言：javascript
复制
--Total Sales
MEMBER [Measures].[Total Sales] AS 
(
  [Form].[Number].[All]
 ,[Measures].[Sales]
)

因此，我们现在已经实现了上面的内容：

代码语言：javascript
复制
WITH 
  --All forms ordered ASC
  SET [OrderedSet] AS 
    Order
    (
      NonEmpty
      (
        [Form].[Number].[Number]
       ,[Measures].[Sales]
      )
     ,[Measures].[Sales]
     ,BASC
    ) 
  --Rank the forms in [orderedSet]
  MEMBER [Measures].[Rank] AS 
    Rank
    (
      [Form].[Number].CurrentMember
     ,[OrderedSet]
    ) 
  --Running Total
  MEMBER [Measures].[Running Total] AS 
    Sum
    (
      Head
      (
        [OrderedSet]
       ,(
          [Measures].[Rank]
         ,[Form].[Number].CurrentMember
        )
      )
     ,[Measures].[Sales]
    ) 
  --Total Sales
  MEMBER [Measures].[Total Sales] AS 
  (
    [Form].[Number].[All]
   ,[Measures].[Sales]
  )
  --Find the spending band Sales limits
  MEMBER [Measures].[3%Sales] AS 
    0.03 * [Measures].[Total Sales] 
  MEMBER [Measures].[8%Sales] AS 
    0.08 * [Measures].[Total Sales] 
  MEMBER [Measures].[18%Sales] AS 
    0.18 * [Measures].[Total Sales] 
  MEMBER [Measures].[38%Sales] AS 
    0.38 * [Measures].[Total Sales] 
  MEMBER [Measures].[62%Sales] AS 
    0.62 * [Measures].[Total Sales] 
  MEMBER [Measures].[82%Sales] AS 
    0.82 * [Measures].[Total Sales] 
  MEMBER [Measures].[92%Sales] AS 
    0.92 * [Measures].[Total Sales] 
  MEMBER [Measures].[97%Sales] AS 
    0.97 * [Measures].[Total Sales] 
  --Create Spending bands based on the Running Total ranges
  MEMBER [Form].[Number].[0-3] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > 0
        AND 
          [Measures].[Running Total] <= [Measures].[3%Sales]
      )
    ) 
  MEMBER [Form].[Number].[3-8] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > [Measures].[3%Sales]
        AND 
          [Measures].[Running Total] <= [Measures].[8%Sales]
      )
    ) 
  MEMBER [Form].[Number].[8-18] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > [Measures].[8%Sales]
        AND 
          [Measures].[Running Total] <= [Measures].[18%Sales]
      )
    ) 
  MEMBER [Form].[Number].[18-38] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > [Measures].[18%Sales]
        AND 
          [Measures].[Running Total] <= [Measures].[38%Sales]
      )
    ) 
  MEMBER [Form].[Number].[38-62] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > [Measures].[38%Sales]
        AND 
          [Measures].[Running Total] <= [Measures].[62%Sales]
      )
    ) 
  MEMBER [Form].[Number].[62-82] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > [Measures].[62%Sales]
        AND 
          [Measures].[Running Total] <= [Measures].[82%Sales]
      )
    ) 
  MEMBER [Form].[Number].[82-92] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > [Measures].[82%Sales]
        AND 
          [Measures].[Running Total] <= [Measures].[92%Sales]
      )
    ) 
  MEMBER [Form].[Number].[92-97] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > [Measures].[92%Sales]
        AND 
          [Measures].[Running Total] <= [Measures].[97%Sales]
      )
    ) 
  MEMBER [Form].[Number].[97-100] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > [Measures].[97%Sales]
        AND 
          [Measures].[Running Total] <= [Measures].[Total Sales]
      )
    ) 
  --Assign Sales limits for each spending bands
  MEMBER [Measures].[MinSales] AS 
    CASE 
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[0-3] 
      THEN 1
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[3-8] 
      THEN 
        [Measures].[3%Sales]
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[8-18] 
      THEN 
        [Measures].[8%Sales]
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[18-38] 
      THEN 
        [Measures].[18%Sales]
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[38-62] 
      THEN 
        [Measures].[38%Sales]
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[62-82] 
      THEN 
        [Measures].[62%Sales]
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[82-92] 
      THEN 
        [Measures].[82%Sales]
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[92-97] 
      THEN 
        [Measures].[92%Sales]
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[97-100] 
      THEN 
        [Measures].[97%Sales]
    END 
  MEMBER [Measures].[MaxSales] AS 
    CASE 
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[0-3] 
      THEN 
        [Measures].[3%Sales]
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[3-8] 
      THEN 
        [Measures].[8%Sales]
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[8-18] 
      THEN 
        [Measures].[18%Sales]
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[18-38] 
      THEN 
        [Measures].[38%Sales]
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[38-62] 
      THEN 
        [Measures].[62%Sales]
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[62-82] 
      THEN 
        [Measures].[82%Sales]
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[82-92] 
      THEN 
        [Measures].[92%Sales]
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[92-97] 
      THEN 
        [Measures].[97%Sales]
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[97-100] 
      THEN 
        [Measures].[Total Sales]
    END 
SELECT 
  NON EMPTY 
    {
      [Measures].[MinSales]
     ,[Measures].[MaxSales]
     ,[Measures].[Form Count]
     ,[Measures].[Sales]
    } ON 0
 ,{
    [Form].[Number].[0-3]
   ,[Form].[Number].[3-8]
   ,[Form].[Number].[8-18]
   ,[Form].[Number].[18-38]
   ,[Form].[Number].[38-62]
   ,[Form].[Number].[62-82]
   ,[Form].[Number].[82-92]
   ,[Form].[Number].[92-97]
   ,[Form].[Number].[97-100]
  } ON 1
FROM 
(
  SELECT 
    [blah] ON COLUMNS
  FROM [Cube]
);
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/40302271

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档