MDX新手求助：LastNonEmpty跨维度计数性能优化及方案咨询

Hey there! Let's dig into this performance problem you're facing—your requirement to count tasks where their last status matches a value as of a selected date makes total sense, but with 40 million rows and 8 million unique tasks, we need to tweak both your MDX and the underlying data model to get fast, reliable results.

First: Fix Your MDX Query Logic #

Your current query uses TAIL(NONEMPTY(...)) which loops through huge volumes of historical data for every task—no wonder it's slow. Try these two optimized approaches:

1. Use SSAS's Built-in LastNonEmpty Function #

LastNonEmpty is purpose-built for "get the most recent non-empty value" scenarios and leverages SSAS's storage engine optimizations, so it's way faster than rolling your own TAIL logic:

WITH MEMBER Measures.LatestTaskStatusID AS
  LastNonEmpty([DimTaskStatus].[TaskStatusID].Members, Measures.CountOfRows)
MEMBER Measures.TargetTaskCount AS
  SUM(
    [DimTask].[TaskID].MEMBERS,
    IIF(
      Measures.LatestTaskStatusID = [DimTaskStatus].[TaskStatusID].&[2],
      1,
      NULL
    )
  )
SELECT Measures.TargetTaskCount ON 0,
       [Date].[Date].&[20171111] ON 1
FROM [Cube Name]
-- Restrict to only status changes on or before the selected date
WHERE ({NULL:[Date].[Date].CURRENTMEMBER})

2. Narrow the Task Scope First with Exists #

If the above still feels slow, use Exists to first filter only tasks that had status changes before your selected date, then check their last status:

WITH MEMBER Measures.LatestStatusID AS
  LastNonEmpty([DimTaskStatus].[TaskStatusID].Members, Measures.CountOfRows)
SELECT
  COUNT(
    Filter(
      Exists([DimTask].[TaskID].MEMBERS, {NULL:[Date].[Date].&[20171111]}, "FactTaskStatus"),
      Measures.LatestStatusID = [DimTaskStatus].[TaskStatusID].&[2]
    )
  ) ON 0
FROM [Cube Name]

This cuts down the number of tasks you need to evaluate by ignoring ones with no activity before your target date.

Second: Overhaul Your Fact Table Design (The Biggest Win) #

No amount of MDX tweaking will fix a 40M-row transactional fact table for this kind of "latest state" query. The real solution is precomputing state snapshots:

1. Build a Daily Task Status Snapshot Table #

Add a daily snapshot table FactTaskDailySnapshot to your ETL pipeline. This table will store one row per task per day, representing the task's last status as of that date:

• Fields: TaskID, SnapshotDate, TaskStatusID, TaskStatusName
• ETL Logic:
  • For tasks with status changes on the day, take their latest status record
  • For tasks with no changes, copy their previous day's snapshot entry (so every task has a row every day)

Add this snapshot table to your cube as a fact table. Now your query becomes trivial: just filter for SnapshotDate = [selected date] and count tasks with the matching TaskStatusID. This will be 10-100x faster since you're no longer scanning years of historical changes.

2. Add a "Latest Status Flag" to Your Original Fact Table #

If snapshots feel like too much overhead, add a bit field IsLatestStatus to your existing FactTaskStatus table:

• When inserting a new status change for a task, first set IsLatestStatus = 0 for all old records of that task, then set IsLatestStatus = 1 for the new record
• Create a cube measure LatestStatusCount that counts rows where IsLatestStatus = 1

Your query can now filter for Date <= [selected date] and count matching TaskStatusID values using this measure—no more scanning all historical rows.

Third: Optimize Your ETL Pipeline #

1. Incremental Snapshot Updates #

Don't scan all 40M rows to build daily snapshots. Instead:

• Only process tasks that had status changes on the current day
• For all other tasks, copy their previous day's snapshot entry directly. This cuts ETL runtime drastically.

2. Update Task Dimension with Current Status #

Add CurrentStatusID and CurrentStatusDate attributes to your DimTask dimension. Update these attributes daily only for tasks that had status changes. Then you can create a cube calculation that counts tasks directly from the dimension where CurrentStatusID matches your target and CurrentStatusDate <= [selected date]—this shifts the work to the dimension, which is optimized for fast lookups.

Fourth: Bonus SSAS Performance Tweaks #

• Add indexes to DimTask.TaskID, DimTaskStatus.TaskStatusID, and Date.Date to speed up filtering
• Partition your cube by date (e.g., yearly or quarterly partitions) so queries only scan the relevant date range
• Adjust SSAS memory allocation to ensure enough cache for frequently used dimensions and measures

内容的提问来源于stack exchange，提问作者Sohi