# Thread: Power and log functions

1. Junior Member
Join Date
Oct 2007
Posts
6

## Power and log functions

Hi

I'm trying to create a measure that calculates the total probability of a given set of values. Since there is no product aggregate I first need to calculate the log of each probability and then apply the sum aggregate to these log values, followed by call to a power function.

I'm aware that power and log functions have not been implemented yet (?) so I need a workaround for this.

Since I'm new to mondrian (and OLAP) I need a little help to point me in the right direct.

Any help would be much appreciated.

Regards

shaarm

2. Member
Join Date
May 2007
Posts
77
Hi shaarm!
So, I'm not an expert in mondrian and olap, but what I think is this:

If I understood you right, you want to use the sum-aggregator and before aggregating a log-function (for each single measure-value in database) and a power-function for the displayed, aggregated result.

The last requirement is the easier, I think.
I could not find a power-function in mdx (mathematical, MDX is really poor), so a solution is to write your own as a user defined function. It's simple.
Take a look to this description: http://mondrian.pentaho.org/document...fined_function

Now create a measure in your cube with the sum-aggregator and a calculated measure like this:
Code:
```<CalculatedMember name ="probability" dimension="Measures">
<Formula>power([Measures].[your_summed_measure])</Formula>
<CalculatedMemberProperty name="FORMAT_STRING" value="#,##0.00"/>
</CalculatedMember>```
In which power is your user defined function and [Measures].[your_summed_measure] is your measure with summed log-values.

So, I think this should work for getting the power of a sum-aggregated measure. The last question is how to get log-values as input for the olap-system. One way - of course - is to do that calculation one system-level deeper and to write the log-values directly into the database. But I see, that solution is not very nice.
I think the solution is to find here: http://mondrian.pentaho.org/document...rchies,_Levels in subchapter 3.2.
It is possible to execute an sql-expression for getting the value from database. If sql provides a log-function (I hope so...), it should be easy to to do the log-calculation with sql, embedded in the schema file of your cube.

Sorry for my bad english. I hope I understood you right and you could understood me.
When you're a bit wiser, I would be nice to hear something about the solution.

Regards
Daniel

3. Junior Member
Join Date
Oct 2007
Posts
6
Hi Daniel

This is what I'm trying to do:

http://sqljunkies.com/WebLog/mosha/a...plication.aspx

I'll let you know how I get on.

Regards

Shane

4. Junior Member
Join Date
Oct 2007
Posts
6
Hi

I implemented the log10 function just by calling out to a corresponding log10 function in mySQL via a sql-expression as you suggested. This is then summed together in the measure. So this all works fine.

As you also suggested I will implement the power function as a user defined function. I decided to first try integrating the sample PlusOneUdf user defined function. I'm not a java developer and whilst I understand the PlusOneUdf class I'm having difficulty integrating this. I'm using the schema workbench to test my mdx queries.

I've saved the file as PlusOneUdf.java and then proceeded to compile this to a class file? I guess then I need to drop this into the workbench lib directory? However I can't compile the PlusOneUdf.java file as I keep getting two error messages. I have included the mondrian.jar file in the class path when using javac and this has reduced the error count from 14 down to 2!

I've searched the forum for some advice but alas I cannot find any with respect to creating a user defined function. Any suggestions as to what I should be doing would help greatly.

Thanks

Shane

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•