PowerPivot查询中MDX Filter函数性能过慢，求优化方案

Great question! Your current approach using Filter() with multiple Instr() calls gets slow because it has to iterate over every member in the set and run string checks for each one—this doesn’t leverage the OLAP engine’s optimized set processing capabilities. Let’s go through several much more efficient solutions, ordered by effectiveness:

1. Use Except() to Explicitly Remove Members (Best If Possible) #

If the account numbers you want to exclude map directly to individual members in the [ZCE Level08] hierarchy (i.e., the member’s key or caption is exactly the account number), use the Except() function instead. This is way faster because it operates on sets at the engine level, avoiding row-by-row string checks.

Except(
  [Cost Element].[ZCE Level08].[ZCE Level08].ALLMEMBERS,
  {
    [Cost Element].[ZCE Level08].[A12600100],
    [Cost Element].[ZCE Level08].[A12600300],
    -- Add the other 17 account members here
  }
)

Why this works: Except() leverages the OLAP engine’s built-in set operations and can use indexes or precomputed structures to quickly remove unwanted members, instead of scanning every member individually.

2. Add a Dimension Attribute for Filtering (Long-Term Optimal Solution) #

If you have control over the cube’s design, add a boolean attribute (like Is_Excluded) to the [Cost Element] dimension. Mark all the accounts you want to exclude as True, then filter on that attribute.

This is the most performant option because dimension attribute filters are heavily optimized by the OLAP engine, using pre-aggregated data and indexes.

Example Query: #

[Cost Element].[ZCE Level08].[ZCE Level08].ALLMEMBERS
WHERE [Cost Element].[Is_Excluded].[False]

Setup: #

1. In your ETL process, add a flag column to the cost element table indicating whether the account should be excluded.
2. Add this column as an attribute to the [Cost Element] dimension in your cube.
3. Process the cube—now you can filter on this attribute in any query with near-instant performance.

3. Replace Instr() with StrMatch() for Better Optimization #

If you must filter based on substring matches in the member caption (because the account number is part of a longer caption), use StrMatch() instead of Instr(). Most OLAP engines (like SSAS) optimize StrMatch() better than Instr(), and the syntax is cleaner for wildcard matches.

Filter(
  [Cost Element].[ZCE Level08].[ZCE Level08].ALLMEMBERS,
  NOT (
    StrMatch([Cost Element].[ZCE Level08].CurrentMember.Properties('Member_Caption'), "*A12600100*")
    OR StrMatch([Cost Element].[ZCE Level08].CurrentMember.Properties('Member_Caption'), "*A12600300*")
    -- Add the other 17 substring checks here
  )
)

Pro Tip: If all the excluded accounts share a common pattern (e.g., starting with A126), you can simplify the condition to a single check:

Filter(
  [Cost Element].[ZCE Level08].[ZCE Level08].ALLMEMBERS,
  NOT StrMatch([Cost Element].[ZCE Level08].CurrentMember.Properties('Member_Caption'), "*A126*")
)

4. Precompute a Named Set #

If you need to reuse this filtered set across multiple queries, define a named set in the cube’s calculation script. This lets the engine precompute or cache the set, so you don’t recalculate it every time you run a query.

Example Calculation Script: #

CREATE SET CURRENTCUBE.[Filtered Cost Elements] AS
Except(
  [Cost Element].[ZCE Level08].[ZCE Level08].ALLMEMBERS,
  {
    [Cost Element].[ZCE Level08].[A12600100],
    [Cost Element].[ZCE Level08].[A12600300],
    -- Add other accounts here
  }
);

Use in Queries: #

[Filtered Cost Elements]

内容的提问来源于stack exchange，提问作者Julio Cesar Avila Padilla