Javascript Code (ETL) - Cached Tables

Multiple tables can be used in a Javascript view for more complex and powerful transformations. Those additional tables are cached and might be accessed during the row processing code. The main table might be cached, in this case 'Row Processing Code' will not be called and the generation of rows has to be performed in 'Completion Code'.

How is this working

Cached table can be handled through three interfaces provided by icCube:

        IOlapBuilderCacheTableWrapper   : represents the whole table
        IOlapBuilderCacheRowWrapper     : represents a row
        IOlapBuilderCacheRowListWrapper : represents a list of rows
    

Few examples:

        - table("My Table").numberOfRows()        : returns the number of rows for the table "My Table".
        - table("My Table").row(3).get("Country") : returns the value for "Country" colum on row number 3 (0-index)
        - stream("My Table").count()              : counts the number of rows as the first example.
    

A nice introduction on Java 8 stream can be found on Benjamin Winterberg's blog here.

Join Example

Following is an example of a join using the data from two tables.

Init. Code :

var t_sales = table("Sales Details");

Row Processing Code :

// find a row in country with matching current country
var matchingRow = t_country.firstMatching("Country", get("Country") );
// get the continent from the 'Countries' table
var continent = matchingRow.get("Continent");
// set the new row value;
set("CONTINENT", continent );
// fire current row
fire();
    

Group By Example

Following is an example of a group by using the data from two tables.

Init. Code :

var t_country = table("Countries");

Row Processing Code :

// like select city,currency, sum(amount) .. group by city,currency
var grouped = t_sales.groupBy(["city","currency"],["amount"], ["SUM"] );
// fire the table
fireTable(grouped);
    

FX Example

Following is an example of FX

Init. Code :

    var t_fx = table("Fx");

Row Processing Code :

// currency of sales table
var curr = get("currency");

var fx;
if ( curr === "CHF" )
{
    // if already CHF nothing to convert
	fx = 1.0;
}
else {
    // find in fx table the rate
	var matchingRow = t_fx.firstMatching( ["year","currency"], [get("year"),get("currency")] );
    // get the fx
    fx = matchingRow.get("fx2ch") ;
}
// set the amount in CHF
set("Amount_CHF", get("amount") * fx );
// fire current row
fire();

IOlapBuilderCacheTableWrapper

The interface returned by table("My Table") javascript function.

/**
 * Represents a cached table as seen in the javascript code
 */
interface IOlapBuilderCacheTableWrapper
{
    /**
     * @return the row at rowNumber, exception if rowNumber is out of bounds
     */
    OlapBuilderCacheRowWrapper row(int rowNumber);

    /**
     * @return the number of row
     */
    int count();

    /**
     * @return the smallest value on columnName
     */
    @Nullable
    Object min(String columnName);

    /**
     * @return the biggest value on columnName
     */
    @Nullable
    Object max(String columnName);

    /**
     * @return the sum of all values on columnName
     */
    @Nullable
    Object sum(String columnName);

    /**
     * @return the count of all non null values on columnName
     */
    int countNotNull(String columnName);

    /**
     * @return a array with the distinct values for columnName
     */
    Object[] distinct(String columnName);

    /**
     * @return a array of distinct values for columnNames, for values are again an array of values for each column
     */
    Object[][] distinct(String[] columnNames);

    /**
     * @return the aggregation of all columns using AggregationType
     * @See
     */
    Object aggregate(String columnName, String aggregationType);

    /**
     * @return a list with the names of all columns
     */
    @Nullable
    ArrayList<String> columns();

    /**
     * @return a Java8 stream of OlapBuilderCacheRowWrapper
     */
    Stream<OlapBuilderCacheRowWrapper> stream();

    /**
     * @return
     */
    IOlapBuilderCacheTableWrapper groupBy(String[] groupByColumns, String[] aggregationColumns, String[] aggregationTypes);

    /**
     * @return a IOlapBuilderCacheRowListWrapper representing the filtered table
     */
    IOlapBuilderCacheRowListWrapper filter(String columnName, Object value);

    /**
     * @return a IOlapBuilderCacheRowListWrapper representing the filtered table
     */
    IOlapBuilderCacheRowListWrapper filter(String[] columnName, Object[] values);

    /**
     * @return the first row matching value on columnName, MissingRow if none
     */
    IOlapBuilderCacheRowWrapper firstMatching(String columnName, Object value);

    /**
     * @return the first row matching values on columnNames, MissingRow if none
     */
    IOlapBuilderCacheRowWrapper firstMatching(String[] columnName, Object[] values);

    /**
     * @return the value on columName at rowId
     */
    Comparable get(int rowId, String columnName);

    /**
     * @return set newValue on columName at rowId
     */
    void set(int rowId, String columnName, Comparable newValue);

    /**
     * @return replaces on columnName all values equal to lookupValue by newValue
     */
    void replace(String columnName,@Nullable Comparable lookupValue,@Nullable Comparable newValue);

    /**
     * @return fill null values on columName with a previous row no null value on the same column
     */
    void fillMissingPrev(String columnName);

    /**
     * @return fill null values on columName with a next row no null value on the same column
     */
    void fillMissingNext(String columnName);

    /**
     * Sorts the Table on columnName (null values and bigger first)
     */
    void sortAsc(String... columnName);

    /**
     * Sorts the Table on columnName (smaller first , null values last)
     */
    void sortDesc(String... columnName);
}
    

AggregationType corresponds to the aggregation without rollup available for measures (SUM, MIN, MAX, AVG_LEAVES, COUNT,DISTINCT_COUNT, SUM_SQUARE, VECTOR, VECTOR_SORTED).

IOlapBuilderCacheRowWrapper

The interface representing a row of the table.

/**
 * Represents a row in a cached table as seen in the javascript code
 *
 * MissingRow is a row without actual representation on the table, it's returned
 * on missing searchs.
 */
interface IOlapBuilderCacheRowWrapper
{
    /**
     * @return the value of the cell defined by 'columnName' ,always null for an MissingRow
     */
    Comparable get(String columnName);

    /**
     * Sets newValue in the cell defined by 'columnName' on the current row
     */
    void set(String columnName, Comparable newValue);

    /**
     * @return a previous row with a non null value on cell 'columnName' , MissingRow if not found
     */
    IOlapBuilderCacheRowWrapper prevRowNotNull(String columnName);

    /**
     * @return a next row with a non null value on cell 'columnName' , MissingRow if not found
     */
    IOlapBuilderCacheRowWrapper nextRowNotNull(String columnName);

    /**
     * @return previous table row, MissingRow for the first row
     */
    IOlapBuilderCacheRowWrapper prevRow();

    /**
     * @return next table row, MissingRow for the last row
     */
    IOlapBuilderCacheRowWrapper nextRow();

    /**
     * @return the row at position 'rowNumber' in the table
     */
    IOlapBuilderCacheRowWrapper row(int rowNumber);

    /**
     * @return the row position in the table
     */
    int index();

    /**
     * @return return the underlying table
     */
    IOlapBuilderCacheTableWrapper table();

    /**
     * @return true, if it's a MissingRow
     */
    boolean isMissing();
}
    

IOlapBuilderCacheRowListWrapper

The interface representing a list of table rows.

/**
 * Represents a list of rows table as seen in the javascript code
 */
interface IOlapBuilderCacheRowListWrapper
{
    /**
     * @return the number of row
     */
    int count();

    /**
     * @return the smallest value on columnName
     */
    @Nullable
    Object min(String columnName);

    /**
     * @return the biggest value on columnName
     */
    @Nullable
    Object max(String columnName);

    /**
     * @return the sum of all values on columnName
     */
    @Nullable
    Object sum(String columnName);

    /**
     * @return the count of all non null values on columnName
     */
    int countNotNull(String columnName);

    /**
     * @return the aggregation of all columns using AggregationType
     * @See
     */
    @Nullable
    public Object aggregate(String columnName, String aggregationType);

    /**
     * @return a Java8 stream of OlapBuilderCacheRowWrapper
     */
    Stream<OlapBuilderCacheRowWrapper> stream();

    /**
     * @return the underlying table
     */
    IOlapBuilderCacheTableWrapper table();
}
    

Walkthrough

The schema mentioned in this page can be downloaded here.

Next chapter: Real-Time Data Sources describes real-time datasources used to create cubes being updated with new data.