MDX and Logarithm
I am using Analysis Services 2005 and I need to create a new measure “y”,
for which I have to take the logarithm of another calculated measure "x".
i.e. y= ln (x) (where x is another calculated measure within the cube).
In AS 2000, there is the function LOG available, and in AS 2005, it somehowrecognizesthe function but it returns an error.
Why is the LOG not working? Is there something I am doing wrong? What about LN?
I searched the web and was not able to find any info about this topic.
Does anyone know how to do this?
Thanks
Please write [LOG] instead of LOG in your query.
This is necessary because LOG is a reserved word in Yukon, so it has to be escaped when used to designate the logarithm function.
Hope this helps
I ended up writing my own assembly to reference the .net system math type to do the dirty work. There may be a way to reference the functions in the VBMDX an EXCELMDX assemblies, but I haven't had any luck yet.
To use this, create a C# Class Library project and drop in the code below. There are more examples in the sample application AMOAdventureWorks.
Once you compile the assembly, add it to your Analysis services project as an assembly reference and deploy it to the server. Then you'll be able to use it in MDX queries directly. The syntax requires the full namespace path:
select Statistics.Statistics.Statistics.Log10( [Measures].[Parameter Value])
on columns from [samples]
//assembly code starts here...
using System;
using Microsoft.AnalysisServices;
using Microsoft.AnalysisServices.AdomdServer;
namespace Statistics
{
public sealed class Statistics
{
private Statistics()
{
}
// create a wrapper for math functions not available in SAS
public static double Log10( double nValue)
{
return Math.Log10(nValue);
}
}
}
I recall all sorts of problems using the VBA and Excel functions in AS2000. I think the final resolution was something along the lines of "Install Excel First - Then install AS", becuase then it picks up the references OK.
I don't know about 2005, but it might be a similar issue.
Hope this helps,
Richard.
