|Using a Custom Assembly to search an Analysis Services dimension|
|Written by Jens Vestergaard|
|Wednesday, 20 June 2012 22:20|
Reposted from Jens Vestergaard's blog with the author's permission.
A client of ours had the business requirement that they wanted to be able to search for members in certain dimensions in their cube. In particular two specific dimensions, patients and health care persons (doctors, dentists, nurses etc.). And they wanted to be able to search by first name, last name, age, social security number and other attributes.
Now, in Analysis Services (SSAS) this can be done by means of MDX, but it’s not performing very well, at least not when the number of members in the dimensions exceeds 10.000. Of course this is variable in respect to hardware, but the main point is that SSAS doesn’t scale very well in this matter.
The solution was to create a custom assembly that was able to utilize the fact that the dimensions are build on a table from at datamart. In short, we query the underlying datamart rather than the cube itself. This of course requires a bit of configuration, since we wan’t to be able to use the assembly in more than one cube.
The method I made in this example has the flollowing signature:Find( string dimensionName, string attributeName, string searchValues );
The signature is chosen in order to control the output in accordance with the dimension/attribute we want to query on through MDX (not T-SQL). The method uses the two first parameters to build the member that is going to be part of the result, eg. “[<dimensionName>].[<attributeName>].&[<key_from_sql_statement>]“. One could argue to put this into the config file as well, and yes that could be done. The details of the configuration is not the point of this post, so I will leave it up to the individual situation to deal with that issue, and choose how this is done.
This really was the hardest part. I have a .Net background and took some things for granted when I tried to implement this assembly. First I thought that if I was to deploy the <Assembly Name>.dll.config, the assembly would be able to use the build in .Net Configuration Manager and read the configuration settings in the file. Even when following the advice of this post (although in SSRS) by setting the permission level, I didn’t succeed in getting the configuration values “the ordinary way”. The end result was a Null reference exception every time. The only way, then, is to manually load the config file as an XML document, and query it for the needed attributes. This is a bit of a hassle, and I had hoped the more direct and regular approach had worked out. If anyone has clues to make this work please let me know.
The really nice part about this solution, is that once the custom assembly is defined, you can fire MDX at will from SQL Server Management Studio (SSMS), Reporting Services (SSRS) or any other tool that allows you to modify the MDX. Exceptions here are Targit amongst others…
An example of an MDX statement that includes a call to a custom assembly:
The resulting T-SQL, based on the definition in the config file, looks like this:
The method in the assembly then uses the c.[CustomerKey] to build the MDX member.
Certainly there are more ways to use this construct than the way I’ve showed here, but the part I really like about it, is that I am able to do my own T-SQL and do my own MDX on top of the result. There is no hidden part that does some magic trick, thats left entirely to the magician behind the keys.
This is probably the most compelling reason for doing this. I have no hard numbers at the moment, but it shouldn’t be too hard to figure out, that when the number of members climb, the query time including an MDX InStr( …. ) comparison sky rockets as well. The Analysis engine can in no way beat SQL Engine on its home turf.
VS 2010 Project + MDX Query: Custom Assembly Search Project