Equivalent of SQL Not Exists in MDX (or finding another way to accomplish same result)

mdxssas

I am trying to help a contractor implement a cube in Multi-Dimensional SSAS 2017 and do not have very much experience in MDX.

I have a table that looks something like this:

enter image description here

The goal is to only include rows in the query that have the lowest [seq] per each combo of [Part], [CustomerID], and PersonID after filters have been applied, and then sum the value column. So if no filters are applied, only rows 1 and 3 should be returned and the sum of the value column should be 2. But if the user filters data for only the month of June, only rows 2 and 3 should be returned and the sum of the value column should be 1.

We accomplished this in SQL like this:

SELECT SUM(Value) 
FROM (SELECT * 
 ,ROW_NUMBER() OVER (PARTITION BY CustomerID,PersonID, Part ORDER BY SEQ asc) AS Seq
 FROM Table WHERE DATEKeY BETWEEN @StartDate AND @EndDate
 )A
WHERE Seq=1;

But the contractor is having big problems with performance of the MDX rank function. Not having very much experience with MDX, I am having a hard time knowing what alternatives there are that will perform well and the contractor has not provided any alternatives that match our business need.

Another approach I came up with is this:

SELECT SUM(Value) 
FROM [Table]  AS A
WHERE DATEKey BETWEEN @StartDate AND @ENDDate 
AND CustomerID=@CustomerID 
AND NOT EXISTS(SELECT 1 FROM Table AS B 
                WHERE B.Part=A.Part
                  AND B.CustomerID=@CustomerID
                  AND B.PersonID=A.PersonID
                  AND B.SEQ<A.SEQ)

This is actually performing better than the old way in SQL but I have no clue how to implement either this, the previous method, or equivalent logic in MDX that is going to perform well.

I know the MDX will be hard to do without seeing the whole cube but faux code or just some advice on what functions will offer the best performance for this logic would be a big help.

Best Answer

As you say it's hard to try out without your actual cube and it depends a bit on how your dimensions are layed out, but I think you can get there using the BOTTOMCOUNT function if you create a measure on seq.

BottomCount(Set_Expression, Count [,Numeric_Expression])

Your set_expression could be a crossjoin between the dimensions you care about and you could create a dynamic set based on that expression.

Something along the lines of

WITH DYNAMIC SET LowestSeq AS
BottomCount({Part.Members * Customer.Members * Person.Members})
   , 1  
   , [Measures].[seq])

It would help if you published a small repro of your scenario as an XMLA script on pastebin or something. If your dsv is based on named queries producing the data we can easily create the same cube on our machine.

Related Question