使用Excel客户端中的Subselect MDX更改上下文 [英] Change the context with Subselect MDX from Excel client

问题描述

我有两个时间维度，即生产期和会计期，还有一个度量，当用户从excel查询多维数据集时，我想与这两个维度合计，但不希望与这两个维度合计.为此，我创建了一个标志度量以检查是否同时使用了两个维度

I have two time dimensions, production period and accounting period, and a measure that I want to aggregate with either dimension but not with both when users query the cube from excel. To do that I created a flag measure to check if both dimensions are being used

CREATE MEMBER CURRENTCUBE.[Measures].[AcctProdFlag] AS  
    IIF (
       [DIM Accounting Period].[Accounting Period Hierarchy].CURRENTMEMBER.level.ordinal <> 0 and 
          [DIM Production Period].[Production Month Hierarchy].currentmember.level.ordinal = 0 , 
      1, 
 IIF ( [DIM Production Period].[Production Month Hierarchy].currentmember.level.ordinal <> 0 and 
       [DIM Accounting Period].[Accounting Period Hierarchy].currentmember.level.ordinal = 0 , 
      2, 
      3
     )
), VISIBLE =0;

然后我使用此标志来创建自己的度量

Then I use this flag to create my measure as such

CREATE MEMBER CURRENTCUBE.[Measures].[Sales/day] AS 

  IIF([Measures].[AcctProdFlag] = 1 , 
      ([Measures].[Sales] / [Measures].[Accounting Period Day Count]),
   IIF([Measures].[AcctProdFlag] = 2, 
       ([Measures].[Sales] / [Measures].[Production Period Day Count]), 
        "NA")),
VISIBLE = 1, DISPLAY_FOLDER = 'Sales\Daily' , FORMAT_STRING = "#,###";

当我从Management Studio使用此查询时，它会正常工作并返回"NA"，因为两个维度都在使用中

When I use this query from management studio, it works and returns "NA" as expected because both dimensions are being used

SELECT {[Measures].[Sales/day]} ON COLUMNS , 
       [DIM Production Period].[Production Month].MEMBERS ON ROWS  
 FROM [My Cube] 
 WHERE {[DIM Accounting Period].[Accounting Year].[2014], 
         [DIM Accounting Period].[Accounting Year].[2015]};

但是当我添加会计年度以在excel中过滤并按行添加生产期间时，当我选择多个会计年度时该度量显示值(不应该按照逻辑)，但在选择多个会计年度时显示"NA"(按预期)我选择一个会计年度.事实证明，当我选择多年时，excel将以下查询发送到SSAS，导致它失去上下文

But when I add accounting year to filter in excel and add production period in rows, the measure shows values (it shouldn't as per the logic) when I select multiple accounting years but shows "NA" (as expected) when I select one accounting year. Turns out that when I select multiple years, excel send the following query to SSAS causing it to loose the context

 SELECT {[Measures].[Sales/day]} ON COLUMNS , 
         [DIM Production Period].[Production Month].MEMBERS ON ROWS 
FROM (SELECT (
              {[DIM Accounting Period].[Accounting Year].[2014],[DIM Accounting Period].[Accounting Year].[2015]}
              ) ON COLUMNS
       FROM [My Cube]
     ) 

有什么我可以解决的吗?我将SSAS用于SQL Server 2008 R2和Excel2013.

Is there anything I can do to fix this?. I am using SSAS for SQL Server 2008 R2 and Excel 2013.

推荐答案

所以我最终做了以下操作来解决此问题.我在数据源视图中将两个度量创建为NULL并将其称为"NULL". [Measures].[Prod Months Used]和[Measures].[Acct Months Used] 我还创建了计算成员以计算所有生产月份和所有会计月份.那些是这样工作的 -创建此度量是为了计算多维数据集中的生产月份数

So I ended up doing the following to resolve the issue. I created two measures as NULL in the data source view and called them; [Measures].[Prod Months Used] and [Measures].[Acct Months Used] I also created calculated members to count all production months and all accounting months. Those worked as such -- This measure is created to count the number of production months in the cube

CREATE MEMBER CURRENTCUBE.[Measures].[AllProdMonths] AS 

    SUM(
      DESCENDANTS(
                   [DIM Production Period].[Production Month Hierarchy].[All],
                    [DIM Production Period].[Production Month Hierarchy].[Production Month], SELF
                   )
       , 1
       ), VISIBLE=0 ; 

-- This measure is created to count the number of accounting months in the cube
CREATE MEMBER CURRENTCUBE.[Measures].[AllAcctMonths] AS 

    SUM(
      DESCENDANTS(
                   [DIM Accounting Period].[Accounting Period Hierarchy].[All],
                    [DIM Accounting Period].[Accounting Period Hierarchy].[Accounting Month], SELF
                   )
       , 1
       ), VISIBLE =0; 

最后要计算使用的会计月份和生产月份的数量，我做了以下操作:

Finally to count the number of used accounting months and production months I did the following:

-- This measure is scoped to count the number of used Production months in excel (including filters) 
-- It does that by overwritting all values except the All member. 
SCOPE (
          ([Measures].[Prod Months Used],
          [DIM Production Period].[Production Month Hierarchy].[Production Month].MEMBERS)
       ); 
 SCOPE DESCENDANTS(
                   [DIM Production Period].[Production Month Hierarchy].[All],, AFTER
                  );

      THIS =   1; 
          END SCOPE; 
 END SCOPE; 



-- This measure is scoped to count the number of used Accounting months in excel (including filters) 
-- It does that by overwritting all values except the All member. 
SCOPE (
          ([Measures].[Acct Months Used],
          [DIM Accounting Period].[Accounting Period Hierarchy].[Accounting Month].MEMBERS)
       ); 
 SCOPE DESCENDANTS(
                   [DIM Accounting Period].[Accounting Period Hierarchy].[All],, AFTER
                  );

      THIS =   1; 
          END SCOPE; 
 END SCOPE; 

和我的标志将根据以下表达式求值

and my flag would be evaluated according to the following expression

CREATE MEMBER CURRENTCUBE.[Measures].[AcctProdFlag] AS  

    IIF (
            [Measures].[Acct Months Used] < [Measures].[AllAcctMonths] AND 
            [Measures].[Prod Months Used] = [Measures].[AllProdMonths], 
      1, 
 IIF ( [Measures].[Acct Months Used] = [Measures].[AllAcctMonths] AND 
            [Measures].[Prod Months Used] < [Measures].[AllProdMonths] , 
      2, 
      3
     )
), VISIBLE =0;

此解决方案运行良好，性能非常好.

This solution is working perfectly and performance is really good.

这篇关于使用Excel客户端中的Subselect MDX更改上下文的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！