MDX - calculated member with unrelated dimension

Question

I have two measures: [Measure].[ChildCount] and [Measure].[Buyings] and two dimensions: [Buyers] and [Sellers].

[Measure].[ChildCount] uses [Buyers] dimension to count child organization for each of the buyer. [Measure].[Buyings] uses [Buyers] and [Sellers] to count buying from [Seller] to [Buyer].

What I want to achieve is select all buyings for Buyers with ChildCount < 1 and Buyers with ChildCount > 0.

Currently these queries are working fine: First one that count buying for each sender/buyer:

SELECT [Measure].[Buyings] on COLUMNS,
[Sellers].[Code].[Code] *
[Buyers].[Code].[Code] ON ROWS
FROM MyCube

And second that calculates buyings for buyers with and without childs:

WITH MEMBER [Measure].[BuyingsWithChilds]
as SUM
(
FILTER([Buyers].[Code].[Code],[Measure].[ChildCount]>0),
[Measure].[Buyings]
)
MEMBER [Measure].[BuyingsWithoutChilds] 
as SUM
(
FILTER([Buyers].[Code].[Code],[Measure].[ChildCount]<1),
[Measure].[Buyings]
)
SELECT
{
[Measure].[BuyingsWithChilds],
[Measure].[BuyingsWithoutChilds]
} ON COLUMNS,
[Buyers].[Code].[Code] ON ROWS
FROM MyCube

But if I trying to combine these queries into desired one:

    WITH MEMBER [Measure].[BuyingsWithChilds]
    as SUM
    (
    FILTER([Buyers].[Code].[Code],[Measure].[ChildCount]>0),
    [Measure].[Buyings]
    )
    MEMBER [Measure].[BuyingsWithoutChilds] 
    as SUM
    (
    FILTER([Buyers].[Code].[Code],[Measure].[ChildCount]<1),
    [Measure].[Buyings]
    )
    SELECT
    {
    [Measure].[BuyingsWithChilds],
    [Measure].[BuyingsWithoutChilds]
    } ON COLUMNS,
    [Sellers].[Code].[Code] ON ROWS
    FROM MyCube

This query's execution takes forever. Is it possible to fix or optimize this?

FrankPl FrankPl · Accepted Answer · 2013-11-06T22:22:25

If you convert [Measure].[ChildCount]>0 and [Measure].[ChildCount]<1 to an attribute like "HasChildren", then you can avoid the Filter function which is normally slow, and which you use two times.

Then your WITH clause would be simplified to

WITH MEMBER [Measure].[BuyingsWithChilds]
as ([Buyers].[HasChildren].[Yes], [Measure].[Buyings])
MEMBER [Measure].[BuyingsWithoutChilds]
as ([Buyers].[HasChildren].[No], [Measure].[Buyings])

which should be much faster, as it uses the standard aggregation of [Measure].[Buyings].

MDX - calculated member with unrelated dimension

1 Answers