MDX查询-出售所有给定产品的最佳推销员 [英] MDX query - best salesmen who sold all of given products

问题描述

假设我有两个简单的维度:

Let's say I have two simple dimensions:

产品-具有ID和名称的

Products - with id and name

推销员-具有ID和名称 我的事实表名为SALES，其中包含上述ID.

Salesmen - with id and name My fact table is named SALES and contains the ids of the abovementioned.

我需要产生一个查询，该查询将显示出售所有给定产品的推销员的姓名.

I need to produce a query that will show the names of salesmen who sold all of the given products.

此代码解决了X和Y两个项目的问题:

This code solves the problem for two items X and Y:

SELECT 
  {} on 0,
  EXISTS(
    EXISTS(
       {[Salesmen].[Name].MEMBERS}, 
       {[Products].[Name].&[X]}
    )
    ,{[Products].[Name].&[Y]}
  )
  ON 1
FROM [Test];

另一个版本是:

SELECT 
  {} on 0,
  INTERSECT(
    NONEMPTY(
       {[Salesmen].[Name].MEMBERS}
       ,([Products].[Name].&[X])
    )
   ,NONEMPTY(
       {[Salesmen].[Name].MEMBERS}
       ,([Products].[Name].&[Y])
    )
  )
  ON 1
FROM [Test];

但是，如果给定产品的列表很大(例如-100个随机产品)，则此方法会很麻烦.

However, this method becomes troublesome if the list of given products is large, for example - 100 random products..

推荐答案

您是否对层次结构[Products].[Name]具有属性member_key?我们可以这样测试:

Do you have a property member_key for the hierarchy [Products].[Name] ? We can test like this:

WITH
MEMBER [Measures].[Meas1] AS
 [Products].[Name].CurrentMember.PROPERTIES("KEY ID")
MEMBER [Measures].[Meas2] AS
 [Products].[Name].CurrentMember.MEMBER_Key
MEMBER [Measures].[Meas3] AS
 [Products].[Name].CurrentMember.MEMBERvalue
select
 {
  [Measures].[Meas1]
 ,[Measures].[Meas2]
 ,[Measures].[Meas3]
 } on COLUMNS, 
 [Products].[Name].MEMBERS on ROWS
FROM [Test];

希望其中一项自定义指标能给您带来价值?我假设Meas2正在工作(如果Meas1或Meas3返回数字，请交换到另一个)

Hopefully one of the custom measures gives you a value? I'll assume Meas2 is working (swap to a different one if Meas1 or Meas3 is returning numbers)

WITH
  MEMBER [Measures].[Meas2] AS
   [Products].[Name].CurrentMember.MEMBER_Key
  SET [ProdsetA] AS
   FILTER(
     [Products].[Name].MEMBERS
     ,[Measures].[Meas2] <100
   )
  SET [ProdsetB] AS
   FILTER(
     [Products].[Name].MEMBERS
     ,[Measures].[Meas2] >500
   )
SELECT 
 {} on 0,
 INTERSECT(
   NONEMPTY(
      {[Salesmen].[Name].MEMBERS}
      ,[ProdsetA]
   )
  ,NONEMPTY(
      {[Salesmen].[Name].MEMBERS}
      ,[ProdsetB]
   )
 )
 ON 1
FROM [Test];

... >100和<500很重要.这些是filter函数要使用的条件.定制集[ProdsetA]将仅包含具有MEMBER_Key为<100的产品，而定制集[ProdsetB]将仅包含具有MEMBER_Key为>500的产品.您需要使用第一个脚本提供给您的成员值来确定多维数据集上下文中的值100和500(...我不知道您多维数据集中的键值，因此仅使用100和500作为占位符)

... the >100 and <500 are important. These are the criteria for the filter function to use. The custom set [ProdsetA] will only contain Products that have MEMBER_Key that are <100 whereas the custom set [ProdsetB] will only contain Products that have MEMBER_Key that are >500. You need to use the member values presented to you by the first script to decide what values 100 and 500 should be in your cube context (...I don't know the key values in your cube so just used 100 and 500 as placeholders)

这篇关于MDX查询-出售所有给定产品的最佳推销员的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！