Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Power and log functions

  1. #1
    Join Date
    Oct 2007
    Posts
    6

    Default 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. #2

    Default

    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. #3
    Join Date
    Oct 2007
    Posts
    6

    Default

    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.

    Thanks for your help.

    Regards

    Shane

  4. #4
    Join Date
    Oct 2007
    Posts
    6

    Default

    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
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.