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

Question

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:

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.

Answer 1

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.

Answer 2

My recommendation is not to attempt to do the sequence logic in MDX. You will be disappointed in performance. I would recommend the following approach:

First create a view (or a physical table if you prefer) which returns one row per part/customer/person per day showing the currently effective value. The view would look something like this:

select x.PersonID, x.Part, x.CustomerID, x.Value, d.DateKey
from (
    select *
    ,LEAD(x.DateKey,1,100000000) OVER (PARTITION BY CustomerID,PersonID, Part ORDER BY SEQ asc)-1 as EndDateKey
    from YourTable x
) x
join DimDate d on d.DateKey between x.DateKey and x.EndDateKey
where d.[Date] < getdate()

Next, load that new view as a measure group in the cube. Instead of having a Sum measure, use a measure on the Value column with AggregateFunction=FirstChild. This will cause the February 2018 total to reflect the rows that were effective on February 1, 2018.

FirstChild is a semi-additive measure which causes it to return the first member in the selected date range. I would recommend you read this blog post to make sure you mark the Date dimension appropriately so it will work.

Counterintuitively, even though you are exploding the amount of data, this approach will perform better in a cube because all the hard work is being done during cube processing time and during query time it just displays the right date of data.

Please do the math on how many rows you will end up with if you follow my recommendation. Hopefully it is a reasonable number of rows (say, 100,000,000 or less, or a billion or more if you have good hardware). If it's an unreasonable amount of rows (like 100,000,000,000) then there are other options like a many-to-many date range dimension that are much more complex to implement. I wouldn't recommend that approach unless my recommendation isn't appropriate.