Academy

icCube now integrates native Java and R. This means that you can build Java and R scripts into your MDX statements. The advantage of having those two languages natively integrated into icCube is that you are able to expand your analytical capabilities (predictive analytics, time series, data mining algorithms, etc.) with two of the richest mathematical programming languages available today, and directly apply those methods on your production data, analysis and dashboards.

Find the detailed documentation here: Java integration and R integration.

In this article we’ll show the same example, once in R then in Java with icCube. The schema used is the “Sales” schema that is available when you download icCube’s trial version.

In order to build a Java or R function, you need to define functions as “natives” (not defined using MDX.) Native functions are declared by NATIVE keyword. Here is a simple R example of a weekly day count between two dates. The function will count all Mondays, Tuesdays, Wednesdays, Thursdays and Fridays, between the two dates evaluated.

 WITH
    NATIVE FUNCTION RweekdaysBetween(date1,date2) AS
        /* R
sum(!weekdays(seq(date1, date2, "days")) %in% c("Saturday", "Sunday"))
        */
     MEMBER days as RweekDaysBetween(
                        [Time].[Calendar].[Day].[1 Jan 2005].key,
                        [Time].[Calendar].[Day].[1 Jan 2006].key
                    )
SELECT
days on 0
FROM [Sales]

Let’s show this same example using Java.

In order to put the algorithms in production, just add your corresponding Java or R code into your schema’s Script. Remember you can create a function in the MDX IDE on an existing schema without the need to reload it (DROP and CREATE the functions in the MDX IDE.)

 create native function weekDaysBetween(d1, d2 /* exclusive */) as
/* JAVA
	import org.joda.time.*;
    final int WEEK_START = DateTimeConstants.MONDAY;
	final LocalDate start = toLocalDate( toWeekDay( toLocalDate( d1)));
    final LocalDate end   = toLocalDate( toWeekDay( toLocalDate( d2)));
    final int daysBetween = Days.daysBetween(start, end).getDays();
    final int weekendsBetween = Weeks.weeksBetween(start.withDayOfWeek(WEEK_START), end.withDayOfWeek(WEEK_START)).getWeeks();
    return daysBetween - (weekendsBetween * 2 /* DAYS_PER_WEEKEND */);
*/
create native function toWeekDay(d) as
/* JAVA
	import org.joda.time.*;
    final int WEEK_END = DateTimeConstants.FRIDAY;
    final LocalDate day = toLocalDate( d );
	if (day.getDayOfWeek() > WEEK_END)
    {
      return day.plusDays(DateTimeConstants.DAYS_PER_WEEK - day.getDayOfWeek() + 1);
    }
    return day;
*/

Then those functions are available for querying in icCube’s IDE.

WITH
MEMBER days as weekDaysBetween(
                        [Time].[Calendar].[Day].[1 Jan 2005].key,
                        [Time].[Calendar].[Day].[1 Jan 2006].key
                    )
SELECT
days on 0
FROM [Sales]

(btw, results should both be 260 🙂 )

What language should I use, Java or R ?

From a performance point of view, Java is faster, and potentially quite a lot, as you are using the same code icCube is running. Once the Java code is compiled, it’s as fast as icCube’s code. But you need to have a Java JDK in one of your servers (no need in production as the bytecode is saved in the schema definition). Remember you can add your own libraries, jar files, in icCube and they will be visible for your Java code.

From a functionality point of view, R has an amazing set of existing libraries that do not exist in Java. Please note that icCube is using Renjin that has not a 100% of all R libraries (check here).

From my side, this is just the first step to allow users making amazing maths in icCube, so stay tuned for what will be coming!

By Nathalie Leroy Tapia Heredia