SQL Server Performance

Optimize filtering

Discussion in 'Analysis Services/Data Warehousing' started by Flip-Oh, Oct 28, 2004.

  1. Flip-Oh New Member

    Hi there,

    I've created a Calculated Measure that returns the number of buying customers from a cube with sales/customer data. I'm using the following code:

    Iif(
    Count(
    Filter(
    Descendants( [Customers].CurrentMember, [Customers].[Customer] )
    , ([Net Retail Price] <> null) Or ([Return Amount] > 0)
    )
    ) = 0, null,
    Count(
    Filter(
    Descendants( [Customers].CurrentMember, [Customers].[Customer] )
    , ([Net Retail Price] <> null) Or ([Return Amount] > 0)
    )
    )
    )

    This measure is performing very poorly. Queries take up to 5 minutes to complete.

    I tried playing with local variables to be able to only run the count(filter()) statement once, but that doesn't seem to be possible.

    The Filter action is the one that seems to inflict the biggest performance hit.

    Any suggestions on how I might boost the performance?


    Thanks in advance,
    Jorg

Share This Page