Utility.Export.Cube to CSV

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

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 -> CSV Export
 * -----------------------------------------
 * Purpose:
 *   Prompts for a cube name and an output CSV filename, reads that
 *   cube's full dimension structure to build a header row (one column
 *   per dimension + a "value" column), then streams every cell in the
 *   cube out to a CSV file on the datasource.
 *
 */

// --- Config -------------------------------------------------------------
var delim = ","   // Field delimiter used when writing the CSV

/**
 * pre()
 * Runs once before the process executes. Registers the two prompts
 * MODLR asks 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", "");

    // Registers a text prompt named "csv name" — its answer becomes the
    // global `csv_name` variable, used as the output filename (without
    // the .csv extension, which is appended in begin()).
    script.prompt("csv name", "csv_name", "example_name");
}

/**
 * begin()
 * Runs once at the start of the process. This is where the whole
 * cube -> CSV export happens:
 *   1. Read the cube's dimension list to build the header row.
 *   2. Open the output CSV file and write the header.
 *   3. Stream every cell in the cube out as a CSV row.
 */
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 = [];   // header/column names for the CSV, in order
    let sliceArray = [];       // one "" per dimension = "give me every member" for cube.slice()

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

        dimensionArray.push(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");

    // datasource.csv() opens/creates a CSV file on the datasource for writing.
    let csv = datasource.csv(`${csv_name}.csv`);
    csv.setDelimeter(delim);

    // First write is the header row (dimension names + "value").
    csv.write(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. Popped and pushed back
        // as-is here — unlike the MySQL export variant of this script,
        // there's no fixed column width to clip against for a CSV file,
        // so the value is written out unchanged.
        let value = elms.pop();
        elms.push(value);

        csv.write(elms);

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

/**
 * data(record)
 * Runs once per record on a second pass. Not used by this script —
 * the export is done entirely in begin() in a single pass.
 */
function data(record) {
}

/**
 * end()
 * Runs once at the end of the process.
 */
function end() {
    script.log('process execution finished.');
}