I was recently involved in a discussion about how to implement buckets dynamically with MDX in our SSAS solution.
Let’s go inside this issue:
The user had a Dimension “TICKET” with two attributes:

TICKET_ID: Integer, key of the ticket.

UPDATE_TIMESTAMP : Datetime which represents the last time when the ticket was updated.

And the requirements were:

– Create a measure [A] which is the difference between right now and the UPDATE_TIMESTAMP attribute .

– Create a measure [B] which is the SUM of [A]

– Create dynamically buckets for [A]

If [A] value is greater than 1 and less than 5, assign bucket 1.

If [A] value is greater than 5 and less than 10, assign bucket 2.

Besides that, all these measures should work dynamically without the need of using TICKET_ID neither columns or rows.

 

We can start thinking about approaches and we could end up in a Integration Services solution which calculates everything in a complex SSIS Package before bringing all this data to our SSAS cube. Extra developing time, extra loading time, extra complexity but clean SSAS solution.

However, there is another approach which calculates everything dynamically in our SSAS solution using MDX. Let’s take a look at a particular example:

First of all, we have been designing our DSV:

As you can see, we just added  to our DimTicket table on the DSV a new column named “BUCKET” with a NULL default value.

And we have some test data inside DimTicket and FactSales tables:

 

 

Because of the new column, we have to add the attribute “BUCKET” to our Ticket Dimension and it should be reprocessed. But this is the only step when we have to do that and next steps will use MDX, promise :).  After all, we need this attribute to filter our analysis by buckets physically in the  SQL Server Management Studio.

After that, we are ready to start creating our MDX calculated measures on the MDX Script of our cube with no need to process again. We can do this in Calculations tab, and activating the “Script View” option.

 

The first one is a measure which represents the difference between  right now and the value of the “UPDATE TIMESTAMP” attribute. We have to consider the behavior when we are on the [All] level, the UNKNOWNMEMBER and/or the leaf level:

– [All] level: The total will be the average between all the values from their leaves in a recursive way.

– UNKNOWNMEMBER: The result is 0.

– Leaf level: The result is the difference in days between the date value of the member and now.

Note that, we should hide all the values that don’t have [Sales] at the leaf level so we have to simulate a NON_EMPTY_BEHAVIOR by using another IIF clausure with “ (([Dim Ticket].[UPDATE TIMESTAMP].currentmember,[Sales])<>0 “.

 

This is the final MDX:

CREATE MEMBER CURRENTCUBE.[Measures].[Diff Time]
 AS 
iif(([Dim Ticket].[UPDATE TIMESTAMP].currentmember,[Sales])<>0,

iif([Dim Ticket].[UPDATE TIMESTAMP].currentmember is [Dim Ticket].[UPDATE TIMESTAMP].[All],
AVG(DESCENDANTS([Dim Ticket].[UPDATE TIMESTAMP].currentmember,[Dim Ticket].[UPDATE TIMESTAMP].[UPDATE TIMESTAMP]),
[Diff Time]),
iif([Dim Ticket].[UPDATE TIMESTAMP].currentmember is [Dim Ticket].[UPDATE TIMESTAMP].[All].UNKNOWNMEMBER,
0,
datediff("d",CDATE([Dim Ticket].[UPDATE TIMESTAMP].currentmember.membervalue),now())
)
),
null), 
NON_EMPTY_BEHAVIOR = { [Sales] }, 
VISIBLE = 1  ;

 

Next step, we have to create an invisible measure named “Bucket”. This one will hold the logic of the creation of the buckets explained on the top of the post and will use the [Diff Time] measure created in the previous step. For those results which are out of the buckets the value will be “-1”.

The MDX:

 

CREATE MEMBER CURRENTCUBE.[Measures].[Bucket]
 AS iif([Measures].[Diff Time] = 0,
-1, 
iif([Measures].[Diff Time] > 1 and [Measures].[Diff Time] <5,
1,
iif([Measures].[Diff Time] > 5 and [Measures].[Diff Time] <10,
2,
-1
)
)
), 
NON_EMPTY_BEHAVIOR = { [Sales] }, 
VISIBLE = 0  ;

 

At this point we have all the calculations related to the logic of the problem.

Now, we can fill the “BUCKET” attribute of our Ticket Dimension dynamically. With MDX we are able to create with new members, besides measures, and add them to an attribute of a dimension. That’s why we are going to create one member which represents all the members from the Bucket 1, and another one which represents all ones from the Bucket 2.

 

To calculate all the members of the Ticket Dimension which belong to the Bucket 1, we will use a CROSSJOIN between the [TICKET ID] attribute and the [UPDATE TIMESTAMP] attribute of the Ticket Dimension.

For each [TICKET ID] and [UPDATE TIMESTAMP] we are going to get the value of the measure [Bucket] to know which bucket belongs to and to be sure that this calculation happens for each pair we will use  EXISTING.

Once we have all the members and their own value for the [Bucket] measure, we are ready to filter all of them which belong to the Bucket 1 with the FILTER function.

Finally, we can convert this bunch of members from the bucket 1 to a one member which represents all of them with the AGGREGATE function.

 

The MDX should look like this:

CREATE MEMBER CURRENTCUBE.[Dim Ticket].[BUCKET].[All].[Bucket 1]
 AS Aggregate(Filter(EXISTING {[Dim Ticket].[TICKET ID].[TICKET ID]*[Dim Ticket].[UPDATE TIMESTAMP].[UPDATE TIMESTAMP]}
, [Measures].[Bucket] = 1)), 
VISIBLE = 1  ;

 

Even if we are using the Form View and not the Script View, it should look like this:

 

After that, we can follow some similar steps to create the calculation for the Ticket members which belong to the Bucket 2 :

CREATE MEMBER CURRENTCUBE.[Dim Ticket].[BUCKET].[All].[Bucket 2]
 AS Aggregate(Filter(EXISTING {[Dim Ticket].[TICKET ID].[TICKET ID]*[Dim Ticket].[UPDATE TIMESTAMP].[UPDATE TIMESTAMP]}
, [Measures].[Bucket] = 2)), 
VISIBLE = 1;

 

Finally, we have to accomplish the last requirement: A measure which sums the [Diff Time] values of all members of the bucket 1 and another one which sums the same  for members of the bucket 2.

The structure of these MDX are similar to the latest one, but instead of using the AGGREGATE function, we  are using SUM function to get the total of the [Diff Time] for the members of the buckets:

 

CREATE MEMBER CURRENTCUBE.[Measures].[Sum Bucket 1]
 AS 
iif(Count(Filter(EXISTING {[Dim Ticket].[TICKET ID].[TICKET ID]*[Dim Ticket].[UPDATE TIMESTAMP].[UPDATE TIMESTAMP]}
, [Measures].[Bucket] = 1 )) >0,
sum(Filter(EXISTING {[Dim Ticket].[TICKET ID].[TICKET ID]*[Dim Ticket].[UPDATE TIMESTAMP].[UPDATE TIMESTAMP]}
, [Measures].[Bucket] = 1),[Measures].[Diff Time]),
null),
 
VISIBLE = 1  ;   




CREATE MEMBER CURRENTCUBE.[Measures].[Sum Bucket 2]
 AS 
iif(Count(Filter(EXISTING {[Dim Ticket].[TICKET ID].[TICKET ID]*[Dim Ticket].[UPDATE TIMESTAMP].[UPDATE TIMESTAMP]}
, [Measures].[Bucket] = 2 )) >0,
sum(Filter(EXISTING {[Dim Ticket].[TICKET ID].[TICKET ID]*[Dim Ticket].[UPDATE TIMESTAMP].[UPDATE TIMESTAMP]}
, [Measures].[Bucket] = 2),[Measures].[Diff Time]),
null), 
VISIBLE = 1  ;

 

 

We are done with calculations and we are able to see results.

For example, the list of all tickets and the value of the [Diff Time] measure:

 

And then we can filter for the BUCKET attribute of the Ticket Dimension to get just the tickets of the bucket:

 

Even without dropping the [TICKET ID] attribute nor [UPDATE TIMESTAMP] attribute to rows or columns, we get same results:

In conclusion, this solution could be an approach or idea to face this problem without using Integration Services and calculating everything with MDX. It should be tested on a production enviroment to check if every behavior of the calculations are correct, even in other clients like Microsoft Excel.

 

You can find all the MDX functions in detail here: ​http://msdn.microsoft.com/en-us/library/ms145970.aspx