Utility.Export.Cube to Table

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

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;
}