VBA functions in MDX and DAX

It turns out that the Visual Basic for Applications library functions are a great way to extend the functionality of your MDX statements, allowing you to do some very handy stuff.

Analysis Services uses stored procedure technology to extend the number of built-in MDX functions. It automatically registers two libraries: the Visual Basic for Applications library and the Microsoft Excel worksheet library. Visual Basic for Applications library is registered as a CLR assembly and Excel library – as COM assembly. MDX expressions can use any of supported VBA and Excel functions, as if there were native MDX functions.

However, it comes with a cost of performance,  CLR assemblies are hosted in a separate application domain and in addition to marshaling from native to managed code and back, all the calls are also marshaled across application domains, therefore calling a VBA function is an expensive operation comparing to calling an MDX function.

Here is a list of all the VBA functions available in Microsoft Visual Basic for Applications Language Reference that are supported in MDX; also, the list includes a note when there is functional equivalence with the DAX language.

MDX last day of week with tuples

So, working on a project with the TFS cube, a business requirement needed to retrieve the Last Day of the Week.

Now, in the TFS cube, there is a Date Dimension with two hierarchies already created out of the box.

The problem started when I was trying to use the LastChild function to get the last day of the week from the [Year – Week – Date] hierarchy. This function expects a Member of the hierarchy, and I needed to pass a tuple (or a set of members).

After doing some research and ask a couple of senior colleagues, we found the answer:

[Date].[Year - Week - Date Hierarchy].[Week]
[Date].[Year - Week - Date Hierarchy].CURRENTMEMBER

By using the Generate function we work with each member of a set separately, and then the function  joins the resulting sets by union. The Tail function is similar to LastChild, but Tail returns a subset from the end of a set and can be used to return the last member of the dimension. The Descendants function is needed to return the set of descendants of a member at a specified level or distance to memory.