MDX查询以按相同维度的成员计算具有AND条件的度量 [英] MDX query to calculate measure with AND condition by members from same dimension

问题描述

我在OLAP数据库(SSAS 2012)中有一个多维数据集，其中[人员]，[日期](订单日期)，[类别](订单中项目的类别)是维度，[订单]，[OrdersCategories]是事实表. [OrdersCategories]是用于将订单与类别连接的多对多表.

I have cube within OLAP database (SSAS 2012) where [Persons], [Dates] (date of order), [Categories] (category of item within order) are dimensions and [Orders], [OrdersCategories] are fact tables. [OrdersCategories] is many-to-many table to connect orders with categiries.

使用查询:

SELECT
    {[Measures].[Orders Distinct Count]} ON COLUMNS,
    {[Persons].[Id].Children} ON ROWS
FROM [DB]
WHERE ( 
    {[Dates].[Id].&[2015-06-11T00:00:00] : [Dates].[Id].&[2015-06-17T00:00:00]},  
    {[Categories].[Id].&[10], [Categories].[Id].&[11]}
) 

我可以计算每个人的订单数量(在指定时间段和按指定类别).这种情况下按类别的条件是否像或"条件那样起作用，并且无论顺序与第一类别或第二类别或两者都有关系.

i can count number of orders (for specified time period and by specified categories) for each person. Is this case condition by category works like 'OR' condition and no matter order has relation with first category or second category or both.

是否可以按类别写条件查询，就像"AND"条件一样工作，即只有与两个类别都相关的订单才被计数?

Is it possible to write query with condition by categories which would work like 'AND' condition, namely order was counted only if it is related with both categories?

推荐答案

AND具有相同层次结构的成员通常按以下方式实现:

AND with members from the same hierarchy is generally implemented like the following:

SELECT 
  {[Measures].[Orders Distinct Count]} ON COLUMNS
 ,{[Persons].[Id].Children} ON ROWS
FROM [DB]
WHERE 
  Exists
  (
    Exists
    (
      {
        [Dates].[Id].&[2015-06-11T00:00:00] : [Dates].[Id].&[2015-06-17T00:00:00]
      }
     ,{[Categories].[Id].&[10]}
     ,"MeasuresGroupName"
    )
   ,{[Categories].[Id].&[11]}
   ,"MeasuresGroupName"
  );

您需要从多维数据集中添加度量值组名称.度量组名称与度量"层次结构中的文件夹名称相对应-Adventure Works中的一个示例为"Internet Sales"

You need to add a measure group name from your cube. Ameasure group name corresponds to the folder names in the Measures hierarchy - an example in Adventure Works is "Internet Sales"

可能的选择

我认为您可以尝试使用嵌套的NonEmpty函数来实现上述功能:

I think you could try implementing the above using nested NonEmpty functions:

SELECT 
  {[Measures].[Orders Distinct Count]} ON COLUMNS
 ,{[Persons].[Id].Children} ON ROWS
FROM [DB]
WHERE 
  NonEmpty
  (
    NonEmpty
    (
      {
        [Dates].[Id].&[2015-06-11T00:00:00] : [Dates].[Id].&[2015-06-17T00:00:00]
      }
     ,{([Categories].[Id].&[10],[Measures].[Orders Distinct Count])}
    )
   ,{([Categories].[Id].&[11],[Measures].[Orders Distinct Count])}
  );

这篇关于MDX查询以按相同维度的成员计算具有AND条件的度量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！