MDX Step by Step 读书笔记(六) - Building Complex Sets (复杂集合的处理) - Generate 和 Extract 函数的使用

Performing Advanced Set Construction

Assembling Sets with the Generate Function

如果你对C#或者VB 等其它变成语言熟悉的话，应该对循环比较熟悉。在循环中，可以遍历集合里的每一个对象，在遍历的时候，对对象会执行一些操作。换一种方式来看MDX  SET中的元组就如同看集合中的对象，使用 SET集合的一些表达式来执行一些操作，对 Generate 函数来说这个可能就是对它的一个初步概念和认识。

Generate ({Set}, {Set Expression} [, ALL])

在Generate 函数中迭代的 SET 集合中的每一个元组，都会参与到一个基于 SET 集合的表达式中计算。每一次迭代返回的一个集合都会和其它迭代后产生的集合连接起来重新组成一个新的集合并返回，ALL 关键字的作用就是定义是否需要保留重复的元组。

使用Generate 函数的机会不是非常的多，但是如果碰到需要循环迭代的逻辑，这时就需要考虑是否需要使用Generate 来解决一些问题。

示例一 - 先看一个简单的查询 Reseller Sales 销售前5的产品

SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
TopCount(
　　　　　　{[Product].[Product].[Product].Members},
　　　　　　5,
　　　　　　([Measures].[Reseller Sales Amount])
) ON ROWS
FROM [Step-by-Step];

这个示例在之前的章节中一直在使用，观察一下这个查询返回的都是自行车，当然事先并不知道返回的一定就是自行车。如果我们想查询每个产品分类下前5名销售的产品，可以使用 Generate 函数。

示例二

SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Generate(
　　　　　　{[Product].[Category].[Category].Members},
　　　　　　TopCount(
　　　　　　　　{[Product].[Product].[Product].Members},
　　　　　　　　5,
　　　　　　　　([Measures].[Reseller Sales Amount])
　　　　)
) ON ROWS
FROM [Step-by-Step]

这次查询的结果如上图所示和之前查询的结果完全一样，如何解释这个问题？ 我们加上 ALL 关键字。

示例三 – 带 ALL 的 Generate 函数

SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Generate(
　　{[Product].[Category].[Category].Members},
　　TopCount(
　　　　{[Product].[Product].[Product].Members},
　　　　5,
　　　　([Measures].[Reseller Sales Amount])
　　),
　　ALL
) ON ROWS
FROM [Step-by-Step]

这个查询结果应该是一个包含有20个产品的集合，因为首先我们的产品分类有四种，我们可以看到Generate 函数的第一个参数是一个集合{[Product].[Category].[Category].Members} 有四个成员。

四个成员就意味着有四轮循环，每一轮循环中经过 TopCount 函数会返回5个产品信息，那么总共有20个成员的集合返回。由于在 TopCount 函数中取销售最多的前5名产品的过程中，没有使用到 Category 这个上下文环境，那么这样就造成了每个迭代过程中取销售前5名的范围都是在所有产品中取。这样循环4次每次取到的结果都是一样，我们使用 ALL 关键字就看得出来这几次循环取的产品是一样的。因为在上一个示例中，在 Generate 函数中并没有使用 All 函数，因此重复的元组被自动的剔出掉， 所以最终的结果是只返回了5个产品。

为了解决这个问题，我们应该记得在 MDX Step by Step 读书笔记(五) - Working with Expressions (MDX 表达式) - CurrentMember - Properties - Existing 的使用 中讲解了 Existing 关键字的作用，它可以强制当前集合关联到上下文环境。在这个例子中，什么是上下文环境？Category 就是上下文环境，要求在每个Category的上下文环境(可以理解为查询范围)中来查询前5的产品。

示例四 – 在 Generate 函数中使用 Existing 上下文环境的应用

SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Generate(
　　{[Product].[Category].[Category].Members},
　  TopCount(
　　　　　　　　EXISTING {[Product].[Product].[Product].Members},
　　　　　　　　5,
　　　　　　　　([Measures].[Reseller Sales Amount])
　　　　　　),
　　 ALL
) ON ROWS
FROM [Step-by-Step]

这样就查询出来了，但是查询结果看得有点费劲，因此再改一改。

示例五 – 使用 Existing 的 Generate 函数

SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Generate(
                {[Product].[Category].[Category].Members},
                {[Product].[Category].CurrentMember}*
                TopCount(
                    EXISTING {[Product].[Product].[Product].Members},
                    5,
                    ([Measures].[Reseller Sales Amount])
            ),
    ALL
) ON ROWS
FROM [Step-by-Step];

这样就可以看到它们各自的分类以及分类下前5的产品。

Assembling Sets with the Extract Function

Extract 函数的主要作用就是从一个集合中根据其它的层次结构来抽取元组并形成一个新的集合。

Extract ({Set}, Hierarchy1 [, Hierarchy2, .. HierarchyN])

第一个参数是一个集合，第二个参数以后都是属性层次结构。相当于比如集合中的元组由 Product 和 Calendar 这两个成员组成，假设第二个参数是 Product 层次结构，那么最后的结构就是将集合中与 Product 层次结构关联的那部分元组(即Product成员)取出，而 Calendar 成员将被排除在返回的集合中。这个操作与 CrossJoin 的操作正好相反，CrossJoin 是两组集合相结合，而 Extract是按照提供的层次结构对集合进行分解。

示例一 – 先看使用 * 或者 CrossJoin 的示例

SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{[Product].[Product].[Product].Members} *
{[Date].[Calendar].[Month].Members} ON ROWS
FROM [Step-by-Step]

在这个查询中，可以看到月份和产品结合后 Reseller Sales 的情况。这个查询的数据量可能有点多，那么我们可能更希望关注那些卖的比较不错的产品，比如查询那些大于 16000 的产品。

SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Filter(
    {[Product].[Product].[Product].Members} *
    {[Date].[Calendar].[Month].Members},
    ([Measures].[Reseller Sales Amount])>160000
) ON ROWS
FROM [Step-by-Step]

这下可以看清楚一些，行中有两列组成，一列是来源于{[Product].[Product].[Product].Members} 一列来源于{[Date].[Calendar].[Month].Members} 它们通过 CrossJoin 关联起来。那么如果我们在这里只想看到 Product 或者 Date 方面的内容，那么就需要对这个查询结果进行拆分。

示例二 – 使用 Extract 根据层次结构分拆集合

SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Extract(
Filter(
    {[Product].[Product].[Product].Members} *
    {[Date].[Calendar].[Month].Members},
    ([Measures].[Reseller Sales Amount])>160000
),
[Product].[Product]
) ON ROWS
FROM [Step-by-Step]

对比上一个例子，抽取出来的部分只有产品部分内容，并且它们是唯一的。因为对比上一个示例，一个产品在多个月份都有数据，现在只提取产品部分，那么它们的数据将自动合并累加到一个唯一的产品了。

更多 BI 文章请参看 BI 系列随笔列表 (SSIS, SSRS, SSAS, MDX, SQL Server)  如果觉得这篇文章看了对您有帮助，请帮助推荐，以方便他人在 BIWORK 博客推荐栏中快速看到这些文章。