Finding lowest common ancestor in set
This thread on MSDN forums asks seemingly easy question – given a multiple selection of elements in the parent-child hierarchy – how to find lowest common ancestor among them ? While it is fairly easy to do using stored procedure, it turned out to be far from trivial using pure MDX. Here is my solution to this puzzle, it isn’t fully optimized, I cared more to keep it simple to understand instead.
Below is my solution, applied to the example of set
{ [Employee].[Employees].&[289] ,[Employee].[Employees].&[281] ,[Employee].[Employees].&[296] }