Report Portal

Using a Custom Assembly to search an Analysis Services dimension

Written by Jens Vestergaard on . Posted in Management

Reposted from Jens Vestergaard's blog with the author's permission.

Problem

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.

Solution

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.

Configuration

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.

Execution

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:

Code Snippet
  1. WITH
  2. SET [Results] AS
  3. STRTOSET(
  4. CustomSearch.Find(
  5. ‘Customer’
  6. , ‘Customer’
  7. , ‘#LASTNAME#=Car|#FIRSTNAME#=Rob’
  8. )
  9. )
  10. SELECT
  11. {} ON COLUMNS
  12. , {[Results]} ON ROWS
  13. FROM [Adventure Works]

The resulting T-SQL, based on the definition in the config file, looks like this:

Code Snippet
  1. SELECT
  2. DISTINCT c.[CustomerKey]
  3. FROM [AdventureWorksDW].[dbo].[DimCustomer] c
  4. INNER JOIN [AdventureWorksDW].[dbo].[DimGeography] g ON c.GeographyKey = g.GeographyKey
  5. WHERE
  6. c.FirstName LIKE ‘%#Rob#%’
  7. OR
  8. c.LastName LIKE ‘%#Car#%’
  9. OR
  10. g.PostalCode LIKE ‘%%’
  11. OR
  12. c.BirthDate LIKE ‘%%’

The method in the assembly then uses the c.[CustomerKey] to build the MDX member.
If the result is empty, and empty set ‘{}‘ is returned.

Flexibility

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.
A side effect that I first spotted during the making of this post, is that often there is no attribute in SSAS for both first name and last name. They would be somehow concatenated into a name attribute or similar. With this solution, we are actually allowed, due to the way the datamart is constructed, to seperate the search for ‘Rob’ in the first name and ‘Car’ in the last name. When done with MDX, we would be flooded by members that contained ‘Rob’ in the last name and ‘Car’ in the first name.

Performance

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.

Resources:

VS 2010 Project + MDX Query: Custom Assembly Search Project

Jens Vestergaard  

Jens has been working in the IT Industry since mid 90s. He started focusing in on the SQL Server Stack around 2003, mainly Analysis Services and the RDBMS. His blog is at http://www.t-sql.dk and he currently works for http://www.rehfeld.dk as a senior BI consultant.

Tags: management

Similar Articles

 

2007-2015 VidasSoft Systems Inc.