Multiselect friendly MDX calculations (By Mosha Pasumansky)

One very common problem with authoring MDX calculations historically has been the behavior of these calculations when the user who browses the cube selects multiple members in the slicer as opposed to single one. Of course this functionality (usually called multiselect) is present in all of the major AS client tools - Excel, OWC, Panorama, Proclarity, various Excel Add-Ins, Data Analyzer etc. It often happens that MDX calculations which work fine in single member slice mode don't work that well when multiselect is used, or do work with multiselect by some dimensions, but not by others. The problems manifest themselves either as cell errors, or, worse, as wrong (i.e. non-intended) results. This problem is common, yet, to the best of my knowledge, it has not been addressed before. So calculation authors are left struggling to resolve it, and usual resolution is to ignore the problem, and hope that users won't do multiselect over offending dimensions. In this article I will try to address some of the typical scenarios where this problem can occur and recommend possible approaches for solution. I will use Foodmart 2000 database for examples, but most of the content applies to Analysis Services 2005 only.

Read more...