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
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.