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

by schiznig   Last Updated March 12, 2018 21:06 PM

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 [email protected] 
    AND NOT EXISTS(SELECT 1 FROM Table AS B 
    WHERE B.Part=A.Part
    AND [email protected]
    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

Tags : ssas mdx


Related Questions




How make an SSAS Tabular model case sensitive?

Updated August 10, 2017 10:06 AM