Set operation within MDX and months of cooperation case

When it comes to MDX optimization, it is most likely that you have to avoid Set functions, really. Sometimes it's impossible, but avoiding them as often as they show up is the key toward reaching good performance. The idea behind SSAS is every that measure has a coordinate (tuple) and you can't put there more then one member. For example, if you have the following calculation:

([Date].[Day].&[20141001],[Measures].[Invoice Amount])

It returns amount for the hard-coded day, but what are you going to do when you want, say, the same for two days? Probably, the first variant that will come to your mind would be appending the missing member:

([Date].[Day].&[20141001],[Date].[Day].&[20141002],[Measures].[Invoice Amount])

The result is NULL. Why? Because you put here two members in one tuple. It's like you set the coordinate (X1,X2,Y1,Z1) which is invalid, because there can be only one X. In order to calculate it you should tell what to do with this members. You may aggregate it by the native aggregation function:

Aggregate(
    {[Date].[Day].&[20141001],[Date].[Day].&[20141002]},
    [Measures].[Invoice Amount]
)

Now, the result is a proper one, the one that you expected for. Note that, you cannot use Aggregate function with calculated measures, you have to set a certain one instead (Sum, Avg, Min, etc).

In order to experience the difference, let's get to the perfomance testing. What we get when recalculating the same measure by days within one months or using only on month member.

The difference is huge and when you have to recalculate the measure for every tuple. It's not so noticeable for small number of tuples, but when your cube is huge, your performance will be very low. So as I said at the beginning — avoid using Set functions within MDX. Let's get to the real example.

Months of cooperation case

Let's say, we want to analyze for how many months the client has been already working with us. In order to do so we have to add two extra measures the min and the max date by invoices.

Since the measures are done we get the result in YYYYMMDD format and can't get arithmetical count of months. Actually we can get it by using Set functions without extra measures:

As you see, this is a high memory-requiring operation. Let's use new measures without Set operations. We have to find the differences between years and months:

The code:

 (
    Round(
        ([Measures].[Invoice Last Date] - [Measures].[Invoice First Date]) / 10000 -- (20151009 - 20140906) / 10000 = 1 (years diff)
    ) * 12 -- 1 * 12 = 12 (convert years count to months count)
)
+
(
    Round([Measures].[Invoice Last Date] / 100) 
    -
    Round([Measures].[Invoice Last Date] / 10000) * 100
) -- 201510 - 201500 = 10 (last months count)
-
(
    Round([Measures].[Invoice First Date] / 100) 
    -
    Round([Measures].[Invoice First Date] / 10000) * 100
) -- 201409 - 20140900 = 9 (first months count)
+
1 -- extra missing month

Also you can do the same by cropping value using left and right functions and a code that is a little bit slower than previous one.

The code:

(
    Left([Measures].[Invoice Last Date],4) 
    -
    Left([Measures].[Invoice First Date],4)
) * 12 
+
Right(Left([Measures].[Invoice Last Date],6),2) 
-
Right(Left([Measures].[Invoice First Date],6),2)
+
1

To sum it up once again: try to avoid Set MDX functions for performance reasons. You can find list of Set functions here.