Hi All.

I would develop a  'COM DLL'  stored procedure.

But I can't find any useful informations about that.

It's strange. There is not guide lines or samples.

I have a performance issue and my intent is to compare COM and .NET implementation

of same procedure.

Thanks in advance

Valter Paiotti

First, COM DLLs have been deprecated, so they will be removed in a future release beyond AS2012. So I wouldn't invest any time in them:
http://technet.microsoft.com/en-us/library/ms143346.aspx

I'm not too familiar with COM DLLs. I could be completely wrong, but I think any DLL with a public class and public function should work. I'm not sure if there are any APIs like there are with .NET assemblies. If you find a scenario which performs differently, do post back as that would be interesting to know.

Ok furmangg.

Your hint was reasonable.

I am able to say something about these type of stored procedures

I searched in old web entries regarding Analysis Services 2005

COM Dlls are COM objects that act as Visual Basic functions.

I created a (by Visual Studio 10) ATL (C++) library with just one COM class ( with dual interface to be like a VB class) with a simple method "Echo",
because I want to estimate the technology cost

...
STDMETHODIMP CCalc::Echo(DOUBLE value, DOUBLE* retValue)
{
 *retValue = value;
 return S_OK;
}
...

I added my COM library ( named ASCOMSP ) to Assemblies section of Analysis Services ( I have a 32bit AS2008R2 version installed on my laptop - Core2Duo + 4GB RAM)

By default COM Dlls are disabled. In Analysis Services Properties it needs to turn on 'Feature\ComUdfEnabled'.

I extended the ASSP project in order to host a 'clone' method.

public static double Echo(double value)
{
    return value;
}

And finally I compared the fastness of two technologies running the following queries

//MDX
with
member y as sum([Customer].[Customer Geography].MEMBERS, 1)
select Measures.y on 0
from [Adventure Works]

//COM
with
member y as sum([Customer].[Customer Geography].MEMBERS, ASCOMSP.Echo(1))
select Measures.y on 0
from [Adventure Works]

//.NET
with
member y as sum([Customer].[Customer Geography].MEMBERS, ASSP.Echo(1))
select Measures.y on 0
from [Adventure Works]

In my Adventure cube there are 19804 customers.
Durations:
MDX  -> 34(ms)
COM  -> 171(ms)
.NET -> 614(ms)

In another cube with a dimension with 100000 members, I obtained the following results
Durations:
MDX  -> 71(ms)
COM  -> 827(ms)
.NET -> 3065(ms)

The cost of marshaling is evident.
Of course in COM we haven't the richness of AdomdServer namespace.
I think that people can adopt it only for specific purpose.
For example my target was to use a stored procedure in weight expression of UPDATE CUBE statement in order to drive
the allocation on leaves, faster as possible
Maybe people can use it to create an 'ad hoc cache' of some complex calculation...

Valter Paiotti

Those results are really interesting! Thanks for posting them. It does appear that on that simple sproc, COM is 3.5x faster. Of course, implementing our logic in MDX may be the most performant, especially if you manage to use functions which keep the calculation in block computation mode. I would definitely focus on native MDX first. You might post on the forums if you struggle to express your calculation in performant MDX.

One other suggestion I've heard is to use C++ for all your calculation logic. For now you can compile it into a COM DLL, but when support for COM DLLs is finally removed, then you could compile it into a .NET DLL with Managed C++. Or maybe a wrapper DLL of each flavor would do the trick instead.