MDX / R Integration

icCube natively integrates R, which is accessible directly from an MDX query. This integration is based on Renjin, a JVM-based interpreter enabling the integration of R in Java solutions (thanks BeDataDriven!).

Configuration

By default R support is turned off. It can be activated via the user defined property _ r.active _ in the file icCube.xml.

Usage

Accessing R can be done via MDX native methods. For example the following is creating a normal distribution:

        WITH NATIVE FUNCTION fun(avg,std) AS
        /* R

           rnorm( 5, mean=avg, sd=std )

        */
        SELECT ...
    

Calling only an R function can be using the notation R!method( ... ) that allows for accessing any R methods from an MDX statement. Note that the conversion of the parameters (as well as the result) is handled automatically by the MDX engine. The following calculated member is computing the natural logarithm of a measure :

WITH MEMBER [Measures].[val] AS R!log( [Measures].[Amount] )

R constants can be used accessed the same way. E.g., the following calculated measure is defined as TRUE:

WITH MEMBER [Measures].[val] AS R!is.na( R -> NA )

Note that the sequence operator (e.g., from:to) is not yet supported when passing parameters to R functions. As a workaround, the function seq(from,to) which is equivalent can be used.

Examples

Random values

We can also use R to generate a random vector of normal distributed values. Pay attention, we use the const flag to ensure the vector of random values is the same for each request.

WITH
  /* creates the vector of n normal distributed values */
  CONST FUNCTION randomVector() as R!rnorm(500, mean=0.5, sd=0.25)

  /* quick check that is the same 'random' vector at request level */
  MEMBER [var 0.5%]      as R!quantile(randomVector(), 0.05 )
  MEMBER [median (Java)] as randomVector()->median()
  MEMBER [median (R) ]   as R!quantile(randomVector(), 0.5 )

  /* an example to calculate the histogram, us p so the sum is 1.0 */
  MEMBER [p-(,0)]        as randomVector()->phist(NULL,0)
  MEMBER [p-(0,0.25)]    as randomVector()->phist(0,0.25)
  MEMBER [p-(0.25,0.5)]  as randomVector()->phist(0.25,0.5)
  MEMBER [p-(0.5,0.75)]  as randomVector()->phist(0.5,0.75)
  MEMBER [p-(0.75,1.00)] as randomVector()->phist(0.75,1.0)
  MEMBER [p-(1.0,)]      as randomVector()->phist(1,NULL)
SELECT
    [Measures].allmembers - [Measures].members on 0
FROM [MyCube]
    

Installation

To install Renjin you have to download the standalone Java library jar file from here (the latest version we tested was 0.8.2357). Once downloaded, copy the jar file to icCube plugin directory: ${ic3-data}/plugins/.

Configuration

Activation

The R / MDX integration can be (de)activated using the property r.active within the file $install/bin/icCube.xml.

Multithreading

R integration is currently not multithreaded (contact us if it's needed). Only one and only one R function will be evaluated at a given point in time.

Support

Renjin is not part of icCube, and icCube does not make any guarantee other than on the integration code. For a production integration please test carefully.

License

The integration is based on Renjin that is licenced under a GPLv2 license. This license has some limitations mainly when integrating into a product (wikipedia), check the compatibility with your solution before starting the integration.