使用MDX将SSAS成员分组到存储桶中 [英] Grouping SSAS members into buckets using MDX

问题描述

此处是SSAS和MDX新手.

SSAS and MDX newbie here.

如果我有一个包含地理位置"的SSAS多维数据集，则将产品"作为维度，将总销售额"作为度量.

If I have an SSAS cube with Geography, Product as the dimensions and total sales as a measure.

以下元素处于地理条件下:

The following elements are under geography:

EAST
WEST
NORTH
SOUTH
MID-ATLANTIC
MOUNTAIN
NORTH-WEST

产品是:

1
2
3
4

对于产品ID = 1，是否可以将某些成员归入该国其他地区"存储区并汇总销售额?

For Product ID = 1, is there a way I can group some members into a "rest of the country" bucket and aggregate the sales?

意思是，预期的输出是:

Meaning, the expected output is:

Product ID    Geography          Sales

1             East              100
1             West              200
1             North             300
1             South             400
1             RestOfNation      1200

2             East              100
2             West              50
2             RestOfNation      1500

是否有一种方法可以使用MDX查询将某些成员归入"RestOfNation"?

Is there a way I can buck some members into "RestOfNation" using an MDX query?

推荐答案

您可以尝试这样的操作.

You could try something like this.

1. 创建一个命名集，该集合由要放入存储桶RestOfWorld中的成员组成.
2. 在地理范围内使成员成为世界其他成员的聚集.
3. 查找不在世界其他地方的国家/地区.

1. Create a named set made up of the members that you'd like to throw into the bucket RestOfWorld.
2. Make a member, within the geography dimension, that is the aggregation of the rest of world members.
3. Find the countries that are not in the rest of the world set.

以下是针对AdvWrks的脚本(值得安装，因为它是使用mdx并发布到论坛时的常用原型工具):

Here is a script against AdvWrks (worth installing as it is a common prototyping tool when playing with mdx and posting to forums):

WITH 
  SET [RestOfWorld] AS 
    {
      [Customer].[Customer Geography].[Country].&[United Kingdom]
     ,[Customer].[Customer Geography].[Country].&[Germany]
    } 
  MEMBER [Customer].[Customer Geography].[All].[RestOfWorld] AS 
    Aggregate
    (
      {
        [Customer].[Customer Geography].[Country].&[United Kingdom]
       ,[Customer].[Customer Geography].[Country].&[Germany]
      }
    ) 
  SET [CountriesMinusROW] AS 
    [Customer].[Customer Geography].[Country].MEMBERS - [RestOfWorld] 
SELECT 
  NON EMPTY 
    {[Measures].[Internet Sales Amount]} ON 0
 ,NON EMPTY 
      [Product].[Category].[Category]
    * 
      {
        [CountriesMinusROW]
       ,[Customer].[Customer Geography].[All].[RestOfWorld]
      } ON 1
FROM [Adventure Works]
WHERE 
  [Date].[Calendar Year].&[2007];

上面给出了以下cellset:

这篇关于使用MDX将SSAS成员分组到存储桶中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！