SSAS: Overriding Built-in functions

A few of us that worked on the Analysis Services Stored Procedure project were having a discussion on email the other day and the issue of trying to override some of the built-in functions with a different implementation came up. (specifically some of the Excel functions)

My first attempt at this was to register an assembly at the server level with a name of "Excel", thinking that when you called "Excel.Round(...)" it might call my assembly instead of the built-in one, but this did not work. Then I started looking at the other assemblies that are registered at the server level. If you look at these you will see that there is one called ExcelMDX. I suggested to the guys that it was probably a bad idea to touch this assembly and had it confirmed that this would definitely be a bad idea. Best case - Analysis Services would correct itself after your meddling. Worst case - you might have to re-install Analysis Services. So my suggestion is don't mess with it. :)

However, what one of the guys discovered was that if you create an assembly at the database level called ExcelMDX, then this assembly gets called whenever you make an "Excel." function call against a cube in that database.

So at the expense of maybe having to register the same assembly in each database on your server you have the ability to override some of the built-in assemblies (VBAMDX should work the same way)

This technique could be particularly helpful if you are using Report Builder against an SSAS cube, as if one of your users chooses to use Round() from report builder the report model translates that into Excel.Round() and if you don't have Excel on the server it will fail. If you do have Excel on the server it may take ages. Writing your own Round() equivalent will not perform as well as using a built-in function, but the anecdotal evidence suggests that it will be at least twice as fast as calling cross-process to Excel.

Print | posted on Wednesday, December 13, 2006 2:42 PM