MDX filter problem

Question

I'm pretty new to the whole MDX thing, but the following is just driving me batty. A FILTER statement I'm using is acting... strangely. Code sample, followed by description:

SELECT
    {
        FILTER(
            MEMBERS([Time].[5-4-4 Week Year]),
            [Measures].[Ship Gross Units] > 0
        )
    }
    ON COLUMNS,
    {
        FILTER(
            MEMBERS([Group].[Alternate Hierarchies]),
            [Measures].[Ship Gross Units] > 0
        )
    }
    ON ROWS
FROM SBD.SBD
WHERE
    (
        [FiscalYear].[FY09],
        [Scenario].[Actuals Total],
        [Measures].[Ship Gross Units],
        [Channel].[FOS]
    )

I'm trying to pull gross units for a particular sales channel, by week of the fiscal year (some columns obfuscated slightly). All those filters are in place due to the fact that I often need these broken out at the SKU level, and it's simpler to deal with a truncated dataset on my machine (let the DB do the work, I say!).

The problem is, this query returns 0 sales in the FOS channel. That seemed strange, so I removed the row filter:

SELECT
    {
        FILTER(
            MEMBERS([Time].[5-4-4 Week Year]),
            [Measures].[Ship Gross Units] > 0
        )
    }
    ON COLUMNS,
    MEMBERS([Group].[Alternate Hierarchies])
    ON ROWS
FROM SBD.SBD
WHERE
    (
        [FiscalYear].[FY09],
        [Scenario].[Actuals Total],
        [Measures].[Ship Gross Units],
        [Channel].[FOS]
    )

And all of a sudden, sales show up in the FOS channel. This blows my mind; previously, I'd assumed I was filtering to just receive rows showing sales, and I got none. Now I'm showing everything, and there are rows with sales. It's easy enough to work around this problem with Perl or whatever, but I'd rather solve it "right".

I'm reasonably certain I'm just misunderstanding some niggling detail, but I'm tired of bashing my head against the desk.

Thanks!

Answer 1

I usually work with Microsoft Analysis Services, but the MDX is generally very similar to that used in Essbase.

The axis will be getting evaluated independantly, so the filter statement on Group will be looking at only the members in the where clause. So is it possible that either your Gross Units have some negatives and are less than or equal to 0 for FY09? Or are your unit counts large enough that they are potentially overflowing the data type and wrapping around to negative numbers?

One possible work around if you are just looking for non-empty cells is to use the NON EMPTY keyword on the axis. eg.

SELECT    
   NON EMPTY MEMBERS([Time].[5-4-4 Week Year]),
     ON COLUMNS,
   NON EMPTY  MEMBERS([Group].[Alternate Hierarchies])
     ON ROWS
FROM SBD.SBD
WHERE
    (
        [FiscalYear].[FY09],
        [Scenario].[Actuals Total],
        [Measures].[Ship Gross Units],
        [Channel].[FOS]
    )