Share via

Using Custom assembly in SSAS

  • Article

Sometimes default function and features of SSAS are not sufficient to fulfil the requirement. "Therefore, Analysis Services lets you add assemblies to an Analysis Services instance or database. Assemblies let you create external, user-defined functions using any common language runtime (CLR) language, such as Microsoft Visual Basic .NET or Microsoft Visual C#. You can also use Component Object Model (COM) Automation languages such as Microsoft Visual Basic or Microsoft Visual C++." [MSDN]

Following is an example where we have used an assembly to create a custom drillthrough.

Requirement

Requirement is to have huge text column (BLOB) to be displayed when doing a drillthrough in SSAS using excel as tool.

Problem statement

  1. By default column which included into drillthrough needs to be present in cube. If we don’t have columns in cube we can not see then in drill through.
  2. Having huge text column into the cube will increase the cube storage as well will degrade the performance of cube. Also it will increase cube processing time.

 If we keep the required drillthrough columns in cube, our cube performance will be impacted, if don’t keep it we wont be able to drilldown on it usnign default drillthrough functionality.

Solution

Using an assembly

We come up with a solution where we will not keep the required BLOB columns into the cube, but will use an assembly to query background SQL DB and send a result back into excel.

  

  • A dot net based assembly having function to which will call database stored procedure or T SQL. This function will take MDX syntax based parameter as input.  

  • To make a call to this assembly use action tab of cube designer and use following kind of syntax –

Call MyAssembly.MyClass.MyFunction(a, b, c)

http://2.bp.blogspot.com/-DZIkcIeg4mc/VadI2r0Tt9I/AAAAAAAAHGA/30WkKMAd6VM/s320/SSAS.PNG

  • This function can internally (within assembly) call multiple functions.
  • Function will have a connection string to SQL DB. 
  • In Action tab select the return type as dataset. This will allow you to display return result in a new excel sheet.
  • This assembly and its function can be used in multiple ways, instead of returning a result set it can also return a web page URL or SSRS report URL. 

I have used assembly created by "https://asstoredprocedures.codeplex.com/" and used it as basic template. i modified it for my purpose and use.

Deploy

This assembly can be deployed using SQL Server Management Studio.

SSMS -> Expand Assemblies folder -> Right click -> Click on New Assembly -> it opens Register Server Assembly window -> choose type and through File Name add your assembly

Security

Whenever we use custom assembly we also faces security challenges.

By default SSAS provide a security options as follows [MSDN]:

                  

Permission Setting Description
Safe Provides internal computation permission. This permission bucket does not assign permissions to access any of the protected resources in the .NET Framework. This is the default permission bucket for an assembly if none is specified with the PermissionSet property.
ExternalAccess Provides the same access as the Safe setting, with the additional ability to access external system resources. This permission bucket does not offer security guarantees (although it is possible to secure this scenario), but it does give reliability guarantees.
Unsafe Provides no restrictions. No security or reliability guarantees can be made for managed code running under this permission set. Any permission, even a custom permission included by the administrator, is granted to code running at this level of trust.

**Conclusion **

Using custom assembly is very easy. It lets you customized your cube/MDX in multiple ways. 

**Reference **
My blog http://sqlservermsbiblog.blogspot.com.au/2015/07/uing-custom-assembly-in-ssas.html