Mdx 查询与多维数据集浏览器不一致 [英] Mdx query incosistent with cube browser

问题描述

我用一些计算构建了一个多维 SSAS 立方体.我有一个计算成员，其中我使用 parallelperiod 函数根据以下代码计算上个月的值:

I have built a multidimensional SSAS cube with some calculations. I have a calculated member in which I use the the parallelperiod function to calculate the previous month value according to the following code :

Sum
(
  (EXISTING 
    [TimeDim Transactions].[Year -  Quarter -  Month -  Date].[date].MEMBERS)
 ,(
    ParallelPeriod
    (
      [TimeDim Transactions].[Year -  Quarter -  Month -  Date].[month]
     ,1
     ,[TimeDim Transactions].[Year -  Quarter -  Month -  Date].CurrentMember
    )
   ,[Measures].[Net Amount]
  )
)

当我运行以下查询时，它按预期工作.

When I run the following query it works as expected.

SELECT 
  {
    NetAmountSamePeriodLastMonth
   ,[Measures].[net amount]
  } ON COLUMNS
 ,[Stores Dim].[Store Code].Children ON ROWS
FROM [SalesDW_v1]
WHERE 
  {
    [TimeDim Transactions].[Year -  Quarter -  Month -  Date].[Date].&[2014-12-04T00:00:00]
   ,[TimeDim Transactions].[Year -  Quarter -  Month -  Date].[Date].&[2014-12-05T00:00:00]
  };

但是当我浏览多维数据集以使用相同的日期过滤器获取相同的数据时，它给出了错误的数字.从浏览器生成的 mdx 脚本是:

But when I browse the cube to get the same data with the same date filters It gives wrong numbers. The generated from browser mdx script is :

SELECT 
  NON EMPTY 
    {
      [Measures].[Net Amount]
     ,[Measures].[NetAmountSamePeriodLastMonth]
    } ON COLUMNS
 ,NON EMPTY 
    {[Stores Dim].[Store Code].[Store Code].ALLMEMBERS}
  DIMENSION PROPERTIES 
    MEMBER_CAPTION
   ,MEMBER_UNIQUE_NAME
   ON ROWS
FROM 
(
  SELECT 
    {
      [TimeDim Transactions].[Year -  Quarter -  Month -  Date].[Date].&[2014-12-04T00:00:00]
     ,[TimeDim Transactions].[Year -  Quarter -  Month -  Date].[Date].&[2014-12-05T00:00:00]
    } ON COLUMNS
  FROM [SalesDW_v1]
)
WHERE 
  [TimeDim Transactions].[Year -  Quarter -  Month -  Date].CurrentMember
CELL PROPERTIES 
  VALUE
 ,BACK_COLOR
 ,FORE_COLOR
 ,FORMATTED_VALUE
 ,FORMAT_STRING
 ,FONT_NAME
 ,FONT_SIZE
 ,FONT_FLAGS;

更多，浏览器在选择一个日期时显示正确的数字.

Something more, the browser shows correct numbers when one date is selected.

有没有办法修改计算成员以便在浏览器中正常工作.我在 Power View 报告中提供了数据，但我得到的是我在浏览器中看到的错误数字.

Is there a way to modify the calculated member in order to work correctly at the browser. I present the data on a power view report, but what i get is the incorrect numbers I see at the browser.

当我只使用一个日期成员时浏览器生成的代码:

Below the code generated by browser when I use only one date member :

SELECT 
  NON EMPTY 
    { [Measures].[Net Amount]
     ,[Measures].[NetAmountSamePeriodLastMonth] 
    } 

    ON COLUMNS 
    ,NON EMPTY 
    { ([Stores Dim].[Store Code].[Store Code].ALLMEMBERS ) } 
    DIMENSION PROPERTIES 
    MEMBER_CAPTION
   ,MEMBER_UNIQUE_NAME ON ROWS 

   FROM 
       ( 
        SELECT (
                {[TimeDim Transactions].[Year -  Quarter -  Month -  Date].[Date].&[2014-12-05T00:00:00] } 
               ) ON COLUMNS 

  FROM [SalesDW_v1]
       )
  WHERE ( [TimeDim Transactions].[Year -  Quarter -  Month -  Date].[Date].&[2014-12-05T00:00:00] ) 
CELL PROPERTIES VALUE
, BACK_COLOR, FORE_COLOR
, FORMATTED_VALUE
, FORMAT_STRING
, FONT_NAME
, FONT_SIZE
, FONT_FLAGS

推荐答案

问题是当您有两个日期时，范围([TimeDim Transactions] 维度)没有正确设置筛选.但是，在单个日期的情况下，范围设置正确，因此答案匹配.

The problem is that the scope(for [TimeDim Transactions] dimension) is not getting set correctly when you are having two dates in filter. But, in case of single date, the scope sets correctly, and hence the answer matches.

选择一个日期时

请参阅与切片器相关的子句.它是 [TimeDim Transactions].[Year - Quarter - Month - Date].[Date].&[2014-12-05T00:00:00] )

See the clause associated with slicer. It is [TimeDim Transactions].[Year - Quarter - Month - Date].[Date].&[2014-12-05T00:00:00] )

选择两个日期时

切片器变为:[TimeDim Transactions].[Year - Quarter - Month - Date].CurrentMember

The slicer becomes: [TimeDim Transactions].[Year - Quarter - Month - Date].CurrentMember

请注意，子选择不会设置上下文.因此，当您说要按 [TimeDim Transactions].[Year - Quarter - Month - Date].CurrentMember 过滤时，实际上根本没有过滤，因为当前成员没有作用域是 [All] 成员.这就是为什么你有一个奇怪的大图.

Note that a sub-select does not set a context. So when you say that you want to filter by [TimeDim Transactions].[Year - Quarter - Month - Date].CurrentMember, you are not actually filtering at all, since the current member ins absence of scope is the [All] member. That's why you are having a weirdly large figure.

要解决这个问题，请尝试在轴中使用日期，如下所示，而不是切片器，以便选择范围.

To get rid of the issue, try having the dates in the axes, as below, instead of slicer, so that the scope gets selected.

 ,NON EMPTY 
    {[Stores Dim].[Store Code].[Store Code].ALLMEMBERS} * 
    {
      [TimeDim Transactions].[Year -  Quarter -  Month -  Date].[Date].&[2014-12-04T00:00:00]
     ,[TimeDim Transactions].[Year -  Quarter -  Month -  Date].[Date].&[2014-12-05T00:00:00]
    }

  DIMENSION PROPERTIES 
    MEMBER_CAPTION
   ,MEMBER_UNIQUE_NAME
   ON ROWS

这篇关于Mdx 查询与多维数据集浏览器不一致的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！