US and Worldwide: +1 (866) 660-7555
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Create an UDF in Pentaho Workbench Schema

  1. #1
    Join Date
    Jan 2013
    Posts
    28

    Default Create an UDF in Pentaho Workbench Schema

    Hi everyboy,

    I want to create un UDF in my Schema but i have an error...
    Code:
    Mondrian Error : No function matches signature 'sivtyp(<Numeric Expression>,<Numeric Expression>)'
    Of course , I want to create a function that takes two numeric parameters. I am a novice in Javascript, there is my code :
    Code:
    function getParameterTypes() {
    	return new Array(new mondrian.olap.type.NumericType());
    }
    function getReturnType(parameterTypes) {
    	return new mondrian.olap.type.NumericType();
    }
    function execute(evaluator, arguments) {
    	var n = arguments[0].evaluateScalar(evaluator);
    	var m  = arguments[1].evaluateScalar(evaluator);
    	return sivtyp(n,m);
    }
    function sivtyp(n,m) {
    	return m *n
    }
    Is my code is correct ? Where is the problem ?

    Thank you in advance

  2. #2
    Join Date
    Jan 2013
    Posts
    510

    Default

    Your getParameterTypes() function only returns a single type. Since you have 2 numeric parameters you'll need something like:

    return new Array( new mondrian.olap.type.NumericType(), new mondrian.olap.type.NumericType())

  3. #3
    Join Date
    Jan 2013
    Posts
    28

    Default

    Ok very good thank you for you reply, it's work !

    But I have another question, How can I declare my function getParameterTypes() is I want to use a Level in parameters ? a Member ?

    return new Array( new mondrian.olap.type.NumericType(), new mondrian.olap.type.LevelType()) ? I have an error ...

    In my function I multiply the first parameter with the second, is it possible to do this with a Numeric parameter and with a Level parameter ? The values of Level parameter could be only -1 or 1 ...




  4. #4
    Join Date
    Jan 2013
    Posts
    28

    Default

    this is my new function :
    function getParameterTypes() {
    return new Array(new mondrian.olap.type.NumericType(),new mondrian.olap.type.SetType(null));
    }

    function getReturnType(parameterTypes) {
    return new mondrian.olap.type.NumericType();
    }
    function execute(evaluator, arguments) {
    var n = arguments[0].evaluateScalar(evaluator);
    var m = arguments[1].evaluateScalar(evaluator);
    return factorial(n,m);
    }
    function factorial(n,m) {
    return n*m
    }
    This is my calculated member CA_mnt:
    factorial([Measures].[CA],{[Lignes.hLignes].[SNS_0].Members})
    This is the résult :


    And this is my dimension :


    Can someone help me to find the solution ?

  5. #5
    Join Date
    Jan 2013
    Posts
    510

    Default

    Your example (factorial([Measures].[CA],{[Lignes.hLignes].[SNS_0].Members})) has a measure as the first parameter, and a set as the second parameter. Rather than having a NumericType specified as the first param you should use MemberType. E.g.

    new mondrian.olap.type.MemberType(null,null,null,null)

    What is the intention of the second parameter? Are you trying to multiply the first param by each of the members of the set?









  6. #6
    Join Date
    Jan 2013
    Posts
    28

    Default

    I try to multiply the first parameter with the second.
    The first parameter is not a problem,for exemple if its value is 100 , I try :
    factorial([Measures].[CA],-1)
    The result is OK (-100) with
    function getParameterTypes() {
    return new Array(new mondrian.olap.type.NumericType(),new mondrian.olap.type.NumericType());
    }
    But I want to multiply a measure with a dimension's value ([Lignes.hLignes].[SNS_0].CurrentMember).
    What will be the declaration of the second parameter's type ?
    How can I get the value of the dimension ?


    Last edited by OMDCAPSYS; 09-27-2013 at 08:43 AM.

  7. #7
    Join Date
    Jan 2013
    Posts
    510

    Default

    If your second parameter is a member, use

    new mondrian.olap.type.MemberType(null,null,null,null)


    Before it had looked like you were using a set.

  8. #8
    Join Date
    Jan 2013
    Posts
    28

    Default

    I tried to use : new mondrian.olap.type.MemberType(null,null,null,null)

    There is no error in publication but when I use the new calculated member It seems the value is multiplied by itself
    if the value is 100 I obtain 10000 instead of -100 if SNS_0 is -1 or 100 if SNS is 1...



  9. #9
    Join Date
    Jan 2013
    Posts
    510

    Default

    Can you post your complete function? Also, if possible test against FoodMart- it will be easier to discuss if we're using the same model.

    Here's one that multiplies a numeric value by the scalar evaluation of the member. Below it are the results of a test against Foodmart.

    function getParameterTypes() {
    return new Array(new mondrian.olap.type.NumericType(),
    new mondrian.olap.type.MemberType(null,null,null,null));
    }


    function getReturnType(parameterTypes) {
    return new mondrian.olap.type.NumericType();
    }
    function execute(evaluator, arguments) {
    var n = arguments[0].evaluateScalar(evaluator);
    var m = arguments[1].evaluateScalar(evaluator);
    return timesX(n,m);
    }
    function timesX(n,m) {
    return n*m
    }


    ----------------------------

    > select gender.gender.members on 0 from sales;
    Axis #0:
    {}
    Axis #1:
    {[Gender].[F]}
    {[Gender].[M]}
    Row #0: 131,558
    Row #0: 135,215
    > with member measures.x2 as 'timesX(2, gender.f)'
    ? select measures.x2 on 0 from sales;
    Axis #0:
    {}
    Axis #1:
    {[Measures].[x2]}
    Row #0: 263,116
    >

  10. #10
    Join Date
    Jan 2013
    Posts
    28

    Default

    unfortunatly I dont have the footmart modele. Here is mycomplete function :
    function getParameterTypes() {
    return new Array(new mondrian.olap.type.NumericType(),
    new mondrian.olap.type.MemberType(null,null,null,null));
    }
    function getReturnType(parameterTypes) {
    return new mondrian.olap.type.NumericType();
    }
    function execute(evaluator, arguments) {
    var n = arguments[0].evaluateScalar(evaluator);
    var m = arguments[1].evaluateScalar(evaluator);
    return sivtyp(n,m);
    }
    function sivtyp(n,m) {
    return n*m
    }
    Also, I use Pentaho Schema Workbench in order to build my cube and Saiku Analytic for the result...

    Here you can see the calculated member that uses the UDF :


    And here the result :


    "SNS_0" determine if amount is positif or negatif
    "Chiffre d'affaire" is the amount , always positif in the database
    "CA_mnt" is my calculated member that uses sivtyp() (UDF)

    Normaly I should have -1232 for the first line and 16 166 629,98 for the second
    but we can see that it multiply the value by itself instead of -1 or 1

    Also I want to precise that if I use 2 dimensions (one in row and the other in column), it uses by default CA measure (Chiffre d'affaire), as you can see here :



    Maybe the multiplication is because of that ?
    but why it doesn't multiply the Measures by SNS_0's value ?

Posting Permissions

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