3

you can use R functions in icCube as demoed on https://www.iccube.com/support/documentation/mdx_integration/r_integration.php

I need to invoke a more complex custom R script that does a calculation on a matrix that I want to provide. The result should be a vector.

Example: - Matrix: projects with measures: total weeks, hours/week - Vector: (in same order as projects): start week

Is that possible, and how is the syntax to do this from MDX?

Arthur
  • 1,692
  • 10
  • 14

1 Answers1

1

Something like this, R_RandomgVector is generating a vector of random values of the size defined by the parameter. The member TEST is just returning the lenght of the returned vector. I guess v_ can be a matrix, transformed in the R function and return a vector as here

WITH
    NATIVE FUNCTION R_RandomVector(Value v_) AS
        /* R    

          round(runif(v_,0,1), 2)    
        */
    MEMBER TEST as R_RandomVector(100)->length()
SELECT
  TEST on 0
FROM [Sales]

and with a matrix

WITH
    NATIVE FUNCTION R_RandomVector(Value v_) AS
        /* R

          t( v_ %*% runif( nrow(v_) ,0,1) )

        */
    MEMBER TEST as R_RandomVector( Matrix([Product].[Product].[Article],[Product].[Product].[Article], [Measures].[Count])  )
SELECT
  TEST on 0
FROM [Sales]
ic3
  • 7,917
  • 14
  • 67
  • 115
  • Thanks, With your advice and hints I finally got it to work. I had some issues with binding the vector results to a real MDX statement, but I managed to do that in the following way: R function TEST .... MEMBER [Measures].[result] as TEST->value(rank(set, member)-1) SELECT {[Measures].[result]} on 0 , set on 1 FROM [cube] The only issue I have now, is that the function TEST is called upon for each member of the set. Is there a way to cache the result for TEST? – Arthur Oct 07 '17 at 12:53
  • Perhaps adding an intermediate calc. member (using the R function) that is going to be cached ? – Marc Polizzi Oct 09 '17 at 07:19