Uing Custom assembly in SSAS

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++.

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)

• 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 -

                  

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. 

References  

https://asstoredprocedures.codeplex.com/

MDX Introduction

MDX: Well you must have heard of it. So like you need T SQL or SQL to communicate with a RDBS, same way you need MDX to communicate with Multi-Dimensional OLAP (MOLAP).

MDX is Multi-Dimensional Expression. .  It is part of the OLEDB for OLAP specification which was developed by Microsoft but is now supported by multiple vendors.

Main purpose of MDX is to create query statements for data retrieval. We can also perform some of the management functions, such as the UPDATE CUBE statement etc.

It is good if you have knowledge of SQL as that will help in learning MDX.

MDX seems to be tough, but believe me once you have an understanding of it, it will not be complex and you will enjoy writing it.

Let see how MDX statement looks like –

SELECT, FROM and WHERE clause you have seen in SQL as well, so they are self-explanatory.

 SELECT is to select , FROM is to from where do you want to select. In MDX it will a cube or a sub query. And WHERE to filter your selection that we call it SLICING.

In addition to it, here you define Axis. Well its Multi-Dimensional, isn’t it??  So need different dimensional Axis here.

Let see an example here -

 I consider you have basic knowledge of Facts and Dimensions and you have Adventure Works DB & Cube installed.

SELECT {[Measures].[Internet Sales Amount]} ON COLUMNS,

[Date].[Calendar Year].&[2009] on 1

FROM [Adventure Works]

Well its very simple SELECT statement, where we have selected measure Sales Amount with respect to Calder Year 2007 of date dimension.

Result will be as follows –

So writing first statement was not that tough??

You can put different member of different dimension and can try by your Self.

In the Next blog I will go more detail about MDX and will discuss some of the function of MDX.