Utility.Export.Cube to Table

The below code is used to export data from a Cube to a Table.

This is also the recommended way to make Cube data available to a system outside MODLR: export it to a Table in the Internal Datastore on a schedule, then have the external system query that Table over SQL - see Accessing MySQL Directly. Querying a database lets the external system filter and aggregate in the query itself and stream back only the rows it asked for, against the Datastore's own memory allocation, rather than having the engine resolve and serialise the whole dataset out of the model - see Reading MODLR data from another system.

TIP

By changing cube.slice to cube.sliceStatic, formula values will be ignored, and only cells with a static value will be sliced.

Process Code

/**
 * MODLR PROCESS SCRIPT: Cube -> MySQL Export
 * ------------------------------------------
 * Purpose:
 *   Prompts for a cube name, reads that cube's full dimension structure,
 *   mirrors it as a MySQL table (one column per dimension + a "value"
 *   column), and bulk-loads every cell in the cube into that table.
 *
 *   Re-running the process against the same cube truncates and reloads
 *   the table rather than dropping/recreating it, so downstream reports
 *   pointed at the table keep working.
 */

// --- Config -----------------------------------------------------------
const ds = "Internal Datastore";      // MODLR datasource name to write to
const schema = "cube_data";           // MySQL schema to hold exported cubes
const VARCHAR_LENGTH = 116;           // VARCHAR() length of the table fields

/**
 * pre()
 * Runs once before the process executes. Used only to register prompts
 * that MODLR will ask the user for at run time.
 */
function pre() {
    script.log('process pre-execution parameters parsed.');
    // Registers a text prompt named "cube_name" — its answer becomes the
    // global `cube_name` variable available in begin()/data()/end().
    script.prompt("cube name", "cube_name", "");
}

/**
 * begin()
 * Runs once at the start of the process. This is where the whole
 * cube -> MySQL export happens:
 *   1. Read the cube's dimension list.
 *   2. Ensure the target schema/table exist (or truncate the table).
 *   3. Stream every cell in the cube into the table via a batch insert.
 */
function begin() {
    script.log('process execution started.');

    // cube.dimensions() returns the cube's dimension metadata as a JSON string.
    let dimensions = JSON.parse(cube.dimensions(cube_name));

    let dimensionArray = [];   // column names for the batch insert, in order
    let sliceArray = [];       // one "" per dimension = "give me every member" for cube.slice()
    let dimensionObj = {};     // used only to describe columns to createTable()

    for (let i = 0; i < dimensions.length; i++) {
        let dimension = dimensions[i]; // 
        let dname = dimension.name.toLowerCase().replace(/\s+/g, '_');

        dimensionArray.push(dname);
        dimensionObj[dname] = "";

        // Empty string = no filter on this dimension, i.e. pull every member
        sliceArray.push("");

        if (script.IsCancelled()) {
            return;
        }
    }

    // The measure itself is exported as an extra "value" column
    dimensionArray.push("value");
    dimensionObj["value"] = "";

    // --- Ensure schema/table exist -------------------------------------
    if (!schemaExists(ds, schema)) {
        datasource.update(ds, `CREATE SCHEMA ${schema}`);
    }

    const tableName = cube_name.toLowerCase().replace(/\s+/g, '_');

    if (tableExists(ds, schema, tableName)) {
        // Table already matches this cube's shape from a prior run — wipe
        // and reload rather than dropping/recreating it.
        let truncSql = `TRUNCATE \`${schema}\`.\`${tableName}\``;
        datasource.update(ds, truncSql);
    } else {
        createTable(ds, schema, tableName, dimensionObj);
    }

    // datasource.createBatch() opens a bulk-insert batch against the
    // schema.table, targeting the given column list.
    var batch = datasource.createBatch(
        ds,
        `${schema}.${tableName}`,
        dimensionArray
    );

    // cube.slice() returns a cursor over every cell matching sliceArray
    // (here: every member of every dimension, i.e. the whole cube).
    let slice = cube.slice(cube_name, sliceArray);
    console.log("slice", cube_name, sliceArray);

    for (let elms of slice) {
        elms = [...elms];

        // The last element is the cell's value. Cast to string and clip
        // it to the column's VARCHAR length so long numbers/text can't
        // overflow the column.
        let value = elms.pop() + "";
        value = value.substring(0, VARCHAR_LENGTH);
        elms.push(value);

        batch.insert(elms);

        if (script.IsCancelled()) {
            return;
        }
    }

    batch.flush(); // commit the batch to the datasource
}


function data(record) {
}


function end() {
    script.log('process execution finished.');
}

/**
 * createTable()
 * Creates `schema.tableName` with an auto-increment primary key plus one
 * VARCHAR(116) column per entry in `fields` (dimension names + "value").
 * No-ops if the table already exists.
 */
function createTable(ds, schema, tableName, fields = {}) {
    const defaultDataType = `VARCHAR(${VARCHAR_LENGTH})`; // 🔧 CHANGE: derives from the shared constant

    let columnNames = []; // 🔧 CHANGE: renamed from `array` for readability

    if (tableExists(ds, schema, tableName)) {
        console.log("Table Already Exists");
        return;
    }

    let primaryKey = `\`${tableName}_id\` `;
    let createStatement = `CREATE TABLE \`${schema}\`.\`${tableName}\` (${primaryKey} int(11) NOT NULL AUTO_INCREMENT,`;

    for (let property in fields) {
        columnNames.push(property);
        createStatement += `\`${property}\` ${defaultDataType} DEFAULT NULL, `;
    }

    if (columnNames.length > 0) {
        createStatement += ` PRIMARY KEY (${primaryKey})) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci`;
        console.log(`Creating Table \`${schema}\`.\`${tableName}\``, createStatement);
        datasource.update(ds, createStatement);
    } else {
        console.log("Failed to create table");
    }
}

/**
 * tableExists()
 * Checks information_schema.tables for the given schema.table.
 */
function tableExists(ds, schema, tableName) {
    const sql = `SELECT * FROM information_schema.tables WHERE table_schema = ? AND table_name = ?`;
    const result = JSON.parse(datasource.select(ds, sql, [schema, tableName]));
    return result.length > 0;
}

/**
 * schemaExists()
 * Checks information_schema.SCHEMATA for the given schema name.
 */
function schemaExists(ds, schema) {
    const sql = `SELECT * FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = ?`;
    const result = JSON.parse(datasource.select(ds, sql, [schema]));
    return result.length > 0;
}